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>>
