Linq query with subquery as comma-separated values

Question

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", "[email protected],[email protected],[email protected]"

"Company2", "[email protected],[email protected],[email protected]"

...

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.

Answer 1

It's actually rather difficult to do this in pure Linq to SQL (or Entity Framework, whichever one you're using) because SQL Server itself doesn't have any aggregate operator that can produce a comma delimited list, so it has no way to transform this entire statement into a single query. I could give you a "single-statement" Linq to SQL answer but it's actually not going to give you very good performance, and I'm not sure if it would work at all in EF.

It's uglier but still better if you just do a regular join, materialize the results, then do your concatenation using Linq to 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)
    };

Answer 2

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