Simple OData aggregation fails on Entity Framework Core (SQL Sever) and Asp.Net Core:
odata/file?$apply=groupby((FileType))
Due to inability to translate used GroupBy LINQ expression warning is issued:
Microsoft.EntityFrameworkCore.Query: Warning: The LINQ expression 'GroupBy(new GroupByWrapper() {GroupByContainer = new LastInChain() {Name = "FileType", Value = [$it].FileType}}, [$it])' could not be translated and will be evaluated locally.
And then exception is thrown:
system.security.verificationexception operation could destabilize the runtime
most relevant stack:
at lambda_method(Closure , File )
at System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
at System.Linq.GroupedEnumerable`3.GetEnumerator()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
Used NuGet packages versions:
Repo showing the issue: https://github.com/xmichaelx/ODataGroupByTest
Issue on GitHub: https://github.com/OData/WebApi/issues/1578
I'm not sure where the issue lies, whether EF Core should be able to convert LINQ query to valid SQL or OData generates difficult to convert GroupBy expression. Until it's sorted out I'm looking for some workaround.
Use Linq2Db extensions to EF Core as workaround.
After adding NuGet packages:
And changing from
public IActionResult Get()
{
return Ok(_db.Files);
}
to
public IActionResult Get()
{
return Ok(_db.Files.ToLinqToDB());
}
everything works.
Branch with fix: https://github.com/xmichaelx/ODataGroupByTest/tree/issue-workaround linq2db.EntityFrameworkCore : https://github.com/linq2db/linq2db.EntityFrameworkCore
EDIT: sum, avg, min, max works, countdistinct does not work