Difference between Where().Count() and Count()

Question

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

}

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

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

Answer 1

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

Method 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

Method 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

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.

Edit 1

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.

Method 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

Method 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

Edit 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.

Edit 3

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);

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

00:00:00.0019229
00:00:00.0007023

Answer 2

There is no difference in performance. It is just about readability of the code.

I created a table and run both query in LINQPad. Following are the output:

SQL for Method 1:

-- Region Parameters
DECLARE @p0 Int = 1
-- EndRegion
SELECT COUNT(*) AS [value]
FROM [Employees] AS [t0]
WHERE [t0].[Type] = @p0

Execution time: 00:00.279

SQL for Method 2:

-- Region Parameters
DECLARE @p0 Int = 1
-- EndRegion
SELECT COUNT(*) AS [value]
FROM [Employees] AS [t0]
WHERE [t0].[Type] = @p0

Execution time: 00:00.275

Here you can see there is no difference in SQL generated by LINQ and minor difference in execution time.