Still pretty new to entity framework. So forgive me if this is a noob question. Hoping someone can shed some light on this.
I am trying to select data from 3 related tables.
Leagues -> Teams -> Rosters ->
The relationships are League.LeagueID => Team.LeagueID => Roster.TeamID
In the Roster table there is a PlayerID column
I need a query that can select all leagues where Roster has PlayerID = 1
I cannot seem to filter results on the grandchild record no matter what I try. Not finding too much on the internet either.
I have found a way to do this with anonymous types but those are read only so i can make changes to the data. I must be able to update the data after it returns.
db.Leagues.Where(l => l.Teams.Any(t => t.Roster.PlayerID == 1));
The SQL generated should get you what you want, even it looks unreadable ;)
If you want to specifically use inner joins to do this, you can do so with code like this:
from l in db.Leagues
join t in db.Teams on l.LeagueID equals t.LeagueID
join r in db.Rosters on t.TeamID equals r.TeamID
where r.PlayerID = 1
select l
UPDATE
To do with with eager loading the child associations use Include():
((from l in db.Leagues
join t in db.Teams on l.LeagueID equals t.LeagueID
join r in db.Rosters on t.TeamID equals r.TeamID
where r.PlayerID = 1
select l) as ObjectQuery<League>).Include(l => l.Teams.Select(t => t.Rosters))
db.Roasters.Where(r=>r.PlayerId ==1).Select(r=>r.Team).Select(t=>t.League).Distinct()
If Roaster has many teams and team has many leagues you can use .SelectMany instead of .Select
Example of .SelectMany from MSDN:
PetOwner[] petOwners =
{ new PetOwner { Name="Higa, Sidney",
Pets = new List<string>{ "Scruffy", "Sam" } },
new PetOwner { Name="Ashkenazi, Ronen",
Pets = new List<string>{ "Walker", "Sugar" } },
new PetOwner { Name="Price, Vernette",
Pets = new List<string>{ "Scratches", "Diesel" } } };
// Query using SelectMany().
IEnumerable<string> query1 = petOwners.SelectMany(petOwner => petOwner.Pets);
