LINQ: Get the Parent object properties and a single child property as a flat structure

Question

I have a list of addresses contained in a parent object called Branch. Branch may or may not have these addresses defined and I need to get a flat hierarchy or these branch and address.

var x = from p in CurrentBranchList 
        where p.ScheduledForDeletion == false
        from c in p.Addresses 
        where c.ScheduledForDeletion == false && c.AddressTypeId == 3
        select new
        {
          BranchId = p.BranchId,
          Name = p.Name,
          Address = (c == null) ? "" : c.Address1 + " " + c.Address2,
          City = (c == null) ? "" : c.City,
          State = (c == null) ? 0 : c.StateId
        };

The above is what I tried but if the Address is missing I get no information about the Branch...I'm still trying to figure out how to get this going with Linq. In SQL I would have just left joined the two tables to get that info.

Can anyone help me on this...I'm sure it's a pretty easy thing. Thanks. PS. I know this is very similar to (http://stackoverflow.com/questions/277634/linq-query-to-return-a-flatened-list-of-parent-child) but in that one the child always exists.

---

EDIT - WORKING SOLUTION The following is code that seemed to work for me. I can't go against a database for the source because the objects contained in the CurrentBranchList are edited in memory and persistence is performed in a single operation.

var x = from p in CurrentBranchList
        join c in CurrentBranchList.SelectMany(b => b.Addresses) 
          on p.EntityId equals c.EntityId into ur 
        where p.ScheduledForDeletion == false      
        from u in ur.DefaultIfEmpty() 
        select new
        {
          BranchId = p.BranchId,
          Name = p.Name,
          Address = (u == null) ? "" : u.Address1 + " " + u.Address2,
          City = (u == null) ? "" : u.City,
          State = (u == null) ? 0 : u.StateId
        };

Thank you for your help. Those links really helped me understand what needed to happen.

I also tried Daniel Brückner's solution and that appears to be more elegant and require less typing. :-) Seems to work in the couple scenarios I tried.

Here's what that looks like.

var xx = CurrentBranchList.SelectMany(b => b.Addresses.DefaultIfEmpty().Select(a => new 
        {
          BranchId = b.BranchId,
          Name = b.Name,
          Address = (a == null) ? "" : a.Address1 + " " + a.Address2,
          City = (a == null) ? "" : a.City,
          State = (a == null) ? 0 : a.StateId
        }));

Answer 1

You need a left outer join rather than an inner join. Here's how.

Answer 2

Take a look at this post which shows how to use the DefaultIfEmtpy construct to carry out a LEFT JOIN.

Not the most discoverable feature of Linq I'm afraid.

Answer 3

IQueryable<Branch> branches = GetBranches();

var result = braches.
   SelectMany(b => b.Addresses.
      DefaultIfEmpty().
      Select(a => new { Branch = b, Address = a }));