有没有一种方法可以运行查询并检查聚合数据，而无需再次重新运行查询? [英] Is there a way to run the query and inspect the aggregate data without rerunning the query again?

问题描述

在决定枚举LINQ查询的结果集之前，我想查看查询结果计数(可能还有其他汇总数据).

I want to look at the query result count (and possibly other aggregate data) before deciding to enumerate the result set of a LINQ query.

几乎每个LINQ示例都立即在foreach循环中枚举结果.但是我想检查计数以确定是否需要遍历每个结果.有没有一种方法可以运行查询并检查聚合数据，而无需再次重新运行查询?如果我做类似的事情

Almost every LINQ example immediately enumerates the results in a foreach loop. But I want to inspect the count to determine if I need to loop through each result or not. Is there a way to run the query and inspect the aggregate data without rerunning the query again? If I do something like

query.Count()
foreach(var item in query) { ... }

我想它将执行两次查询，但是不需要再次重新执行...只枚举Count()已经返回的结果集.

I suppose it will execute the query twice, but it doesn't need to be re-executed again... only enumerated over the already-returned result set from Count().

查询SQL数据库时，通常非常有效的方法是先运行一个单独的聚合查询，然后再运行另一个查询以获取行数据(如果需要).但是，如果我正确理解LINQ，无论我获得Count()还是进行foreach，它都会执行相同的操作-在本地进行相同的循环和逻辑，而无需任何特定的优化.是否有一种有效和/或内置的方式来保存多个操作的结果?

When querying a SQL database, it is often very efficient to first run a separate aggregate query and then another query to get the row data if needed. But if I understand LINQ correctly, it will perform the same operation--same loops and logic locally without any particular optimization--no matter if I get the Count() or do a foreach. Is there an efficient and/or built-in way to persist the results for multiple operations?

推荐答案

在.NET中，IEnumerable<T>序列是惰性的:这意味着仅在需要时才执行底层逻辑.副作用是，如果需要两次结果，则逻辑将执行两次.

In .NET, IEnumerable<T> sequences are lazy: this means that the underlying logic is executed when and only when you need it. A side effect is that if you need the result twice, the logic is executed twice.

例如，如果序列绑定到SQL数据库的结果，那么如果您先计算元素的数量然后列出所有元素，则SQL查询将执行两次.

For instance, if the sequence is bound to the results from an SQL database, the SQL query will be executed two times if you first count the number of elements and then list them all.

某些LINQ方法只是转换您将将获得的结果，而没有实际获得结果.例如，var b = a.Where(a > 0);不会运行基础逻辑，而是推迟到您实际需要结果为止.

Some LINQ methods just transform the result you will get, without actually getting the results. For instance, var b = a.Where(a > 0); won't run the underlying logic, but postpone until you actually need the result.

其他LINQ方法有效地强制执行逻辑.例如，Count()将强制加载所有元素以对其进行计数. ToArray()或ToList()也会这样做，将元素存储在内存中以备将来使用.

Other LINQ methods effectively force the logic to be executed. Count(), for instance, will force all elements to be loaded in order to count them. ToArray() or ToList() will do that too, storing the elements in memory for further use.

您要寻找的方法完全取决于上下文.

The approach you are looking for depends entirely on the context.

通常，您可能希望将序列存储在内存中(例如，通过使用ToList())，以便以后对其进行多项操作，例如计算元素的数量，然后遍历所有元素.另一方面，在某些情况下，您还需要其他东西:

In general, you may want to store the sequence in memory—for example by using ToList()—in order to do multiple operations on it later, such as counting the number of elements and then looping through all of them. On the other hand, there are cases where you will need something else:

• 底层逻辑的影响可能太大，无法将所有内容加载到内存中，从而迫使您搜索更智能的解决方案.假设您正在执行数据库搜索，并且想要显示结果数(例如2 000 000)以及前20个条目.您是否真的需要将所有2,000,000个结果存储在内存中?可能不是.

• The impact of the underlying logic may be too high in order to load everything in memory, forcing you to search for a smarter solution. Imagine that you are doing database search and you want to show the number of results (say 2 000 000) as well as the first twenty entries. Do you really need to store all 2 000 000 results in memory? Probably not.

这里的解决方案是执行count SQL查询(数据库引擎将能够对其进行优化)，然后执行实际查询，该查询将获得前二十个结果.

The solution here would be to do a count SQL query (that the database engine will be able to optimize), followed by the actual query which gets the first twenty results.

一个类似的例子是您只有很少的元素，但是要获得单个元素需要很多CPU周期.有时，当您只需要计算元素时，可以使用快捷方式来简化复杂的工作.

A similar example is when you get few elements, but it requires many CPU cycles to get a single element. Sometimes, shortcuts can be used to short-circuit the complex work when all you need is to count the elements.

否则可能会发生相反的情况:基础逻辑和数据量太小而无法实际处理.假设您正在处理A-Za-z范围内的字符.真的有必要做ToList()吗?

Or the opposite may happen: the underlying logic and the amount of data is too small to actually care. Imagine that you are dealing with characters within A-Za-z range. Is it really necessary to do a ToList()? Wouldn't it be easier to count, and then to loop through the characters separately?

有时，甚至不可能将所有元素存储在内存中.例如，如果您有一个无限序列(例如从不断写入的日志文件中刷新的行)，该怎么办?

Sometimes, it won't even be possible to store all elements in memory. For instance, what if you have an infinite sequence (such as the lines flushed from a log file which is constantly written to)?

但是，如果我正确理解LINQ，无论我获得Count()还是进行foreach，它都会执行相同的操作-在本地进行相同的循环和逻辑，而无需任何特定的优化.

But if I understand LINQ correctly, it will perform the same operation--same loops and logic locally without any particular optimization--no matter if I get the Count() or do a foreach.

这完全取决于您使用的ORM.创建LINQ查询时，例如:

This depends entirely on the ORM you use. When you create your LINQ query, such as:

var count = this.SequenceFromDatabase
    .Where(product => product.Price > 0)
    .Count();

foreach (var product in this.SequenceFromDatabase.Take(10))
{
    ...
}

一些(如果不是大多数的话)ORM实际上会将其转换为:

some (if not most) ORMs will actually translate this into:

select count(1) from [Schema].[Table] where [Price] > 0

select top 10 [Name], [Price], [Availability] from [Schema].[Table]

鉴于从LINQ到SQL查询的实际转换取决于ORM和您编写的实际LINQ代码，因此必须密切注意正在执行的SQL查询.许多ORM都具有跟踪SQL查询的能力，并且某些SQL引擎还允许您分析查询.

Given that the actual translation from LINQ to SQL queries depends on the ORMs and the actual LINQ code you write, it is essential to keep an eye on the SQL queries being executed. Many ORMs have the ability to trace the SQL queries, and some SQL engines allow you to profile the queries as well.

这篇关于有没有一种方法可以运行查询并检查聚合数据，而无需再次重新运行查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！