NullReferenceException when materializing a Query type on the right side of a left join

[dchristensen]

When executing a query where a Query Type is being used on the right side of a left outer join, when there is no row that matches the left side EF Core attempts to construct an object and map NULL to the properties, which fails when the Query Type model has non-nullable types. Executing the same query with an Entity Type correctly produces a null value instead of attempting to construct the object.

Exception message: System.InvalidOperationException
  HResult=0x80131509
  Message=An exception occurred while reading a database value for property 'PostView.BlogId'. The expected type was 'System.Int32' but the actual value was null.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.TryReadValue[TValue](ValueBuffer& valueBuffer, Int32 index, IPropertyBase property)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.UnbufferedEntityShaper`1.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.UnbufferedOffsetEntityShaper`1.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.CompositeShaper.TypedCompositeShaper`5.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EfCoreTest.Program.Main(String[] args)

Inner Exception 1:
NullReferenceException: Object reference not set to an instance of an object.

Steps to reproduce

1. Create a Query type as part of an EF data model
2. Execute a query where the Query type ends up on the right side of a left outer join
3. Insert data so that there are rows on the left side of the query that have no matching rows on the right side.
4. Execute a query with a left join.

In this following sample I would expect data[0].Post to be null instead of throwing the exception.

    public class DatabaseContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        public DbQuery<PostView> PostViews { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Query<PostView>().ToView("Posts");
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EfCoreTest;Trusted_Connection=True;");
        }
    }
    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
        public int Rating { get; set; }
        public List<Post> Posts { get; set; }
    }
    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; }
    }
    public class PostView
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new DatabaseContext())
            {
                var blogs = new List<Blog>
                {
                    new Blog {Url = "1"},
                    new Blog {Url = "2"},
                    new Blog {Url = "3"}
                };
                ctx.Blogs.AddRange(blogs);

                var posts = new List<Post>
                {
                    new Post {Blog = blogs[1], Title = "2"}
                };
                ctx.Posts.AddRange(posts);

                ctx.SaveChanges();

                var data = (
                    from b in ctx.Blogs
                    join p in ctx.PostViews on b.BlogId equals p.BlogId into pj
//                    join p in ctx.Posts on b.BlogId equals p.BlogId into pj  // This produces correct results
                    from p in pj.DefaultIfEmpty()
                    select new
                    {
                        Blog = b,
                        Post = p
                    }).ToList();
            }
        }
    }

Further technical details

EF Core version: 2.1.3
Database Provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Operating system: Windows 10 17134
IDE: Visual Studio 2017 15.8

[ajcvickers]

@dchristensen In this line:

 modelBuilder.Query<PostView>().ToView("Posts");

is "Posts" the same table that the Post maps to, or is it a database view? If it is a view, how is it defined?

[dchristensen]

@ajcvickers In the example I've given it is just referencing the Posts table, for simplicity's sake, but the same behavior exists when I target the Query Type to an actual view.

[LeyuSisay]

@maumar
The same behavior exists when LEFT JOINing with a sub-query returning an anonymous object or typed instance.

var data = (
  from b in ctx.Blogs
  join p in ( from post in ctx.Posts
              join blog in ctx.Blogs on post.BlogId equals blog.PostId
              select new (){post.PostId, post.Title,  post.Content,  blog.BlogId}
            ) on b.BlogId equals p.BlogId into pj 
//    join p in ctx.PostViews on b.BlogId equals p.BlogId into pj   //This produces error
//    join p in ctx.Posts on b.BlogId equals p.BlogId into pj  // This produces correct results
  from p in pj.DefaultIfEmpty()
 select new   { Blog = b, Post = p }).ToList();

[smitpatel]

Now that query types are entity types
above mapping throws

Unhandled exception. System.InvalidOperationException: Cannot use table 'Posts' for entity type 'PostView' since it is being used for entity type 'Post' and there is no relationship between their primary keys.
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.ValidateSharedTableCompatibility(IReadOnlyList`1 mappedTypes, String tableName, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.ValidateSharedTableCompatibility(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.SqlServer.Internal.SqlServerModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.ValidatingConvention.ProcessModelFinalized(IConventionModelBuilder modelBuilder, IConventionContext`1 context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelFinalized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelFinalized(IConventionModelBuilder modelBuilder)

cc: @AndriySvyryd

[smitpatel]

If mapped to actual view, queries work correctly
Generated SQL for query 1

      SELECT [b].[BlogId], [b].[Rating], [b].[Url], [p].[BlogId], [p].[Content], [p].[PostId], [p].[Title]
      FROM [Blogs] AS [b]
      LEFT JOIN [PostView] AS [p] ON [b].[BlogId] = [p].[BlogId]

Generated SQL for 2nd case

      SELECT [b].[BlogId], [b].[Rating], [b].[Url], [t].[PostId], [t].[Title], [t].[Content], [t].[BlogId0] AS [BlogId]
      FROM [Blogs] AS [b]
      LEFT JOIN (
          SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title], [b0].[BlogId] AS [BlogId0], [b0].[Rating], [b0].[Url]
          FROM [Posts] AS [p]
          INNER JOIN [Blogs] AS [b0] ON [p].[BlogId] = [b0].[BlogId]
      ) AS [t] ON [b].[BlogId] = [t].[BlogId0]

[ajcvickers]

Split out mapping issue to #19257

[davidbrinton]

Forgive my ignorance, but after this fix has been pushed, how long should we expect before it's available in an official patch or release and how can I tell in which release it's included?

[ajcvickers]

@davidbrinton The milestone. In this case the milestone is 5.0.0. This will be updated to indicate the preview it is in when we cut that preview--most likely preview 7 at this point.