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 更多信息.

推荐答案

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

尝试像这样重写查询:

 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的来源，似乎IMethodCallTranslator 仍然在那里.这意味着您很有可能构建一个自定义函数，该函数将在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屋！