c# - query multi-level entity with filter at the lowest level -

so have 3 entity classes:

public partial class event {     public event()     {         recurrences = new hashset<recurrence>();     }     public int id { get; set; }     public icollection<recurrence> recurrences { get; set; } }  public partial class recurrence {     public recurrence()     {         aspnetusers = new hashset<aspnetuser>();     }     public int id { get; set; }     public int eventid { get; set; }     public icollection<aspnetuser> aspnetusers { get; set; } }  public partial class aspnetuser {     public aspnetuser()     {         recurrences = new hashset<recurrence>();     }     public string id { get; set; }     public string username { get; set; }     public icollection<recurrence> recurrences { get; set; } } 

i event given aspnetuser.id using line entity. far have it's returning error:

// get: api/events?userid={userid}     public iqueryable<event> getevents(string userid)     {         return db.events              .include(e => e.recurrences                 .select(u => u.aspnetusers.where(i => i.id == userid)));     } 

when exclude clause works fine. please help. in advance!

i don't think include() means think means. (https://msdn.microsoft.com/en-us/library/bb738708%28v=vs.110%29.aspx) tell db set sure bring in relationships object. default (last checked), db context auto pull in relationships, isn't necessary. however, if you've turned off lazy-loading (http://www.entityframeworktutorial.net/entityframework4.3/lazy-loading-with-dbcontext.aspx) you'll need .include() relationships want have in query.

this should solve problem. don't guarantee sql generated won't silly, though.

if have lazy-loading turned on:

db.events.include("recurrences").include("recurrences.aspnetusers")   .where(e => e.recurrences     .any(r => r.aspnetusers       .any(u => u.id ==userid))); 

if have lazy-loading turned off:

db.events   .where(e => e.recurrences     .any(r => r.aspnetusers       .any(u => u.id ==userid))); 

also, if have trouble seeing errors, can .tolist() query before returning fails in code , not deep inside web api stack. personally, can try/catch query , handle properly.