Entity Framework 4 generated queries are joining full tables

Question

Hello, I have two entities: Master and Details. When I query them, the resulting query to database is:

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 not the filter is in the inner query? How can I get this query? I've tried a lot of EF and Linq expressions.

What I need is something like:

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

I'm having a full sequential scan in both tables, and in my production environment, I have milions of rows in each table.

Thanks a lot !!

Answer 1

Sometimes The entity Framework does not produce the best query. You can do a few of the following to optimize.

1. Modify the linq statement (test with LINQPad)
2. Create a stored proc and map the stored proc to return an entity
3. Create a view that handles the join and map the view to a new entity