.net - 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();