User define function can't map correctly

[KINGGUOKUN]

CLR Method:
[DbFunction("ExtractMonth")]
public static string ExtractMonth(DateTime dateTime)
{
throw new Exception();
}

User Define Function:
CREATE DEFINER=root@% FUNCTION ExtractMonth(date datetime) RETURNS char(7) CHARSET utf8
begin
RETURN (DATE_FORMAT(date,'%Y-%m'));
END
And the function name is ExtractMonth

The method in the Linq:
var source = DbSet
.Where(x => x.Date >= DateTime.Parse(start) && x.Date <= DateTime.Parse(end).AddMonths(1).AddSeconds(-1))
.GroupBy(x => AccountDbFunctions.ExtractMonth(x.Date))
.Select(g => new Monthly
{
ID = Guid.NewGuid().ToString(),
Month = g.Key,
Cost = g.Sum(x => x.Cost)
});
int count = await source.CountAsync();

The Exception is as follows:
2018-07-23 20:56:32,718 [14] WARN Microsoft.EntityFrameworkCore.Query - The LINQ expression 'GroupBy(ExtractMonth([x].Date), [x])' could not be translated and will be evaluated locally.
2018-07-23 20:56:32,727 [14] WARN Microsoft.EntityFrameworkCore.Query - The LINQ expression 'GroupBy(ExtractMonth([x].Date), [x])' could not be translated and will be evaluated locally.
2018-07-23 20:56:32,735 [14] WARN Microsoft.EntityFrameworkCore.Query - The LINQ expression 'Sum()' could not be translated and will be evaluated locally.
2018-07-23 20:56:32,741 [14] WARN Microsoft.EntityFrameworkCore.Query - The LINQ expression 'Count()' could not be translated and will be evaluated locally.
2018-07-23 20:56:33,125 [14] ERROR Account.Infrustures.CustomExceptionFilterAttribute - 服务器处理出错
System.ArgumentException: must be reducible node
at System.Linq.Expressions.Expression.ReduceAndCheck()
at System.Linq.Expressions.Expression.ReduceExtensions()
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExtensionExpression(Expression expr, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteMemberExpression(Expression expr, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression1 lambda) at System.Linq.Expressions.Expression1.Accept(StackSpiller spiller)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression1 lambda) at System.Linq.Expressions.Expression1.Accept(StackSpiller spiller)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr)
at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(ReadOnlyCollection1 expressions) at System.Linq.Expressions.Compiler.StackSpiller.RewriteNewArrayExpression(Expression expr, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions) at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression1 lambda)
at System.Linq.Expressions.Expression1.Accept(StackSpiller spiller) at System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions) at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions) at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression1 lambda)
at System.Linq.Expressions.Expression1.Accept(StackSpiller spiller) at System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions) at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression) at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions) at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack) at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression1 lambda)
at System.Linq.Expressions.Expression1.Accept(StackSpiller spiller) at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda) at System.Linq.Expressions.Expression1.Compile(Boolean preferInterpretation)
at System.Linq.Expressions.Expression1.Compile() at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateExecutorLambda[TResults]() at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.CreateExecutorLambda[TResults]() at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)
at Account.Repository.EF.DailyRepository.GetMonthlys(String start, String end, Int32 pageIndex, Int32 pageSize) in D:\GitHub\Account\Account.Core\Account.Repository.EF\DailyRepository.cs:line 55
at Account.Service.MonthlyService.GetMonthlys(String start, String end, Int32 pageIndex, Int32 pageSize) in D:\GitHub\Account\Account.Core\Account.Service\MonthlyService.cs:line 23
at Account.Controllers.MonthlyController.GetPagedList(String start, String end, Int32 pageIndex, Int32 pageSize) in D:\GitHub\Account\Account.Core\Account\Controllers\MonthlyController.cs:line 40
at lambda_method(Closure , Object )
at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at System.Threading.Tasks.ValueTask`1.get_Result()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextExceptionFilterAsync()

[mguinness]

Are you using 2.0 or 2.1? There's a reason we ask for Pomelo.EntityFrameworkCore.MySql version in the new issue template. If the former then you can't use MySQL function as GroupBy is always evaluated locally.

I tested your function in both Select and Where without issue, I suggest you try to confirm that as well. It may also be an upstream issue with GroupBy as there are still unresolved issues needing work.

[KINGGUOKUN]

My current version is 2.1, and exactly this issue is caused by GroupBy expression。It seems that I need to wait for some version later for the Pomelo.EntityFrameworkCore.MySql。

[mguinness]

We don't know if the issue is in this provider or upstream. Can you try with another database provider (like PostgreSQL)?

[KINGGUOKUN]

OK，I'll test it in EFCore.SqlServer sometimes later, and give a result. Thanks !