Linq query with subquery as comma-separated values

delimited, linq, subquery

Solution

Here's now I solved the problem:

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
group a.Email by new {c.Name} into g
select new {
                Company=g.Key.Name,
                Email=g.Select(e=>e).Distinct()
            }
).ToList()
.Select(l=> 
           new {
                    l.Name,
                    Email=string.Join(",", l.Email.ToArray())
                }
        )

Problem

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.

Original source