4

I have an Entity Framework 4 model, with 2 entities containing many-to-many relationship, so 3 tables, [Q], [P] and [Q2P]-cross table. Running code like:

context.Q.Include("P");

Results in long time wait (I waited like 5 mins then aborted it). Then I checked SQL generated 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 suprise, WTF is this? The usual many-to-many SQL query 

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

executes in less than 1ms, while EF query executes forever.

Alex Burtsev
  • 12,418
  • 8
  • 60
  • 87
  • Please vote [here](http://connect.microsoft.com/VisualStudio/feedback/details/522369/minimize-entity-framework-query-weight "Minimize Entity Framework query weight") – Shimmy Weitzhandler Dec 25 '10 at 18:16
  • I was trying to load entities using ExecuteStoreQuery() with native SQL but it doesn't load related entities (navigation properties)... Looks like it can materialize only one EntityType from DbReader – Alex Burtsev Dec 25 '10 at 23:54

2 Answers2

3

Yep, that's not a secret that it takes long, please vote on the connection I opened about a year ago.

However, 5 minutes is something it's definitely not supposed to take.

Try separating the execution from the query generation, and use ToTraceString to see how long does it take to determine query generation time.

Eager-loading is not the great deal in current version, they said they're planning toreduce the performance cost in future.

Anyway, what you could do is use Stored Procedures or create your own ObjectQueries.

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

Shimmy Weitzhandler
  • 101,809
  • 122
  • 424
  • 632
  • 1
    How to write object Query or Entity SQL query against cross table when EF doesn't expose it as entity type? – Alex Burtsev Dec 25 '10 at 18:43
  • See http://stackoverflow.com/questions/750719/entity-sql-for-a-many-to-many-relationship/813739#813739 and http://stackoverflow.com/questions/553918/entity-framework-and-many-to-many-queries-unusable/554149#554149 to get the idea. – Shimmy Weitzhandler Dec 25 '10 at 18:47
2

I have switched to Nhibernate.

Alex Burtsev
  • 12,418
  • 8
  • 60
  • 87