Querying Entity Model. Part 17 – ORDER BY

LINQ supplies 4 methods to order the result set, and all these methods are implemented in Entity Framework. The first method should be OrderBy() for ascending ordering or OrderByDescending() otherwise. The second and next subsequent methods should be ThenBy() or ThenByDescending().

This LINQ query will generate the following T-SQL code on the model used in all my posts about Querying Entity Model.

Good! Entity Framework produces the correct ORDER BY clause.

If you place two OrderBy() methods in one LINQ query one after another, you’ll get a wrong result – it will be sorted only by the last expression.

So the correct sequence must be OrderBy – ThenBy.

SSDT Database Project Publishing vs Code First Migration

I work both with SSDT database projects and .NET projects based on Entity Framework Code First models. Now I’ll say some words about how these two types of projects do the deployment.

1. SSDT database project

  • You write the code (it is Transact-SQL code) to create tables, stored procedures, functions and other SQL Server database objects in declarative style. It means that you write it in a way of how it should be in the final stage. Don’t worry about changes, no ALTER commands – Visual Studio do it during deployment.
  • In SSDT database project terminology deployment is called “publishing”. To deploy your database you should find Publish command 🙂
  • During publishing Visual Studio compare the actual state of the database with the state that should be according to your project. Then it generate the ALTER script.

2. Code First Migration

  • You write your model in declarative style too but as a C# class.
  • To create the database you use NuGet Package Manager to run two commands: Add-Migration and Update-Database.
  • During migration Visual Studio looks into previous migration classes and generates the difference. Then it creates a new class with changes.
  • Update-Database generates T-SQL commands according to the code in the migration class. The info about applied migrations is saved in the database in the dbo.__MigrationHistory table.

The main difference between these two deployments is that SSDT database project produces change script based on actual database, whereas Code First Migration does not. So if you use migrations, change your tables only through migrations. Direct changes to database would not be taken into account by migration process.

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.

One-To-Many And One-To-One Relationship In Code First Model

It’s very simple to create a one-to-many relationship between entities in Code First model. Just place ICollection<T2> at “one” end of the relationship, and T1 at the “many” end.

The result is shown in the following diagram. EF creates new field for the foreign key, the name is autogenerated in the form Entity_Key (in my case, Client_Id).
one-to-many diagram

In order to convert this relationship to one-to-one (yeah, Entity Framework allows you to do this), it seems that it’s enough to change ICollection to Phone. But EF throws an error:

Unable to determine the principal end of an association between the types 'Clients.Phone' and 'Clients.Client'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

It means you need to explicitly mark one end of the relationship as Required attribute.

But EF won’t create a field for foreign key, it uses the primary key of Phone entity to be the foreign key too.
one-to-one diagram

The same action can be achieved by adding ForeignKey attribute before primary key field instead a Required. So my Phone class will look like this:

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.