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.
[cid:[email protected]]
<<inline: image001.jpg>>
