Entity Framework many-to-many relationships include extremely slow

Question

I have an Entity Framework 4 model with two objects containing many-to-many relationships, so 3 tables, [Q], [P] and [Q2P] is a scrolling table. Running code, for example:

context.Q.Include("P"); 

Results for a long time (I waited like 5 minutes and then interrupted it). Then I checked the generated SQL and found this:

 SELECT * FROM ( SELECT * FROM [Q] AS [Extent1] LEFT OUTER JOIN (SELECT *, CASE WHEN ([Join1].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2] FROM [Q_P] AS [Extent2] INNER JOIN [P] AS [Extent3] ON [Extent3].[Id] = [Extent2].[Id] ) AS [Join1] ON [Extent1].[Id] = [Join1].[Id] ) AS [Project1] ORDER BY [Project1].[Id] ASC, [Project1].[C2] ASC 

I can’t hide my surprise, WTF it? Plain many-to-many SQL query

 select * from [q2p] join [q] on qId=q.Id join [p] on pId=p.Id 

It is executed in less than 1 ms, and the EF request is executed forever.

Answer 1

I switched to Nhibernate.

Answer 2

Yes, it’s no secret that it lasts a long time, vote for the connection that I opened about a year ago.

However, 5 minutes is something that is definitely not intended.

Try to separate execution from query generation and use ToTraceString to find out how long it takes to determine when the query was generated.

Eager-loading in the current version is not so much, they said they plan to reduce the cost of execution in the future.

In any case, what you can do is use stored procedures or create your own ObjectQueries.

Cm:
- http://msdn.microsoft.com/en-us/library/bb896241.aspx
- http://msdn.microsoft.com/en-us/library/bb896238.aspx