Querying Entity Model. Part 9 – Distinct Count

In my previous post Querying Entity Model. Part 8 – Implementing GROUP BY And HAVING I used Count as aggregate function on grouping sets. Now I want to get a distinct count for client names on groups of clients from each country. In classic T-SQL it’s a simple query:

But LINQ does not have a CountDistinct() function, it can offer only Count() and Distinct(). To get the desired result we need to introduce a sub-query from which we can get a distinct sequence, and then to count these distinct values.

Entity Framework generates sub-queries instead of using HAVING clause. This is the resulting T-SQL code:

Comparing the execution plans for Code First queries and T-SQL COUNT DISTINCT, I have to say that Entity Framework makes it worse. This picture is the best evidence.

Comparing distinct count

Leave a Reply

Your email address will not be published. Required fields are marked *