Thanks Dude, I will have a look at this, I was trying to avoid this as I will have to return 3 classes in a anon type, then reconstruct them via navigation properties.
Thanks From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of DotNet Dude Sent: Monday, 18 April 2011 11:18 AM To: ozDotNet Subject: Re: Entity Framework and Linq On Mon, Apr 18, 2011 at 11:12 AM, Clint Colefax <clint.cole...@dataaspects.com.au> 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. Clint_C