CODEWITHSUNDEEP
sql-serverentity-framework
h.ralitera
h.ralitera

Reputation: 31

Entity Framework 4 and T-SQL Query results don't match

After three years on using Entity Framework v.X, today I've seen a strange behavior from EF4. The fact is that :

On the database AdventureWork, I execute the following command :

var query = (ObjectQuery) context.Products.Select(p => p.ProductDocuments.Where(c => c.ProductID == p.ProductID));
Console.WriteLine(query.ToTraceString());

The ToTraceSstring() shows the real query that will be executed :

SELECT
[Project1].[ProductID] AS [ProductID],
[Project1].[C1] AS [C1],
[Project1].[ProductID1] AS [ProductID1],
[Project1].[DocumentID] AS [DocumentID],
[Project1].[ModifiedDate] AS [ModifiedDate]
FROM ( SELECT
        [Extent1].[ProductID] AS [ProductID],
        [Extent2].[ProductID] AS [ProductID1],
        [Extent2].[DocumentID] AS [DocumentID],
        [Extent2].[ModifiedDate] AS [ModifiedDate],
        CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1
END AS [C1]
        FROM  [Production].[Product] AS [Extent1]
        LEFT OUTER JOIN [Production].[ProductDocument] AS [Extent2] ON ([Extent1].[ProductID] = [Extent2].[ProductID]) AND ([Extent2].[ProductID] = [Extent1].[ProductID])
)  AS [Project1]
ORDER BY [Project1].[ProductID] ASC, [Project1].[C1] ASC

Once executed from SSMS, it displays 505 lines.

But when I try to execute from EF :

var query=  context.Products.Select(p => p.ProductDocuments.Where(c => c.ProductID == p.ProductID));
Console.WriteLine(query.Count());

it will only return 504 lines.

After comparing the result, it seems that there's two rows in the ProductDocument having the same ProductID = 506, which is entirely normal. Those duplicate lines are only retrieved once instead of twice as expected.

Any idea for that issue?

Upvotes: 1

Views: 230

Answers (2)

devenva
devenva

Reputation: 11

The SQL is correct. And the LEFT OUTER JOIN is expected in that request.

But the question is now why the queries results are not the same. is there a dummy interpretation from the ToTraceString() ? I use now reflector to have a closest look on how the navigation is when reading the IQueryable

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89721

You are correct, in that a naive interpretation of your lambda expressions would imply you are simply asking for:

SELECT Products.*
FROM Products
INNER JOIN ProductDocuments
    ON ProductDocuments.ProductID = Products.ProductID

and are instead getting:

SELECT DISTINCT Products.*
FROM Products
INNER JOIN ProductDocuments
    ON ProductDocuments.ProductID = Products.ProductID

However, are you sure that SQL you posted is the result of your code, because the SQL is returning parts of the document, while your code is requesting Product entities? And, of course, for a set of Products, returning duplicate entities would be pointless and incorrect.

Upvotes: 1

Related Questions