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

Querying Entity Model. Part 8 – Implementing GROUP BY And HAVING

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

I expect this T-SQL query

but Entity Framework generates rather complicated T-SQL query that produce the same results.

Query with GROUP BY and HAVING
To implement HAVING BY clause just add an additional Where method after the aggregating query.

If you were a classical SQL developer, you would write this command

but Entity Framework is using WHERE clause instead of HAVING

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).

LINQ group by having

List SQL Server Database File’s Sizes

Here’s a simple script for Microsoft SQL Server that list all databases, database file names and locations, and their sizes. In addition it shows recovery model and last backup LSN.

Speaking At 24 Hours Of PASS Russian Edition 2015

24HOP_Russian_2016

The 5th online conference 24 Hours Of PASS Russian Edition has started today. My session was the first in the list. I’ve talked a lot about transaction log, its internals and troubleshooting.

Here are my slide deck and demo code for my session:
Slide deck – Transaction log and troubleshooting (Russian)
Demo – Transaction-log

Also you can watch video from SQL PASS Russian Virtual Chapter channel
Direct link https://www.youtube.com/watch?v=WnLupnOoPXw (in Russian)