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.

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

Entity Framework. C# To SQL Type Translation

When you declare a variable in Entity Framework model, migration translates the C# type to SQL Server type. Here is a table describing how each C# type is implemented in SQL.

C# primitive type C# type in System namespace SQL Server type
byte Byte tinyint
short Int16 smallint
int Int32 int
long Int64 bigint
 float Single real
double Double float
decimal Decimal decimal(18,2)
bool Boolean bit
DateTime datetime
char Char
string String nvarchar(max) null

Just two interesting things:

  1. char type is not translated to any SQL type;
  2. string type is the only type that will be nullable, the others are not.

And some tips:

  1. to make a field NULL, for example int, you should use a nullable type int? instead;
  2. to make a string field NOT NULL, place a [Required] attribute before declaration in EF model;
  3. if your string field won’t have an infinite length, you can (and I recommend to) place a [StringLength(n)] attribute where n can be up to 4000.

[UPDATE 2016-11-17]
Unsigned integer types and sbyte are not translated too. See here Not All .NET Types Can Be Used In Entity Framework

Code First. Splitting Entity Into Two Tables

Entity Framework allows to divide a single entity to 2 separate physical tables. To do this you need to declare an entity as usual, and add a Fluent API command to map this entity to tables.

Declaration of entity:

Table annotation is not necessary here and will be omitted by Visual Studio.

Entity-table mapping:

After migration Entity Framework will create 2 tables with the names defined in OnModelCreating function.

In Entity Framework you work with a single entity, i.e. you can create/update entity as usual, but when you save changes, the database context will generate separate SQL commands to modify the corresponding tables (first, second or both).

Be aware if you skip a property in mapping, Entity Framework will create a 3rd table with key field and a new created one.

Code First. Executing Stored Procedures

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.

Querying Entity Model. Part 7 – Left Join With First Row

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.