On Mon, Apr 18, 2011 at 11:12 AM, Clint Colefax < [email protected]> wrote:
> I have 3 tables with a many to one to many relationship. > > > > Customers => Countries => CountryRegions > > > > I want the first Customer where name = “Fred” and the CountryRegions where > Language = “English”. I want to include the Country and Country Region > entities > > > > Sql would be something like this. > > > > Select top 1 * > > From Customers as c1 > > Inner join Countries as c2 > > On c1.CountryNo = c2.CountryNo > > Inner join CountryRegions as c3 > > On c2.CountryNo = c3.CountryNo > > And c3.RegionLanguage = “English” > > Where c1.name = “Fred” > > Order c1.CreatedDate > > > > I am trying to replicate this in Entity Framework with Linq, and I just > can’t seem to get there at all. Any pointer would be helpful. I’m able to > either include all the sub elements, or condition on them, but can’t seem to > do both. I know I could do a load statement, but I’m trying to avoid that. > > > > One of my attempts > > > > Dim q = from c2 in context.Countries > > From c1 in c2.customers > > Where c2.Name = “Fred” > > From c3 in c2.CountryRegions > > Where c3.RegionLanguage = “English” > > Order by c1.CreatedDate > > Select c1 Take 1 > only had a quick look but you're only selecting the customer, if you want the country and region then you'd need to select them too in the last line > > > This gives me the correct customer, but without the country and country > region. I tried adding .Include() on the end of context.Countries without > luck. But by taking the result of this (q) and running q.include(“Country”) > I could get the country included, but I also need the CountryRegion, and > that’s on the other side of the one to many to one relationship. > > > > Any ideas? > > > > Thanks > > > > p.s. This is in entity framework 3.5 if that makes any difference. > > [image: Clint_C] > > >
