Thanks James,
That's pretty much what I ended up with, although I'm using EF, so navigating the joins via EF navigation properties, rather then do a linq join. No worries about it being c#, I'm actually starting to prefer c# I think.. Thanks From: [email protected] [mailto:[email protected]] On Behalf Of James Chapman-Smith Sent: Tuesday, 19 April 2011 12:25 PM To: 'ozDotNet' Subject: RE: Entity Framework and Linq Hi Clint, Is this what you're after? var q = from c1 in context.Customers where c1.Name == "Fred" join c2 in context.Countries on c1.Country equals c2 join c3 in context.CountryRegions on c2 equals c3.Country where c3.RegionLanguage == "English" orderby c1.CreatedDate select new { Customer = c1, Country = c2, Region = c3, }; My apologies for it being c#. It assumes that your model looks like this: public class Context { public List<Customer> Customers = new List<Customer>(); public List<Country> Countries = new List<Country>(); public List<Region> CountryRegions = new List<Region>(); } public class Country { public List<Customer> Customers = new List<Customer>(); public List<Region> CountryRegions = new List<Region>(); } public class Customer { public string Name; public Country Country; public DateTime CreatedDate; } public class Region { public Country Country; public string RegionLanguage; } From: [email protected] [mailto:[email protected]] On Behalf Of Clint Colefax Sent: Monday, 18 April 2011 10:42 To: ozDotNet Subject: Entity Framework and Linq 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 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.
<<image001.jpg>>
