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");
});
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:
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!
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.
NOTE: In this solution we are only exploring the EF schema constraints, not OData controller or query logic was presented. Having the EF schema correct is only the first step, how you assign the related objects can still have an affect that we should explore
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]
[Column("Person_ID")]
public int PersonId { get; set; }
public virtual ICollection<StaffView> StaffViews { get; set; }
= new HashSet<StaffView>();
}
[Table("Staff", Schema = "Person")]
public class StaffView
{
[Key]
[Column("Staff_ID")]
public int StaffId { get; set; }
[Column("Person_ID")]
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.
See this Fiddle: https://dotnetfiddle.net/dZ3kvq
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 works well in .Net 6 but you do not assign the related entities, like what you are doing here.
Attribute notation saves a lot of time and allows you to manage key database schema metadata with your data models. Data annotations are processed by the EF ModuleBuilder using built in and customisable conventions into the configuration for the model.
You can choose to override the standard implementations using fluent configuration, if you do not use data annotations then the built in conventions will still pickup a lot of the configuration but it relies on the naming convention of your properties and keys to match these conventions. If you are not using Attribute notation then you should express any key structural elements like foreign keys, explicitly in your fluent configuration.
So if you do not want to use Attribute notation, the first-principals approach in fluent configuration looks like this:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<PersonView>(entity =>
{
entity.ToTable("Persons", "Person");
entity.Property(e => e.PersonId).HasColumnName("Person_ID").ValueGeneratedOnAdd();
entity.HasKey(e => e.PersonId);
entity.HasMany(person => person.StaffViews)
.WithOne(staff => staff.PersonView)
.HasPrincipalKey(person => person.PersonId)
.HasForeignKey(staff => staff.PersonId)
.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. Property/dotnetfiddle.net/hEjwqK
The fiddles there demonstrate assigning the relationship using the navigation property to link the objects:
// Using first, because there is only one of each entity in this DB
var person = context2.PersonViews.First();
var staff = context2.StaffViews.First();
staff. Person = person;
context2.SaveChanges();
However you could use the FK PersonId to do this directly without the object link:
// Using first, because there is only one of each entity in this DB
var person = context2.PersonViews.First();
var staff = context2.StaffViews.First();
staff.PersonId = person.PersonId;
context2.SaveChanges();
Fiddle: https://dotnetfiddle.net/Ohvv5q
How an OData context behaves with this will depend on how you have setup your OData Model and how you are querying the service to assign the keys. As long as you stick to the convention of representing the 1:0-1 relationship using the 1:Many structure in EF, the default OData conventional model builder should setup the required elements for this to work in OData.
Using fluent configuration over annotations is a one of those long running conflicts between developers, just like light mode vs dark mode.
My personal bias is to use Data annotations, and conventions, and to minimise any fluent configuration in a code base. It con significantly reduces the management overheads of changes to the schema over time but also makes the database metadata, relationships and indexes discoverable through the model. For larger schemas or larger teams this can significantly reduce negative time spent by new (and seasoned) developers who haven't fully memorised the data schema and don't want to traverse the fluent scripts to determine if indexes or relationships have been applied or what relationships might exist at all for an individual entity.
In many online tutorials fluent configuration is used because it is visually explicit to the reader and it overrides many previous configurations or annotations which makes it more likely that your config will be obeyed by the reader's code base when they cut'n'paste the content. Do not confuse this as best practice or the more current way to configure EF, both methodologies are current and relevant.