Entity Framework: Navigation Properties Issue

Question

I am working with Entity Framework code-first, and I have a class Course which has a navigation property Students:

public virtual Collection<Student> Students { get; set;}

It works ok, but as I access this navigation property, all the data is retrieved from the database:

var allStudents = course.Students; // Here it retrieves the data
var activeStudents = allStudents.Where(n => n.Active); // Here it filter the data on memory
var listOfActiveStudents = activeStudents.ToList(); // It already has the data on memory.

As you can imagine, I need the query to be executed when I do the .ToList() because I don't want to bring all the Students from the database, only the active ones.

Do you know what I am doing wrong?

Answer 1

If you were to use proper variable typing then you would see what is happening. The entire set is lazy loaded loaded into memory by the navigation property.

//user is an instance of the class User referenced by DbSet<User>
//when you lazy load a navigation property in that set, it loads the data
ICollection<Student> allStudents = user.Students;

//At this point, all of the data was lazy loaded
//But the Where creates an IEnumerable of the in memory set
IEnumerable<Student> activeStudents = allStudents.Where(n => n.Active);

//At this point, the IEnumerable is iterated, and a List is returned
List<Student> listOfActiveStudents = activeStudents.ToList();

Answer 2

use dbContext.Entry( user ).Collection( u => u.Students ).Query() to get an IQueryable<Student> for the students collection navigation property, at which point you can add your filter and enumerate whenever you're ready for the data

Answer 3

Lazy loading loads the entire set into memory. If you don't want that, switch lazy loading off by removing the virtual keyword and use the Query object on the DbEntry:

public GetCourseWithActiveStudentsLoaded(int courseid)
{
   var course= context.Courses.Find(courseid); 

   context.Entry(course)
          .Collection(c => c.Students)
          .Query()
          .Where(s => s.Active)
          .Load();

   return user
}

Is the "Active" flag an indicator that you are trying to implement soft delete? If so there is a solution here: Soft Delete in Entity Framework

You can vote for filtered includes here: Allow filtering for Include extension method

Another way to do it would be with inheritance. You could have an ActiveStudent inheriting from Student and an ActiveStudents navigation property as well as an AllStudents navigation property in the Course class

public virtual Collection<Student> AllStudents { get; set;}
public virtual Collection<ActiveStudent> ActiveStudents { get; set;}

Reference:

Applying filters when explicitly loading related entities:

Answer 4

One workaround for this is to flip your query around, though it means avoiding using navigation properties in general. (Actual implementation will vary with your model.)

var allStudents =
   context
   .Students
   .Where(s => s.CourseID == course.ID) // depends on your model
   .Where(s => s.Active)
   .ToList();

I prefer this to using the Entry method, since I use a general interface with my models and I don't want to expose EF6 types.

Another way to avoid exposing EF6 types is to write a method like this:

public IQueryable<TChild> Nav<TParent, TChild>(
   TParent pParent,
   Expression<Func<TParent, ICollection<TChild>>> pNavigationExpression
) where TParent : class
where TChild : class =>
   Entry(pParent)
   .Collection(pNavigationExpression)
   .Query();

Used something like this:

var allStudents =
   context
   .Nav(course, c => c.Students)
   .Where(s => s.Active)
   .ToList()