问题描述:

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

网友答案:

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.

网友答案:

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
});
网友答案:

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
相关阅读:
Top