huge query generation in Code-First by calling List.Contains in where Clause



This is very strange behavior. I have three entities, and a simple query:
var a = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 };
using (var context = new Sales_ReleaseEntities())
context.TreasuryDocumentDetails.Where(x => a.Contains(x.TreasuryDocument.Customer.PersonID)).ToList();

This query generates a normal query, when used with an Edmx model. However, with the code-first approach it generates a large query which joins to Customers table 19 times and uses each on the joins to check for a single value in the int[] array.
A complete working project is attached, which includes both models, the database creation script, and the query results.

While this query is not huge by itself, we have situations in the actual project in which the generated query is several thousand lines long. We even have a query that fails to translate to sql in the first step!

How can we avoid this problem?

file attachments

Closed Jan 21, 2014 at 7:04 PM by BriceLambson


AlirezaHaghshenas wrote Nov 10, 2012 at 2:46 PM

I found a cue. There is a ForeignKeyProperty in TreasuryDocumentDetail, named TreasuryDocumentID. in the Code first approach, it is int?. If I change it to int, the problem will be solved.

Unfortunately, In the actual project I cannot do this, as some of the foreign-keys are actually nullable.

BTW, why such a difference exists between required and not required FKs?

RoMiller wrote Nov 29, 2012 at 11:56 PM

EF Team Triage: We should investigate this and see if the fix is easy. If so, we'll take it in EF6.

BriceLambson wrote Jan 20, 2014 at 8:18 PM

Fixed in changeset a2ff5155c3ecde98e990864efe0112d8fc250923