Filtering Results Using Filtered Include Method in EF Core

In this article, we are going to learn about Filtered Include in Entity Framework Core, what operations it supports, and what are its use cases.

Let’s start.

Why Do We Need Filtered Include

When we use eager loading in EF Core, we use the Include method to load related entities.

But what if we want to filter or sort these related entities?

We can do that with a new feature in EF Core 5.0 that allows using LINQ methods inside the Include method. We can use this feature on the collection navigation property, not the reference navigation property.

To start with an example, let’s create two entities: Course and Student, with a One-To-Many relationship.

First the Student entity:

public class Student
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public int Mark { get; set; }

    public int CourseId { get; set; }
    public Course? Course { get; set; }
}

And theCourse entity:

public class Course
{
    public int Id { get; set; }
    public string? Title { get; set; }

    public ICollection<Student>? Students { get; set; }
}

Note: We create this relationship for the sake of simplicity and will use it in all the following examples in our article.

Let’s see an example of how Filtered Include affects the result:

var query = context.Courses.Include(x => x.Students.Where(x => x.Mark > 90)).ToList();

The query returns all the courses with the students that have a mark greater than 90.

Supported Operations

The supported operations inside Include are: Where, OrderBy, OrderByDescending, ThenBy, ThenByDescending, Skip, and Take. 

So the following query will work:

var goodQuery = context.Courses.Include(c => c.Students.Where(s => s.Mark > 50)).ToList();

But the next query will not:

var badQuery = context.Courses.Include(c => c.Students.Any(s => s.Mark > 50)).ToList();

This query throws InvalidOperationException because the Any method is not supported.

Stand-Alone Filter

The applied filter on Include must be stand-alone, i.e. it must work independently of Include.

To make it clear, let’s see an example:

var goodQuery = context.Courses.Include(c => c.Students.Where(s => s.Id == s.Course.Id)).ToList();

This query is correct and works because Where(s => s.Id == s.Course.Id) can work independently:

var query = context.Students.Where(s => s.Id == s.Course.Id).ToList();

But if we rewrite the query:

var badQuery = context.Courses.Include(c => c.Students.Where(s => s.Id == c.Id)).ToList();

This query throws InvalidOperationException because the LINQ expression ‘c’ could not be translated.

Filtering on Multiple Include

We can have only one filter per collection navigation. So when we need to include the same navigation multiple times, we should apply the same filter:

var goodQuery = context.Courses
    .Include(c => c.Students.Where(s => s.Mark > 50)).ThenInclude(s => s.Entity1)
    .Include(c => c.Students.Where(s => s.Mark > 50)).ThenInclude(s => s.Entity2)
    .ToList();

But if we try to apply different filters for the same navigation:

var badQuery = context.Courses
    .Include(c => c.Students.Where(s => s.Mark > 50)).ThenInclude(s => s.Entity1)
    .Include(c => c.Students.Where(s => s.Mark <= 50)).ThenInclude(s => s.Entity2)
    .ToList();

It throws InvalidOperationException.

Filtered Include with Tracking Queries

In the case of tracking queries, when we execute a query, the relevant entities will be stored in the change tracker. Because of this, we may get unexpected results.

So let’s see an example:

var query1 = context.Courses
    .Include(c => c.Students.Where(s => s.Mark > 50)).ToList(); 
var query2 = context.Courses
    .Include(c => c.Students.Where(s => s.Mark <= 50)).ToList();

After executing the queries, the result is an aggregate between the first and second predicates, so we return all the students related to courses. To solve this issue we can use a new context or AsNoTracking method:

var query1 = context.Courses
    .AsNoTracking()
    .Include(c => c.Students.Where(s => s.Mark > 50)).ToList();
var query2 = context.Courses
    .AsNoTracking()
    .Include(c => c.Students.Where(s => s.Mark <= 50)).ToList();

Another example to show the effect:

var courses = context.Courses.Include(c => c.Students.Where(s => s.Mark > 50)).ToList();
var students = context.Students!.Where(s => s.Mark <= 50).ToList();

The courses contains all the students as in the previous example.

Filtered Include and Select Method

First, let’s explain the relationship between Select and Include. When we use Select, we don’t need Include:

var query1 = context.Courses
    .Select(c => new
    {
        c.Id,
        c.Title
    }).ToList();

var query2 = context.Courses.Include(c => c.Students)
    .Select(c => new
    {
        c.Id,
        c.Title
    }).ToList();

Both query1 and query2 translate to SQL in the same way. Due to the projection operation with the Select method in the second query, Include will be ignored.

We can have a look at another example:

var query3 = context.Courses //.Include(x => x.Students)
    .Select(c => new
    {
        c.Id,
        c.Title,
        Students = c.Students.ToList()
    }).ToList();

Now, since Select uses Students collection, EF Core does a left-join whether or not we use Include.

Back to Filtered Include:

var query4 = context.Courses
    .Include(x => x.Students.Where(x => x.Mark > 50))
    .Select(c => new Course 
    { 
        Id = c.Id,
        Title = c.Title,
        Students = c.Students!.ToList() 
    }).ToList();

In this case EF Core ignores Include. The result contains all the students and not only those with marks greater than 50.

If we want to use Select and filter the collection navigation at the same time, how can we do this?

We can do this by filtering inside Select:

var query5 = context.Courses
    .Select(c => new Course 
    { 
        Id = c.Id,
        Title = c.Title,
        Students = c.Students.Where(x => x.Mark > 50).ToList()
    }).ToList();

Conclusion

In this article, we’ve learned about Filtered Include and its implementation, and we have seen many uses that lead to exceptions and which should be avoided.

Finally, we have tried to explain the complex relationship between Include and Select methods with many examples.