为什么在载有（）运算符降低实体框架“LINQ查询？ [英] Why does the Contains() operator degrade Entity Framework' Linq queries?
问题描述
我读<一href="http://www.dotnet-tricks.com/Tutorial/entityframework/J8bO140912-Tips-to-improve-Entity-Framework-Performance.html"相对=nofollow>此处，在实体框架，如果执行包含操作它会降低性能：
I read here that in entity framework if you perform contains operation it reduces performance:
包含转换为凡在SQL中引起性能下降
Contains is converted to "WHERE IN" in SQL which cause performance degrades"
现在我有近10具有10列的表，并同时获取我使用记录包含了近8列。
Now I have nearly 10 tables having 10 column and while fetching records I use Contains for nearly 8 columns.
我的问题是上面真的吗？如果有什么替代品呢？
My question is above true? If yes what is the alternate to that?
推荐答案
是包含（）将在很大程度上降低性能。
Yes Contains() will degrade performance heavily.
所以，你可以试试下面提及的解决方案。
我们能够解决的EF增加一个中间表中并加入来自LINQ查询需要使用包含子句，表中包含的问题。我们能够取得惊人的效果，这种方法。我们有一个大的EF模型，并为包含时pre-编译EF查询，我们对使用的查询变得非常差的性能是不允许的包含条款。
We were able to solve the EF Contains problem by adding an intermediate table and joining on that table from LINQ query that needed to use Contains clause. We were able to get amazing results with this approach. We have a large EF model and as "Contains" is not allowed when pre-compiling EF queries we were getting very poor performance for queries that use "Contains" clause.
概述：的
An overview:
-
创建SQL Server中的表 - 例如HelperForContainsOfIntType与GUID数据类型HelperID和int数据类型的列ReferenceID。与需要不同的数据类型的ReferenceID创建不同的表。
Create a table in SQL Server - for example HelperForContainsOfIntType with HelperID of Guid data-type and ReferenceID of int data-type columns. Create different tables with ReferenceID of differing data-types as needed.
创建实体/ EntitySet的为HelperForContainsOfIntType和EF模型等这样的表。根据需要为不同的数据类型创建不同的实体/ EntitySet的。
Create an Entity / EntitySet for HelperForContainsOfIntType and other such tables in EF model. Create different Entity / EntitySet for different data-types as needed.
在创建.NET code的辅助方法，需要一个IEnumerable的输入，并返回一个GUID。这种方法生成一个新的GUID，并自IEnumerable将值插入HelperForContainsOfIntType一起生成的GUID。接着，该方法返回这个新产生的Guid给调用者。对于快速插入HelperForContainsOfIntType表，创建一个存储过程这需要值的列表中输入并执行插入。请参见表值参数在SQL Server 2008（ADO.NET）。创建不同的助手为不同的数据类型，或创建一个通用的辅助方法来处理不同的数据类型。
Create a helper method in .NET code which takes the input of an IEnumerable and returns an Guid. This method generates a new Guid and inserts the values from IEnumerable into HelperForContainsOfIntType along with the generated Guid. Next, the method returns this newly generated Guid to the caller. For fast inserting into HelperForContainsOfIntType table, create a stored-procedure which takes input of an list of values and does the insertion. See Table-Valued Parameters in SQL Server 2008 (ADO.NET). Create different helpers for different data-types or create a generic helper method to handle different data-types.
创建EF编译的查询这是类似的东西象下面这样：
static Func<MyEntities, Guid, IEnumerable<Customer>> _selectCustomers =
CompiledQuery.Compile(
(MyEntities db, Guid containsHelperID) =>
from cust in db.Customers
join x in db.HelperForContainsOfIntType on cust.CustomerID equals x.ReferenceID where x.HelperID == containsHelperID
select cust
);
调用助手方法，其值将用在包含条款并获得GUID来在查询中使用。 例如：
Call the helper method with values to be used in the Contains clause and get the Guid to use in the query. For example:
var containsHelperID = dbHelper.InsertIntoHelperForContainsOfIntType(new int[] { 1, 2, 3 });
var result = _selectCustomers(_dbContext, containsHelperID).ToList();
我从 <一个href="http://stackoverflow.com/questions/7897630/why-does-the-contains-operator-degrade-entity-frameworks-performance-so-drama">Here
I got that from Here
这篇关于为什么在载有（）运算符降低实体框架“LINQ查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
