Cant add Includes (eager-loading) to correct place

Question

I'm trying to speed up some of my db tier functions by adding the proper Include statements to force eager loading and reduce the number of queries I make to the database.

However in 90% of the cases I run into the problem that I'm not at the correct "starting location" to enter the includes I want.

The simplest example I could come up with:

I have Department, Course and Student entities with many-to-many relationships between them (some courses below to multiple departments).

Now I have a function

GetMasterCourses(Department dep) which does something like

return dep.Courses.Where(c => c.level == "Master")

The question is: how do I tell EF to load all students associated with each queried course?

The only solutions I have found are things like:

courseIDs = dep.Courses
  .Where(c => c.level == "Master").Select(c => c.courseID)
dbcontext.Courses
  .Include("Students")
  .Where(c => courseIDs.Contains(c.courseID) and c.level == Master)

This seems rather silly to have to do such a workaround just to be able to specify the correct Include. I have looked at many examples of Include and searched many questions on stackoverflow but can't really find anyone with this problem, eventhough it seems a quite common problem.

So your input is a `Department` that is (I assume) not part of a context. What is your expected return type? — D Stanley, Jun 26 '13 at 17:34
Are you eager-loading the `Courses` and `Students` when you load the `Department`? — D Stanley, Jun 26 '13 at 17:35
The department is an argument of a function and is retrieved for example by `dbcontext.Departments.First()`. The courses and students are indeed not eager loaded during this. The expected return type would be IQueryable, so a set of courses which have their associated student collection eager loaded. — Wannes, Jun 27 '13 at 09:04

score 1 · Answer 1 · answered Jun 26 '13 at 17:42

1

I don't see why you are selecting IDs. It looks like you could just:

var courses = dep.Courses
  .Include(i => i.Students)
  .Where(c => c.level == "Master")
  .ToList() // or whatever

answered Jun 26 '13 at 17:42

Erik Philips

53,428
11
128
150

`dep.Courses.Include(i => i.Students)` is exactly what I WANT to do, but EF wont allow this. It seems I an only do includes on ObjectQuery objects (namely context.Courses). I can't use the include function on IQueryable like association sets (dep.Courses). I am using EF 4 not 5, but I havent seen a single example on the internet which is able to use include like that. If it is possible, please let me know. – Wannes Jun 27 '13 at 08:58

score 1 · Answer 2 · edited May 23 '17 at 11:56

If you are using DbContext (EF >= 4.1):

dbContext.Departments.Attach(dep);
dep.Courses = dbContext.Entry(dep).Collection(d => d.Courses).Query()
    .Include(c => c.Students)
    .Where(c => c.level == "Master")
    .ToList();

If you are using ObjectContext with EntityObject derived entities (not POCOs):

objectContext.Departments.Attach(dep);
dep.Courses.Attach(dep.Courses.CreateSourceQuery()
    .Include("Students")
    .Where(c => c.level == "Master")
    .ToList());

If you are using ObjectContext with POCOs it's possible but a bit hairy.

All queries above are actually not the same like yours because maybe your dep.Courses collection does not contain all the courses that are related to the department dep in the database. (Who knows if you didn't remove a course from the dep.Courses collection after loading it?) In this case I'm afraid your query is the only way to go.

Because you were talking about performance and database request optimization: If you want to avoid the overhead to load the courses again (which you already have in memory) you could also try:

courseIDs = dep.Courses
    .Where(c => c.level == "Master").Select(c => c.courseID);
var studentDict = dbcontext.Courses
    .Where(c => courseIDs.Contains(c.courseID))
    .Select(c => new
    {
        courseID = c.courseID,
        Students = c.Students
    })
    .ToDictionary(x => x.courseID, x => x.Students);
foreach (var course in dep.Courses)
    course.Students = studentDict[course.courseID];

It loads less data but is not necessarily more performant because Contains has significant costs of translation into SQL for large courseIDs collections.

Cant add Includes (eager-loading) to correct place

2 Answers2