EF Lambda: The Include path expression must refer to a navigation property

Question

Here is my expression:

Course course = db.Courses
  .Include(
    i => i.Modules.Where(m => m.IsDeleted == false)
      .Select(s => s.Chapters.Where(c => c.IsDeleted == false))
  ).Include(i => i.Lab).Single(x => x.Id == id);

I know the cause is Where(m => m.IsDeleted == false) in the Modules portion, but why does it cause the error? More importantly, how do I fix it?

If I remove the where clause it works fine but I want to filter out deleted modules.

Solution 1

.Include is used to eagerly load related entities from the db. I.e. in your case make sure the data for modules and labs is loaded with the course.

The lamba expression inside the .Include should be telling Entity Framework which related table to include.

In your case you are also trying to perform a condition inside of the include, which is why you are receiving an error.

It looks like your query is this:

Find the course matching a given id, with the related module and lab. As long as the matching module and chapter are not deleted.

If that is right, then this should work:

Course course = db.Courses.Include(c => c.Modules)
                          .Include(c => c.Lab)
                          .Single(c => c.Id == id && 
                                       !c.Module.IsDeleted &&
                                       !c.Chapter.IsDeleted);

Solution 2

but why does it cause the error?

I can imagine that sometimes the EF team regrets the day they introduces this Include syntax. The lambda expressions suggest that any valid linq expression can be used to subtly manipulate the eager loading. But too bad, not so. As I explained here the lambdas only serve as a disguised string argument to the underlying "real" Include method.

how do I fix it?

Best would be to project to another class (say, a DTO)

db.Courses.Select(x => new CourseDto {
    Id = x.Id,
    Lab = x.Lab,
    Modules = x.Modules.Where(m => !m.IsDeleted).Select( m => new ModuleDto {
        Moudle = m,
        Chapters = x.Chapters.Where(c => c.IsDeleted)
    }
}).Single(x => x.Id == id);

but that may be a major modification for you.

Another option is to disable lazy loading and pre-load the non-deleted Modules and Chapters of the course in the context by the Load command. Relationship fixup will fill the right navigation properties. The Include for Lab will work normally.

By the way, there is a change request for this feature.