Entityframework Core 3 linq 表达式无法翻译 [英] Entityframework Core 3 linq expression could not be translated

问题描述

我刚刚升级到 EF 3，我的一个曾经可以工作的查询现在出现异常

I just upgrade to EF 3 and one of my queries that used to work, gives an exception now

   ProductionRecords = _context.ProductionRecords
          .Where(r => r.DataCriacao.Date == DateTime.Now.Date)
            .Select(pr => new ProductionRecordViewModel
            {
                Id = pr.Id,
                Operador = pr.Operador,
                DataCriacao = pr.DataCriacao,
                Celula = pr.Celula.Name,
                Turno = pr.Turno.Name,
                TotalPecasSemDefeito = pr.ReferenceRecords.Sum(c => c.Quantity),
                TotalPecasComDefeito = pr.DefectRecords.Sum(c => c.Quantidade),
                TotalTempoParado = pr.StopRecords.Sum(c => Convert.ToInt32(c.Duration.TotalMinutes)),
            })
          .AsNoTracking()
          .ToList();

当我试图用时间跨度和持续时间对集合求和时发生异常....

The exception happens when i'm trying to sum the collection with the timespan with the duration....

我现在该如何处理?

这里是个例外

InvalidOperationException:LINQ 表达式'(EntityShaperExpression: EntityType: StopRecordValueBufferExpression: (ProjectionBindingExpression:EmptyProjectionMember) IsNullable: False ).Duration.TotalMinutes'无法翻译.要么以可以的形式重写查询被翻译，或通过插入显式切换到客户评估调用 AsEnumerable()、AsAsyncEnumerable()、ToList() 或ToListAsync().请参阅 https://go.microsoft.com/fwlink/?linkid=2101038 了解更多信息.

InvalidOperationException: The LINQ expression '(EntityShaperExpression: EntityType: StopRecord ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ).Duration.TotalMinutes' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

推荐答案

EF3 有一个突破性的变化，除非在查询链的最后(你的 Convert.ToInt32(c.Duration.TotalMinutes) 可能依赖).

There's been a breaking change in EF3 that will not automatically revert to client side evaluation unless at the very end of query chain (which your Convert.ToInt32(c.Duration.TotalMinutes) was likely relying on).

尝试像这样重写您的查询:

Try rewriting your query like so:

 ProductionRecords = _context.ProductionRecords
      .Where(r => r.DataCriacao.Date == DateTime.Now.Date)
        .AsNoTracking()
        .AsEnumerable()
        .Select(pr => new ProductionRecordViewModel
        {
            Id = pr.Id,
            Operador = pr.Operador,
            DataCriacao = pr.DataCriacao,
            Celula = pr.Celula.Name,
            Turno = pr.Turno.Name,
            TotalPecasSemDefeito = pr.ReferenceRecords.Sum(c => c.Quantity),
            TotalPecasComDefeito = pr.DefectRecords.Sum(c => c.Quantidade),
            TotalTempoParado = pr.StopRecords pr.StopRecords.Sum(c => Convert.ToInt32(c.Duration.TotalMinutes)),
        })
      .ToList();

UPD 正如评论中正确指出的那样 - 这基本上会将 .Select 评估推迟到客户端.这可能会导致性能问题.这种行为很可能是最初对 EF Core 3 进行此更改的原因.

UPD As it's been rightly pointed out in the comments - this will basically defer the .Select evaluation to the client side. Which will likely cause performance issues. Most likely this behaviour has been the reason this change was made to EF Core 3 in the first place.

我没有足够的细节向您推荐合适的解决方案，但您似乎无法真正摆脱在所有结果上加载 StopRecords.这就是编写自定义方法转换器可以帮助您的地方.请参阅我的 关于如何做到这一点的其他答案.我快速检查了 EF Core 3 源代码，它似乎 I 还在.这意味着您很有可能构建一个自定义函数，将日期转换为 SQL 中的 TotalMinutes.

I don't have enough specifics to recommend you a proper solution, but it seems you can't really get away from loading StopRecords on all your results. Which is where writing a custom method translator can help you. See my other answer on how to do that. I quickly checked EF Core 3 source and it seems IMethodCallTranslator is still there. Which means you have pretty high chance of building a custom function that will convert dates to TotalMinutes in SQL.

这篇关于Entityframework Core 3 linq 表达式无法翻译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！