时间戳

首页 > 解决方案 > 从查询 postgresql 转换为 linq C#

c# - 从查询 postgresql 转换为 linq C#

问题描述

我想使用带有“string_agg”的“groupby”与 linq 一起工作，但我无法从此查询中找到参考。

SELECT DISTINCT "TB_M_GROUP_EMP"."GROUP_ID" AS "ID", 
  string_agg(Distinct "TB_M_EMPLOYEES"."EMP_NAME",',' order by "TB_M_EMPLOYEES"."EMP_NAME" ) as "Employee Name"
FROM public."TB_M_GROUP_EMP", "TB_M_EMPLOYEES"
GROUP BY "TB_M_GROUP_EMP"."GROUP_ID";

或此代码

SELECT DISTINCT "TB_M_GROUP_EMP"."GROUP_ID" as "Group Id", array_agg(Distinct"TB_M_EMPLOYEES"."EMP_CODE" || ',' ||"TB_M_EMPLOYEES"."EMP_NAME"|| ',' ||"TB_M_EMPLOYEES"."EMP_EMAIL" ) as "Employees"
FROM public."TB_M_GROUP_EMP", "TB_M_EMPLOYEES"
GROUP BY "TB_M_GROUP_EMP"."GROUP_ID";

请帮助将此查询转换为 linq C# 并提供参考或文档 linq！

我曾尝试使用此代码，但这并不是我想要的输出。

groupEmployee
   .GroupBy(e => new { e.GroupId, e.Employee }, (key, group) => new
   {
      Employee = key.Employee,
      Employees = group.ToList()
   })

示例表：

--------------
TB_M_EMPLOYEES
--------------
|EMP_ID|EMP_CODE|EMP_NAME|EMP_EMAIL|
------------------------------------
|1     |E0012   |Ali     |@ali     |
|2     |E0013   |AL      |@al      |
|3     |E0014   |Abi     |@abi     |
|4     |E0015   |Ad      |@ad      |
|5     |E0016   |Ea      |@ea      |

----------------------
TB_M_GROUP_EMP
----------------------
|Id|GROUP_ID|EMP_ID|
----------------------
|1 |1       |1     |
|2 |1       |2     |
|3 |1       |3     |
|4 |1       |4     |
|5 |1       |5     |

从上面的两个表中。我希望输出是这个底部的表格。

TB_M_GROUP_EMP
-----------------
|GROUP_ID|EMP|
-----------------
|1       |{"E0012,Ali,@ali","E0013,Al,@al","E0014,Abi,@abi","E0015,Ad,@ad","E0016,Ea,@ea"}|

谢谢。

标签： c#postgresqlapilinqasp.net-core

解决方案

假设我们有以下模型：

public class Employee
{
    public int EmployeeId { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public class EmployeeGroup
{
    public int Id { get; set; }
    public int GroupId { get; set; }
    public int EmployeeId { get; set; }
}

试试看，下面：

var result = groups
    .Join(employees, grp => grp.EmployeeId, emp => emp.EmployeeId, (group, employee) => 
        new
        {
            group.GroupId,
            employee.EmployeeId,
            employee.Code,
            employee.Name,
            employee.Email,
        })
    .GroupBy(res => res.GroupId)
    .Select(res => new
    {
        GroupId = res.Key,
        Names = string.Join(",", res
                        .OrderBy(e => e.Name)
                        .Select(e => $"{e.Code},{e.Name},{e.Email}"))
    })
    .ToList();

然而，这里的另一个版本可能是（可能更接近你想要的输出）：

var result = groups.GroupJoin(employees, grp => grp.EmployeeId, emp => emp.EmployeeId,
    (grp, emp) => new
    {
        grp.GroupId,
        Names = string.Join(",", emp.Select(e => $"{e.Code},{e.Name},{e.Email}"))
    }).
    GroupBy(e=> e.GroupId)
    .ToList();

如果您想要上面的有序（按名称）版本，那么查询可能会变得更复杂一些：

var result = groups
    .GroupJoin(employees,
        grp => grp.EmployeeId,
        emp => emp.EmployeeId,
        (grp, emps) =>
        {
            var empsList = emps.ToList();

            return new
            {
                grp.GroupId,
                NamesCmp = empsList.Select(e => e.Name).FirstOrDefault(), // this needed for Ordering
                Names = string.Join(",", empsList.Select(e => $"{e.Code},{e.Name},{e.Email}"))
            };
        })
    .GroupBy(e => e.GroupId)
    .Select(grp => new
    {
        GroupId = grp.Key,
        Items = grp.OrderBy(g => g.NamesCmp)
            .Select(g => g.Names)
    })
    .ToList();

请注意，对上述使用真实类模型而不是匿名类型更可取。

推荐阅读