Category: Querying Entity Framework 6

This time I’ll show how we can select data from one entity when there’s a dependency on the other entity based on two or more fields. This Entity Framework LINQ query is the equivalent of the SQL query in the form of SELECT … WHERE EXISTS.

I use the same Clients and Phones entities, and I want to find all clients that have phone number in their home countries, i.e. if John is from USA and he has a U.S. phone, not Canadian or Mexican number.

The LINQ query has lambda syntax and is quite simple.

I use Any() function to get a boolean result – if there’s a record that would satisfy the conditions in Where clause.

T-SQL query will be the following:

Yeah, Entity Framework has made a SELECT … WHERE EXISTS command with two conditions on ClientId and CountryCode, but…
Do you notice that T-SQL query has an additional comparison where both Codes are null? It’s a feature of Entity Framework I’ll tell about in my next post Comparison: Entity Framework vs C#. When Equality Operators Are Not Equal.

Working with SQL databases, you could encounter some cases where you write something like:

SELECT ...
FROM ...
WHERE ... IN (subquery)


This type of queries can be implemented in Entity Framework model. We need to place a subquery in Where clause.
My example uses the same Client and Phone entities from the first post about Querying Entity Model. The LINQ query is quite simple, and it has lambda syntax.

T-SQL query generated by Entity Framework uses EXISTS command instead of WHERE … IN. But the result is the same comparing with the alternative query.

If you want to get a WHERE … NOT IN query, place negation operator ! in Where clause right before the subquery:

[UPDATE 2016-08-31]
You can use another form of writing the subquery evaluation with Any() method.

Entity Framework generates the same T-SQL query.

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.

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:

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:

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.

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.