Entity Framework 4 generated queries join complete tables

Question

I have two objects: Master and Details. When I request them, the final database query is as follows:

SELECT [Extent2]."needed columns listed here", [Extent1]."needed columns listed here"
FROM (SELECT * [Details]."all columns listed here"...
     FROM [dbo].[Details] AS [Details]) AS [Extent1]
LEFT OUTER JOIN [dbo].[Master] AS [Extent2] ON [Extent1].[key] = [Extent2].[key]
WHERE [Extent1].[filterColumn] = @p__linq__0

My question is: why is the filter not in the internal query? How can I get this request? I have tried many expressions of EF and Linq.

I need something like:

SELECT <anything needed>
  FROM Master LEFT JOIN Details ON Master.key = Details.Key
 WHERE filterColumn = @param

I have a full sequential scan in both tables, and in my production environment I have millions of rows in each table.

Thank you so much!

Answer 1

Sometimes the Entity Framework does not create a better query. You can do several of the following to optimize.

• Change linq statement (test with LINQPad )
• proc proc
• ,