EF Core 3.1引发Contains异常 [英] EF Core 3.1 throws an exception for Contains

问题描述

最近，我将项目代码更新为.NET Core 3.1和EF Core 3.1，现在我的大多数linq查询都是EX.

I recently updated the project code into .NET Core 3.1 and EF Core 3.1, now most of my linq queries brake, EX.

public override ICollection<ContactDetailModel> GetAll(ICollection<int> ids)
{
            return _context
                .Set<TEntity>()
                .IgnoreDeletedEntities()                
                .Where(x => ids.Distinct().Contains(x.ContactId))                
                .Select(EntityToDTOMapper)
                .ToList();
}

此代码在我使用包含"的地方引发了错误，我在其他一些帖子中看到此问题已作为错误修复，但失败了.

This code throws an error where I use Contains, I saw in some other posts this issues has been fixed as a bug, but yet it fails.

我得到的错误是无法翻译.要么以可以翻译的形式重写查询，要么通过插入对AsEnumerable()，AsAsyncEnumerable()，ToList()或ToListAsync()."

Error I get is "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()."

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression 'DbSet<SupplierContactDetails>
    .Where(s => !(s.DeletedOn.HasValue) && !(s.DeletedBy.HasValue))
    .Where(s => __Distinct_0
        .Contains(s.ContactId))' 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.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at BlueTag.DAL.Repositories.ContactRepository`1.GetAll(ICollection`1 ids) in D:\Projects\BlueTag Version 2\.net-core-server\DAL\Repositories\ContactRepository.cs:line 95
   at BlueTag.DAL.Repositories.SupplierRepository.ToDomain(IEnumerable`1 supplier) in D:\Projects\BlueTag Version 2\.net-core-server\DAL\Repositories\SupplierRepository.cs:line 216
   at BlueTag.DAL.Repositories.SupplierRepository.GetFilteredSuppliers(RequestPagingOptionsModel`1 options) in D:\Projects\BlueTag Version 2\.net-core-server\DAL\Repositories\SupplierRepository.cs:line 129
   at BlueTag.Supplier.Services.SupplierService.GetFilteredSupplierDetails(RequestPagingOptionsModel`1 options) in D:\Projects\BlueTag Version 2\.net-core-server\Supplier\Serrvices\SupplierService.cs:line 49
   at BlueTag.Supplier.Controllers.SupplierController.GetFilteredSuppliers(RequestPagingOptionsModel`1 options) in D:\Projects\BlueTag Version 2\.net-core-server\Supplier\Controllers\SupplierController.cs:line 54
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()

推荐答案

实体框架无法转换每个查询，因此有时必须加载所有数据并在内存中执行LINQ表达式，这称为客户端评估.并且这是不希望的，因为它需要更多的资源，并且需要更长的时间.对于您的特定问题，有2种解决方案，错误消息中都列出了这两种解决方案.

Entity Framework cannot translate every query, and as such it sometimes has to load all the data and perform the LINQ expression in-memory, this is called Client Side Evaluation and is not desired, as it is more resource intensive and takes longer. For your specific problem, there are 2 solutions, both of which are outlined in the error message.

1)重写LINQ查询，以包括对客户端评估的显式调用与隐式调用

1) Rewrite your LINQ queries to include explicit calls to client side evaluation versus implicit calls

或

2)重写您的LINQ查询，不需要客户端评估

2) Rewrite your LINQ queries to not need client side evaluation

您可以像这样做1号:

public override ICollection<ContactDetailModel> GetAll(ICollection<int> ids)
{
    return _context
        .Set<TEntity>()
        .IgnoreDeletedEntities()
        .ToList()                
        .Where(x => ids.Distinct().Contains(x.ContactId))                
        .Select(EntityToDTOMapper)
        .ToList();
}

请注意在IgnoreDeletedEntities之后对ToList的显式调用，需要执行此操作以显式切换到客户端评估，以便您的Where语句将正确执行并且不会引发任何错误.这是因为x => ids.Distinct().Contains(x.ContactId)无法通过您的EF版本转换为SQL(或其他任何形式).

Notice the explicit call to ToList after IgnoreDeletedEntities, this needs to be done to explicitly switch to client side evaluation so your Where statement will properly execute and not throw any errors. This is because x => ids.Distinct().Contains(x.ContactId) cannot be translated to SQL (or whatever) by your version of EF.

数字2可以这样解决:

public override ICollection<ContactDetailModel> GetAll(ICollection<int> ids)
{
    ids = ids.Distinct();
    return _context
        .Set<TEntity>()
        .IgnoreDeletedEntities()
        .Where(x => ids.Contains(x.ContactId))
        .Select(EntityToDTOMapper)
        .ToList();
}

请注意我是如何将ids.Distinct()的使用从Where移到顶部的，因为那是EF无法翻译的部分

Notice how I moved the use of ids.Distinct() from the Where to the top, as that was the part EF couldn't translate

这篇关于EF Core 3.1引发Contains异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！