Neil and DotNet Dude, Thanks for your help on this one, I end up returning all 3 items, and it then maintained the navigation property automatically... strange, but great.
So end result is.. 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, c2, c3 Take 1 return q.First.c2 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Neil Young Sent: Monday, 18 April 2011 1:04 PM To: ozDotNet Subject: Re: Entity Framework and Linq Clint, ok didn't click on the bit about needing to retain the filter on the third level down. I don't think there's a way to do that with include statements, as by their very nature they are just meant to include the related objects to save a database call, it would the navigation properties a little exciting if you didn't know whether they were a filtered set or not. I don't think you are going to have much of a choice but to go out to a new specific type for this. On 18 April 2011 12:47, Clint Colefax <[email protected]> wrote: > I like the look of those strategies, however it's probably to late to > implement that in this current version of our project. Using you 2nd option > does not filter the "OrderLines". In my case, I get the right Customer, and > Countries, then every CountryRegion for that country. It doesn't maintain > the filter. > > I think I'll be looking at the 3rd option, into a non type, then > reconstructing the Customer class by added the other items to it via > navigation properties. This was something I was trying to avoid, but my > options are shrinking. > > > > Thanks for you help. > > > > From: [email protected] [mailto:[email protected]] > On Behalf Of Neil Young > Sent: Monday, 18 April 2011 12:10 PM > > To: ozDotNet > Subject: Re: Entity Framework and Linq > > > > Clint, > > > > Have three suggestions. > > > > 1. We are using EF in a repositry pattern and each repositry has it's own > include strategy using the code here (nicely strongly type): > > http://blogs.msdn.com/b/alexj/archive/2009/07/25/tip-28-how-to-implement -include-strategies.aspx, > this works really well and the blog post does say that it works in 3.5 (we > are in 4). Regardless of the repositry pattern you can still use this. > > > > 2. The help seems to indicate that you just call > Include("Orders.OrderLines"), and in that case it will include orders and > orderlines (haven't used include in a long time so can't comment on the > veracity of this) > > > > 3. You can always create an anonymous type and (or non anonymous) and > return the things you want explicitly in the select as DotNet Dude was > suggesting. > > > > Neil. > > On 18 April 2011 11:18, DotNet Dude <[email protected]> wrote: > > > > 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. > > Error! Filename not specified. > > > > > >
