Efcore: DefaultIfEmpty().Max() InvalidOperationException throws 'Sequence contains no elements.'

[anddrzejb]

After update to dotnet core 3.0 RC1 running following code

var value = context.Products.Where(t => t.CategoryId == catId).Select(t => t.Version).DefaultIfEmpty().Max();

throws

System.InvalidOperationException: Sequence contains no elements.
   at int lambda_method(Closure, QueryContext, DbDataReader, ResultContext, int[], ResultCoordinator)
   at bool Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor+QueryingEnumerable<T>+Enumerator.MoveNext()
   at TSource System.Linq.Enumerable.Single<TSource>(IEnumerable<TSource> source)
   at TResult Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute<TResult>(Expression query)
   at TResult Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute<TResult>(Expression expression)
   at TSource System.Linq.Queryable.Max<TSource>(IQueryable<TSource> source)
   at ... (my method that run the code)

Steps to reproduce

Create entity that maps to a simple table Product.cs
``` C#
[Table("tmpExtProduct", Schema = "ext")]
public partial class Product
{
public int Version { get; set; }
public int CategoryId { get; set; }
}

with context 
``` C#
        public virtual DbSet<Product> Products { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>(entity =>
            {
                entity.HasKey(e => e.Version)
                    .HasName("PK__Product");

                entity.Property(e => e.Version).ValueGeneratedNever();
            });

            OnModelCreatingPartial(modelBuilder);
        }

Minimal repro

Further technical details

EF Core version: 3.0.0-rc1.19456.14
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: W10 Pro
IDE: Visual Studio 2019 16.3 Preview 4.0

[smitpatel]

Currently we always throw for null result in Min/Max/Average. It should consider existence of DefaultIfEmpty or perhaps other scenarios around nullability.

[smitpatel]

Work-around
C# var valueFail = context.Products.Where(t => t.CategoryId == catId) .GroupBy(e => 1) .Select(t => t.Max(e => e.Version)) .ToList()[0];
Groupby a constant so whole table will be in one group and then apply aggregate operator.

[anddrzejb]

Is your workaround better then what I found?
C# var value = context.Products.Where(t => t.CategoryId == catId) .Select(t => t.Version) .DefaultIfEmpty().ToList().Max();
This at least gives me hope for a simple replace once maybe previously working syntax comes back.

[smitpatel]

@anddrzejb - The work-around I posted will process everything on server-side. In your case it will get all the versions from server side to compute max when the categoryId you are comparing gives multiple reasons.

[smitpatel]

May have same root cause as #18955

[liamwang]

How's it going now?

My current workaround is:

var maxSn = await Entities
    .Where(x => x.Date == date)
    .OrderByDescending(x => x.Sn)
    .Select(x => x.Sn)
    .FirstOrDefaultAsync();

It would be nice if provide the following API:

var maxSn= await Entities.MaxOrDefaultAsync(x => x.Sn);

[fellow7000]

Looks like this stuff does not work even in 3.1
Why is the issue closed?..

[smitpatel]

The issue is still open...

[smitpatel]

Duplicate of #20589

[arendvw]

How's it going now?

My current workaround is:

var maxSn = await Entities
    .Where(x => x.Date == date)
    .OrderByDescending(x => x.Sn)
    .Select(x => x.Sn)
    .FirstOrDefaultAsync();

It would be nice if provide the following API:

var maxSn= await Entities.MaxOrDefaultAsync(x => x.Sn);

This workaround gets similar performance (sorting done by SQL server) in indexed cases, with the benefit of having a much less awkward syntax.