Using Max( ) is not working after upgrading from 2.2 to 3.1.

[suadev]

Using Max() is not working after upgrading from 2.2 to 3.1.

The Excption and code :

Processing of the LINQ expression 't => t
    .Where(s => s.Date == t
        .Max(m => m.Date))' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either 
a bug or a limitation in EF Core.

 var groupRates = GetPastRatesAsGroupByCodeId(businessDate);

 return groupRates
                .SelectMany(t =>
                    t.Where(s => s.Date == t.Max(m => m.Date)))  
                .ProjectToType<ExchangeCodeDto>()
                .GetPagedAsync(page, size);

Further technical details

EF Core version:
Database provider: (e.g. Npgsql.EntityFrameworkCore.PostgreSQL)
Target framework: (e.g. .NET Core 3.1.2)
IDE: (e.g. VS Code)

[suadev]

Update:

I guess this is not about Max(). I changed my query like this and the exception is not changed.

         var groupRates = GetPastRatesAsGroupByCodeId(businessDate);

          return groupRates
                .SelectMany(t => t.Where(s => s.Date == DateTime.Now)) 
                .ProjectToType<ExchangeCodeDto>()
                .GetPagedAsync(page, size);

groupRates comes from the following method. So this issue is about GroupBy or SelectMany ?

      private IQueryable<IGrouping<Guid, ExchangeRate>> GetPastRatesAsGroupByCodeId(DateTime businessDate)
        {
            var query = QueryAsNoTracking().Where(s => s.Date < businessDate); 
            return query.GroupBy(g => g.ExchangeCode.Id);
        }

[suadev]

Any thoughts or workaround ? @ajcvickers @smitpatel

[smitpatel]

GroupBy().SelectMany() is not supported since IGrouping does not have SQL representation. Though given SelectMany would flatten out all groupings anyway, probably query can be written differently.
Untested code. Something like below should give you same result.

QueryAsNoTracking().Where(s => s.Date < businessDate).OrderBy(g => g.ExchangeCode.Id).Where(t => t.Date == DateTime.Now).ProjectToType<ExchangeCodeDto>().GetPagedAsync(page, size);

[suadev]

Ok, as it seems this is duplication of #17068 so i am closing.