Why is Entity Framework so slow with join statemen

Question

I have an EF 6 query that has about 6 linq join statements in it. When I step through my code with the debugger, I can see that the query statement takes roughly 6 seconds to run. With a SQL trace, I can tell that the actual query, which looks like I would expect it to, takes 0 ms and returns 0 rows. I removed the join statements 1 by one. With each join statement removed, the EF statement took 1 second less to execute. The select portion of the query never changed, only the number of joins.

My question is, what is happening? What is EF doing that it requires so much time to process this query? Oddly, it looks like most of this time is actually spent after the SQL execution is already finished, so the time is not generating the query, but whatever EF is doing afterward.

Answer 1

Are you utilizing lazy-loading and initializing each join individually? If you are calling 6 joins on 6 large data sets that are returning decently sized objects, it will be slow. It is probably taking a long time because the data-set you are returning might be very large and you are doing that 6 times. I've had this problem with huge data-sets and tables on web pages that filter with ajax. I was able to improve performance a lot by utilizing lazy-loading and building all my quires together and then only initializing it once. I'm guessing eventually after the 6 joins you want one dataset that i comprised of all those joins. You want to build those joins into one single query and have it only fire against your DB once so EF only has to build the objects into your code once.