Using an Odata API when a Foreign Key relationship is null in the database, a SqlNullValueException:-CodePudding

I am trying to create a relationship between two entities and found that when the foreign key is null in the database, EF Core throws a SqlNullValueException: Data is Null exception.

Here is the primary entity (Person table):

[Key]
public int PersonId { get; set; }
public int? StaffId { get; set; }

public virtual StaffView? StaffView { get; set; }

And this is the related entity (Staff table):

[Key]
public int StaffId { get; set; }
public int? PersonId { get; set; }
public virtual PersonView? PersonView { get; set; }

Here is my DbContext class:

public virtual DbSet<PersonView>? PersonViews { get; set; } = null!;
public virtual DbSet<StaffView> StaffViews { get; set; } = null!;

modelBuilder.Entity<PersonView>(entity =>
{
    entity.ToTable("Persons", "Person");
    entity.Property(e => e.PersonId).HasColumnName("Person_ID").ValueGeneratedOnAdd();
    entity.HasKey(e => e.PersonId);

    entity.Property(e => e.StaffId).HasColumnName("Staff_ID");

    entity.HasOne(a => a.StaffView)
        .WithOne(b => b.PersonView)
        .HasPrincipalKey<PersonView>(b => b.StaffId)
        .HasForeignKey<StaffView>(b => b.StaffId)
        .IsRequired(false);
});

modelBuilder.Entity<StaffView>(entity =>
{
    entity.ToTable("Staff", "Person");
    entity.Property(e => e.StaffId).HasColumnName("Staff_ID").ValueGeneratedOnAdd();

    entity.HasKey(e => e.StaffId);
    entity.Property(e => e.PersonId).HasColumnName("Person_ID");

    entity.HasOne(a => a.PersonView)
        .WithOne(b => b.StaffView)
        .HasPrincipalKey<PersonView>(b => b.StaffId)
        .HasForeignKey<StaffView>(b => b.StaffId)
        .IsRequired(false);
});

EDM entry (in Program.cs):

builder.EntitySet<PersonView>("OViewPersons");

Controller:

public class OViewPersonsController : ODataController
{
    private readonly ViewContext? _context;
    
    public OViewPersonsController(ViewContext context)
    {
        _context = context;
    }

    [HttpGet]
    [EnableQuery(MaxExpansionDepth = 6)]
    public ActionResult<IQueryable<PersonView>> Get()
    {
        try
        {
            IQueryable<PersonView> queryResults = _context.PersonViews
                                                          .Include(sta => sta.StaffView);
            return Ok(queryResults);
        }
        catch (Exception e)
        {
            return StatusCode(StatusCodes.Status500InternalServerError, e.Message);
        }
    }
}

Database schema:

[Person].[Persons]
    [Person_ID] [int] IDENTITY(1,1) NOT NULL
    [Staff_ID] [int] NULL

[Person].[Staff]
    [Staff_ID] [int] IDENTITY(1,1) NOT NULL
    [Person_ID] [int] NULL

When the Staff ID in the Persons table is Null:

Person_Id   Staff_Id
--------------------
397748      NULL

The following exception is thrown:

Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'GatewayApi.DbContexts.GatewayApiContext'. System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

at Microsoft.Data.SqlClient.SqlBuffer.get_Int32()
at lambda_method321(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()

Software used and its versions:

VS 2022 - Version 17.3.6
Microsoft.EntityFrameworkCore {6.0.10} GatewayApi
Microsoft.EntityFrameworkCore.SqlServer {6.0.10} GatewayApi
Swashbuckle.AspNetCore {6.4.0} GatewayApi
Microsoft.AspNetCore.Mvc.NewtonsoftJson {6.0.10} GatewayApi
System.Configuration.ConfigurationManager {6.0.1} GatewayApi
Microsoft.EntityFrameworkCore.Tools {6.0.10} GatewayApi
Twilio {5.81.0} GatewayApi
Microsoft.VisualStudio.Web.CodeGeneration.Design {6.0.10} GatewayApi
Microsoft.AspNetCore.OData {8.0.11} GatewayApi

I need a result set that does not error and allows nulls in foreign key fields.

I have tried a number of solutions but nothing seems to work

I added "Foreign Key" annotations in the model classes:

[ForeignKey("StaffView")]
public int? StaffId { get; set; }

and

[ForeignKey("StaffId")]
public virtual StaffView? StaffView { get; set; }

I tried with and without the .IsRequired(false) in the DbContext.

I tried various combinations of identifying all of the different parameters as null or not null

public int? StaffId { get; set; }

and

public int StaffId { get; set; }

I tried disabling Nullable in the Project Build configuration.

I tried changing the Staff_ID in the Person table from nullable to not nullable.

I have searched the net and tried a bunch of other suggestions but can not remember them all.

I have searched far and wide and believe it is related to the Int32 foreign key being null in the primary entity. I found a lot of answers that are related to annotating or marking the parameters as null but haven't been successful in finding a solution related to nulls in the foreign key database field.

I have tried to be a detailed as possible but if I missed something, please let me know.

I have been trying to figure this out for a few days now and I just can not seem to get it to work as I expect. Any and all help is greatly appreciated.

Thanks a lot in advance!

CodePudding user response：

If we think in terms of real world object hierarchies, a Staff member is a Person, but not all people are Staff members, so 1 Person entity to 0 or 1 Staff makes sense. In EF though, the reciprocal of that relationship is
actually 1 Staff : many Persons. If you need to deliberately constrain the many Persons to only 1, then that is easier to achieve by implementing a unique constraint on the Staff table.

This is not the only way to achieve this type of relationship, but in EF it is a lot easier both in terms of configuration and longer term management.

We don't even need to use fluent configuration for this simple setup, we can use attribute notation:

[Table("Persons", Schema = "Person")]
public class PersonView
{
    [Key]
    public int PersonId { get; set; }
    public virtual ICollection<StaffView> StaffViews { get; set; } 
        = new HashSet<StaffView>();
}

[Table("Staff", Schema = "Person")]
public class StaffView
{
    [Key]  
    public int StaffId { get; set; }
    public int? PersonId { get; set; }
    [ForeignKey("PersonId")]
    public virtual PersonView? PersonView { get; set; }
}

This still gives you navigation property access to the Staff from the Person. This type of relationship clearly defines Person as the principal and `Staff' as the dependant.

If your relationship needs to be 1:0-1 then EF handles this through inheritance, re-using the PersonId as the primary key of the StaffView. To do this you are effectively implementing Table-Per-Type (TPT) hierarchy, which is problematic to implement in EF Core. It can be done, my projects that have upgraded from EF6 are still working well with TPT but in most cases it introduces a level of technical debt that you can do without.