EF not grabbing an object when its navigation property is null

user_11755942 0
Viewed 1384

My issue is - if a navigation property is 'null' in the DB, my query won't return the main object I'm querying for.

For clarity, my issue is NOT the navigation property inside .Include() being returned null. My issue is the property IS expected to be null, and when it is, Market is not being returned.

Here's an example of what I mean:

My code:

            markets = await _context.Markets
                .Include(x => x.Agency)
                .Include(x => x.Location)?.ThenInclude(x => x.State)?
                .Include(x => x.Location).ThenInclude(x => x.Country)
                .Where(x => x.Deleted == false
                && x.Agency.Deleted == false).ToListAsync();

I have some Markets, who have a navigation property Location, which in turn has a navigation property State. For some Location, State is null. The foreign key to State defined in the Location model is a long?.

However, for some reason, my Markets with null States' aren't being returned in the resulting list, entities which have all those fields included in the .Include() statements, and are not null, are being returned.

I originally had my code without the ? after my includes... like:

.Include(x => x.Location).ThenInclude(x => x.State) and that did not work. I added the nullable thinking it may help, though it did not.

Does anyone have any tips here? When running this statement, I get this exception: .SqlNullValueException: Data is Null. This method or property cannot be called on Null values. However, the exception does not break the flow, and I get objects returned that don't have a null State.

Any and all tips would be appreciated!

EDIT:

It's important to note, the code works perfectly fine when all my navigation properties are NOT null. It's when State is null (the only nullable navigation property) that this issue shows up.

Models:

public class Market
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long MarketId { get; set; }
        
        [ForeignKey("Location")]
        public long HeadquartersId { get; set; }
        
        [Required]
        public virtual Location Location { get; set; }
}

public class Location
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long LocationId { get; set; }
        
        [ForeignKey("State")]
        public long? StateId { get; set; }

        [ForeignKey("Country")]
        public long CountryId { get; set; }

        [Required]
        public virtual State State { get; set; }

        [Required]
        public virtual Country Country { get; set; }
    }
entity-framework-core linq c#
2 Answers

It's a bit of a longshot, but see if you can manually configure the relationships in your DBContext's OnModelCreating.

Example:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Market>(a =>
    {
        a.HasOne(e => e.Location).WithMany().HasForeignKey(e => e.HeadquartersId).IsRequired(false);                
    });

    builder.Entity<Location>(a =>
    {
        a.HasOne(e => e.State).WithMany().HasForeignKey(e => e.StateId).IsRequired(false);                
        a.HasOne(e => e.Country).WithMany().HasForeignKey(e => e.CountryId).IsRequired(false);                
    });

}
user_12511270

Ultimately, the issue was the [Required] annotation on my State navigation property in my Location model.

Unfortunately, this is one of many examples of conflicts between Entity Framework and Blazor using the same data annotations.

In my example, State is required because it's needed for form validation in Blazor. However, in my database, StateId is actually a nullable foreign key, because it's not necessarily required in the backend.

I've come up with two workarounds, feel free to add addtional ones if you know of a better solution.

  1. Create separate frontend models for Blazor, and backend models for EF. I know, ugly duplicate code. However, This can keep concerns separated, and avoid a mess of "required here, but not here" type issues.

  2. In my situation, I could also have a separate query, checking my Country first. if Country is a certain value, I won't .Include(x => x.State). This is more specific, but may be necessary for some in similar situations where there is already a codebase that cannot be changed so much.

user_117559420
Related