Class with multiple List Properties of same type but with different restrictions

Question

Here's my problem: I have a class that have 2 list properties of the same class type (but with some different restriction as on how to be 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 some point, I need to take a list of Teams, with it's members and leaders, and for this I would assume something 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 this case I would be assuming it wrong (cause it's not working at all). Any ideas on how to achieve that?

EDIT: To clarify a bit more, the 2 list properties of the team class should be filled according to:

1 - Members attribute - Should include all related proggramers with no experience (proggramer.Experience == null or "");

2 - Leaders attribute - Should include all related proggramers with any experience (programmer.Experiente != null nor "");

EDIT 2: Here's the MyProjectDbContext declaration:

public class MyProjectDBContext : DbContext
{
    public DbSet<Team> Teams { get; set; }
    public DbSet<Programmer> Programmers { get; set; }
}

Answer 1

You are talking about EntityFramework (Linq to entities) right? If so, Include() is a Method of Linq To Entities to include a sub-relation in the result set. I think you should place the Where() outside of the Inlcude().

On this topic you'll find some examples on how to use the Include() method.

So I suggest to add the Include()'s first to include the relations "Members" and "Leaders" and then apply your Where-Statement (can be done with one Where()).

return db.Teams
    .Include("Team.Members")
    .Include("Team.Leaders")
    .Where(t => string.IsNullOrWhitespace(t.Members.Experience) ... )

What is unclear to me is your where criteria and your use-case at all as you are talking of getting a list of Teams with Leaders and Members. May above example will return a list of Teams that match the Where() statement. You can look though it and within that loop you can list its members and leaders - if that is the use-case.

An alternative is something like this:

return db.Members
    .Where(m => string.IsNullOrWhitespace(m.Experience))
    .GroupBy(m => m.Team)

This get you a list of members with no experience grouped by Team. You can loop the groups (Teams) and within on its members. If you like to get each team only once you can add a Distinct(m => m.Team) at the end.

Hope this helps. If you need some more detailed code samples it would help to understand your requirements better. So maybe you can say a few more words on what you expect from the query.

Update:

Just read our edits which sound interesting. I don't think you can do this all in one Linq-To-Entities statement. Personally I would do that on the getters of the properties Members and Leaders which do their own query (as a read-only property). To get performance for huge data amount I would even do it with SQL-views on the DB itself. But this depends a little on the context the "Members" and "Leaders" are used (high frequent etc).

Update 2:

Using a single query to get a table of teams with sublists for members and leaders I would do a query on "Programmers" and group them nested by Team and Experience. The result is then a list of groups (=Teams) with Groups (Experienced/Non-experience) with Programmers in it. The final table then can be build with three nested foreach-Statements. See here for some grouping examples (see the example "GroupBy - Nested").

Answer 2

Whenever you fetch entities, they will be stored in the context -- regardless of the form they are "selected" in. That means you can fetch the teams along with all the necessary related entities into an anonymous type, like this:

var teams =
    (from team in db.Teams
     select new {
         team,
         relatedProgrammers = team.Programmers.Where(
                                   [query that gets all leaders OR members])
     }).ToList().Select(x => x.team);

It looks like we're throwing away the relatedProgrammers field here, but those Programmer entities are still in memory. So, when you execute this:

foreach (var team in teams) team.LoadLists(db);

...it will populate the lists from the programmers that were already fetched, without querying the database again (assuming db is the same context instance as above).

Note: I haven't tested this myself. It's based on a similar technique shown in this answer.

EDIT - Actually, it looks like your "leaders" and "members" cover all programmers associated with a team, so you should be able to just do Teams.Include(t => t.Programmers) and then LoadLists.