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.
|
1 |
public string CountryCode { get; set; } |
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
|
1 2 3 4 5 6 |
var query41 = ctx.Clients .Join(ctx.Phones, client => new { Id = client.Id, client.CountryCode }, phone => new { Id = phone.ClientId, phone.CountryCode }, (client, phone) => new { client.Name, phone.Number, client.CountryCode } ); |
2. Join on multiple fields using cross join and where
|
1 2 3 4 5 6 |
var query42 = ctx.Clients .SelectMany( client => ctx.Phones.Select(phone => new { client, phone }) ) .Where(x => x.client.Id == x.phone.ClientId && x.client.CountryCode == x.phone.CountryCode) .Select(x => new { x.client.Name, x.phone.Number, x.client.CountryCode }); |
And again Entity Framework shows an excellent result. It generates the same T-SQL code with inner join.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent2].[Number] AS [Number], [Extent1].[CountryCode] AS [CountryCode] FROM [dbo].[Clients] AS [Extent1] INNER JOIN [dbo].[Phones] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[ClientId]) AND (([Extent1].[CountryCode] = [Extent2].[CountryCode]) OR (([Extent1].[CountryCode] IS NULL) AND ([Extent2].[CountryCode] IS NULL)) ) |