Querying Entity Model. Part 16 – WHERE IN With Concrete Values

In one of my recent posts Querying Entity Model. Part 5 – Implementing WHERE IN Analogue I described how Entity Framework implements Contains() method. In that case I had one LINQ query and one T-SQL query.

As you know LINQ could assemble multiple statements and Entity Framework would generate one T-SQL query. I mentioned it in my post Querying Entity Model. Part 14 – Dynamic Where Clause. But you can break this behaivor (accidentally or by intention) if you would save one of the intermediate query results to a .NET memory structure like List.

Let’s take a look at this example.

EF would generate one T-SQL query when you will get the results from query161b.

But if you cast first LINQ query to List (simply add .ToList() at the end), EF will generate two separate T-SQL queries.

The most important thing is EF had generated the second query with concrete values that were passed from the first list. If the first query extracts another set of values, EF would generate another T-SQL query. In result SQL Server needs to compile the second query and save its execution plan in procedure cache.

Querying Entity Model. Part 15 – Union

Entity Framework allows to gather two result sets into one large with a Union method. Union would make a unique list of items. A simple LINQ query could look like the following:

Entity Framework generates a bit strange T-SQL code with SELECT DISTINCT and UNION ALL instead of an obvious UNION operator.

This query do the same.

It’s more clear and concise, but if you look at execution plans there will no difference.

Dynamic LINQ Query

While searching a solution to build Where clause in LINQ queries dynamically, I found a ScottGu’s post about Dynamic Expression API. You can look here.

To use Dynamic expressions you need to install package in NuGet package manager. The current version is 1.0.6.

Then add namespace to your code.

Now you can write a LINQ query in dynamic expression form. You can even use it against Entity Framework. The following query is equivalent to the first example in my post Querying Entity Model. Part 14 – Dynamic Where Clause.

In my case EF generates the following T-SQL query.

Querying Entity Model. Part 14 – Dynamic Where Clause

Usually we write LINQ queries as a single statement. But C# allows to assemble a query by multiple statements.

This gives an excellent possibility to create a dynamic Where clause. In the most simple way just put a line with a Where assignment in if {…} block like this.

Entity Framework would generate a SQL query at the last moment when you start to retrieve data from database. So it would generate a particular SQL code for a given set of Where conditions.

If you need to select a particular set of fields after assembling a dynamic Where clause, this can be done with a second LINQ query.

Pay attention that I don’t use any ToList() or similar functions in query142a that would cause immediate execution. When Entity Framework retrieves data from query142b, it combines both queries and creates a single SQL query.

Querying Entity Model. Part 13 – Raw SQL

Entity Framework allows to execute queries written in Transact-SQL. EF has a SqlQuery method. You can read about it in MSDN article Raw SQL Queries
But it has a very important limitation – query must return concrete types (like int or string), entity instances or any other class instances.

Query from Entity

Query from generic type

Query arbitrary fields

At first, I need to create a custom class that will contain data from my query.

Now I can execute any SQL query that would return the same fields as declared in the class. The order of properties in the class and the order of fields in the query are not relevant.

It’s worth to mention that class ClientInfo is not included in the DbContext class as DbSet<ClientInfo>. It’s just a class in your code.

LINQ queries allows to create anonymous types, SqlQuery does not. Nevertheless, it’s a very good feature to run your own T-SQL code. Also you can create stored procedures in SQL Server database and run them from Entity Framework. I wrote about it in my post Code First. Executing Stored Procedures

[UPDATE 2016-08-24]
SqlQuery returns a collection of type System.Data.Entity.Infrastructure.DbRawSqlQuery. For example, you have a stored procedure that will return a single bigint value.

To get this value you need to iterate the collection.

Querying Entity Model. Part 12 – Unpivot

LINQ allows you to create both pivot and unpivot queries to the Entity Framework model. For this experiment I have a table with 6 columns: OperationDate and five columns Op1Result… Op5Result. Now I want to unpivot data to get a regular tables as it was in my previous post Querying Entity Model. Part 11 – Pivot

Unpivot operation can be made with two LINQ queries. First query will split table into separate result entities (rows). The second query will unite all rows into the regular table.

Entity Framework generates two SQL queries.

The second query has a rather bad execution plan. 5 table scans and 4 distinct sort operations are just for 5 columns to be unpivoted. I expect it will degrade when the number of columns would raise.

Unpivot execution plan

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.

 

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