EFCore 3.1-通过Any存在查询；查询无法翻译 [英] EFCore 3.1 - Exists query via Any; Query cannot be translated

问题描述

我们正在使用EFCore 3.1，并尝试通过.Any()跨越2个属性来使用Exists构建查询.

  var selectionCriteria = someHugeList.Select(sh => new {sh.Id，sh.StatusCode}).ToList()var resultsQry = _myContext.SomeClass.include(sc => sc.DetailRecords).where(sc => selectionCriteria.Any(crit => crit.Id == sc.Id&&crit.StatusCode == sc.StatusCode));var结果=等待resultsQry.ToListAsync() 

运行此查询(即使只有少量(5个项目)选择条件项)时，也会提供以下错误消息；

System.InvalidOperationException:LINQ表达式'DbSet哪里(c => __selectionCriteria_0.Any(crit => crit.Id == sc.Id&& crit.StatusCode == sc.StatusCode))'无法翻译.以可以翻译的形式重写查询，或者通过以下方式显式切换到客户端评估插入对AsEnumerable()，AsAsyncEnumerable()，ToList()或ToListAsync()的调用.请参阅 https://go.microsoft.com/fwlink/?linkid=2101038 有关更多信息.'

似乎问题出在以下事实上:.Any子句中包含2个属性.sql中存在的where通常可以毫无问题地执行此操作.EFCore似乎很难做到这一点.

有人对如何解决这个问题有想法吗?

解决方案

刚刚发现了这一点；https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

Seems the problem resides in the fact that there are 2 properties included in the .Any clause. A where exists in sql normally can do this without problem. EFCore seems to find this difficult.

Does anyone have an idea on how to solve this?

解决方案

just found this; https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

long story short; client evaluation is no longer working in EFCore 3.1 meaning this type of query (comparing a clientside list to a serverside list) doesn't work. You need to bring it to the client. My collegue pointed out to me just now that I didn't appreciate the error message to it's full potential :).

Changed my query as follows (not optimal, but no other solution yet):

var selectionCriteria = someHugeList.Select(sh => new { sh.Id, sh.StatusCode }).ToList()
var resultsQry = _myContext.SomeClass
                           .Include(sc => sc.DetailRecords)
                           .AsEnumerable() // this is the important part, pulling all the records client side so we can execute the .Any on the client.
                           .Where(sc => selectionCriteria.Any(crit => crit.Id == sc.Id 
                                                                   && crit.StatusCode == sc.StatusCode));

var results = await resultsQry.ToList() // no more async, because clientside

这篇关于EFCore 3.1-通过Any存在查询；查询无法翻译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！