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?

score 3 · Answer 1 · answered Dec 03 '13 at 17:26

3

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

answered Dec 03 '13 at 17:26

Travis J

81,153
41
202
273

Thanks, i know that... the problem is that the Students are retrieved at the first line as you access the navigation property. How can i do for retrieving only the active students? – ascherman Dec 03 '13 at 17:38
I mean... the query is executed at the first line, and then it applies the filter on memory. But i want the query to be executed when i do the "ToList()" – ascherman Dec 03 '13 at 17:40
1

@ArielScherman - You need an `IQueryable` to accomplish that. You should issue a query against the students table using the foreign key relation. Something like `db.Students.Where( s => s.Active && s.UserId == user.UserId )`. This will give you an `IQueryable` capable of taking advantage of the deferred execution you are looking for. – Travis J Dec 03 '13 at 17:45
1

@Ariel - Then I would suggest going with Moho's suggestion and using: `db.Entry(user).Collection( u => u.Students ).Query().Where( s => s.Active ).ToList();`. This will only load the user's active students. `.Collection` returns a `DbCollectionEntry`, and `Query` returns an `IQueryable` neither of which will load the set into memory. Using `Where` will also return an `IQueryable` here, and the final call to `ToList` is where the set is loaded into memory. – Travis J Dec 03 '13 at 18:03

score 3 · Answer 2 · answered Dec 03 '13 at 17:40

3

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

answered Dec 03 '13 at 17:40

Moho

15,457
1
30
31

3

So.. there isn't a solution for the navigation properties? How is it possible? I work with the repository pattern, so doing what you say is a pain in the ass – ascherman Dec 03 '13 at 17:42
3

Lazy loading navigation properties is an all or nothing operation, which is essentially `dbContext.Entry( user ).Collection( u => u.Students ).Load()`. By using `.Query()`, you are able to filter results prior to loading them from the DB. I would think a repo pattern would make this less cumbersome as you are already abstracting away the loading of the entities from the developer – Moho Dec 03 '13 at 17:45

score 3 · Accepted Answer · edited May 23 '17 at 11:46

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:

edited May 23 '17 at 11:46

Community

1
1

answered Dec 03 '13 at 17:46

Colin

22,328
17
103
197

what is the type of `user.Students` that it has a `.Query()` method? – Moho Dec 03 '13 at 17:56
The "active" field is an example. I have to apply more complex filters. I've tried remove the "virtual", and it doesn't run the query... but it doesn't bring anything when the "ToList()" is executed (it doesn't execute any query) – ascherman Dec 03 '13 at 18:00
@Moho ah. My bad ;-( I'm at the wrong level here. I need a `DbCollectionEntry`. – Colin Dec 03 '13 at 18:07
@ArielScherman when you switch off lazy loading you have to explicitly load. Have a look at how to eager load at the link I included in my answer. – Colin Dec 03 '13 at 18:12
To avoid eager loading all the items you can use the .Query method as described on the answer from @Moho, my edited answer, and the link I put in my answer – Colin Dec 03 '13 at 22:30
Thanks @Colin, it's great! But do you know how can i do that for more than one collection? Here is the question: http://stackoverflow.com/questions/20524311/entity-framework-fetch-multiple-collections-with-filters-applied – ascherman Dec 11 '13 at 17:36

score 1 · Answer 4 · answered Oct 01 '19 at 22:04

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()

Entity Framework: Navigation Properties Issue

4 Answers4

Linked