Efcore: Nullable object must have a value when left joining a nominal type

[skclusive]

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.*

```c#

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

<!--
What steps can we follow to reproduce the issue?

We ❤ code! Include a complete code listing or attach a simplified project


Got Exceptions? Include both the message and the stack trace

For `dotnet ef` and PMC, share the --verbose output
-->

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)

[ajcvickers]

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

[smitpatel]

@ajcvickers - What is the generated SQL? Can you post query plan?

[ajcvickers]

@smitpatel Here's the logs:

dbug: Microsoft.EntityFrameworkCore.Query[10111]
      Compiling query expression: 
      'DbSet<Blog>()
          .GroupJoin(
              inner: DbSet<Post>()
                  .Select(p => new Post{ 
                      PostId = p.PostId, 
                      BlogId = p.BlogId, 
                      Content = p.Content 
                  }
                  ), 
              outerKeySelector: blog => blog.BlogId, 
              innerKeySelector: post => post.BlogId, 
              resultSelector: (blog, posts) => new { 
                  blog = blog, 
                  posts = posts
               })
          .SelectMany(
              collectionSelector: <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.posts
                  .DefaultIfEmpty(), 
              resultSelector: (<>h__TransparentIdentifier0, xpost) => new Blog{ 
                  Url = <>h__TransparentIdentifier0.blog.Url, 
                  Post = xpost 
              }
          )'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      queryContext => new SingleQueryingEnumerable<Blog>(
          (RelationalQueryContext)queryContext, 
          RelationalCommandCache.SelectExpression(
              Projection Mapping:
                  Url -> 0
                  Post.PostId -> 1
                  Post.BlogId -> 2
                  Post.Content -> 3
              SELECT b.Url, p.PostId, p.BlogId, p.Content
              FROM Blogs AS b
              LEFT JOIN Posts AS p ON b.BlogId == p.BlogId), 
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, Blog>, 
          EFLeftJoinBug.BloggingContext, 
          False, 
          False
      )
dbug: Microsoft.EntityFrameworkCore.Database.Command[20103]
      Creating DbCommand for 'ExecuteReader'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20104]
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'Test' on server '(local)'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'Test' on server '(local)'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [b].[Url], [p].[PostId], [p].[BlogId], [p].[Content]
      FROM [Blogs] AS [b]
      LEFT JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [b].[Url], [p].[PostId], [p].[BlogId], [p].[Content]
      FROM [Blogs] AS [b]
      LEFT JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId]
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'EFLeftJoinBug.BloggingContext'.
      System.InvalidOperationException: Nullable object must have a value.
         at lambda_method(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
System.InvalidOperationException: Nullable object must have a value.
   at lambda_method(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
Unhandled exception. System.InvalidOperationException: Nullable object must have a value.
   at lambda_method(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at EFLeftJoinBug.Program.Main(String[] args) in /home/ajcvickers/AllTogetherNow/Daily/Daily.cs:line 43

[smitpatel]

SingleQueryResultCoordinator is not the error. We are probably trying to materialize null into something which is non-nullable which should have been skipped from materialization.

[smitpatel]

Note: This gave incorrect result in 3.1.x by initialize post with default values rather than returning null (the result of DefaultIfEmpty).

With #20633 fixed, it now throws error.

Right fix would be not to create instance if no rows matched.

[smitpatel]

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.

[smitpatel]

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
                   };

[skclusive]

i did try the following work-around before raising the bug. this workaround doesn't work :(

c# 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 };

[ajcvickers]

@skclusive Did you try the workaround using EF Core 5.0 RC1 or EF Core 3.1.x?

[skclusive]

@ajcvickers i tested with 6.0.0-* nightly. now tried with EF Core RC1 also. same issue.

[skclusive]

i guess this is critical issue.

if i use in memory objects. following does work with standard linq query. so i am wondering why #20633 considered bug.

```c#
var memoryBlogs = new List
{
new Blog
{
BlogId = 1,
Url = "http://blogs.msdn.com/adonet"
}
};

            var memoryPosts = from p in new List<Post>()
                        select new Post
                        {
                            PostId = p.PostId,
                            BlogId = p.BlogId,
                            Content = p.Content
                        };

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

when there is no posts, following does not throw error in the projection. so why throwing error when composed.


```c#

var posts = from p in db.Posts
                            select new Post
                            {
                                PostId = p.PostId, // we dont consider p null here
                                BlogId = p.BlogId,
                                Content = p.Content
                            };

                var post = posts.SingleOrDefault(); // query is success with null value. no error on projection.

                Console.WriteLine(post?.PostId);

i am composing IQueryable extensively in my code and this recent change breaks my framework. so please consider to address this issue.

[smitpatel]

@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.

```C#
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.

[skclusive]

@smitpatel got it. thanks.

the above mentioned workaround does work in nightly and rc-1.

only issue is this comparison produce warning xpost.BlogId == null (comparing int to null). Also will this be documented as i guess some might get this issue frequently.

being workaround will this be addressed in future releases?

you can close the issue if no further action involved.

[shadowfoxish]

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
}