Category: Querying Entity Framework 6

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.

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.

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.

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.

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.

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.

 

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.

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

Entity Framework can execute stored procedures in SQL Server. For example, I have a simple stored procedure as following:

In Code First model this can be done by different ways depending on whether you want to get a resultset from a stored procedure.

1. SqlQuery (returning a resultset)

or

If you need to pass a parameters, you need to add a parameter name after the name of stored procedure and supply SqlParameter. The command will be like this:

[UPDATE 2016-08-24]
SqlQuery returns a collection of type System.Data.Entity.Infrastructure.DbRawSqlQuery.

2. ExecuteSqlCommand (no resultset)

If you want to execute a stored procedure or an SQL command that won’t return a result set, you can call this function.

3. Low-level methods through ObjectContext

There’s an excellent article on MSDN Stored Procedures with Multiple Result Sets that describes how to get a couple of resultsets with the help of Database.Connection.CreateCommand and ObjectContext.Translate.

Let’s imagine the situation when a person could have a few phone numbers, for example, home, work, mobile for home country, mobile for traveling, etc. I want to make a list of clients with just one phone number. This phone number can be chosen arbitrary or the first from an ordered list. This task can be done in Entity Framework Code First model by using a LINQ query.

I have Clients and Phones entities, and I’ll create a query in lambda syntax to select client names with a phone number with a first country code. I’ve found three ways to do this.

1. Join with first row using subquery

2. Join with first row using Navigation properties

3. Join with first row using GroupJoin

Entity Framework generates the same T-SQL query in all these cases.

From the SQL Server perspective, using APPLY operator is not a good solution. APPLY executes a subquery for each client row. You can read more about APPLY operator in MSDN article Using APPLY.

The most efficient way for SQL Server will be a query with Window function such as ROW_NUMBER().

To check the efficiency you need to insert a lot of records to your database, copy both scripts to SQL Server Management Studio and look at the Estimated or Actual Execution Plan. In a test with 4 client rows I have 74% and 26% relative to a batch. The percentages of the query cost could differ due to number of rows/data pages/clustered indexes to be processed, but the result is the query with APPLY is much heavier than that with a ROW_NUMBER() function.

I think you should avoid queries with APPLY to get many records. In these cases it would better to invoke a T-SQL queries or stored procedures from Entity Framework.