Linq查询，子查询为逗号分隔值 [英] Linq query with subquery as comma-separated values

问题描述

在我的应用程序中，公司可以有很多员工，每个员工可能有多个电子邮件地址.

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:

公司-> CompanyEmployeeXref->雇员-> EmployeeAddressXref->电子邮件

Company -> CompanyEmployeeXref -> Employee -> EmployeeAddressXref -> Email

我使用的是Entity Framework，我想创建一个LINQ查询，该查询返回公司名称和其员工电子邮件地址的逗号分隔列表.这是我正在尝试的查询:

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", "[e-mail],[e-mail],[e-mail]"

"Company2", "[e-mail],[e-mail],[e-mail]"

...

我知道这段代码是错误的，我想我错过了一个依据，但是它说明了这一点.我不确定语法.这有可能吗?感谢您的帮助.

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.

推荐答案

现在我解决了这个问题:

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

这篇关于Linq查询，子查询为逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！