LEFT JOIN with GROUP BY: System.InvalidOperationException: Nullable object must have a value.

[igaichenkov]

I'm joining a table (LEFT OUTER JOIN) with a subquery having a GROUP BY clause and returning an aggregation function result. If there are no matching rows in the right side table (NULLs are fetched), it fails with a InvalidOperationException: Nullable object must have a value.. Here is the query I'm having problems with:

from order in dbContext.Orders
join maxExpirationDate in
(
    from orderItem in dbContext.OrderItems
    group orderItem by orderItem.OrderId
    into itemsGroup
    select new {OrderId = itemsGroup.Key, MaxDate = itemsGroup.Max(item => item.ExpirationDate)}
) on order.Id equals maxExpirationDate.OrderId into joinedMaxDate
from maxExpirationDate in joinedMaxDate.DefaultIfEmpty()
select new { Order = order, maxExpirationDate };

Exception message: System.InvalidOperationException: Nullable object must have a value.
Stack trace: 
   at lambda_method(Closure , QueryContext , ValueBuffer )
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.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._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at ConsoleApp1.Program.Main(String[] args) in C:\dev\repos\EFCoreBug\ConsoleApp1\Program.cs:line 32

Steps to reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

public class Order
{
    public int Id { get; set; }

    public DateTime CreationDate { get; set; }
}

public class OrderItem
{
    public int Id { get; set; }

    public int OrderId { get; set; }

    public Order Order { get; set; }

    public DateTime ExpirationDate { get; set; }
}

public class OrdersDbContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderItem> OrderItems { get; set; }

    public OrdersDbContext(DbContextOptions<OrdersDbContext> options)
        : base(options)
    {
        
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        using (OrdersDbContext dbContext = CreateDbContext())
        {
            dbContext.Database.EnsureDeleted();
            dbContext.Database.EnsureCreated();
            dbContext.Orders.Add(new Order() { CreationDate = DateTime.Now });
            dbContext.SaveChanges();

            var query = 
                from order in dbContext.Orders
                join maxExpirationDate in
                (
                    from orderItem in dbContext.OrderItems
                    group orderItem by orderItem.OrderId
                    into itemsGroup
                    select new {OrderId = itemsGroup.Key, MaxDate = itemsGroup.Max(item => item.ExpirationDate)}
                ) on order.Id equals maxExpirationDate.OrderId into joinedMaxDate
                from maxExpirationDate in joinedMaxDate.DefaultIfEmpty()
                select new {Order = order, ExpDate = maxExpirationDate == null ? DateTime.MinValue : maxExpirationDate.MaxDate};

            query.ToArray();
        }
    }

    private static OrdersDbContext CreateDbContext()
    {
        var options = new DbContextOptionsBuilder<OrdersDbContext>()
            .UseSqlServer("Server=(local);Database=TestOrders;Trusted_Connection=True;")
            .Options;
        
        return new OrdersDbContext(options);
    }
}

Further technical details

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 1803
IDE: Visual Studio 2017 15.7.3

[igaichenkov]

The generated SQL query:

SELECT [order].[Id], [order].[CreationDate], [t].[OrderId], [t].[MaxDate]
FROM [Orders] AS [order]
LEFT JOIN (
    SELECT [orderItem].[OrderId], MAX([orderItem].[ExpirationDate]) AS [MaxDate]
    FROM [OrderItems] AS [orderItem]
    GROUP BY [orderItem].[OrderId]
) AS [t] ON [order].[Id] = [t].[OrderId]

[igaichenkov]

It starts working if I cast the anonymous class properties to nullable:

from order in dbContext.Orders
join maxExpirationDate in
(
    from orderItem in dbContext.OrderItems
    group orderItem by orderItem.OrderId
    into itemsGroup
    select new { OrderId = (int?)itemsGroup.Key, MaxExpDate = (DateTime?)itemsGroup.Max(item => item.ExpirationDate)}
) on order.Id equals maxExpirationDate.OrderId into joinedMaxDate
from maxExpirationDate in joinedMaxDate.DefaultIfEmpty()
select new { order.Id, maxExpirationDate };

[ajcvickers]

Note for triage: this doesn't fail in L2O.

[ajcvickers]

@maumar Can you investigate the root cause of this?

[fschlaef]

The same exception can be obtained without GroupBy : #12520

[smitpatel]

This works in 3.1