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

At all costs upgrade to EF 5.0+ and take advantage of EF 5.0+ eager loading capabilities, specifically Microsoft Docs Eager Loading - Filtered Include

Example:

context.hotel.Include(y => y.Reservations.Where(resy=>resy.type==ExecutiveSuite && resy.Any())).ToListAsync();

Note that it is not currently possible to filter which related entities are loaded. Include will always bring in all related entities Msdn reference

Request this feature here

In order to filter child collection you can try to select that to model or anonymous projection.

var anonymousProjection = dbContext.CustomerEntity
                                 .Where(c => ! c.IsDeleted)
                                 .Select(x=> new 
                                  {
                                       customers = x,
                                       orders = x.Orders.Where(h=>h.IsDeleted)
                                  }).ToList();

Similar answers