Efcore: 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:
```C#
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.TypedProjectionShaper3.Shape(QueryContext queryContext, ValueBuffer& valueBuffer) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.CompositeShaper.TypedCompositeShaper5.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper3.Shape(QueryContext queryContext, ValueBuffer& valueBuffer) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteTState,TResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable1 results, QueryContext queryContext, IList1 entityTrackingInfos, IList1 entityAccessors)+MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext() at System.Collections.Generic.LargeArrayBuilder1.AddRange(IEnumerable1 items) at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable1 source)
at System.Linq.Enumerable.ToArrayTSource
at ConsoleApp1.Program.Main(String[] args) in C:devreposEFCoreBugConsoleApp1Program.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.

```c#
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:
c# 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