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)
|
1 2 3 4 5 6 7 8 9 10 |
var query21 = ctx.Clients .GroupJoin(ctx.Phones, client => client.Id, phone => phone.ClientId, (client, phone) => new { client, phone } ) .SelectMany( x => x.phone.DefaultIfEmpty(), (x, y) => new { x.client.Name, y.Number } ); |
2. Left outer join – rearranged (DefaultIfEmpty inside GroupJoin)
|
1 2 3 4 5 6 7 8 9 |
var query22 = ctx.Clients .GroupJoin(ctx.Phones, client => client.Id, phone => phone.ClientId, (client, phone) => new { client, phone = phone.DefaultIfEmpty() } ) .SelectMany( x => x.phone.Select(y => new { x.client.Name, y.Number }) ); |
3. Left outer join using SelectMany
|
1 2 3 4 5 |
var query23 = ctx.Clients .SelectMany( client => ctx.Phones.Where(phone => client.Id == phone.ClientId).DefaultIfEmpty(), (client, phone) => new { client.Name, phone.Number } ); |
As I mentioned earlier, all these LINQ queries generate the same T-SQL code:
|
1 2 3 4 5 6 |
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent2].[Number] AS [Number] FROM [dbo].[Clients] AS [Extent1] LEFT OUTER JOIN [dbo].[Phones] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ClientId] |