c# - How to filter "Include" entities in entity framework? -

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 , seed 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 have instance of hotel                 var hotel = context.hotels.first();                  // explicit loading load() provides opportunity filter related data                  // obtained include() method                  context.entry(hotel)                        .collection(x => x.rooms)                        .query()                        .include(y => y.reservations)                        .where(y => y executivesuite && y.reservations.any())                        .load();                  console.writeline("executive suites {0} reservations", hotel.name);                  foreach (var room in hotel.rooms)                 {                     console.writeline("\nexecutive suite {0} {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> continue...");             console.readline();         }    using ( var context = new datacontext() ) {          //context.configuration.lazyloadingenabled = false;          // assume have instance of hotel         var hotel = context.hotels.first();         var rooms = context.rooms.include( r => r.reservations ).where( r => r executivesuite && r.reservations.any() ).where( r => r.hotel.id == hotel.id );         console.writeline( "executive suites {0} reservations", hotel.name );          foreach ( var room in hotel.rooms )         {            console.writeline( "\nexecutive suite {0} {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 , putting in anonymous object:

       var hotel = context.hotels.select(h =>         new          {                id = h.id,             name = h.name,             rooms = h.rooms.where(r => r.reservations executivesuite && r.reservations.any())         }).first(); 

but exception: "dbisofexpression requires expression argument polymorphic result type compatible type argument."

now, if notice, implemented in 2 different ways, first explicitly loading related entities, second having 2 different queries, question be, there way can load object graph , filter entities "include" single trip database?

thanks.

there 2 way filter include entity.

• using projection (see @eldho answer)
• using third party library

disclaimer: i'm owner of project entity framework plus

the ef+ query includefilter allow filter included entities.

context.entry(hotel)        .collection(x => x.rooms)        .query()        .includefilter(y => y.reservations                             .where(z => z executivesuite && z.reservations.any())        .load(); 

under hood, library projection.

wiki: ef+ query include filter

edit: answer subquestion

you did it. rooms included , filtered, didn't include reservations.

var hotel = context.hotels     // include executive suite reservation     .includefilter(x => x.rooms.where(y => y executivesuite && y.reservations.any()))     // include reservation executive suite     .includefilter(x => x.rooms.where(y => y executivesuite).select(z => z.reservations))     .first();