Linq query with subquery as comma-separated values
- by Keith
In my application, a company can have many employees and each employee may have have multiple email addresses.
The database schema relates the tables like this:
Company - CompanyEmployeeXref - Employee - EmployeeAddressXref - Email
I am using Entity Framework and I want to create a LINQ query that returns the name of the company and a comma-separated list of it's employee's email addresses. Here is the query I am attempting:
from c in Company
join ex in CompanyEmployeeXref on c.Id equals ex.CompanyId
join e in Employee on ex.EmployeeId equals e.Id
join ax in EmployeeAddressXref on e.Id equals ax.EmployeeId
join a in Address on ax.AddressId equals a.Id
select new {
c.Name,
a.Email.Aggregate(x=x + ",")
}
Desired Output:
"Company1", "a@company1.com,b@company1.com,c@company1.com"
"Company2", "a@company2.com,b@company2.com,c@company2.com"
...
I know this code is wrong, I think I'm missing a group by, but it illustrates the point. I'm not sure of the syntax. Is this even possible? Thanks for any help.
