Querying Entity Model. Part 4 – Join On Multiple Fields

Usually we join tables on just one field, but there may be situations when you need to link records based on two or more fields. Entity Framework, along with T-SQL, is capable to do such joins.

This time I slightly modify the EF model that I use before. Now I add just one property with country code to each of my classes Client and Phone. This code would mean which country the client and his/her phone number are registered in.

My query will select all clients and their home phone numbers, i.e. the country code must be the same. Queries are written in lambda syntax.

1. Join on multiple fields using anonymous types

2. Join on multiple fields using cross join and where

And again Entity Framework shows an excellent result. It generates the same T-SQL code with inner join.

Querying Entity Model. Part 3 – Cross Join

If you need to create a cross join in Entity Framework model, i.e. to link every record from the first table with every record from the second table, it can be implemented with a quite simple LINQ query.

In this example I use the same EF model with Client and Phone entities. LINQ query written in lambda syntax selects all possible pairs of client name and phone number.

This query generates the following T-SQL code:

Querying Entity Model. Part 2 – Left Outer Join

After investigating inner joins in Entity Framework model I started to explore left outer joins. It’s where you have one record in left table and zero or many records in the right table.

I’ve found three different ways to write LINQ query in lambda syntax and fortunately they all produce the same T-SQL code. So you can choose any style of writing that looks good for your taste.

I use the same entities as described in my previous post Querying Entity Model. Part 1 – Inner Join

1. Left outer join using GroupJoin and SelectMany (DefaultIfEmpty inside SelectMany)

2. Left outer join – rearranged (DefaultIfEmpty inside GroupJoin)

3. Left outer join using SelectMany

As I mentioned earlier, all these LINQ queries generate the same T-SQL code:

Querying Entity Model. Part 1 – Inner Join

Here are some investigations about how we can query an Entity Framework tables created in Code First model. The most important is how it might be done with LINQ and what T-SQL code the EF would create in any situation. This time I touch only inner joins whereas there are left outer joins, not in/not exists, and so on.

UPDATE 2015-01-15
My LINQ queries have lambda syntax cause I like it too much 🙂

I use a couple of very simple tables (entities):

Note that I use some features to precisely create the table structure using annotations and explicit foreign key field. For more details look at my post Code First. Creating Relationships Between Entities.

Generally, there are 2 ways to join entities: using Join, like in ordinary SQL query, or using Navigation properties defined in EF model. But there are some nuances how it can be implemented.

1. Inner join on Client entity

The resulting T-SQL query will be:

2. Inner join on dedicated foreign key property
Pay attention that my Phone class has a dedicated property ClientId. This field is served as a foreign key and links with Client entity.

The resulting T-SQL query will be:

The most curious that EF produces different T-SQL code in these two queries, but the execution plans are the same.

EF inner join execution plan

3. Inner join through Navigation property
We define Navigation properties in each classes, thus it gives us access to corresponding entity. In our case we will get access from Phone to the corresponding Client.

Wow! The most compact code you could ever write for inner join. And this LINQ query produces the same T-SQL code as in case 2.

4. Inner join using DbContext.Set
This approach could be handy when you write universal processing with generic types, for example, <T1> and <T2>. When a generic type does not know about the presence of navigation properties, the only possible way is to use an explicit Join here.

And again, T-SQL code is the same as in step 2.

Code First Model Conventions. Something You’d Like To Turn Off

When Entity Framework creates a database according to your Code First model, it applies some conventions that makes this process more intelligent. For example, it can recognize primary key, set a relationships between entities (1:1, 1:many, many:many), and so on. The full list is available on MSDN article System.Data.Entity.ModelConfiguration.Conventions Namespace. Currently (January 2016) there’s 46 conventions.

In my opinion Entity Framework makes some unnecessary work with all these conventions. So it would better to turn off some of them, and now I’ll explain why I think so.

ForeignKeyIndexConvention

