How to filter "Include" entities in entity framework?

Question

Entities:

    public class Room
    {
        public Room()
        {
            this.Reservations = new HashSet<Reservation>();
        }

        public int Id { get; set; }

        public decimal Rate { get; set; }

        public int HotelId { get; set; }

        public virtual Hotel Hotel { get; set; }

        public virtual ICollection<Reservation> Reservations { get; set; }
    }

    public class Hotel
    {
        public Hotel()
        {
            this.Rooms = new HashSet<Room>();
        }

        public int Id { get; set; }

        public string Name { get; set; }

        public virtual ICollection<Room> Rooms { get; set; }
    }

    public class Reservation
    {
        public int Id { get; set; }

        public DateTime StartDate { get; set; }

        public DateTime EndDate { get; set; }

        public string ContactName { get; set; }

        public int RoomId { get; set; }

        public virtual Room Room { get; set; }
    }

  public class ExecutiveSuite : Room
  {
  }

  public class DataContext : DbContext
    {
        public DbSet<Hotel> Hotels { get; set; }

        public DbSet<Reservation> Reservations { get; set; }

        public DbSet<Room> Rooms { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Room>()
                .HasKey(r => r.Id)
                .HasRequired(r => r.Hotel)
                .WithMany(r => r.Rooms)
                .HasForeignKey(r => r.HotelId);

            modelBuilder.Entity<Hotel>()
                .HasKey(h => h.Id);

            modelBuilder.Entity<Room>()
                .HasMany(r => r.Reservations)
                .WithRequired(r => r.Room)
                .HasForeignKey(r => r.RoomId);

        }
    }

The client code(console app):

static void Main(string[] args)
        {
            // initialize and seed the database
            using (var context = new DataContext())
            {
                var hotel = new Hotel { Name = "Grand Seasons Hotel" };
                var r101 = new Room { Rate = 79.95M, Hotel = hotel };
                var es201 = new ExecutiveSuite { Rate = 179.95M, Hotel = hotel };
                var es301 = new ExecutiveSuite { Rate = 299.95M, Hotel = hotel };

                var res1 = new Reservation
                {
                    StartDate = DateTime.Parse("3/12/2010"),
                    EndDate = DateTime.Parse("3/14/2010"),
                    ContactName = "Roberta Jones",
                    Room = es301
                };
                var res2 = new Reservation
                {
                    StartDate = DateTime.Parse("1/18/2010"),
                    EndDate = DateTime.Parse("1/28/2010"),
                    ContactName = "Bill Meyers",
                    Room = es301
                };
                var res3 = new Reservation
                {
                    StartDate = DateTime.Parse("2/5/2010"),
                    EndDate = DateTime.Parse("2/6/2010"),
                    ContactName = "Robin Rosen",
                    Room = r101
                };

                es301.Reservations.Add(res1);
                es301.Reservations.Add(res2);
                r101.Reservations.Add(res3);

                hotel.Rooms.Add(r101);
                hotel.Rooms.Add(es201);
                hotel.Rooms.Add(es301);

                context.Hotels.Add(hotel);
                context.SaveChanges();
            }

            using (var context = new DataContext())
            {
                context.Configuration.LazyLoadingEnabled = false;
                // Assume we have an instance of hotel
                var hotel = context.Hotels.First();

                // Explicit loading with Load() provides opportunity to filter related data 
                // obtained from the Include() method 
                context.Entry(hotel)
                       .Collection(x => x.Rooms)
                       .Query()
                       .Include(y => y.Reservations)
                       .Where(y => y is ExecutiveSuite && y.Reservations.Any())
                       .Load();

                Console.WriteLine("Executive Suites for {0} with reservations", hotel.Name);

                foreach (var room in hotel.Rooms)
                {
                    Console.WriteLine("\nExecutive Suite {0} is {1} per night", room.Id,
                                      room.Rate.ToString("C"));
                    Console.WriteLine("Current reservations are:");
                    foreach (var res in room.Reservations.OrderBy(r => r.StartDate))
                    {
                        Console.WriteLine("\t{0} thru {1} ({2})", res.StartDate.ToShortDateString(),
                                          res.EndDate.ToShortDateString(), res.ContactName);
                    }
                }
            }

            Console.WriteLine("Press <enter> to continue...");
            Console.ReadLine();
        }



using ( var context = new DataContext() )
{

        //context.Configuration.LazyLoadingEnabled = false;

        // Assume we have an instance of hotel
        var hotel = context.Hotels.First();
        var rooms = context.Rooms.Include( r => r.Reservations ).Where( r => r is ExecutiveSuite && r.Reservations.Any() ).Where( r => r.Hotel.Id == hotel.Id );
        Console.WriteLine( "Executive Suites for {0} with reservations", hotel.Name );

        foreach ( var room in hotel.Rooms )
        {
           Console.WriteLine( "\nExecutive Suite {0} is {1} per night", room.Id,
                             room.Rate.ToString( "C" ) );
           Console.WriteLine( "Current reservations are:" );
           foreach ( var res in room.Reservations.OrderBy( r => r.StartDate ) )
           {
              Console.WriteLine( "\t{0} thru {1} ({2})", res.StartDate.ToShortDateString(),
                                res.EndDate.ToShortDateString(), res.ContactName );
           }
        }
     }

