Multiple Left Joins against Entity Framework

Question

I've been looking but can't find a solution to this...

Here are my Entities...

• ContentPage (has many ContentPageZones)
  
• ContentPageZone (Has One Content)
  
• Content

I want to query for a ContentPage by ID , and, I want it to contain all related ContentPageZones that are active and that have at least one Content that is active (both have IsActive bool property). If there are no ContentPageZones available that have active Content, I still want the ContentPage with an empty list of ContentPageZones.

Suggestions?

Answer 1

In your question, you say:

ContentPageZone (Has One Content)

Which implies that ContentPageZone -> Content is 1..[0,1].

Then later you say:

...all related ContentPageZones that are active and that have at least one Content that is active...

Which implies that ContentPageZone -> Content is 1..*.

I'm presuming that the first is correct, but the query would be different for the second.

var q = from cp in Context.ContentPages
        where cp.ID == someId
        select new 
        {
            ID = cp.ID,
            Name = cp.Name,
            ContentPageZones = from cpz in cp.ContentPageZones
                               where cpz.Content.IsActive
                               select new 
                               {
                                   ID = cpz.ID,
                                   // etc.
                               }
        };