Entity Framework - Efficient eager loading with long chains of dependent objects?

Question

I have an issue, which I assume many professional developers will run into. My workplace has adopted entity framework. We use it, and love it. However, we seem to have run into a very frustrating limitation.

Let's assume you have an object chain as such

A -> B -> C -> D

We are professionals, so these objects have a lot of data, and there is a lot of them in their respective database tables. It seems EF has a terrible time loading anything past object B. The SQL queries it generates are really inefficient and not good. The call would be something like

context.objects.include("bObjectName.cObjectName.dObjectName").FirstOrDefault(x => x.PK == somePK);

We have gotten around this by explicitly loading objects past that second level with the .Load() command. This works well for single objects. However, when we talk about a collection of objects, we start to run into issues with .Load().

Firstly, there does not seems to be a way to keep proxy tracking of objects in a collection without the virtual keyword. This makes sense because it needs to overwrite the get and set functions. However, this enables lazy loading, and .Load() doesn't map entities when lazy loading is enabled. I find this to be somewhat odd myself. If you remove the virtual keyword, .Load() does automatically link loaded objects to the relevant objects in context.

So here is the crux of my issue. I want proxy tracking, but also want .Load() to map the navigation properties for me. None of this would be an issue if EF could generate good queries. I understand why it can't, it has to be a one show fits all kind of thing.

So to load the third tier of objects I might create a loader function in my service layer that takes all the primary keys of the second tier of objects, and then calls a .Load() on them. Does anyone have a solution for this? It seems like EF7, or Core 1.0 solves this by:

1. Removing lazy loading entirely, which we could shut off as well, but it would break a lot of older code.
2. Adding a new "ThenInclude" feature, which supposedly increases the efficiency of chaining includes massively.

If turning off lazy loading is the answer, that's fine, I just want to exhaust all options before I waste a lot of time redeveloping a huge webapps worth of service calls. Does anyone have any ideas? I'm willing to give anything a shot. We are using EF6.

EDIT: It seems the answer is to shut off lazy loading at a context level, or upgrade to EF7. I'll change this if anyone else manages to find a solution whereby you can have proxy tracking with forced eager loading on a single object for EF6.

Answer 1

You're absolutely right about chained .Include() statements, the performance documentation warns against chaining more than 3 as each .Include() adds an outer join or union. I didn't know about ThenInclude but it sounds like a gamechanger!

If you keep your virtual navigation properties but turn off Lazy Loading on the DbContext

 context.ObjectContext().ContextOptions.LazyLoadingEnabled = false;

then (as long as Change Tracking is enabled) you can do the following:

var a = context.aObjectNames.First(x=> x.id == whatever);

context.bObjectNames.Where(x=> x.aId == a.Id).Load()

This should populate a.bObjects