Where().Count()和Count()之间的区别 [英] Difference between Where().Count() and Count()

问题描述

using (DBEntities db = new DBEntities())
{
   var employeeAgedAbove30 = db.Employees.Where(s => s.Age > 30).Count(); // Method 1

   employeeAgedAbove30 = db.Employees.Count(s => s.Age > 30); // Method 2

}

考虑上面的示例，其中列出了30岁以上的员工.

Consider the above example where I take a list of employees aged above 30.

方法1和方法2有什么区别? 您想要哪一个，为什么?

What is the difference between Method 1 and Method 2? Which one would you prefer and why?

推荐答案

出于可读性考虑，我希望使用第二种方法.如果您查看生成的sql代码，它是相同的.

I would prefer the second method for the readability. If you look at the generated sql code it is the same.

方法1:

db.TblEmployees.Where (t =>t.Age>30).Count ()

SQL

-- Region Parameters
DECLARE @p0 Int = 30
-- EndRegion
SELECT COUNT(*) AS [value]
FROM [tblEmployees] AS [t0]
WHERE [t0].[Age] > @p0
GO

方法2:

db.TblEmployees.Count (t =>t.Age>30)

SQL

-- Region Parameters
DECLARE @p0 Int = 30
-- EndRegion
SELECT COUNT(*) AS [value]
FROM [tblEmployees] AS [t0]
WHERE [t0].[Age] > @p0

最后，更多是关于个人喜好.对于不习惯linq的人，在计数之前使用where子句可能更具可读性.

At the end of the day it is more about personal preference. For someone not used to linq it might be more readable with a where clause before the count.

编辑1

在旁注.是的，生成SQL是相同的.但是IL代码在一个地方是不同的.在应用计数和位置时.

On the side note. Yes the generate sql is the same. But the IL code is different in one place. When applying the count and the where.

方法1:

IL_0001:  ldarg.0     
IL_0002:  stloc.0     // db
IL_0003:  ldloc.0     // db
IL_0004:  callvirt    LINQPad.User.TypedDataContext.get_TblEmployees
IL_0009:  ldtoken     LINQPad.User.TblEmployees
IL_000E:  call        System.Type.GetTypeFromHandle
IL_0013:  ldstr       "t"
IL_0018:  call        System.Linq.Expressions.Expression.Parameter
IL_001D:  stloc.1     // CS$0$0000
IL_001E:  ldloc.1     // CS$0$0000
IL_001F:  ldtoken     LINQPad.User.TblEmployees.get_Age
IL_0024:  call        System.Reflection.MethodBase.GetMethodFromHandle
IL_0029:  castclass   System.Reflection.MethodInfo
IL_002E:  call        System.Linq.Expressions.Expression.Property
IL_0033:  ldc.i4.s    1E 
IL_0035:  box         System.Int32
IL_003A:  ldtoken     System.Int32
IL_003F:  call        System.Type.GetTypeFromHandle
IL_0044:  call        System.Linq.Expressions.Expression.Constant
IL_0049:  ldtoken     System.Nullable<System.Int32>
IL_004E:  call        System.Type.GetTypeFromHandle
IL_0053:  call        System.Linq.Expressions.Expression.Convert
IL_0058:  call        System.Linq.Expressions.Expression.GreaterThan
IL_005D:  ldc.i4.1    
IL_005E:  newarr      System.Linq.Expressions.ParameterExpression
IL_0063:  stloc.2     // CS$0$0001
IL_0064:  ldloc.2     // CS$0$0001
IL_0065:  ldc.i4.0    
IL_0066:  ldloc.1     // CS$0$0000
IL_0067:  stelem.ref  
IL_0068:  ldloc.2     // CS$0$0001
IL_0069:  call        System.Linq.Expressions.Expression.Lambda
IL_006E:  call        System.Linq.Queryable.Where
IL_0073:  call        System.Linq.Queryable.Count

方法2:

IL_0001:  ldarg.0     
IL_0002:  stloc.0     // db
IL_0003:  ldloc.0     // db
IL_0004:  callvirt    LINQPad.User.TypedDataContext.get_TblEmployees
IL_0009:  ldtoken     LINQPad.User.TblEmployees
IL_000E:  call        System.Type.GetTypeFromHandle
IL_0013:  ldstr       "t"
IL_0018:  call        System.Linq.Expressions.Expression.Parameter
IL_001D:  stloc.1     // CS$0$0000
IL_001E:  ldloc.1     // CS$0$0000
IL_001F:  ldtoken     LINQPad.User.TblEmployees.get_Age
IL_0024:  call        System.Reflection.MethodBase.GetMethodFromHandle
IL_0029:  castclass   System.Reflection.MethodInfo
IL_002E:  call        System.Linq.Expressions.Expression.Property
IL_0033:  ldc.i4.s    1E 
IL_0035:  box         System.Int32
IL_003A:  ldtoken     System.Int32
IL_003F:  call        System.Type.GetTypeFromHandle
IL_0044:  call        System.Linq.Expressions.Expression.Constant
IL_0049:  ldtoken     System.Nullable<System.Int32>
IL_004E:  call        System.Type.GetTypeFromHandle
IL_0053:  call        System.Linq.Expressions.Expression.Convert
IL_0058:  call        System.Linq.Expressions.Expression.GreaterThan
IL_005D:  ldc.i4.1    
IL_005E:  newarr      System.Linq.Expressions.ParameterExpression
IL_0063:  stloc.2     // CS$0$0001
IL_0064:  ldloc.2     // CS$0$0001
IL_0065:  ldc.i4.0    
IL_0066:  ldloc.1     // CS$0$0000
IL_0067:  stelem.ref  
IL_0068:  ldloc.2     // CS$0$0001
IL_0069:  call        System.Linq.Expressions.Expression.Lambda
IL_006E:  call        System.Linq.Queryable.Count

编辑2

解决评论.是的，我会检查执行时间，看看有什么意义.我仍然相信第二个会更快.但是你必须检查一下.如果它是代码的性能关键部分.从数据库方面，我将检查查询的执行计划.在年龄"列上添加索引可能很有意义.

To address the comment. Yes I would check the execution time and see what make sense. I still would believe that the second would be faster. But you have to check. If it is a performance critical part of your code. From the database side I would check the execution plan of the query. It might make sence to add a index on the Age column.

编辑3

您还可以测量执行命令所花费的时间.这是使用Stopwatch类的简短演示:

You could also measure the time it takes too execute the commands. This is a short demo using the Stopwatch class:

var st=new Stopwatch();
st.Start();
db.TblEmployees.Where (t =>t.Age>30).Count ();
st.Stop();
Console.WriteLine(st.Elapsed);
st.Restart();
db.TblEmployees.Count (t =>t.Age>30);
st.Stop();
Console.WriteLine(st.Elapsed);

在我的测试中，TblEmployees为空.我得到这个结果:

In my test with the TblEmployees empty. I get this result:

00:00:00.0019229
00:00:00.0007023

这篇关于Where().Count()和Count()之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！