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.
1 2 3 |
var query51 = ctx.Clients .Where(client => ctx.Phones.Select(phone => phone.ClientId).Contains(client.Id)) .Select(client => new { client.Name }); |
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.
1 2 3 4 5 6 7 8 9 |
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[Clients] AS [Extent1] WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[Phones] AS [Extent2] WHERE [Extent2].[ClientId] = [Extent1].[Id] ) |
If you want to get a WHERE … NOT IN query, place negation operator ! in Where clause right before the subquery:
1 |
.Where(client => !ctx.Phones.Select(... |
[UPDATE 2016-08-31]
You can use another form of writing the subquery evaluation with Any() method.
1 2 3 |
var query52 = ctx.Clients .Where(client => ctx.Phones.Any(phone => phone.ClientId == client.Id)) .Select(client => new { client.Name, client.CountryCode }); |
Entity Framework generates the same T-SQL query.