Querying Entity Model. Part 16 – WHERE IN With Concrete Values – Andrey Zavadskiy

In one of my recent posts Querying Entity Model. Part 5 – Implementing WHERE IN Analogue I described how Entity Framework implements Contains() method. In that case I had one LINQ query and one T-SQL query.

As you know LINQ could assemble multiple statements and Entity Framework would generate one T-SQL query. I mentioned it in my post Querying Entity Model. Part 14 – Dynamic Where Clause. But you can break this behaivor (accidentally or by intention) if you would save one of the intermediate query results to a .NET memory structure like List.

Let’s take a look at this example.

var query161a = ctx.Phones
    .Where(p => p.CountryCode == "US")
    .Select(p => p.ClientId);

var query161b = ctx.Clients
    .Where(c => query161a.Contains(c.Id));

var query161a = ctx.Phones

.Where(p => p.CountryCode == "US")

.Select(p => p.ClientId);

var query161b = ctx.Clients

.Where(c => query161a.Contains(c.Id));

EF would generate one T-SQL query when you will get the results from query161b.

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[CountryCode] AS [CountryCode], 
    [Extent1].[Timestamp] AS [Timestamp]
    FROM [dbo].[Clients] AS [Extent1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Phones] AS [Extent2]
        WHERE (N'US' = [Extent2].[CountryCode]) AND ([Extent2].[ClientId] = [Extent1].[Id])
    )

SELECT

[Extent1].[Id] AS [Id],

[Extent1].[Name] AS [Name],

[Extent1].[CountryCode] AS [CountryCode],

[Extent1].[Timestamp] AS [Timestamp]

FROM [dbo].[Clients] AS [Extent1]

WHERE EXISTS (SELECT

1 AS [C1]

FROM [dbo].[Phones] AS [Extent2]

WHERE (N'US' = [Extent2].[CountryCode]) AND ([Extent2].[ClientId] = [Extent1].[Id])

)

But if you cast first LINQ query to List (simply add .ToList() at the end), EF will generate two separate T-SQL queries.

var query161a = ctx.Phones
    .Where(p => p.CountryCode == "US")
    .Select(p => p.ClientId)
    .ToList();

var query161a = ctx.Phones

.Where(p => p.CountryCode == "US")

.Select(p => p.ClientId)

.ToList();

SELECT 
    [Extent1].[ClientId] AS [ClientId]
    FROM [dbo].[Phones] AS [Extent1]
    WHERE N'US' = [Extent1].[CountryCode]

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[CountryCode] AS [CountryCode], 
    [Extent1].[Timestamp] AS [Timestamp]
    FROM [dbo].[Clients] AS [Extent1]
    WHERE [Extent1].[Id] IN (1, 3, 4)

SELECT

[Extent1].[ClientId] AS [ClientId]

FROM [dbo].[Phones] AS [Extent1]

WHERE N'US' = [Extent1].[CountryCode]

SELECT

[Extent1].[Id] AS [Id],

[Extent1].[Name] AS [Name],

[Extent1].[CountryCode] AS [CountryCode],

[Extent1].[Timestamp] AS [Timestamp]

FROM [dbo].[Clients] AS [Extent1]

WHERE [Extent1].[Id] IN (1, 3, 4)

The most important thing is EF had generated the second query with concrete values that were passed from the first list. If the first query extracts another set of values, EF would generate another T-SQL query. In result SQL Server needs to compile the second query and save its execution plan in procedure cache.

Leave a Reply Cancel reply