c# - Class with multiple List Properties of same type but with different restrictions -

here's problem: have class have 2 list properties of same class type (but different restriction on how filled), let's say:

public class team  {     [key]     public int idteam { get; set; }      public string teamname { get; set; }      public list<programmer> members { get; set; }      public list<programmer> leaders { get; set; }      public loadlists(myprojectdbcontext db)      {         this.members = db.programmers.where(p => p.idteam = this.idteam                   && (p.experience == "" || p.experience == null)).tolist();          this.leaders = db.programmers.where(p => p.idteam = this.idteam                   && (p.experience != null && p.experience != "")).tolist();     } }  public class programmer  {     [key]     public int idprogrammer { get; set; }      [foreignkey("team")]     public int idteam { get; set; }     public virtual team team { get; set; }      public string name { get; set; }      public string experience { get; set; } } 

at point, need take list of teams, it's members , leaders, , assume like:

return db.teams     .include(m => m.members.where(p => p.experience == "" || p.experience == null)     .include(l => l.leaders.where(p => p.experience != null && p.experience != "")     .orderby(t => t.teamname)     .tolist(); 

and, of course, in case assuming wrong (cause it's not working @ all). ideas on how achieve that?

edit: clarify bit more, 2 list properties of team class should filled according to:

1 - members attribute - should include related proggramers no experience (proggramer.experience == null or "");

2 - leaders attribute - should include related proggramers experience (programmer.experiente != null nor "");

edit 2: here's myprojectdbcontext declaration:

public class myprojectdbcontext : dbcontext {     public dbset<team> teams { get; set; }     public dbset<programmer> programmers { get; set; } } 

you talking entityframework (linq entities) right? if so, include() method of linq entities include sub-relation in result set. think should place where() outside of inlcude().

on this topic you'll find examples on how use include() method.

so suggest add include()'s first include relations "members" , "leaders" , apply where-statement (can done 1 where()).

return db.teams     .include("team.members")     .include("team.leaders")     .where(t => string.isnullorwhitespace(t.members.experience) ... ) 

what unclear me criteria , use-case @ talking of getting list of teams leaders , members. may above example return list of teams match where() statement. can though , within loop can list members , leaders - if use-case.

an alternative this:

return db.members     .where(m => string.isnullorwhitespace(m.experience))     .groupby(m => m.team) 

this list of members no experience grouped team. can loop groups (teams) , within on members. if each team once can add distinct(m => m.team) @ end.

hope helps. if need more detailed code samples understand requirements better. maybe can few more words on expect query.

update:

just read our edits sound interesting. don't think can in 1 linq-to-entities statement. on getters of properties members , leaders own query (as read-only property). performance huge data amount sql-views on db itself. depends little on context "members" , "leaders" used (high frequent etc).

update 2:

using single query table of teams sublists members , leaders query on "programmers" , group them nested team , experience. result list of groups (=teams) groups (experienced/non-experience) programmers in it. final table can build 3 nested foreach-statements. see here grouping examples (see example "groupby - nested").