Linq query with subquery as comma-separated values

Posted by Keith on Stack Overflow See other posts from Stack Overflow or by Keith
Published on 2010-05-18T21:15:01Z Indexed on 2010/05/18 22:30 UTC
Read the original article Hit count: 306

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about LINQ

Related posts about subquery