efcore: Result of left joining a nullable nominal type should be null (i.e. no instance) and not an instance with all null property values

when Blog does not have a Post, following query does not work in 5.0.0-preview.8.* or 6.0.0-* nightly. but works in 5.0.0-preview.7.*


 public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }

        [NotMapped]
        public Post Post { get; set; }

        public List<Post> Posts { get; set; } = new List<Post>();
    }

    public class Post
    {
        public int PostId { get; set; }

        public string Title { get; set; }

        public string Content { get; set; }

        public int BlogId { get; set; }

        public Blog Blog { get; set; }
    }

// this IQueryable would come from other API.

 var dbPosts = from p in db.Posts
                          // select p;
                         select new Post
                         {
                             PostId = p.PostId,
                             BlogId = p.BlogId,
                             Content = p.Content
                         };

 var query = from blog in db.Blogs
                    join post in dbPosts on blog.BlogId equals post.BlogId into posts
                    from xpost in posts.DefaultIfEmpty()
                    select new Blog
                    {
                         Url = blog.Url,
                         Post = xpost
                   };

Steps to reproduce

I have a repo to reproduce the bug.

https://github.com/skclusive/EFLeftJoinBug

Unhandled exception. System.InvalidOperationException: Nullable object must have a value.
   at lambda_method17(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()

Further technical details

EF Core version: Database provider: (e.g. Microsoft.EntityFrameworkCore.Sqlite) Target framework: (e.g. .NET Core 5.0) Operating system: IDE: (e.g. Visual Studio Code)

About this issue

  • Original URL
  • State: open
  • Created 6 years ago
  • Reactions: 57
  • Comments: 42 (13 by maintainers)

Commits related to this issue

Most upvoted comments

We’ve just hit this after (attempting) to upgrade from 3.1 to 5.0. From what I can tell, this is a significant behavioural change.

Suggestion: Mention this is Breaking changes in EF Core 5.0?

Googling stack traces isn’t much fun 😃

This is definitely a breaking change

I can’t believe that after years, this error is still present. I have tons of LINQ queries that work correctly in .NET Framework 3.1, and I’m not sure how they might behave after an update. Has anyone found any workarounds? Is there at least one way to obtain a compile-time error instead of a runtime error?

It’s too bad LINQ expressions don’t support the null conditional operator ?. Then we could indicate a possible null in the navigation to the EF query and quiet the VS Analyzers that complain about a possible null dereference (CS8602).

.Select(x => new {
    x.NavProperty1?.NavProperty2?.NonNullValueTypeProperty,
})

This is very inconvenient, and has been ‘punted’ for 2 versions now…

@Mijalski This issue is in the 6.0 milestone, which means we plan to fix it for EF Core 6.0.

https://stackoverflow.com/a/65207398/1181624

You can also do a cast to a nullable type to make this issue go away. I posted that stack overflow answer before I found this post.

select new {
   ...
  optionalValue = (int?)leftJoinedType.someInt
}

@skclusive - Your query was giving incorrect results in 3.1, The bug fix for #20633 stopped generating incorrect results. In your case it throws exception because the query translation couldn’t figure out a way to generate right results. Your query working in LINQ is irrelevant as it worked in 3.1 also and EF Core generated results different than LINQ.

                var dbPosts = from p in db.Posts
                                  // select p;
                              select new Post
                              {
                                  PostId = p.PostId,
                                  BlogId = p.BlogId,
                                  Content = p.Content
                              };

                var query = from blog in db.Blogs
                            join post in dbPosts on blog.BlogId equals post.BlogId into posts
                            from xpost in posts.DefaultIfEmpty()
                            select new Blog
                            {
                                Url = blog.Url,
                                Post = xpost.BlogId == null ? null : xpost
                            };

Tested that above work-around gives correct result on 5.0 rc2 nightly build.

Really hoping for a fix on this one…

Please vote it

Really hoping for a fix on this one…

We just upgraded to .Net 6 and this is an embarrassing complete disaster. There’s no easy way to determine where this bug occurs other than scouring all 300+ LINQ expressions to see if we’re using a leftjoin, and selecting on the result. This isn’t even listed as a breaking change on the update page. The error message was completely useless and resulted in around 2 hours of debugging because I couldn’t figure out what was going wrong (why would I expect a simple SELECT to be the cause of the error?). Furthermore, this behaviour is completely inconsistent even within the LINQ query itself, and doesn’t follow expected C# paradigms. Consider the following query:

var userData = await (
    from u in db.user
    
    // left join
    join s in db.optional_user_settings_row
    on u.user_id equals s.user_id
    into leftjoin_u from u in leftjoin_u.DefaultIfEmpty()

    // s.suspended Doesn't error ???
    where u.user_id = UserIDToSearchFor && !s.suspended

    select new UserDataResult
    {
        name = u.display_name,
        // s.colourblind_enabled does error ???
        colourblind = s.colourblind_enabled,
    }
).ToArrayAsync();

This query errors because s is null, so it can’t cast the resulting bool? into the expected not-null bool. However, why doesn’t s.suspended in the where clause cause an error? Logically, the actual null value is s, but this seems to be behaving against expectations, as though we’ve just got a completely blank Object instead of a row, and everything inside of it is evaluating to null, but only sometimes. Not only that, but it’s not even evaluating to default as it should be based on the DefaultIfEmpty() name. This makes no sense at all, and honestly we’re seriously considering completely abandoning using Entity Framework LINQ because this isn’t the first time an update has broken queries across our application with no warning or logical way to fix it. I ended up coming to the same workaround conclusions of casting to (bool?) or using == true but again this is crazy behaviour to any user and causes confusion.

I know Microsoft doesn’t take suggestions, and this is a pipe dream, but the following syntax is what I would actually expect LINQ to work like.

var userData = await (
    from u in db.user
    
    // left join - new syntax
    // `s` is highlighted as POSSIBLY NULL
    // and will display a warning in Visual Studio if accessed directly below
    left join s in db.optional_user_settings_row
    on u.user_id equals s.user_id

    // Use null propagating operator since `s` is the actual null value
    // and we can EXPLICITLY HANDLE IT instead of automatically doing weird stuff
    where u.user_id = UserIDToSearchFor && (s?.suspended ?? false)

    select new UserDataResult
    {
        display_name = u.display_name,
        // Use coalescing to decide what the default should be
        colourblind_enabled = s?.colourblind_enabled ?? false,
    }
).ToArrayAsync();

@hhhuut - There is no one answer to the question. Essentially, when you do a left join, right side can have null values so accessing any property over that will give you back null (EF Core will make property access null safe). But in C# there is no concept of left join or null-safe access. So in C# the property access will take CLR type of the property which can/cannot accommodate null value. e.g. entity.Property where entity is coming from right side of left join and Property is of type int will return int value. If entity turns out to be null then it throws null-ref exception. Since EF Core makes the access null safe it is same as writing entity == null ? (int?)null : entity.Property which will make the return value of type int? So when actual null value is encountered then it cannot be put into a int value throwing above exception. This only happens at runtime, when you actually encounter a null value.

So, as a user, you should carefully evaluate queries and see if you are expecting a null value appearing in left join and the property-accesses are able to accommodate that null value by being nullable type. In such cases you are required to put nullable type cast. It is not a “work-around”, it is required to materialize the results from the data you have. Though if your query will never get null from left join (be careful as data can change over the type in an app), or you are not accessing a property on an entity which can be nullable due to left join then you shouldn’t need it. The same exception can also arise when database contains null but client side property cannot take null values (even without left-joins) due to mismatch in model. There is also a possibility that there is a bug in EF Core somewhere. So if you have a query which has result types correct based on expected data and seeing this exception please file a new issue.

I want post again this issue hoping that at least it will be solved on next releases

EF Core Bug Report: DefaultIfEmpty Not Properly Handling Null Values in Left Joins

Description

When using LINQ’s DefaultIfEmpty() method with EF Core to create LEFT JOIN queries, the expected behavior is that when no matching records are found in the right table, the result should be NULL. However, the current implementation doesn’t correctly handle this scenario, requiring manual null checks against the foreign key instead of relying on the expected behavior of DefaultIfEmpty.

Expected Behavior

When using the standard LINQ pattern for a left join with DefaultIfEmpty(), null category should be properly returned for addresses that don’t have a matching category:

Example: var addressesAnon = (from address in dbContext.Set<AMOS_ADDRESS>().AsNoTracking() join c in dbContext.Categories on address.ADDRESSCATEGORYID equals c.Id into categoryGroup from category in categoryGroup.DefaultIfEmpty() select new { Id = address.ADDRESSID, Category = category // Should be null when no match exists }).ToArray();

Actual Behavior

The Category property does not correctly resolve to null when there is no matching category. The query does not properly translate the DefaultIfEmpty() semantics to SQL’s LEFT JOIN behavior.

Workaround

Currently, the only reliable solution is to explicitly check the foreign key for null is:

var addressesAnon = (from address in dbContext.Set<AMOS_ADDRESS>().AsNoTracking() join c in dbContext.Categories on address.ADDRESSCATEGORYID equals c.Id into categoryGroup from category in categoryGroup.DefaultIfEmpty() select new { Id = address.ADDRESSID, Category = address.ADDRESSCATEGORYID == null ? null : category }).ToArray();

Reproduction Steps

  1. Create two entities with a one-to-many relationship (e.g., Address and Category)
  2. Add some addresses without corresponding categories (NULL foreign key)
  3. Execute a LINQ query using the DefaultIfEmpty pattern as shown in the example
  4. Observe that null handling doesn’t work as expected

Environment

  • EF Core Version: EF Core 9.0.4
  • Database Provider: SQL Server/In Memory
  • .NET Version: NET 8
  • Operating System: Windows

Additional Information

This issue impacts a core LINQ operation that’s commonly used for LEFT JOIN scenarios, forcing developers to use additional check for what should be standard functionality. Please note that even if I use the workaround I have issue when I do the following:

var addressesAnon = (from address in dbContext.Set<AMOS_ADDRESS>().AsNoTracking() join c in dbContext.Categories on address.ADDRESSCATEGORYID equals c.Id into categoryGroup from category in categoryGroup.DefaultIfEmpty() select new { Id = address.ADDRESSID, Category = address.ADDRESSCATEGORYID == null ? null : category });

var addressWithCatNull = addressAnon.Where(a => a.Category == null)

Error: System.InvalidOperationException HResult=0x80131509 Message=The LINQ expression ‘DbSet<AMOS_ADDRESS>() .LeftJoin( inner: DbSet<AMOS_ADDRESSCATEGORY>(), outerKeySelector: a => (object)a.ADDRESSCATEGORYID, innerKeySelector: a0 => (object)(decimal?)a0.ADDRESSCATEGORYID, resultSelector: (a, a0) => new TransparentIdentifier<AMOS_ADDRESS, AMOS_ADDRESSCATEGORY>( Outer = a, Inner = a0 )) .Where(ti => !(ti.Outer.ADDRESSCATEGORYID != null) || new Category{ Id = ti.Inner.ADDRESSCATEGORYID, Name = ti.Inner.DESCRIPTION } == null)’ could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to ‘AsEnumerable’, ‘AsAsyncEnumerable’, ‘ToList’, or ‘ToListAsync’. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. Source=Microsoft.EntityFrameworkCore StackTrace: at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_01.<ExecuteCore>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetEnumerator() at System.Collections.Generic.List1…ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at ReproducibleNullObject.Tests.RunInMemory() in C:\Users\luigi.esposito\Desktop\Repos\ReproducibleNullObject\ReproducibleNullObject\RunInMemory.cs:line 145 at Program.

$(String[] args) in C:\Users\luigi.esposito\Desktop\Repos\ReproducibleNullObject\ReproducibleNullObject\Program.cs:line 3 For workarounding it I need to rewrite query as follows: var addressWithCatNull = addressAnon.Where(a => a.Category.Id == null) For which I have warning CS0472 (https://learn.microsoft.com/en-us/dotnet/csharp/misc/cs0472?f1url=%3FappId%3Droslyn%26k%3Dk(CS0472))

Great sum-up, we’re facing something similar and would be great if this could be addressed

I want post again this issue hoping that at least it will be solved on next releases

EF Core Bug Report: DefaultIfEmpty Not Properly Handling Null Values in Left Joins

Description

When using LINQ’s DefaultIfEmpty() method with EF Core to create LEFT JOIN queries, the expected behavior is that when no matching records are found in the right table, the result should be NULL. However, the current implementation doesn’t correctly handle this scenario, requiring manual null checks against the foreign key instead of relying on the expected behavior of DefaultIfEmpty.

Expected Behavior

When using the standard LINQ pattern for a left join with DefaultIfEmpty(), null category should be properly returned for addresses that don’t have a matching category:

Example: var addressesAnon = (from address in dbContext.Set<AMOS_ADDRESS>().AsNoTracking() join c in dbContext.Categories on address.ADDRESSCATEGORYID equals c.Id into categoryGroup from category in categoryGroup.DefaultIfEmpty() select new { Id = address.ADDRESSID, Category = category // Should be null when no match exists }).ToArray();

Actual Behavior

The Category property does not correctly resolve to null when there is no matching category. The query does not properly translate the DefaultIfEmpty() semantics to SQL’s LEFT JOIN behavior.

Workaround

Currently, the only reliable solution is to explicitly check the foreign key for null is:

var addressesAnon = (from address in dbContext.Set<AMOS_ADDRESS>().AsNoTracking() join c in dbContext.Categories on address.ADDRESSCATEGORYID equals c.Id into categoryGroup from category in categoryGroup.DefaultIfEmpty() select new { Id = address.ADDRESSID, Category = address.ADDRESSCATEGORYID == null ? null : category }).ToArray();

Reproduction Steps

  1. Create two entities with a one-to-many relationship (e.g., Address and Category)
  2. Add some addresses without corresponding categories (NULL foreign key)
  3. Execute a LINQ query using the DefaultIfEmpty pattern as shown in the example
  4. Observe that null handling doesn’t work as expected

Environment

  • EF Core Version: EF Core 9.0.4
  • Database Provider: SQL Server/In Memory
  • .NET Version: NET 8
  • Operating System: Windows

Additional Information

This issue impacts a core LINQ operation that’s commonly used for LEFT JOIN scenarios, forcing developers to use additional check for what should be standard functionality. Please note that even if I use the workaround I have issue when I do the following:

var addressesAnon = (from address in dbContext.Set<AMOS_ADDRESS>().AsNoTracking() join c in dbContext.Categories on address.ADDRESSCATEGORYID equals c.Id into categoryGroup from category in categoryGroup.DefaultIfEmpty() select new { Id = address.ADDRESSID, Category = address.ADDRESSCATEGORYID == null ? null : category });

var addressWithCatNull = addressAnon.Where(a => a.Category == null)

Error: System.InvalidOperationException HResult=0x80131509 Message=The LINQ expression ‘DbSet<AMOS_ADDRESS>() .LeftJoin( inner: DbSet<AMOS_ADDRESSCATEGORY>(), outerKeySelector: a => (object)a.ADDRESSCATEGORYID, innerKeySelector: a0 => (object)(decimal?)a0.ADDRESSCATEGORYID, resultSelector: (a, a0) => new TransparentIdentifier<AMOS_ADDRESS, AMOS_ADDRESSCATEGORY>( Outer = a, Inner = a0 )) .Where(ti => !(ti.Outer.ADDRESSCATEGORYID != null) || new Category{ Id = ti.Inner.ADDRESSCATEGORYID, Name = ti.Inner.DESCRIPTION } == null)’ could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to ‘AsEnumerable’, ‘AsAsyncEnumerable’, ‘ToList’, or ‘ToListAsync’. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. Source=Microsoft.EntityFrameworkCore StackTrace: at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_01.<ExecuteCore>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetEnumerator() at System.Collections.Generic.List1…ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at ReproducibleNullObject.Tests.RunInMemory() in C:\Users\luigi.esposito\Desktop\Repos\ReproducibleNullObject\ReproducibleNullObject\RunInMemory.cs:line 145 at Program.<Main>$(String[] args) in C:\Users\luigi.esposito\Desktop\Repos\ReproducibleNullObject\ReproducibleNullObject\Program.cs:line 3

For workarounding it I need to rewrite query as follows: var addressWithCatNull = addressAnon.Where(a => a.Category.Id == null) For which I have warning CS0472 (https://learn.microsoft.com/en-us/dotnet/csharp/misc/cs0472?f1url=%3FappId%3Droslyn%26k%3Dk(CS0472))

Hi, I believe that this is duplicate of same issue I already reported [#30915] (still present on EF 8.0.1) This is definetively a blocker issue for moving from EF to EF Core. I have many LINQ query that raise same error.

Regards Luigi

Same problem in EF 7

@StrangeWill - This is what will happen in EF Core 6.0

  • If the selector does conditional expression before construction of anonymous type, then we will not create instance of anonymous type
  • If there is queryable of anonymous type which has DefaultIfEmpty applied over it then default value should be null rather than anonymous type with default property values (more complicated case of first one only)
  • The selector you wrote above, if you have anonymous type construction without null check and if it ends up with null property value where expected is non-null value then it is going to throw the same error. I have explained in https://github.com/dotnet/efcore/issues/22517#issuecomment-758977488 things to consider when doing left join and why behavior is this way.
  • None of above are going to be same behavior as EF Core 3.1 as EF Core 3.1 behavior was also wrong. It put wrong value rather than throwing exception when incorrect query results encountered.

Finally, this cannot be backported to 5.0 due to complexity of the fix.

Issues faced: When generating left join we need to add additional null check in selector so that we can generate correct default type if sequence is empty and not try to materialize result.

  • This causes a complex selector which when used inside anything other than select, fails to translate since we don’t bind to column eventually. (it is ternary with client side types). On flip side non-select shouldn’t need check either since column would be null anyway.
  • We cannot do when translating LeftJoin easily because projection is lifted after LeftJoin so we don’t have enough data in translation pipeline in that sense.
  • For unexpanded navigations we try to add check which is not necessary. Requires #20291

Due to unintended side effect of breaking working queries due to having a complex expression for entity now, we should postpone this.

Confirmed this works in 3.1, but fails in latest daily, on SQL Server and SQLite.

This issue is a significant blocker for our transition from EF Core 3.1 to later versions. It fundamentally changes the expected behavior of left joins involving nullable types, which would necessitate extensive modifications in our codebase to introduce null checks to avoid these exceptions. This isn’t just a minor inconvenience but a substantial overhaul of existing, stable code.

A configuration option to toggle this behavior would be highly beneficial, offering a compromise. This would allow teams who rely on the pre-5.0 behavior to upgrade without rewriting significant portions of their code, while others who prefer the new behavior could opt-in. Without such an option, we’re effectively stuck, unable to leverage the advancements in newer EF Core versions.

It’s crucial to address this issue to facilitate smoother transitions and maintain backward compatibility, especially for complex, enterprise-level applications.

Work-around to original issue

 var query = from blog in db.Blogs
                    join post in dbPosts on blog.BlogId equals post.BlogId into posts
                    from xpost in posts.DefaultIfEmpty()
                    select new Blog
                    {
                         Url = blog.Url,
                         Post = xpost == null ? null : xpost
                   };