LINQ allows to construct aggregate queries on Entity Framework model. This can be done with GroupBy method. For example, I want to make a list of country codes and the quantity of clients from those countries.
Query with GROUP BY
|
1 2 3 4 5 6 7 |
var query81 = ctx.Clients .GroupBy(c => c.CountryCode) .Select(c => new { c.Key, Count = c.Count() }); |
I expect this T-SQL query
|
1 2 3 |
SELECT CountryCode, COUNT(*) AS cnt FROM [dbo].[Clients] GROUP BY CountryCode |
but Entity Framework generates rather complicated T-SQL query that produce the same results.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT 1 AS [C1], [GroupBy1].[K1] AS [CountryCode], [GroupBy1].[A1] AS [C2] FROM ( SELECT [Extent1].[CountryCode] AS [K1], COUNT(1) AS [A1] FROM [dbo].[Clients] AS [Extent1] GROUP BY [Extent1].[CountryCode] ) AS [GroupBy1] |
Query with GROUP BY and HAVING
To implement HAVING BY clause just add an additional Where method after the aggregating query.
|
1 2 3 4 5 6 7 8 |
var query82 = ctx.Clients .GroupBy(c => c.CountryCode) .Select(c => new { c.Key, Count = c.Count() }) .Where(g => g.Count > 1); |
If you were a classical SQL developer, you would write this command
|
1 2 3 4 |
SELECT CountryCode, COUNT(*) AS cnt FROM [dbo].[Clients] GROUP BY CountryCode HAVING COUNT(*) > 1 |
but Entity Framework is using WHERE clause instead of HAVING
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT 1 AS [C1], [GroupBy1].[K1] AS [CountryCode], [GroupBy1].[A1] AS [C2] FROM ( SELECT [Extent1].[CountryCode] AS [K1], COUNT(1) AS [A1] FROM [dbo].[Clients] AS [Extent1] GROUP BY [Extent1].[CountryCode] ) AS [GroupBy1] WHERE [GroupBy1].[A1] > 1 |
Comparing the efficiency of hand-written and EF generated commands, they are almost identical. Execution plan for the LINQ query adds an additional Compute Scalar operator with a very low cost (second from the left).
One Reply to “Querying Entity Model. Part 8 – Implementing GROUP BY And HAVING”