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

问题描述

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

数据库架构像这样关联表:

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

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

<上一页><代码>来自公司的 c在 c.Id 上的 CompanyEmployeeXref 中加入 ex 等于 ex.CompanyId在 ex.EmployeeId 上加入 e 的 Employee 等于 e.Id在 e.Id 上的 EmployeeAddressXref 中加入 ax 等于 ax.EmployeeId在 ax.AddressId 上加入地址等于 a.Id选择新的{c.姓名，a.Email.Aggregate(x=>x + ",")}期望的输出:"公司 1", "[e-mail],[e-mail],[e-mail]""公司 2", "[e-mail],[e-mail],[e-mail]"...

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

解决方案

现在我解决了这个问题:

<上一页><代码>来自公司的 c在 c.Id 上的 CompanyEmployeeXref 中加入 ex 等于 ex.CompanyId在 ex.EmployeeId 上加入 e 的 Employee 等于 e.Id在 e.Id 上的 EmployeeAddressXref 中加入 ax 等于 ax.EmployeeId在 ax.AddressId 上加入地址等于 a.Id将新 {c.Name} 的 a.Email 分组到 g选择新的{公司=g.Key.Name,电子邮件=g.Select(e=>e).Distinct()}).ToList().选择(升=>新的 {l.姓名，Email=string.Join(",", l.Email.ToArray())})

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", "[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屋！