SQL Server and LINQ both have some commands that operates on sets like Union, Intersect and Except. I’ve investigated the behavor of Union in Entity Framework earlier in my post Querying Entity Model. Part 15 – Union. Now let’s look at intersect and except.
I want to see the intersection between two sets of country codes taken from Clients and Phones tables (I use the same simple EF model through all the Querying Entity Model series).
|
1 2 3 4 |
var query211 = ctx.Clients.Select(c => c.CountryCode) .Intersect( ctx.Phones.Select(p => p.CountryCode) ); |
Wow! Entity Framework uses exactly INTERSECT operator. Well done.
|
1 2 3 4 5 6 7 8 9 |
SELECT [Intersect1].[CountryCode] AS [C1] FROM (SELECT [Extent1].[CountryCode] AS [CountryCode] FROM [dbo].[Clients] AS [Extent1] INTERSECT SELECT [Extent2].[CountryCode] AS [CountryCode] FROM [dbo].[Phones] AS [Extent2]) AS [Intersect1] |
Except case is similar to intersect one.
|
1 2 3 4 |
var query212 = ctx.Clients.Select(c => c.CountryCode) .Except( ctx.Phones.Select(p => p.CountryCode) ); |
|
1 2 3 4 5 6 7 8 9 |
SELECT [Except1].[CountryCode] AS [C1] FROM (SELECT [Extent1].[CountryCode] AS [CountryCode] FROM [dbo].[Clients] AS [Extent1] EXCEPT SELECT [Extent2].[CountryCode] AS [CountryCode] FROM [dbo].[Phones] AS [Extent2]) AS [Except1] |
I used a collection with one field, but EF is capable to operate on collections with many fields. The only thing you should care about is to intersect/except the collections of the same type.