linq – Entity Framework 4 generated queries are joining full tables – Education Career Blog

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 !!

,

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

Leave a Comment