算在LINQ中选择VS - 哪个更快？

I'm using IQueryable<T> interfaces throughout my application and defer execution of SQL on the DB until methods like .ToList()

我在我的应用程序中使用IQueryable 接口并推迟在DB上执行SQL，直到像.ToList（）这样的方法

I will need to find the Count of certain lists sometimes -without- needing to use the data in the list being counted. I know from my SQL experience that a SQL COUNT() is far less work for the DB than the equivalent SELECT statement that returns all the rows.

我有时需要找到某些列表的计数 - 而不需要使用列表中的数据进行计数。我从我的SQL经验中知道，SQL COUNT（）的工作量远远少于返回所有行的等效SELECT语句。

So my question is: will it be less work on the DB to return the count from the IQueryable<T>'s Count() method than rendering the IQueryable<T> to a list and invoking the list's Count() method?

所以我的问题是：如果从IQueryable 的Count（）方法返回计数而不是将IQueryable 渲染到列表并调用列表的Count（）方法，那么DB上的工作是否会更少？

I suspect it will given that the ToList() will fire the SELECT sql and then in a separate query count the rows. I'm hoping the Count() on the IQueryable<T> simply renders out the sql for a sql COUNT() query instead. But im not certain. Do you know?

我怀疑它会给出ToList（）将触发SELECT sql然后在单独的查询中计算行数。我希望IQueryable 上的Count（）只是为sql COUNT（）查询渲染sql。但我不确定。你知道吗？

3 个解决方案

#1

Calling ToList() will return a genuine List<T> with all the data, which means fetching all the data. Not good.

调用ToList（）将返回包含所有数据的正版List ，这意味着获取所有数据。不好。

Calling Count() should indeed render the SQL to do the count on the database side. Much better.

调用Count（）确实应该使SQL在数据库端进行计数。好多了。

The simplest way to check this, however, is to enable logging in your data context (or whatever the equivalent is for your particular provider) and see what queries are actually being sent.

但是，检查此问题的最简单方法是在数据上下文中启用日志记录（或者为特定提供程序提供相应的内容），并查看实际发送的查询。

#2

-1

I'm not sure if it's a hard and fast rule, but linq method you add to an Iqueryable will be added into the linq expression tree - unless they are one of the methods that actually cause the tree to be evaluated (like ToList and Single etc). In the case of LinqToSql you'll know if it can't convert something into the SQL statement because you'll get a runtime exception stating that the method is not supported.

我不确定它是否是一个硬性规则，但是你添加到Iqueryable的linq方法将被添加到linq表达式树中 - 除非它们是实际导致树被评估的方法之一（如ToList和Single）等等）。对于LinqToSql，您将知道它是否无法将某些内容转换为SQL语句，因为您将获得一个运行时异常，指出该方法不受支持。

例如

var something = dbContext.SomeTable
  .Select(c => c.col1 == "foo")
  .Distinct()
  .ToList()
  .Count()

In the above, Select() and Distinct() are included in the sql query passed to the server because they are added to an Iqueryable. Count() is just acting on the list that was returned by the sql query. So you don't want to do it that way :-)

在上面，Select（）和Distinct（）包含在传递给服务器的sql查询中，因为它们被添加到Iqueryable中。 Count（）只是作用于sql查询返回的列表。所以你不想这样做:-)

In your case, Count() will definitely be faster that Select() because the resulting sql statement will indeed incorporate the count so the server only needs to return a single number rather than a list of rows.

在你的情况下，Count（）肯定会比Select（）更快，因为生成的sql语句确实会包含计数，所以服务器只需要返回一个数字而不是一个行列表。

#3

-1

If you're using SQL Server, Count() is still very expensive because it causes a table scan (or index scan, see comments on primary answer). And, by default Linq doesn't use the read uncomitted isolation level, which makes things worse due to locking.

如果您使用的是SQL Server，则Count（）仍然非常昂贵，因为它会导致表扫描（或索引扫描，请参阅主要答案的注释）。并且，默认情况下，Linq不使用读取未注册的隔离级别，这会因锁定而使事情变得更糟。

If you can live with the result being a dirty result and an approximation of the total number of rows, the following code will be considerably faster than using Count(). In my experience, the value returned by this code is rarely different than the true count of rows.

如果您可以将结果视为脏结果和行总数的近似值，则以下代码将比使用Count（）快得多。根据我的经验，此代码返回的值与真正的行数很少不同。

/// <summary>A very fast method for counting rows in a table.</summary>
public static long FastRowCount(DataContext context, string tableName)
{
    const string template = "SELECT rowcnt FROM sys.sysindexes WHERE id = OBJECT_ID('{0}') AND indid < 2";
    string query = string.Format(template, tableName);
    return context.ExecuteQuery<long>(query).Single();
}

#1