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

This query returns employee Id, name, company id, company name, and company city. I'm missing employe开发者_运维知识库e 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();

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
});

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.

I agree with Joe Albahari - use query syntax. This is the one thing (that I'm aware of) that you can do with query that you can't with method syntax.

You could try selecting into an anonymous type that contains your parent and child objects, but I don't think that EF will like it very much.

var employees = Contacts.Where(c => c.ContactAttributes 
.Any (ca => ca.AttributeID == 1153)) 
.SelectMany (x => new { Parent = x, Child = x.ChildPersonOrgMaps })
// etc

I'm learning LINQ and I prefer to split the commands so I can know what is happening where. It is certainly not efficient as the accepted answer but probably can come to use of anyone who's learning LINQ.

I'll prefer to demonstrate with some dummy data

var persons = new [] {"John's", "Mike's", "Albert's"};
var objects = new [] {"car", "house", "bicycle"};
var colors  = new [] {"red", "blue", "green", "yellow"};


var firstPair = persons.SelectMany(_ => objects, (p, o) => new {
   Person = p,
   Object = o
});

var secondPair = firstPair.SelectMany(_ => colors, (fp, c) => new {
   Person = fp.Person,
   Object = fp.Object,
   Color = c
});

It gives output in LINQ Pad like this. enter image description here