Styles

Tuesday, March 8, 2011

Linq to Entities Count() using IQueryable<T> in Extension methods

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>

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:
exec sp_executesql N'SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
      COUNT(1) AS [A1]
      FROM ( SELECT DISTINCT
            [Project1].[C1] AS [C1],
            [Project1].[C2] AS [C2]
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.
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.

2 comments :

kalamaudy said...

Review of the Best Casino Game Provider in 2021 | bet365 casino
Read our in-depth review about 스포츠 토토 라이브 스코어 Bet365 casino software and what 호날두 주니어 makes 꽁 머니 토토 사이트 this game exciting and worth playing. Learn where 토토 검증 먹튀 랭크 to play and 한게임 포커 apk claim your welcome bonus.

fzgpwjayq1 said...

Gambling behavior turns into an issue when it cannot be controlled and when it interferes with funds, relationships, and the workplace. The individual might not notice they have an issue for a while. Gambling can result in a spread of issues, however the addiction can occur to anybody. No one can predict who will develop an addiction to playing. Gambling addiction comes in in} many varieties, the first symptom being a longing for gaming. Losses disguised as 토토사이트 wins also create a “smoother journey,” as some throughout the trade name it, permitting a machine to slowly deplete a player’s money reserves, somewhat than taking them in a couple of of} giant swipes.