EF Core Filtered Include: Filter and Sort Related Entities

Updated on September 14, 2026

Filtered Include lets us put LINQ operators inside EF Core’s Include() call, so eager loading brings back part of a related collection instead of all of it.

context.Courses.Include(c => c.Students.Where(s => s.Mark > 90)) returns every course, and inside each course only the students who scored above 90. The filter travels into the generated SQL, so the rows we do not want never leave the database.

What Is Filtered Include in EF Core?

Filtered Include is an EF Core feature that lets us apply LINQ operators inside the Include() call, so eager loading returns part of a related collection instead of all of it.

Without it, Include() is all or nothing. context.Courses.Include(c => c.Students) loads every student of every course, and any filtering happens in memory after those rows have already crossed the wire.

With it, the filter becomes part of the generated SQL. context.Courses.Include(c => c.Students.Where(s => s.Mark > 90)) returns every course, and inside each course only the students who scored above 90.

The feature works on collection navigation properties only. A reference navigation such as Student.Course points at a single row, so there is nothing to filter and Include() there takes no operators.

One thing does not change. Courses with no matching students still come back, each with an empty Students collection, because filtered Include narrows the related collection and never the root query.

The feature modifies eager loading and how it differs from lazy loading, so that mechanism is worth knowing first. The rest of our EF Core series covers the model and query features around it.

To start with an example, let’s create three entities: Course, Student and Assignment, with One-To-Many relationships.

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; }

    public ICollection<Assignment>? Assignments { get; set; }
}

And the Course entity:

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

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

And finally the Assignment entity, which gives us a second level to reach into later on:

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

    public int StudentId { get; set; }
    public Student? Student { 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(c => c.Students!.Where(s => s.Mark > 90)).ToList();

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

Which LINQ Operations Can We Use Inside Include?

Seven operators work inside Include(): Where(), OrderBy(), OrderByDescending(), ThenBy(), ThenByDescending(), Skip(), and Take().

Everything else throws. The list is closed, and an operator outside it fails when the query executes rather than when it compiles.

That runtime failure is the part that catches people out. Include() accepts any expression the compiler can type, so context.Courses.Include(c => c.Students.Any(s => s.Mark > 50)) builds cleanly and only breaks when we enumerate the results.

The order of the operators matters as much as the list itself. Filtering comes first, sorting second, paging last, exactly as it would in an ordinary LINQ chain: Where(), then OrderByDescending(), then Take().

There is one more constraint worth carrying into the sections below. Each included collection navigation allows only one unique set of these operations per query, which is what the multiple-Include rules further down are really about.

The EF Core documentation states the rule for repeated navigations directly: “Each included navigation allows only one unique set of filter operations.”

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.

Filtered Include is not the only way to keep unwanted rows out of a result. Where the same condition should apply to every query for an entity type rather than to one Include(), we want global query filters, which apply to every query for an entity type instead.

How Do We Sort and Page Included Entities?

Sorting and paging use the same operators as a top-level query, chained inside the Include() call after the filter.

That combination is how we load the top few related rows per parent. Ten courses with a hundred students each become ten courses with three students each, and the trimming happens in SQL rather than in memory.

Skip() and Take() together give us a page of related entities, and ThenBy() breaks ties after the first sort key has been applied.

Sorting is not really optional once we page. Skip() and Take() without an OrderBy() in front of them leave the chosen rows at the mercy of whatever order the database happens to return, which no relational database guarantees to be stable between runs.

ThenInclude() chains after a filtered Include() and loads from the collection we already narrowed. The filter is written once, on the Include(), and the levels below it inherit that narrowed set rather than restating the predicate.

Let’s put the filter, the sort and the page into one chain:

var topStudents = context.Courses!
    .Include(c => c.Students!
        .Where(s => s.Mark > 50)
        .OrderByDescending(s => s.Mark)
        .Take(3))
    .ToList();

Each course comes back with at most three students, the highest marks first, and the Take(3) is applied per course rather than across the whole result.

A query shaped like this one is also where splitting a single query into several starts to matter, because one join can multiply the rows the database sends back.

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))
    .Include(c => c.Students!.Where(s => s.Mark > 50))
    .ToList();

The documented alternative is to write the filter once and leave the repeated Include() plain, which is the shape we need when we want to reach a level below the collection we just narrowed:

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

The plain Include() does not widen the filtered set, and ThenInclude() loads the assignments of the students that survived the filter, not of every student on the course.

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))
    .Include(c => c.Students!.Where(s => s.Mark <= 50))
    .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.

The mechanism behind that has a name: navigation fixup. Every entity the second query loads is attached to the same change tracker, and the tracker wires each new student onto the Course instance it already holds. Both result sets point at that one instance, so the collection we read afterwards carries the union of both predicates.

Nothing about the filter failed. Each query sent exactly the predicate we wrote and the database returned exactly the rows it asked for, which is why the effect is invisible in the generated SQL and only shows up when we count the students in memory.

To solve this issue we can use a new context or the 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.

If we want to check that for ourselves rather than take it on trust, we can see the SQL EF Core actually generates for both queries.

We can have a look at another example:

var query3 = context.Courses! //.Include(c => c.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(c => c.Students!.Where(s => s.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(s => s.Mark > 50).ToList()
    }).ToList();

Why Is Filtered Include Not Supported in EF Core 3.1?

Filtered Include is not available in EF Core 3.1. On that version and earlier, Include() takes a navigation property and nothing else, so putting Where() inside it fails.

There is no package, flag, or compatibility switch that backports the feature. EF Core 3.1 is also long out of support, so upgrading is the answer wherever it is available.

Where an upgrade has to wait, two patterns cover most of the need. Projection is the first: build an anonymous type or a DTO with Select(), and filter the collection inside the projection. That works on every EF Core version and produces a single query.

Explicit loading is the second. Load the parent, then reach the related collection through context.Entry(course).Collection(c => c.Students).Query(), which returns an IQueryable we can filter before enumerating it.

The trade-off is worth knowing up front. Projection gives us a shape of our own choosing rather than populated navigation properties on the entity.

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.

Tested with .NET 10 and EF Core 10.0.11.