Entity Framework 4 Linq help - Pulling data from multiple tables filtered

1
votes

Not sure how this is done, I have my .edmx set up so that the navigation properties match the foreign key relationships on the tables. Not sure if I still need to perform joins or if EF will give me access to the related table data through the navigational properties automatically.

enter image description here

What I need to do it get all the ContentSections and their associated ContentItems based on the ContentView and filtered by the DiversionProgram.CrimeNumber.

I would like to get back IEnumerable, for each ContentSection it should have access to it's ContentItems via the navigation property ContentItems

Thanks

4
entity-framework-4linq-to-entities
Are you setting up a criminal network? Can I Join? ;) - Bernoulli IT
Sure it's easy to join... just find your local cop and give him a big slap in the face and you will instantly be part of the network! LOL - JBeckton

4 Answers

0
votes

Something like:

using(Entities context = new Entities())
{
  IEnumerable<ContentSection> enumerator = context.ContentSections
  .Include("ContentItems")
  .Where<ContentSection>(cs => cs.ContentView.ContentViewID == someID && cs.ContentItems.Where<ContentItem>(ci => ci.DiversionProgram.CrimeNumber == someCrimeNumber))
  .AsEnumerable<ContentSection>
}

I've interpreted

based on the ContentView

as cs.ContentView.ContentViewID == someID

This will give you all the ContentSections for a given ContentView. And interpreted

filtered by the DiversionProgram.CrimeNumber

as cs.ContentItems.Where<ContentItem>(ci => ci.DiversionProgram.CrimeNumber == someCrimeNumber)

which will give you all those ContentItems that have a specific CrimeNumber.

Or did you mean something else with based on / filtered by. Maybe OrderBy, or all those ContentSections where Any of it's ContentItems would have a certain CrimeNumber?

0
votes

You can eager load to get all associated records, but when you want to start filtering/ordering, don't bother with Include.

Just do a projection with anonymous types and EF will work out what it needs to do. It's a bit hairy, but it'll work. If it get's too complicated, bite the bullet and use a SPROC.

Now, with that caveat, something like this (off the top of my head):

var query = ctx.ContentView
               .Select(x => new 
{
   ContentSections = x.ContentSections
                      .Where(y => y.ContentItems
                        .Any(z => z.DivisionProgram.CrimeNumber = 87))
}).ToList().Select(x => x.ContentSections);
0
votes

If you use the CTP5 you can do something very unique it looks like this:

var context = new YourEntitiesContext();

var query = context.ContentView.Include(cs => cs.ContentSections
                   .Select(ci => ci.ContentItems
                   .Select(dp => dp.DiversionProgram)
                   .Where(dp.CrimeNumber == crimeNumber)))
                   .Where(cv => cv.ContentViewID == contentViewID).FirtsOrDefault();

You can learn more about the CTP5 and how it can be used in Database first scenario here

0
votes
 var query = from t1 in studentManagementEntities.StudentRegistrations
                        join t2 in studentManagementEntities.StudentMarks
                        on t1.StudentID equals t2.StudentID
                        select new
                        {
                            t1.selected column name,
                            t2.selected column name
                        };