Having performance in LINQ to SQL

Question

Placing where after group by in LINQ to SQL, works like having in T-SQL. I hope that LINQ generates having statement in result. but when I look at the output statement, I don't see any having statement. Is there any method or trick to force LINQ to use having, and for better performance?

LINQ query:

var result = from item in db.Words
             group item by item.UserID into gp
             where gp.Average(g => g.DownVotes) == 0
             select gp.Key;

result.ToList();

SQL output:

-- Region Parameters
DECLARE @p0 Float = 0
-- EndRegion
SELECT [t1].[UserID]
FROM (
    SELECT AVG([t0].[DownVotes]) AS [value], [t0].[UserID]
    FROM [Words] AS [t0]
    GROUP BY [t0].[UserID]
    ) AS [t1]
WHERE [t1].[value] = @p0

I think this query has low performance vs having

Both queries (generated one and one with `having`) will return the same results. You should verify, that not using `having` actually affects performance in this case. Use execution plan or run some benchmarks. *I think* is really not a great way to start optimizations. — MarcinJuraszek, Mar 16 '15 at 05:51

score 0 · Accepted Answer · answered Mar 16 '15 at 09:55

Those queries are functionally identical, all that's happened is that LINQ has used a subquery to supply the inner layer of the query.

Consider that in a normal group query, you need HAVING to act as a second WHERE clause after the grouping, but in the subquery version, the WHERE operates after the group anyway.

As MarcinJuraszek said, benchmark both queries to get the answer on which is quicker or else just trust that the T-SQL engine is going to optimize the subquery version anyhow.

Having performance in LINQ to SQL

1 Answers1