Understanding EF under the hood. Include vs Joins

Question

I am working with Entity Framework for a while but still have some "black holes" with how it is working under the hood. Even after reading couple of articles I'm not sure that my understanding is correct.

---

Lets start with questions:

• How EF Include is working under the hood?
• What are the differences between Join and Include?
• Performance issues with Include (lazy loading vs eager loading, generated SQL queries, etc.)?
• When I should use Join instead Include and vice versa?

Answer 1

When querying EF through linq or lambda expressions, you only need join statements if the underlying schema doesn't provide FKs, and thus you don't have navigation properties on the objects.

On the other side, include (eager loading) and lazy loading can only work if there are FKs, because it uses the navigation properties.

The underlying sql in both cases will use joins (as sql has no "navigation property" concept).

As for performance, it depends on situations. Lazy loading vs Eager loading (so in FK scenario) can be a difficult choice.

I usually go with lazy loading, useful when you have a large main result, but you need "join" data only of a few items of the whole resultset.

If you know ahead that you'll need the join data of the whole resultset, eager loading could be better for performance. I'd suggest to experiment and see for yourself.