1

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; } }
Improve this question
7
  • Could you please define your team seach criteria. Do you need to get a list of teams that consist of only experienced leads and all programmers with no experience??? Commented Sep 4, 2013 at 11:48
  • Does db.Team (or DataContext.Table for that matter) even expose the .Include method? I can't seem to find it in my project nor in any official MSDN references. Maybe you could try InsertOnSubmit / InsertAllOnSubmit or Attach / AttachAll. Commented Sep 4, 2013 at 11:56
  • @maremp Include is exposed by the DbSet class. msdn.microsoft.com/en-us/library/… Commented Sep 4, 2013 at 12:07
  • Oh, I thought it's a DataContext class, the class name misled me. Commented Sep 4, 2013 at 12:09
  • @maremp MyProjectDBContext is a DbContext class with DbSets on it. Commented Sep 4, 2013 at 12:11

2 Answers 2

Reset to default
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").

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

No good. Youre missing the point. If i follow this path i'll be filtering Teams and this is not the point. One team will have relationship with n programmers, some of them with Experience and some not. I want to list ALL teams, but need to put some programmers in one attribute and others on another attribute. hence the where inside the include (which I know ot's wrong but will give the idea of the objective).
Just read your edits and updated my answer. I would do it with read-only properties "Members" and "Leaders". Each of them does its query and therefore returns a filtered subset of the Programmers relation.
that'll do the trick. But, for getting a list of all teams and then on each one fetch two other lists from the Database... Wouldn't this be too heavy on queries?
Yes... I do agree that it would coast too much... Just read it on your update... Thanks, i'll re-design the solution. :D
Are you loading all Teams with all Members and Leaders (aka Programmers) anyway? Is this in just in just one use-case so a query will do it or do you need them as general available properties? How much Teams and Programmers we are talking about?
|
0

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.

Improve this answer

2 Comments

So, what you're suggesting is: Fetch all related data so that when i need to get Members or Leaders ot'll already be in memory?
@MarceloMyara That's my understanding, yes. Let me know if it works.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.