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.

One Reply to “Querying Entity Model. Part 1 – Inner Join”

Leave a Reply

Your email address will not be published. Required fields are marked *