[DexterMorgan]

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 releationships 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 cant seem to filter results on the grandchild record no matter what i try. not finding too much on the internet either.
if anyone can shed some light i'd appreciate it. Thanks!

[DexterMorgan]

anybody? doesnt seem like anyone is on this forum. i was on another forum and people are telling this isnt possible. i find it hard to beleive that EF cant do this. (i am using 4.0 with vs 2010.

im going to use my real world example
i have 4 tables

Person > Plan > Coverage > CoveredMembers

Each person can have many plans each of those plans can have many coverages. each of those coverages can have many CoveredMembers.

Person 1 to Many Plans
Plan 1 to Many Coverages
Coverage 1 to Many CoveredMembers

i want to apply a filter on Plan.PlanType == 1 and coveredMember.TermDate == null
this query should bring back any person who has a medical type plan that is not terminated

this real sql statement would do just that.
SELECT Person.*, Plans.*, Coverages.*, CoveredMembers.* FROM Person P INNER JOIN Plan PL ON P.PersonID = PL.PersonID INNER JOIN Coverage C on PL.PlanID = C.PlanID INNER JOIN CoveredMember CM on C.CoverageID = CM.CoverageID WHERE CM.TermDate = NULL AND PL.PlanType = 1

i have figured out how to do this using anynomus types but i sometimes need to update the data and save back to the database and anyomus types are read only.

i also figured out how to query this with the filters but i only get the Persons back
i can then loop thru each person
foreach (var person in persons) {
foreach (var plan in person.Plans
}

but wouldnt that make a db call for each iteration of the loop? so i have 500 persons with 3 untermed medical plans each, it woud call the db 1500 times?
this is why i wanted to bring the whole data tree from Persons to CoveredMembers back in one shot. is this not possible?

This post has been edited by DexterMorgan: 25 January 2013 - 12:02 PM