How to Filter "Include" Entities in Entity Framework

How to filter Include entities in entity framework?

There are two ways to filter include Entity.

Using a projection (See @Eldho answer)
Using a third party library

Disclaimer: I'm the owner of the project Entity Framework Plus

The EF+ Query IncludeFilter allows to easily filter included entities.

context.Entry(hotel)
       .Collection(x => x.Rooms)
       .Query()
       .IncludeFilter(y => y.Reservations
                            .Where(z => z is ExecutiveSuite && z.Reservations.Any())
       .Load();

Under the hood, the library does exactly a projection.

Wiki: EF+ Query Include Filter

EDIT: Answer subquestion

You almost did it. The rooms were included and filtered, but you didn't include the reservations.

var hotel = context.Hotels
    // Include only executive suite with a reservation
    .IncludeFilter(x => x.Rooms.Where(y => y is ExecutiveSuite && y.Reservations.Any()))
    // Include only reservation from executive suite
    .IncludeFilter(x => x.Rooms.Where(y => y is ExecutiveSuite).Select(z => z.Reservations))
    .First();

EDIT: Answer Comment

How can we include multilevel properties with include filter

You can include multilevel by specifying each path (one per IncludeFilter)

So qry.Include("Rooms.Hotel") become:

qry.IncludeFilter(x => x.Rooms)
   .IncludeFilter(x => x.Rooms.Select(y => y.Hotel))

EDIT: Answer Comment

does EF+ have dotnet 5.0 support?

Yes, it supports dotnet 5.0 and EF Core 5.0. However, for IncludeFilter, you should also look at the filtered include built-in directly in EF Core 5: https://www.learnentityframeworkcore5.com/whats-new-in-ef-core-5/filtered-included

Filtering include entities in EF Core

devnull show the next How to filter "Include" entities in entity framework?, and there the same problem, I read it, and find the answer. Solve my problem can with the next:

var temp = _context.Persons.Select(s => new
  {
    Person = s,
    PersonRoles= s.PersonRoles
      .Where(p => p.RoleInDuty.typeOfDutyId == this.typeOfDuty.typeOfDutyId)
      .ToList()
  }).ToList();

Filter in EF Include

There are a few things I think need improving here.

Includes are not meant for filtering.

It's simply not the correct place to do so.

Include is meant to automatically retrieve all linked entities. Since you don't want all of the entities (you only want a subset), you shouldn't be using an Include.

Alternatively, you could still use an Include, so long as you're happy to remove the unwanted entries in memory (i.e. after they've been loaded). But I assume you don't want that.

Instead, you could use an explicit Select statement. As a simple example:

context.Projects
             .Where(p => p.Id == projectId)
             .Select(p => new ConsultantSearchResult() {
                   Project = p,
                   ConsultantsNamedBob = p.Consultants.Where(c => c.FirstName == "Bob")
              }).ToList();

Notice the absence of the Include. As I said before, Include is used for automatically (and implicitly) loading the related data. But because you explicitly stated the data you want in the Select, there's no need for an implicit inclusion anymore. EF will give you exactly what you ask for.

Your `Select` is unintuitive

I think you're expecting something different than what you're getting. Looking at the code:

return context.Timesheets    //1
    .Where(...)              //2
    .Select(t => t.Project)  //3

Look at what happens:

You select all timesheets.
You filter the timesheets and are left with a subset of timesheets
You get a list of the projects of each timesheet.

If your filtering (step 2) leaves you with multiple timesheets from the same project, then .Select(t => t.Project) will give you multiple instances of the same project. And that's not good.

There are two exceptions here:

You know that you are going to find one timesheet in total. But then you should be using First, Single, FirstOrDefault or SingleOrDefault. You should only use Where if it's possible that you get more than one result.
You expect more than one timesheet, but you know that you'll never find two timesheets from the same project (thus never creating duplicates when you call the Select). I would assume (by reading the entity names) that it's possible for a specific consultant to have multiple timesheets for the same project, but maybe that's not true.
- If my inference is correct, then you will experience the problem with duplicate projects after you do the Select.
- If my inference is not correct, then I would expect a much stronger relationship between timesheets and consultant, as every project consultant would have exactly 1 (or no) timesheet, never more than 1. But your current data structure lacks any real relationship between timesheets and consultants.

