title: How to filter "Include" entities in entity framework?
body: <p>Entities:</p>
  
  <pre><code>    public class Room
      {
          public Room()
          {
              this.Reservations = new HashSet&lt;Reservation&gt;();
          }
  
          public int Id { get; set; }
  
          public decimal Rate { get; set; }
  
          public int HotelId { get; set; }
  
          public virtual Hotel Hotel { get; set; }
  
          public virtual ICollection&lt;Reservation&gt; Reservations { get; set; }
      }
  
      public class Hotel
      {
          public Hotel()
          {
              this.Rooms = new HashSet&lt;Room&gt;();
          }
  
          public int Id { get; set; }
  
          public string Name { get; set; }
  
          public virtual ICollection&lt;Room&gt; 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&lt;Hotel&gt; Hotels { get; set; }
  
          public DbSet&lt;Reservation&gt; Reservations { get; set; }
  
          public DbSet&lt;Room&gt; Rooms { get; set; }
  
          protected override void OnModelCreating(DbModelBuilder modelBuilder)
          {
              modelBuilder.Entity&lt;Room&gt;()
                  .HasKey(r =&gt; r.Id)
                  .HasRequired(r =&gt; r.Hotel)
                  .WithMany(r =&gt; r.Rooms)
                  .HasForeignKey(r =&gt; r.HotelId);
  
              modelBuilder.Entity&lt;Hotel&gt;()
                  .HasKey(h =&gt; h.Id);
  
              modelBuilder.Entity&lt;Room&gt;()
                  .HasMany(r =&gt; r.Reservations)
                  .WithRequired(r =&gt; r.Room)
                  .HasForeignKey(r =&gt; r.RoomId);
  
          }
      }
  </code></pre>
  
  <p>The client code(console app):</p>
  
  <pre><code>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 =&gt; x.Rooms)
                         .Query()
                         .Include(y =&gt; y.Reservations)
                         .Where(y =&gt; y is ExecutiveSuite &amp;&amp; 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 =&gt; r.StartDate))
                      {
                          Console.WriteLine("\t{0} thru {1} ({2})", res.StartDate.ToShortDateString(),
                                            res.EndDate.ToShortDateString(), res.ContactName);
                      }
                  }
              }
  
              Console.WriteLine("Press &lt;enter&gt; 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 =&gt; r.Reservations ).Where( r =&gt; r is ExecutiveSuite &amp;&amp; r.Reservations.Any() ).Where( r =&gt; 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 =&gt; r.StartDate ) )
             {
                Console.WriteLine( "\t{0} thru {1} ({2})", res.StartDate.ToShortDateString(),
                                  res.EndDate.ToShortDateString(), res.ContactName );
             }
          }
       }
  </code></pre>
  
  <p>I tried projecting and putting it in an anonymous object:</p>
  
  <pre><code>       var hotel = context.Hotels.Select(h =&gt;
          new 
          {   
              Id = h.Id,
              Name = h.Name,
              Rooms = h.Rooms.Where(r =&gt; r.Reservations is ExecutiveSuite &amp;&amp; r.Reservations.Any())
          }).First();
  </code></pre>
  
  <p>but I get an exception: "DbIsOfExpression requires an expression argument with a polymorphic result type that is compatible with the type argument."</p>
  
  <p>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?</p>