Add support for groupby translate on EF Core 6.0.0-rc2

[xiaokun]

Simple code test new feature Support ability to select top N of each group from EF Core 6.0.0 rc2 throw InvalidOperationException:

The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY v0.AuthorId ORDER BY v0.PlayCount DESC)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Code:

var videos= await _db.Videos.GroupBy(x => x.AuthorId)
  .Select(x => new {
    AuthorId = x.Key,
    Items = x.OrderByDescending(x => x.PlayCount ).Take(3)
  })
  .ToListAsync();

Stack Trace:

at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.CheckTranslated(Expression translated, Expression original)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.CheckSupport(Expression expression, Boolean isSupported)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitRowNumber(RowNumberExpression rowNumberExpression)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.ProjectionExpression.VisitChildren(ExpressionVisitor visitor)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.<VisitChildren>g__VisitList|120_0[T](List`1 list, Boolean inPlace, Boolean& changed, <>c__DisplayClass120_0& )
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.<VisitChildren>g__VisitList|120_0[T](List`1 list, Boolean inPlace, Boolean& changed, <>c__DisplayClass120_0& )
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.LeftJoinExpression.VisitChildren(ExpressionVisitor visitor)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.<VisitChildren>g__VisitList|120_0[T](List`1 list, Boolean inPlace, Boolean& changed, <>c__DisplayClass120_0& )
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
   at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlCompatibilityExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Pomelo.EntityFrameworkCore.MySql.Query.Internal.MySqlParameterBasedSqlProcessor.Optimize(SelectExpression selectExpression, IReadOnlyDictionary`2 parametersValues, Boolean& canCache)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommandTemplate(IReadOnlyDictionary`2 parameters)
   at Microsoft.EntityFrameworkCore.Internal.RelationCommandCacheExtensions.RentAndPopulateRelationalCommand(RelationalCommandCache relationalCommandCache, RelationalQueryContext queryContext)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.<InitializeReaderAsync>d__19.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass33_0`2.<<ExecuteAsync>b__0>d.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<ExecuteImplementationAsync>d__34`2.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<ExecuteImplementationAsync>d__34`2.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<ExecuteAsync>d__33`2.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.<MoveNextAsync>d__18.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__65`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__65`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()

Package References:

<PackageReference Include="Microsoft.AspNetCore.Mvc.NewtonsoftJson" Version="6.0.0-rtm.21516.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.0-rtm.21515.19" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Abstractions" Version="6.0.0-rtm.21515.19" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Analyzers" Version="6.0.0-rtm.21515.19" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.0-rtm.21515.19" />
<PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.0-rc.1.ci.20211007223757" />

Mysql version is 5.7
Visual studio 2022 preview

[mguinness]

EF Core 6 RC support is still WIP see #1413 (comment), but regardless you'll have to use MySQL 8.0 to be able to use window functions as 5.7 doesn't support them.

[xiaokun]

@mguinness You are right. MYSQL not support function ROW_NUMBER() until version 8.0. What a pity.

[lauxjpn]

You are right. MYSQL not support function ROW_NUMBER() until version 8.0. What a pity.

It should work fine with MySQL 8+.