Querying Entity Model. Part 1 – Inner Join – Andrey Zavadskiy – SQL and .NET developer, trainer, speaker

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):

public class Client
{
  [Key]
  public int Id { get; set; }
  
  public string Name { get; set; }
  
  [InverseProperty("Client")]
  public ICollection Phones { get; set; }
}

public class Phone
{
  [Key]
  public int Id { get; set; }
  
  public string Number { get; set; }

  public int ClientId { get; set; }
  [ForeignKey("ClientId")]
  public virtual Client Client { get; set; }
}

public class Client

{

[Key]

public int Id { get; set; }

public string Name { get; set; }

[InverseProperty("Client")]

public ICollection Phones { get; set; }

}

public class Phone

{

[Key]

public int Id { get; set; }

public string Number { get; set; }

public int ClientId { get; set; }

[ForeignKey("ClientId")]

public virtual Client Client { get; set; }

}

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

var query1 = ctx.Clients
  .Join(ctx.Phones,
    client => client,
    phone => phone.Client,
    (client, phone) => new { client.Name, phone.Number }
  );

var query1 = ctx.Clients

.Join(ctx.Phones,

client => client,

phone => phone.Client,

(client, phone) => new { client.Name, phone.Number }

);

The resulting T-SQL query will be:

SELECT 
  [Extent1].[Id] AS [Id], 
  [Extent1].[Name] AS [Name], 
  [Extent2].[Number] AS [Number]
  FROM  [dbo].[Clients] AS [Extent1]
  INNER JOIN [dbo].[Phones] AS [Extent2] ON  EXISTS (SELECT 
    1 AS [C1]
    FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
    INNER JOIN [dbo].[Clients] AS [Extent3] ON 1 = 1
    WHERE ([Extent2].[ClientId] = [Extent3].[Id]) AND ([Extent1].[Id] = [Extent3].[Id])
  )

SELECT

[Extent1].[Id] AS [Id],

[Extent1].[Name] AS [Name],

[Extent2].[Number] AS [Number]

FROM [dbo].[Clients] AS [Extent1]

INNER JOIN [dbo].[Phones] AS [Extent2] ON EXISTS (SELECT

1 AS [C1]

FROM ( SELECT 1 AS X ) AS [SingleRowTable1]

INNER JOIN [dbo].[Clients] AS [Extent3] ON 1 = 1

WHERE ([Extent2].[ClientId] = [Extent3].[Id]) AND ([Extent1].[Id] = [Extent3].[Id])

)

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.

var query2 = ctx.Clients
  .Join(ctx.Phones,
    client => client.Id,
    phone => phone.ClientId,
    (client, phone) => new { client.Name, phone.Number }
  );

var query2 = ctx.Clients

.Join(ctx.Phones,

client => client.Id,

phone => phone.ClientId,

(client, phone) => new { client.Name, phone.Number }

);

The resulting T-SQL query will be:

SELECT 
  [Extent1].[Id] AS [Id], 
  [Extent1].[Name] AS [Name], 
  [Extent2].[Number] AS [Number]
  FROM  [dbo].[Clients] AS [Extent1]
  INNER JOIN [dbo].[Phones] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ClientId]

SELECT

[Extent1].[Id] AS [Id],

[Extent1].[Name] AS [Name],

[Extent2].[Number] AS [Number]

FROM [dbo].[Clients] AS [Extent1]

INNER JOIN [dbo].[Phones] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ClientId]

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.

var query3 = ctx.Phones
  .Select(ph => new { ph.Client.Name, ph.Number });

1 2	var query3 = ctx.Phones .Select(ph => new { ph.Client.Name, ph.Number });

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.

var query4 = (ctx.Set() as IQueryable)
  .Join((ctx.Set() as IQueryable),
    client => client.Id,
    phone => phone.ClientId,
    (client, phone) => new { client.Name, phone.Number }
  );

var query4 = (ctx.Set() as IQueryable)

.Join((ctx.Set() as IQueryable),

client => client.Id,

phone => phone.ClientId,

(client, phone) => new { client.Name, phone.Number }

);

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

Querying Entity Model. Part 1 – Inner Join

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

Leave a Reply Cancel reply