CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
RBT
RBT

Reputation: 25887

SQL equivalent of Count extension method for LINQ isn't obvious

I'm doing LINQ to entity framework (EF) to get count of records in my table using below code:

 using (var db = new StackOverflowEntities())
 {
      var empLevelCount = db.employeeLevels.Count();
 }

I captured the query fired by EF towards database using SQL Server Profiler. I got the following query :

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[employeeLevels] AS [Extent1]
    )  AS [GroupBy1]

This query remains exactly the same even for LongCount extension method except for the fact that COUNT SQL function gets replaced by COUNT_BIG in the SQL query being created by EF. The query created by LINQ to EF provider looks very weird to me. Why it is not simply doing something like below to return the scalar count value?

SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[employeeLevels] AS [Extent1]

It will be really helpful if someone can help me understand the additional logistics being taken care of by EF internally which is why LINQ to EF provider is creating such a query? It seems EF is trying to deal with some additional use cases as well through some common algorithm which results in some sort of generic query as the one created above.

Upvotes: 3

Views: 205

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Testing both queries (suitably changing the table) in a DB of mine reveals that they both generate exactly the same query plan. So, the structure shouldn't concern you overly much. In SQL, you tell the system what you want, and it works out how best to do it, and here the optimizer is able to generate the optimal plan given either sample.

enter image description here

As to why LINQ generates code like this, I'd suspect it's just a generalized pattern in its code generator that lets it generate similar code for any aggregation and subsequent transformations, not just for unfiltered counts.

Upvotes: 6

Related Questions