A quick solution would be to use Distinct:

return context.Timesheets    
    .Where(...)              
    .Select(t => t.Project)  
    .Distinct()

But I personally think that a better solution would be to invert the lookup: start with projects, filter the projects on their timesheets (rather than filtering the timesheets):

return context.Projects    
    .Include(p => p.Timesheets)  
    .Where(p => p.Timesheets.Any(t => t.UserId == userId && ...))              
    .ToList();

This precludes the issue with the duplicate projects. Note that this doesn't yet solve your "filtered Include" problem.

Separate queries in the same context

This was also mentioned in the comments. This is a viable option, but I find it a dirty approach that is going to create unintuitive code down the line.

A simple example:

context.Configuration.LazyLoadingEnabled = false;
var parent = context.Set<Entity>().First(e => e.Name = "ABC");
// Load relations in separate query
context.Set<Child>()
       .Where(c => c.Parent.Name == "ABC")
       .OrderBy(c => c.Name) // You can at least try it but as mentioned above it may not work in all scenarios
       .Load();
// Now parent.Children collection should be filled

The example uses OrderBy instead of Where, but both will work the same way.

Even though you queried the children and the parent separately, their navigational properties will continuously be updated because you're running your queries in the same context.

It's a viable option for you, but I'm a bit apprehensive of this code, as it is in no way readable that the second query alters the result of the first query.

To me, this feels equally dirty to e.g. having business logic in the get or set of a property. It works, but it leads to unexpected behavior and will make debugging really difficult.

Note that it may be clear to you what is happening behind the scenes, but it's easy for a different developer to gloss over it when looking at the code.

I personally don't like this, but your opinion may differ.

Your incomplete data structure is making it complicated.

Looking at your code sample, I think there's a bit of an issue with your data consistency. You are using the userId filter in two places:

Timesheet: t => t.UserId == userId
Consultant: c => c.UserId == userId

If timesheets are connected to a consultant, then there should be a relationship between these two entities. As it currently stands, your project has a list of timesheets and a list of consultants, with no discernible relationship between timesheets and consultant.

This is why your lookup is co complicated. You're trying to mock a relationship that isn't there.

If the relationship did exist, it would be much easier to look everything up:

return context.Timesheets
                   .Include(t => t.Project)
                   .Include(t => t.Project.Account)
                   .Include(t => t.Consultant)
                   .Where(t => t.Consultant.UserId == userId && t.SubjectDate == date && t.WorkingDaySchedules.Count() > 0)
                   .ToList()

And then you get what you're looking for. You no longer have to do two separate userId checks, you're no longer required to "manually synchronize" the fake relationship, the lookup process is much more streamlined and readable.

Minor comment

Maybe something you didn't know yet. You can rewrite

t.WorkingDaySchedules.Count() > 0

t.WorkingDaySchedules.Any() //is there at least one item in the collection?

With the added benefit that you can add filters if you need to:

t.WorkingDaySchedules.Any(wds => wds.IsActive)  //is there at least one item in the collection that meets the condition?

Filtering on Include in EF Core

Entity Framework core 5 is the first EF version to support filtered Include.

How it works

Supported operations:

Where
OrderBy(Descending)/ThenBy(Descending)
Skip
Take

Some usage examples (from the original feature request and the github commmit)
:

Only one filter allowed per navigation, so for cases where the same navigation needs to be included multiple times (e.g. multiple ThenInclude on the same navigation) apply the filter only once, or apply exactly the same filter for that navigation.

context.Customers
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
    .Include(c => c.Orders).ThenInclude(o => o.Customer)

context.Customers
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.Customer)

Another important note:

Collections included using new filter operations are considered to be loaded.

That means that if lazy loading is enabled, addressing one customer's Orders collection from the last example won't trigger a reload of the entire Orders collection.

Also, two subsequent filtered Includes in the same context will accumulate the results. For example...

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...followed by...

context.Customers.Include(c => c.Orders.Where(o => o.IsDeleted))

...will result in customers with Orders collections containing all orders.

Filtered Include and relationship fixup

If other Orders are loaded into the same context, more of them may get added to a customers.Orders collection because of relationship fixup. This is inevitable because of how EF's change tracker works.

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...followed by...

context.Orders.Where(o => o.IsDeleted).Load();

...will again result in customers with Orders collections containing all orders.

