Querying Entity Model. Part 11 – Pivot

Another kind of ugly T-SQL queries generated by Entity Framework is pivot queries. For example, I have a normalized table with 3 essential columns: operation name, date and numeric result. I need to build a LINQ query that will produce a pivot table with operation date and calculate a sum or results by each operation.

LINQ allows to build a query but I need to place exact operation names in this query.

The T-SQL code is a bit dreadful.

If you look at execution plan, you could find 5 nested loops for each operation name and 6 table scans (for 5 operation names + 1 for operation date). SQL query written with PIVOT operator is more compact.

Comparison of execution plans shows that pure T-SQL code is twice cheaper. This result is based on a small set of records.

SQL PIVOT uses just one table scan, but it makes a sort operation. I see a potential threat that it would lead to TempDB spill where it would be run on a large set of records. So the situation could be different on big amount of data.

Querying Entity Model. Part 10 – Group Concatenation

This time I try to generate a list of clients with their phone numbers as a single string from Entity Framework model. The logic is quite simple: make a group by each client, and concatenate all phone numbers with a group into a comma-separated string.

C# has a simple function to do this: string.Join(). Let’s try to run it on EF model.
LINQ query seems very simple. I use the same model like in previous post about Querying Entity Model. string.Join() should concatenate all phone numbers through Navigation property.

But when you execute this query, Entity Framework throws an exception.

LINQ to Entities does not recognize the method 'System.String Join(System.String, System.Collections.Generic.IEnumerable`1[System.String])' method, and this method cannot be translated into a store expression.

After some investigations I discovered that there’s no solution to make group concatenation in SQL Server and get a single string for each client from it. Instead we need to get a result set from SQL Server in a form of a simple join, then to make concatenation in C#. This can be done using ToList() in LINQ query like in the following example.

Later you should concatenate phone numbers using string.Join() or Aggregate() in your code.

You can look also at this wonderful post Using LINQ Group By And string.Join() / Aggregate() In Entity Framework 3.5 where the author investigates different approaches and performance.

When you look at T-SQL code generated by Entity Framework, you’ll see SQL execute a query with LEFT JOIN, so it produces as so many rows as at least the total number of phone numbers.

Positive and negative effects of this solution:

  • SQL Server: good – less computations (no string concatenation), bad – bigger result set;
  • network: bad – bigger result set is sent from SQL Server to client application;
  • C#: bad – more computations to fetch rows and concatenate strings;
  • architecture: good – the computation load can be distributed from SQL Server to application servers.