使用AspNet Core OData和Entity Framework Core与GROUP进行聚合 [英] Aggregation with GROUP BY using AspNet Core OData and Entity Framework Core

问题描述

简单的OData聚合在Entity Framework Core(SQL Sever)和Asp.Net Core上失败:

Simple OData aggregation fails on Entity Framework Core (SQL Sever) and Asp.Net Core:

odata/file?$apply=groupby((FileType))

由于无法翻译使用过的GroupBy LINQ表达式警告:

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.

然后引发异常:

system.security.verificationexception operation could destabilize the runtime

最相关的堆栈:

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()

使用的NuGet软件包版本:

Used NuGet packages versions:

• Microsoft.AspNetCore.OData 7.0.1
• Microsoft.EntityFrameworkCore.SqlServer 2.1.1
• Microsoft.AspNetCore 2.1.2

显示该问题的回购: https://github.com/xmichaelx/ODataGroupByTest

在GitHub上的问题: https://github.com/OData/WebApi/issues/1578

Issue on GitHub: https://github.com/OData/WebApi/issues/1578

我不确定问题出在哪里，EF Core是否应该能够将LINQ查询转换为有效的SQL或OData会导致难以转换GroupBy表达式.在解决之前，我正在寻找一些解决方法.

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.

推荐答案

使用针对EF Core的Linq2Db扩展作为解决方法.

Use Linq2Db extensions to EF Core as workaround.

添加NuGet软件包后:

After adding NuGet packages:

• linq2db 2.2.0
• linq2db.EntityFrameworkCore 1.0.1

并从

    public IActionResult Get()
    {
        return Ok(_db.Files);
    }

到

    public IActionResult Get()
    {
        return Ok(_db.Files.ToLinqToDB());
    }

一切正常.

修正分支: https://github.com/xmichaelx/ODataGroupByTest/tree/issue-workaround linq2db.EntityFrameworkCore: https://github.com/linq2db/linq2db.EntityFrameworkCore

Branch with fix: https://github.com/xmichaelx/ODataGroupByTest/tree/issue-workaround linq2db.EntityFrameworkCore : https://github.com/linq2db/linq2db.EntityFrameworkCore

求和，平均，最小，最大有效，countdistinct不起作用

sum, avg, min, max works, countdistinct does not work

这篇关于使用AspNet Core OData和Entity Framework Core与GROUP进行聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！