The filter expression

The filter expression should contain predicates that can be used as a stand-alone predicate for the collection. An example will make this clear. Suppose we want to include orders filtered by some property of Customer:

context.Customers.Include(c => c.Orders.Where(o => o.Classification == c.Classification))

It compiles, but it'll throw a very technical runtime exception, basically telling that o.Classification == c.Classification can't be translated because c.Classification can't be found. The query has to be rewritten using a back-reference from Order to Customer:

context.Customers.Include(c => c.Orders.Where(o => o.Classification == o.Customer.Classification))

The predicate o => o.Classification == o.Customer.Classification) is "stand alone" in the sense that it can be used to filter Orders independently:

context.Orders.Where(o => o.Classification == o.Customer.Classification) // No one would try 'c.Classification' here

This restriction may change in later EF versions than the current stable version (EF core 5.0.7).

What can (not) be filtered

Since Where is an extension method on IEnumerable it's clear that only collections can be filtered. It's not possible to filter reference navigation properties. If we want to get orders and only populate their Customer property when the customer is active, we can't use Include:

context.Orders.Include(o => o.Customer.Where( ... // obviously doesn't compile

Filtered Include vs filtering the query

Filtered Include has given rise to some confusion on how it affects filtering a query as a whole. The rule of the thumb is: it doesn't.

The statement...

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...returns all customers from the context, not only the ones with undeleted orders. The filter in the Include doesn't affect the number of items returned by the main query.

On the other hand, the statement...

context.Customers
    .Where(c => c.Orders.Any(o => !o.IsDeleted))
    .Include(c => c.Orders)

...only returns customers having at least one undeleted order, but having all of their orders in the Orders collections. The filter on the main query doesn't affect the orders per customer returned by Include.

To get customers with undeleted orders and only loading their undeleted orders, both filters are required:

context.Customers
    .Where(c => c.Orders.Any(o => !o.IsDeleted))
    .Include(c => c.Orders.Where(o => !o.IsDeleted))

Filtered Include and projections

Another area of confusion is how filtered Include and projections (select new { ... }) are related. The simple rule is: projections ignore Includes, filtered or not. A query like...

context.Customers
    .Include(c => c.Orders)
    .Select(c => new { c.Name, c.RegistrationDate })

...will generate SQL without a join to Orders. As for EF, it's the same as...

context.Customers
    .Select(c => new { c.Name, c.RegistrationDate })

It gets confusing when the Include is filtered, but Orders are also used in the projection:

context.Customers
    .Include(c => c.Orders.Where(o => !o.IsDeleted))
    .Select(c => new 
    { 
        c.Name, 
        c.RegistrationDate,
        OrderDates = c.Orders.Select(o => o.DateSent)
    })

One might expect that OrderDates only contains dates from undeleted orders, but they contain the dates from all Orders. Again, the projection completely ignores the Include. Projection and Include are separate worlds.

How strictly they lead their own lives is amusingly demonstrated by this query:

context.Customers
    .Include(c => c.Orders.Where(o => !o.IsDeleted))
    .Select(c => new 
    { 
        Customer = c, 
        OrderDates = c.Orders.Select(o => o.DateSent)
    })

Now pause for a moment and predict the outcome...

The not so simple rule is: projections don't always ignore Include. When there is an entity in the projection to which the Include can be applied, it is applied. That means that Customer in the projection contains its undeleted Orders, whereas OrderDates still contains all dates. Did you get it right?

Filtering include items in LINQ and Entity Framework

While you cannot filter a collection included via Include, you can use Select and project that collection into a filtered collection.

var rootCategoryItem = DatabaseContext.Categories
    .OrderBy(c => c.CategoryOrder)
    .Select(c => new Category()
    {
        SubCategories = c.SubCategories.Where(sub => !sub.Deleted)
            .OrderBy(sub => sub.CategoryOrder),
        c.CategoryId,
        c.CategoryName,
        //include any other fields needed here
    })
    .Single(c => c.CategoryId == 1);

EF Core Include ThenInclude Filter

Don't forget, it is somewhat simpler to go the other way (from M to 1) along a 1:M relationship :

context.SiteUrls.Where(su => su.Url == url).Select(su => su.Site.Tenant) ...

Filtering on Include in EF Core

Entity Framework core 5 is the first EF version to support filtered Include.

How it works

Supported operations:

Where
OrderBy(Descending)/ThenBy(Descending)
Skip
Take

Some usage examples (from the original feature request and the github commmit)
:

Only one filter allowed per navigation, so for cases where the same navigation needs to be included multiple times (e.g. multiple ThenInclude on the same navigation) apply the filter only once, or apply exactly the same filter for that navigation.

context.Customers
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
    .Include(c => c.Orders).ThenInclude(o => o.Customer)

context.Customers
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.Customer)

Another important note:

Collections included using new filter operations are considered to be loaded.

That means that if lazy loading is enabled, addressing one customer's Orders collection from the last example won't trigger a reload of the entire Orders collection.

Also, two subsequent filtered Includes in the same context will accumulate the results. For example...

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...followed by...

context.Customers.Include(c => c.Orders.Where(o => o.IsDeleted))

...will result in customers with Orders collections containing all orders.

Filtered Include and relationship fixup

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...followed by...

context.Orders.Where(o => o.IsDeleted).Load();

...will again result in customers with Orders collections containing all orders.

The filter expression

context.Customers.Include(c => c.Orders.Where(o => o.Classification == c.Classification))

context.Customers.Include(c => c.Orders.Where(o => o.Classification == o.Customer.Classification))

The predicate o => o.Classification == o.Customer.Classification) is "stand alone" in the sense that it can be used to filter Orders independently:

