Linq query with subquery as comma separated values

Question

In my application, a company can have many employees, and each employee can have multiple email addresses.

The database schema links the tables as follows:

Company → CompanyEmployeeXref → Employee → EmployeeAddressXref → Email

I am using Entity Framework and I want to create a LINQ query that returns the company name and comma-separated list of email addresses of employees. Here is the query I'm trying to make:

 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 that this code is wrong, I think that I am missing a group, but this illustrates the point. I am not sure of the syntax. Is it possible? Thanks for any help.

Answer 1

Here 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()) } )
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()) } ) 

Answer 2

This is actually quite difficult to do in pure Linq to SQL (or Entity Framework, depending on what you are using), because SQL Server itself does not have any aggregate statement that can create comma-separated lists, therefore it has no way to turn this entire statement into a single request. I could give you the "Linq to SQL" answer with one expression, but it really won’t give you very good performance, and I'm not sure if it will work in EF at all.

This is ugly, but still better if you just do regular joins, materialize the results, then do your concatenation using Linq for objects:

 var rows = (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 }).AsEnumerable(); var emails = from r in rows group r by r.Name into g select new { Name = g.Key, Emails = g.Aggregate((x, y) => x + "," + y) };