I tried projecting and putting it in an anonymous object:

       var hotel = context.Hotels.Select(h =>
        new 
        {   
            Id = h.Id,
            Name = h.Name,
            Rooms = h.Rooms.Where(r => r.Reservations is ExecutiveSuite && r.Reservations.Any())
        }).First();

but I get an exception: "DbIsOfExpression requires an expression argument with a polymorphic result type that is compatible with the type argument."

Now, if you would notice, I implemented it in two different ways, first was by explicitly loading the related entities, second was by having two different queries, my question would be, is there a way I can load my object graph and filter the entities I "Include" with just a single trip from the database?

In both example, there are just 2 queries to the database. The first one for Hotel and then for Rooms and Reservations. What else do you want? — sachin, Sep 22 '16 at 10:52
Why not `include` everything in the `Include()`? Something like: `context.Hotels.Include("Rooms.Reservations")`? — haim770, Sep 22 '16 at 10:54
@sachin Include other related entities and then filter/sort those related entities using a single trip to the database if possible. — Randel Ramirez, Sep 22 '16 at 12:24
@haim770 Include other related entities and then filter/sort those related entities using a single trip to the database if possible. — Randel Ramirez, Sep 22 '16 at 12:25
@haim770 that would load all related entitties which contrary to the actual goal. I made an edit btw. thanks for answering though. :) — Randel Ramirez, Sep 22 '16 at 14:16
EF core has filtered Include out of the box: https://stackoverflow.com/q/43618096/861716 — Gert Arnold, Mar 31 '23 at 14:42

Jonathan Magnan · Accepted Answer · 2020-11-23T13:09:45.517

24

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

edited Nov 23 '20 at 13:09

answered Sep 22 '16 at 14:16

Jonathan Magnan

10,874
2
38
60

I tried using it without explicitly loading and that doesn't seem to work. => context.hotels.IncludeFilter(h => h.rooms.Where(x is ExecutiveSuite && x.Reservations.Any())) Why is it like that? I'm getting a different result. Am I using it correctly – Randel Ramirez Sep 26 '16 at 11:46
1

@RandelRamirez, I just did a test and everything seem to work. Which result are you getting? One limitation of this feature is previously loaded related entities will always be included (even if it doesn't satisfy IncludeFilter predicate). If you want, you can also report this issue on our GitHub forum to make it easier to track than using Stack Overflow: https://github.com/zzzprojects/EntityFramework-Plus/issues – Jonathan Magnan Sep 26 '16 at 15:06
What do you mean by "without explicitly loading"? You need to either use .Load() or .ToList() (or any other LINQ immediate method) – Jonathan Magnan Sep 26 '16 at 15:11
I have here a repo, if you have time you could look at it and see the result I'm trying to get. Because I might be using your api the wrong way and there would no need to file for a bug. Thanks! :) https://github.com/randelramirez/EF6_LoadingEntitiesAndNavigation Project's name is FilteringAndOrderingRelatedEntities – Randel Ramirez Sep 26 '16 at 16:30
Let me know if the new answer is working. I get the same result from my side. – Jonathan Magnan Sep 26 '16 at 19:33
yeah, it's working and I would also like to mention that I need to turn off lazy loading to get the correct result. Thanks! :) – Randel Ramirez Sep 27 '16 at 14:29
How can we include multilevel properties with include filter. Ex in above answer, we want to load the list of Rooms and every list should have Reservarion object properties bound to it. Like we do in normal ef as - qry.Include("Rooms.Hotel"); – Oxygen Sep 10 '19 at 10:08
hi Jonathon , can you answer this question? https://stackoverflow.com/questions/62033907/filtering-on-theninclude-nested-levels-down-with-entity-framework-plus-includefi thanks – May 27 '20 at 01:14
Jon, also will IncludeFilter do the filtering in the database or filter after retrieiving the initial IEnumerable results? Just curious for SQL optimization, thanks – May 27 '20 at 01:44
@JonathanMagnan Hey John, does `EF+` have `dotnet 5.0` support? Specifically the include filter is really interesting to me. – WBuck Nov 22 '20 at 16:08
This was the perfect solution for me ... until I found out you can't use projections -`Select()` - with `IncludeFilter()`. Renders this a non-starter for me. :( – xanadont Jan 31 '22 at 18:45

score 15 · Answer 2 · edited May 23 '17 at 11:54

15

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();

How to filter "Include" entities in entity framework?

4 Answers4

Linked

Related