Something I learnt at Resonate Solutions while working particularly on performance tuning of Linq To Entities when I was digging a little deeper into the IQueryable<T>.Count() method.
I realised the when I was using SQL Profiler to see what the generated TSQL code was, it didn't have the equivalent COUNT(*) operator. It was actually evaluating the entire expression first then passed it back to the application so that the count could be made on the instance collection in memory, which would have a serious impact on the performance of the query because of all the data being sent back and forth.
The main reason it was doing this was because the Extension Method I used was for a type IEnumerable<T> as shown below even though the instance passed in was of type IQueryable<T>
Since IQueryable<T> implements IEnumerable<T> I thought it would be good to use a "baser" Interface, but as it happened, it returned the entire collection then did a count on the inferred IEnumerable<T> instance in memory.
Looking into SQL Profiler, the TSQL that gets generated contains the following:
However if I were to use:
I realised the when I was using SQL Profiler to see what the generated TSQL code was, it didn't have the equivalent COUNT(*) operator. It was actually evaluating the entire expression first then passed it back to the application so that the count could be made on the instance collection in memory, which would have a serious impact on the performance of the query because of all the data being sent back and forth.
The main reason it was doing this was because the Extension Method I used was for a type IEnumerable<T> as shown below even though the instance passed in was of type IQueryable<T>
public static int PageCount<T>(this IEnumerable<T> list){int count = list.Count();//rest of the code...}
Since IQueryable<T> implements IEnumerable<T> I thought it would be good to use a "baser" Interface, but as it happened, it returned the entire collection then did a count on the inferred IEnumerable<T> instance in memory.
Looking into SQL Profiler, the TSQL that gets generated contains the following:
exec sp_executesql N'SELECT[Project2].[C1] AS [C1],[Project2].[C2] AS [C2],FROM ( SELECT[Distinct1].[C1] AS [C1],[Distinct1].[C2] AS [C2]
However if I were to use:
public static int PageCount<T>(this IQueryable<T> list){int count = list.Count();
//rest of the code...
}
That evaluates the TSQL with COUNT(1) which is exactly what I wanted as shown below:The main reason for this is that the inference on an interface is quite different than doing it on an abstract function or base class when everything is done in memory.exec sp_executesql N'SELECT[GroupBy1].[A1] AS [C1]FROM ( SELECTCOUNT(1) AS [A1]FROM ( SELECT DISTINCT[Project1].[C1] AS [C1],[Project1].[C2] AS [C2]
When passing in the instance of an object that implements IQueryable<T> through a parameter that is of type IEnumerable<T> the instance is forced to shape itself as an IEnumerable and therefore Linq to Entities will recognise that it needs to evaluate the whole collection first rather than building the expression prior to execution.