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.
|
1 2 3 4 5 6 7 |
var query61 = ctx.Clients .Where(client => ctx.Phones .Where(phone => client.Id == phone.ClientId && client.CountryCode == phone.CountryCode) .Any() ) .Select(client => new { client.Name, client.CountryCode }); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[CountryCode] AS [CountryCode] FROM [dbo].[Clients] AS [Extent1] WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[Phones] AS [Extent2] WHERE ([Extent1].[Id] = [Extent2].[ClientId]) AND (([Extent1].[CountryCode] = [Extent2].[CountryCode]) OR (([Extent1].[CountryCode] IS NULL) AND ([Extent2].[CountryCode] IS NULL)) ) ) |
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.