CODEWITHSUNDEEP
sqlentity-frameworkentity-framework-4
BennyM
BennyM

Reputation: 2856

Why does EF generate this sql when querying reference property

When using the AdventureWorks database and issuing this query:

   AdventureWorksEntities entities = new AdventureWorksEntities();
   entities.Contacts.Where(x => x.FirstName == "A" 
                               || x.FirstName == "B" 
                               || x.FirstName == "C")
                     .ToList();

Will be translated to this sql, which is the best it can:

//ommited for brevity
FROM [Person].[Contact] AS [Extent1]
WHERE [Extent1].[FirstName] IN (N'A',N'B',N'C')

However when I issue this query:

entities.Employee.Where(x => x.Contact.FirstName == "A" 
                             || x.Contact.FirstName == "B" 
                             || x.Contact.FirstName == "C")
                .ToList();

I get this SQL:

//ommited for brevity
FROM   [HumanResources].[Employee] AS [Extent1]
INNER JOIN [Person].[Contact] AS [Extent2] ON [Extent1].[ContactID] = [Extent2].[ContactID]
LEFT OUTER JOIN [Person].[Contact] AS [Extent3] ON [Extent1].[ContactID] = [Extent3].[ContactID]
WHERE [Extent2].[FirstName] = N'A' OR [Extent3].[FirstName] IN (N'B',N'C')

Why am I getting an inner and outer join and is EF splitting the where across both of them?

Of Note using contains creates the same SQL:

var names = new List<string>{"A", "B", "C"};
entities.Employee.Where(x => names.Contains(x.Contact.FirstName)).ToList();

EDIT: So it appears to be a EF bug, I've accepted the answer which provided a work around

EDIT: Opened connect issue, it's located here

Upvotes: 8

Views: 282

Answers (2)

Tim Mahy
Tim Mahy

Reputation: 1319

Write a Stored Procedure which accepts a TVP as input parameter and let EF materialize the results from the SP :)

Upvotes: 1

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65441

Looks like a bug in EF.

I think that the problems is related to you checking against a field in a sub table. EF does not see that all the OR clauses refer to the same table and the same field in that that table. Therefore, it creates that complicated join.

Try rewriting it to use CONTAINS instead of OR.

Using contains() in LINQ to SQL

Upvotes: 0

Related Questions