CODEWITHSUNDEEP
sqlentity-frameworknhibernate
mynkow
mynkow

Reputation: 4548

EF: What is the SQL output when using Contains in LINQ query?

In NHibernate when we execute a query like the one below the SQL generated for it will contains parameters for each element in the collection. If the query is executed against MSSQL Server and there are 2k or more elements in the collection you will get an error because max parameters allowed by the sql engine is 2k.

var bankaccounts = from b in this.unitOfWork.BankAccounts
                    where command.Ids.Contains(b.Id) // command.ids is an array with id's
                    select b;

Sorry for being lazy to test this myself with EntityFramework but I see other developers writing such code: EF cannot delete child object because of associationset

The question is what SQL will be generated from EF for this kind of queries?

Upvotes: 1

Views: 426

Answers (2)

Ben Gulapa
Ben Gulapa

Reputation: 1619

With this query,

    var ids = context.Countries
        .Take(10).Select(c => c.CountryId).ToList();

    var offices = context.Offices.Where(o => ids.Contains(o.CountryId))
        .ToList();

it will generate this SQL:

SELECT 
[Extent1].[OfficeId] AS [OfficeId], 
[Extent1].[CountryId] AS [CountryId],
[Extent1].[OfficeName] AS [OfficeName]
FROM [dbo].[Office] AS [Extent1]
WHERE [Extent1].[CountryId] IN (3,4,5,6,7,8,9,10,11,12)

Upvotes: 2

Martijn
Martijn

Reputation: 24799

I've watched SQL profiler for you. This is what happens in Entity Framework (I quess this is wat NHibernate also will product, but I'm not sure..):

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[AccountNo] AS [AccountNo], 
[Extent1].[Name] AS [Name], 
[Extent1].[Description] AS [Description], 
[Extent1].[IBAN] AS [IBAN], 
[Extent1].[IsActive] AS [IsActive], 
[Extent1].[Customer_Id] AS [Customer_Id]
FROM [dbo].[BankAccounts] AS [Extent1]
WHERE [Extent1].[Id] IN (4,5,6,7,9)

As you can see, it will become a WHERE .. IN ([the contains values])

Upvotes: 2

Related Questions