EntityFramework include vs join performance

Question

I'd like to know, when using entity framework, which one yields better performance?

I've read that if you have foreign relationships between your entites, it's preferred to use include over join.

If I'm just retrieving 1 row with records from 2 different entites (with foreign key relationship), does it make a difference if I were to use include over join?

Does the amount of records I'm fetching back affect the performance between join and include?

Answer 1

I would highly recommend you to do some profiling.

Sometimes eager loading (.Include) is good, sometimes not ;-) Just have a look a the generated T-SQL in the profiler and you'll know why! Have a look at the execution plans as well.

Try to .Include some one-to-many relations in a query and look at the amount of data that will be retrieved: data will be multiplied by the number of rows in all included tables. A lot of duplicated data might be pulled over the wire causing high bandwith consumption and performance issue.

Remember that sometimes it's even better to perform simple and fast separate queries for related data (without lazy loading). It might be more efficient to query, let's say Customers and then query Orders separately and let EF join them itself automatically.

From my own experience, having a strong DBA team looking at all the queries generated by EF, I advised developers not using .Include anymore ;-)