7

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?

Improve this question

4 Answers 4

Reset to default
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:

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

what is the type of user.Students that it has a .Query() method?
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)
@Moho ah. My bad ;-( I'm at the wrong level here. I need a DbCollectionEntry.
@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.
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
|
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();
Improve this answer

4 Comments

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?
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()"
@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.
@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.
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

Improve this answer

2 Comments

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
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
1

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()
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.