Count(x => ...) vs Where(x => ...).Count()

Question

Is there any difference between these two LINQ-to-Entities queries:

context.Table.Count(x => ...)

and

context.Table.Where(x => ...).Count()

in terms of performance and generated SQL?

I tried to look generated SQL myself, but I only know how to get the SQL from IQueryable, but Count returns the value directly.

The **b**est way to know it is to **t**est it... IMO, there seems to be (only) a little difference between those two though.. — Ian, Aug 10 '16 at 08:44
I mean, by timing it... not necessary by looking at the generated SQL. Give difficult cases for both (like querying 1000000 times or so...) — Ian, Aug 10 '16 at 08:46
http://stackoverflow.com/questions/25787555/linq-performance-count-vs-where-and-count — monstertjie_za, Aug 10 '16 at 08:46
[Logging and Intercepting Database Operations](https://msdn.microsoft.com/en-us/data/dn469464.aspx?f=255&MSPPError=-2147217396) — Sergey Kalinichenko, Aug 10 '16 at 08:48
@monstertjie_za, it is LINQ to Objects, and my case is LINQ to Entities. The results may differ highly because LINQ to Entities is translated to SQL. — Aleksey Shubin, Aug 10 '16 at 08:49
@AlekseyShubin I understand, just thought I would put it out there for you to give a read. — monstertjie_za, Aug 10 '16 at 08:52
@dasblinkenlight, thanks! Will try to see the query using this way. — Aleksey Shubin, Aug 10 '16 at 09:11

Aleksey Shubin · Accepted Answer · 2016-08-10T09:33:15.320

0

I have managed to see the SQL (thanks to @dasblinkenlight), the answer is - no, both LINQ queries generate exactly the same SQL query, at least for a simple query without a grouping:

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Table] AS [Extent1]
        WHERE <condition>
    )  AS [GroupBy1]

edited Aug 10 '16 at 09:33

answered Aug 10 '16 at 09:18

Aleksey Shubin

1,960
2
20
39

there may be a little difference since in the second solution there is second function call (first the call to the where function and then a Count(). I don't think this makes sooo much difference when use rarely – Radinator Aug 10 '16 at 09:27
Yes, but I think the additional function call time is negligible compared to overall database connection and SQL execution time. – Aleksey Shubin Aug 10 '16 at 09:31
as i said in my post :D – Radinator Aug 10 '16 at 10:44

Count(x => ...) vs Where(x => ...).Count()

1 Answers1