context.Orders.Where(o => o.Classification == o.Customer.Classification) // No one would try 'c.Classification' here

This restriction may change in later EF versions than the current stable version (EF core 5.0.7).

What can (not) be filtered

context.Orders.Include(o => o.Customer.Where( ... // obviously doesn't compile

Filtered Include vs filtering the query

Filtered Include has given rise to some confusion on how it affects filtering a query as a whole. The rule of the thumb is: it doesn't.

The statement...

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...returns all customers from the context, not only the ones with undeleted orders. The filter in the Include doesn't affect the number of items returned by the main query.

On the other hand, the statement...

context.Customers
    .Where(c => c.Orders.Any(o => !o.IsDeleted))
    .Include(c => c.Orders)

To get customers with undeleted orders and only loading their undeleted orders, both filters are required:

context.Customers
    .Where(c => c.Orders.Any(o => !o.IsDeleted))
    .Include(c => c.Orders.Where(o => !o.IsDeleted))

Filtered Include and projections

Another area of confusion is how filtered Include and projections (select new { ... }) are related. The simple rule is: projections ignore Includes, filtered or not. A query like...

context.Customers
    .Include(c => c.Orders)
    .Select(c => new { c.Name, c.RegistrationDate })

...will generate SQL without a join to Orders. As for EF, it's the same as...

context.Customers
    .Select(c => new { c.Name, c.RegistrationDate })

It gets confusing when the Include is filtered, but Orders are also used in the projection:

context.Customers
    .Include(c => c.Orders.Where(o => !o.IsDeleted))
    .Select(c => new 
    { 
        c.Name, 
        c.RegistrationDate,
        OrderDates = c.Orders.Select(o => o.DateSent)
    })

How strictly they lead their own lives is amusingly demonstrated by this query:

context.Customers
    .Include(c => c.Orders.Where(o => !o.IsDeleted))
    .Select(c => new 
    { 
        Customer = c, 
        OrderDates = c.Orders.Select(o => o.DateSent)
    })

Now pause for a moment and predict the outcome...

Filtering On ThenInclude, with EntityFrameworkPlus IncludeFilter

You cannot mixte Include with IncludeFilter.

In EF Core, the IncludeFilter should automatically add all paths.

We don't have the class definition so it makes it hard to know exactly the relationship but the query should look like this:

var result = await db.Property.IncludeFilter(pm => pm.PropertyParty
                                .Select(x => x.Party)
                                .SelectMany(x => x.PartyMailingAddress)
                                .SelectMany(x => x.PropertyMailingAddress.Where(z => z.Status == true)).ToListAsync();

The filtering is done in the database. So be careful with EF Core 2.x, as their have a client side evaluation they removed in EF Core 3.x which was causing some problem.

If you need more help, just provide a runnable solution in our issue tracker: https://github.com/zzzprojects/EntityFramework-Plus/issues

How to Filter "Include" Entities in Entity Framework