LINQ表达式错误无法翻译 [英] LINQ expression error could not be translated

问题描述

我在尝试执行查询时遇到此错误.

I'm getting this error with trying to execute my query.

System.InvalidOperationException:.where(ti =>(int)ti.Inner.OptionType == 1& ti.Inner.QualifierId == null& ti.Inner.CreditingMethod！="xxx"&& __ToList_0.Any(e => e.Vdate ==(Nullable)ti.Outer.ADate))'无法翻译.以一种可以翻译的形式重写查询，或者通过插入对AsEnumerable()，AsAsyncEnumerable()，ToList()或ToListAsync()的调用来显式切换到客户端评估.请参阅 https://go.microsoft.com/fwlink/?linkid=2101038 有关更多信息.'

我正在填充的对象是

public class OCList
    {
        public DateTime? Vdate{ get; set; }
        public Double? VWeight{ get; set; }
    }

/// example of adding to the list
List<OCList> ocList= new List<OCList>();
            if (request.Date1 != null && request.Weight1 != null)
            {
                ocList.Add(new OCList{ Vdate = request.Date1.Value, VWeight = request.Weight1.Value });
            }

错误在这里:

&& ocList.ToList().Any(e => e.Vdate == x.Tb2.ADate))

Linq表达式:

var results = _context.Tb1
                .Join(_context.Tb2, oc => oc.OptionId, o => o.OptionId, (oc, o) => new { OptionCost = oc, Option = o })
                .Where(x => x.Tb2.Val1 == 1 
                    && x.Tb2.Val2 == null 
                    && ocList.ToList().Any(e => e.Vdate == x.Tb2.ADate))
                ////.........

推荐答案

在此处检查客户端和服务器端评估:

Check client and server side evaluation here: https://docs.microsoft.com/en-us/ef/core/querying/client-eval

EF Core在顶级投影中支持部分客户端评估(实际上是对Select()的最后一次调用).如果查询中的顶级投影无法转换到服务器，则EF Core将从服务器获取所有必需的数据，并在客户端上评估查询的其余部分.如果EF Core在顶级投影之外的任何其他位置检测到无法翻译到服务器的表达式，则它将抛出运行时异常.

EF Core supports partial client evaluation in the top-level projection (essentially, the last call to Select()). If the top-level projection in the query can't be translated to the server, EF Core will fetch any required data from the server and evaluate remaining parts of the query on the client. If EF Core detects an expression, in any place other than the top-level projection, which can't be translated to the server, then it throws a runtime exception.

您的 ToList 和 Any 都无法翻译为sql，因此您会收到错误消息.

Your ToList and then Any can't be translated to sql and thus you get the error.

这行得通

var results = _context.Tb1
                .Join(_context.Tb2, oc => oc.OptionId, o => o.OptionId, (oc, o) => new { OptionCost = oc, Option = o })
                .AsEnumerable()
                .Where(x => x.Tb2.Val1 == 1 
                    && x.Tb2.Val2 == null 
                    && ocList.ToList().Any(e => e.Vdate == x.Tb2.ADate))

但是它将首先从服务器获取所有内容，然后应用 where 子句，从而导致性能不佳.

BUT it would first fetch everything from the server and then apply the where clause, resulting to poor performance.

您可以像这样使它变得更好

You could make it a bit better like this

var results = _context.Tb1
                .Join(_context.Tb2, oc => oc.OptionId, o => o.OptionId, (oc, o) => new { OptionCost = oc, Option = o })
                .Where(x => x.Tb2.Val1 == 1 
                    && x.Tb2.Val2 == null)
                .AsEnumerable()
                 .Where(ocList.ToList().Any(e => e.Vdate == x.Tb2.ADate))

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