重用在另一个LINQ查询LINQ查询结果，而无需重新查询数据库 [英] Reusing LINQ query results in another LINQ query without re-querying the database

问题描述

我在我的应用程序构建使用PredicateBuilder动态LINQ查询根据用户指定的过滤准则（这种情况旁白：看看这个的最好EF​​ predicateBuilder实现链接 ）。问题是，该查询通常需要很长的时间来运行，我需要这个查询的结果来执行其他查询（即，在加入与其它表的结果）。如果我写的T-SQL，我把第一次查询的结果到一个临时表或表变量，然后写我周围的其他查询。我想，要得到ID列表（例如，列表<的Int32> query1IDs ）从第一个查询，然后做这样的事情：

I have a situation where my application constructs a dynamic LINQ query using PredicateBuilder based on user-specified filter criteria (aside: check out this link for the best EF PredicateBuilder implementation). The problem is that this query usually takes a long time to run and I need the results of this query to perform other queries (i.e., joining the results with other tables). If I were writing T-SQL, I'd put the results of the first query into a temporary table or a table variable and then write my other queries around that. I thought of getting a list of IDs (e.g., List<Int32> query1IDs) from the first query and then doing something like this:

VAR QUERY2 = DbContext.TableName.Where（X => query1IDs.Contains（x.ID））

这将在理论工作;然而，ID的在 query1IDs 的数量可以在数百或成千上万（和LINQ表达 X => query1IDs.Contains（x.ID）被转换成T-SQLIN的声明，这是不好的原因很明显）和行数在表名是在百万的。 ？没有任何人有任何建议，应对这种情况的最好办法

This will work in theory; however, the number of IDs in query1IDs can be in the hundreds or thousands (and the LINQ expression x => query1IDs.Contains(x.ID) gets translated into a T-SQL "IN" statement, which is bad for obvious reasons) and the number of rows in TableName is in the millions. Does anyone have any suggestions as to the best way to deal with this kind of situation?

修改1：其他澄清什么，我' ]丢。

Edit 1: Additional clarification as to what I'm doing.

好吧，我建我的第一个查询（QUERY1）这正好包含了我感兴趣的ID。基本上，我将使用QUERY1为过滤器等表格。注：我的不可以使用了ToList（）在LINQ语句结尾---在查询的不可以在这个时候执行和没有结果被发送到客户端：

Okay, I'm constructing my first query (query1) which just contains the IDs that I'm interested in. Basically, I'm going to use query1 to "filter" other tables. Note: I am not using a ToList() at the end of the LINQ statement---the query is not executed at this time and no results are sent to the client:

VAR QUERY1 = DbContext.TableName1.Where（ComplexFilterLogic）。选择（X => x.ID）

然后我拿QUERY1，并用它来过滤另一个表（TableName2）。我现在把了ToList（）这个语句的结束，因为我要执行它，并把结果给客户端：

Then I take query1 and use it to filter another table (TableName2). I now put ToList() at the end of this statement because I want to execute it and bring the results to the client:

VAR QUERY2 =（从等于b.ID选择新的{b.Column1， b.column2，b.column3，...，b.columnM}）了ToList（）;

然后我去QUERY1并重新用它来过滤另一个表（TableName3），执行它，并把结果给客户端：

Then I take query1 and re-use it to filter yet another table (TableName3), execute it and bring the results to the client:

VAR QUERY3 =（从JOIN b在QUERY1上a.ID等于b.ID选择新的{b.Column1，b.column2，b.column3 ,. ..，b.columnM}）了ToList（）;

我能保持这样的作为许多查询，因为我喜欢：

I can keep doing this for as many queries as I like:

VAR queryN =（来自于DbContext.TableNameN JOIN b在a.ID QUERY1等于b.ID选择新的{b.Column1，b.column2，b.column3，...，b.columnM}）了ToList（）;

问题：QUERY1是需要的长执行时间。当我执行QUERY2，QUERY3 ... queryN，正在执行QUERY1（N-1）倍......这是不是做的事情（尤其是因为QUERY1没有改变）的一个非常有效的方式。正如我以前说过，如果我写的T-SQL，我会把QUERY1的结果到一个临时表中，然后使用该表在随后的查询。

The Problem: query1 is takes a long time to execute. When I execute query2, query3...queryN, query1 is being executed (N-1) times...this is not a very efficient way of doing things (especially since query1 isn't changing). As I said before, if I were writing T-SQL, I would put the result of query1 into a temporary table and then use that table in the subsequent queries.

< STRONG>编辑2：

我要给予信贷回答这个问题阿尔宾Sunnanbo了他的意见：

I'm going to give the credit for answering this question to Albin Sunnanbo for his comment:

当我有，我想在其他多个查询重用沉重查询我总是回到创造每个查询的连接，并把更多的解决方案类似的问题努力在优化查询执行（主要通过调整我的指标）。

When I had similar problems with a heavy query that I wanted to reuse in several other queries I always went back to the solution of creating a join in each query and put more effort in optimizing the query execution (mostly by tweaking my indexes).

我认为这是真的，一个可以与实体框架做了最好的。最后，如果表现得非常糟糕，我可能会与约翰·伍利的建议去：

I think that's really the best that one can do with Entity Framework. In the end, if the performance gets really bad, I'll probably go with John Wooley's suggestion:

这可能是一种情况下降反对一个存储过程原生ADO返回多个结果，并使用内部临时表可能是这种操作的最佳选择。使用EF为您的应用程序的其他90％。

This may be a situation where dropping to native ADO against a stored proc returning multiple results and using an internal temp table might be your best option for this operation. Use EF for the other 90% of your app.

感谢大家谁对这个职位评论...我感谢大家的投入！

Thanks to everyone who commented on this post...I appreciate everyone's input!

推荐答案

如果表名的规模并不太大加载使用全表

If the size of TableName is not too big to load the whole table you use

var tableNameById = DbContext.TableName.ToDictionary(x => x.ID);

来获取整个表，并自动把它放在一个地方词典与 ID 的关键。

另一种方式是只力的LINQ评价与 .ToList（），的情况下获取整个表，并与当地Linq2Objects做其中，部分。

Another way is to just "force" the LINQ evaluation with .ToList(), in the case fetch the whole table and do the Where part locally with Linq2Objects.

var query1Lookup = new Hashset<int>(query1IDs);
var query2 = DbContext.TableName.ToList().Where(x => query1IDs.Contains(x.ID));

编辑：结果
存储ID列表：■从列表中选择一个查询，并使用该列表作为过滤器，另一个查询通常可以重写为连接结果
当我怀着沉重的查询类似的问题，我想在其他几个查询重用。我总是回到在创建每个查询联接的解决方案，并投入更多的精力在优化查询执行（主要通过调整我的指标）。

Storing a list of ID:s from one query in a list and use that list as filter in another query can usually be rewritten as a join.
When I had similar problems with a heavy query that I wanted to reuse in several other queries I always went back to the solution of creating a join in each query and put more effort in optimizing the query execution (mostly by tweaking my indexes).

这篇关于重用在另一个LINQ查询LINQ查询结果，而无需重新查询数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！