EF creates indexes for every foreign key field. For example, if we have a Client and Phone entities and the last has a FK to Client, we’ll get an additional index on Phones table.

FK index

Is it good in all cases? I don’t think so. It depends on data in these tables and how you query them. Maybe this index would never be used by SQL Server, or you would have too much indexes that can negatively impact your OLTP performance.

To understand the problem better you can watch video recorded by Kimberly L. Tripp about SQL Server index internals, indexing strategies and analysis available in TechNet SQL Server Video Archive.

OneToManyCascadeDeleteConvention
ManyToManyCascadeDeleteConvention

EF creates foreign keys and automatically sets cascade delete. Just imagine if you have a Product entity that has a foreign key to Category entity, and someone accidentally delete a category record. SQL Server immediately deletes all product records belonging to that category. That’s a rather dangerous option, and I suppose it could be turned on with great precaution and only in those cases where it really needed.

My opinion is to turn them off, and if you really need them, let’s revive them. To do this you need to find your database context class, it’s derived from DbContext class, and place some lines of code there;

  • Add a reference to Conventions namespace

  • Override OnModelCreating method

 

Code First. Creating Relationships Between Entities

When you create a one-to-many relationship between two entities in Code First Entity Framework model, you need to set “links” in both entities. In my case they are Client and Phone entities.

During the migration EF will create a field Client_Id (entity name + field name of the primary key) and a foreign key.
Phone table - Client_Id

I don’t like auto-generated names for table fields and I want to get a precise control over it. For example, I want to give another name for a foreign key field, say, ClientId without underscore. EF allows to do this with a couple of lines of code. You need to add a ClientId property and a ForeignKey attribute pointing to ClientId.

To be frankly, that’s my favorite way to declare a relationship.

Another case is when you need to create two or more relationships between the same two entities. At this time you must set an InverseProperty attribute in Client class, or you’ll get 4 fields and foreign keys instead of 2 required.

Now we get the following structure. Please pay attention that auto-generated fields Client_Id and SecondClient_Id are null.

Phone table - 2 autogenerated relationships

When I placed ForeignKey attributes in Phone class to replace the auto-generated names with my own, I’ve got an error on Update-Database phase.

Introducing FOREIGN KEY constraint 'FK_dbo.Phones_dbo.Clients_OwnerClientId' on table 'Phones' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

That’s because EF tries to make the FK fields not null and use a cascade delete convention. Open the database migration .cs file and look at these lines:

If you change the declaration of FK fields to nullable type (i.e. int?), everything will be OK. The declaration of foreign key in Phone class

and the resulting database structure:

Phone table - 2 relationships

The other option is to change manually the database migration .cs file and set cascadeDelete to false.

Code First. Column Order Annotation

Working with Code First Entity Model, you can set the order of columns in database table. It’s very easy – just add an annotation before property declaration.

But there are some interesting things about this annotation.
1. Order numbers are relative, they are not the exact places of the fields inside the database table. For example, you can set 1 for colA, 10 for colB, 5 for colC, and you will get the following order: colA, colC, colB.
2. If you don’t set order numbers for all columns, Visual Studio will place ordered columns first (according to order numbers), then the rest columns (without order numbers).
3. It works only at the moment when Visual Studio creates a table. If you change the annotation later, migration does not generate code for table recreation.

Auto-generated Guid in Entity Framework Code First

When you create an entity in Code First model, you might need a Guid field. In addition you might want SQL Server should auto-generate values for that field. This can be done with an annotation as following:

After migration you will find that ClientGuid field has a default value newsequentialid().

Auto-generated Guid in Code First

The most curious thing is the default value in SQL Server database could be created only at the creation of field itself. If you create a field without an identity option first, then you try to add annotation, SQL Server does not add a default value.

In that case you can recreate database (but it’s not appropriate in many cases) or to add a class constructor to explicitly assign new Guid value as following: