.SelectMany() and getting data from more than one related table

Question

This query returns employee Id, name, company id, company name, and company city. I'm missing employee email address (emailAddress stored in the EmployeeEmailAddress table) and employee phone numbers (phoneNumber stored in the EmployeePhoneNumbers table).

I needed to add the .SelectMany() to get the parent company relationship and access the company id, name, and city. Now, however, I can't access any properties not found in the PersonOrgMap table. I can't navigate to any other tables. Removing the .SelectMany() allows me to navigate to other tables but I lose access to the parent company information.

var employees = Contacts.Where(c => c.ContactAttributes
.Any (ca => ca.AttributeID == 1153))
.SelectMany (x => x.ChildPersonOrgMaps)
.Select (c => new { employeeId = c.Child.ContactID,
          c.Child.FirstName,
          c.Child.LastName,
          companyId = c.ParentContactID,
          c.Parent.OrganizationName,
          c.Parent.City
        }
         )
.OrderBy (c =>c.LastName ).ThenBy(x => x.FirstName)
.Dump();

Answer 1

If you're keen on the method syntax, then there's an overload on SelectMany() that also gives you access to both the "source" and "result" objects :

.SelectMany(x => x.ChildPersonOrgMaps, (x, c) => new { x, c })
.Select(xc => new 
{ 
    xc.x.Attribute1,
    xc.x.Attribute2,
    xc.c.Child.Attribute3,
    xc.c.Attribute4
});

Answer 2

This is where query expressions really help. If you start the query like this:

from c in Contacts
where c.ContactAttributes.Any (ca => ca.AttributeID == 1153))
from om in c.ChildPersonOrgMaps
...

you will have access to both the c and om variables later in the query. C# translates this into a SelectMany call by selecting into a temporary anonymous type that "carries" the original variable. The best way to see this is to write the query as a query expression in LINQPad, then look at the lambda tab to see the translation into fluent syntax.