c# - 使用 LINQ 对 ID、EmailListID、PhoneListID 等进行分组

Question

我正在使用 Dapper 将我的数据库内容映射到 EmployeeModel对象。

属性的映射是有效的，但最后的分组仍然给我带来麻烦:

EmployeeModel需要 List<PhoneModel>和 List<EmployeeModel>作为属性。

项目根据 EmployeeID 分组，但是返回多个电子邮件和电话结果，因为我无法找到这样做的语法。

我试过循环遍历 EmployeeIDs在 employeeList按 ID 分组后, 在它被 ID 分组之前，虽然它按 ID 分组.

var sql = @"

SELECT
    e.id,
    e.FirstName, e.LastName, e.Nickname, 
    em.id as ID, em.Address, em.Type, 
    jt.id as ID, jt.Name, 
    e.id as ID, p.Number, p.Type,
    d.id as ID, d.Name,
    es.id as ID, es.Name 

FROM
    dbo.Employees e 
    LEFT JOIN dbo.Emails         em ON em.EmployeeID  = e.id
    LEFT JOIN dbo.JobTitles      jt ON e.JobTitleID   = jt.id
    LEFT JOIN Phones             p  ON p.EmployeeID   = e.id
    LEFT JOIN dbo.Departments    d  ON e.DepartmentID = d.id
    LEFT JOIN dbo.EmployeeStatus es ON e.StatusID     = es.id
";

IEnumerable<EmailModel> emailsGrouped = new List<EmailModel>();

var employees = await connection
    .QueryAsync<
        EmployeeModel,EmailModel,TitleModel,
        PhoneModel,DepartmentModel,StatusModel,
        EmployeeModel>
    (
        sql,
        ( e, em, t, p, d, s ) =>
        {
            e.EmailList.Add(em);
            e.JobTitle = t;
            e.PhoneList.Add(p);
            e.Department = d;
            e.Status = s;
            return e;
       },
        splitOn: "ID, ID, ID, ID, ID"
    );


foreach (EmployeeModel emod in employees)
{
    emod.EmailList.GroupBy(em => em.ID);
}
            
var result = employees
    .GroupBy(e => e.ID)
    .Select(g =>
    {
        var groupedEmployee = g.First();
        groupedEmployee.EmailList = g.Select(e => e.EmailList.Single()).ToList();
        groupedEmployee.PhoneList = g.Select(e => e.PhoneList.Single()).ToList();
    
        return groupedEmployee;
    });

return result.ToList();

根据要求，这是我的电子邮件定义。它在我的 EmployeeClass 中，所以我已经发布了整个内容。

public class EmployeeModel
    {
        public int ID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Nickname { get; set; }
        public DepartmentModel Department { get; set; }
        public TitleModel JobTitle { get; set; }
        public DateTime HireDate { get; set; }
        public StatusModel Status { get; set; }
        public List<EmailModel> EmailList { get; set; } = new List<EmailModel>();
        public List<PhoneModel> PhoneList { get; set; } = new List<PhoneModel>();
        public List<RestrictionModel> RestrictionsList { get; set; } = new List<RestrictionModel>();
        public List<CitationModel> CitationsList { get; set; } = new List<CitationModel>();
        public List<CertificationModel> CertificationList { get; set; } = new List<CertificationModel>();

        public string ListView
        {
            get
            {
                return $"{LastName}, {FirstName}";
            }
        }
        public string ToEmailString()
        {
            IEnumerable<string> employeeEmailStrings = EmailList.Select(emmod => emmod.ToString());
            string employeeEmailString = string.Join($"{Environment.NewLine}", employeeEmailStrings);
            return $"{FirstName}, {LastName}: {Environment.NewLine} -{JobTitle.Name}- {Environment.NewLine}";
        }

        //IEnumerable<string> phoneStrings = PhoneList.Select(plistmod => plistmod.ToString());
        //string phoneString = string.Join($"{Environment.NewLine}", phoneStrings);



        public string ToCertificationString()
        {
            IEnumerable<string> certificationStrings = CertificationList.Select(clistmod => clistmod.ToString());
            string certificationString = string.Join($"{Environment.NewLine}", certificationStrings);
            return certificationString;
        }
        public class EmailModel
        {
            public int ID { get; set; }
            public string Address { get; set; }
            public string Type { get; set; }

            public override string ToString()
            {
                return $"{Address} ({Type})";
            }
        }
        public class PhoneModel
        {
            public int ID { get; set; }
            public string Number { get; set; }
            public string Type { get; set; }
            public override string ToString()
            {
                return $"{Number} ({Type})";
            }
        }
    }
}

我现在正在尝试的是遍历 EmployeeModel 中的电子邮件。创建一个新的电子邮件列表，然后将该新列表设置为 EmployeeModel.List<EmailModel> .

Answer 1

因此，看起来您实际上是在尝试使用 SQL 从数据库加载对象图(包含不同类型的节点)，而您正在尝试使用单个查询来执行此操作。
那不行 . (天真，单查询)SQL 不适合查询对象图。这就是 ORM 存在的原因。但是，使用一些 RDBMS 特定的 SQL 扩展(例如 T-SQL、PL/SQL 等)来执行查询批处理，您可以从数据库返回对象图。
好消息是，Dapper 通过 QueryMultiple 支持这种情况。 - 但是据我所知它不会映射集合属性，因此您需要手动执行此操作(请继续阅读!)
(我注意到 Entity Framework ，具体来说，将生成单个 SELECT 查询，这些查询在表示低多重性数据的列中返回冗余数据 - 这有其权衡，但一般来说，单独的查询可以通过正确的调整整体更快地工作(例如使用表值变量来保存 KEY 值，而不是为批处理中的每个查询重新评估相同的 WHERE 条件——一如既往，检查您的索引、STATISTICS 对象和执行计划!)。

当查询一个对象图时，你会写一个 SELECT查询批处理，其中每个查询返回具有 JOIN 的相同类型的所有对象。与具有 1:1 的任何其他实体或 1:0..1多重性(如果在同一批次的单独查询中加载它们不是更有效)。
在你的情况下，我看到你有:

[Employees]---(1:m)---[Phones]
[Employees]---(1:m)---[Emails]

[JobTitles]---(1:m)---[Employees]
[Departments]---(1:m)---[Employees]
[EmployeeStatus]---(1:m)---[Employees]        // is this an enum table? if so, you can probably ditch it

所以试试这个:

为简单起见，JobTitles , Departments , 和 EmployeeStatus可以在单个查询中完成。

我假设外键列是 NOT NULL所以一个 INNER JOIN应该用来代替 LEFT OUTER JOIN .

const String EMPLOYEES_PHONES_EMAILS_SQL = @"

-- Query 1: Employees, Departments, EmployeeStatuses
SELECT
    e.id,
    e.FirstName,
    e.LastName,
    e.Nickname,
    t.Name AS JobTitleName, -- This is to disambiguate column names. Never rely on column ordinals!
    d.Name AS DepartmentName,
    s.Name AS StatusName
FROM
    dbo.Employees AS e
    INNER JOIN dbo.JobTitles      AS t ON e.JobTitleID   = t.id
    INNER JOIN dbo.Departments    AS d ON e.DepartmentId = d.id
    INNER JOIN dbo.EmployeeStatus AS s ON e.StatusID     = s.id;

-- Query 2: Phones
SELECT
    p.EmployeeId,
    p.Number,
    p.Type
FROM
    dbo.Phones AS p;

-- Query 3: Emails
SELECT
    m.id,
    m.EmployeeId,
    m.Address,
    m.Type
FROM
    dbo.Emails AS m;
";

using( SqlMapper.GridReader rdr = connection.QueryMultiple( EMPLOYEES_PHONES_EMAILS_SQL ) )
{
    List<EmployeeModel>          employees          = ( await rdr.ReadAsync<EmployeeModel>() ).ToList();
    var phonesByEmployeeId = ( await rdr.ReadAsync<PhoneModel>   () ).GroupBy( p => p.EmployeeId ).Dictionary( grp => grp.Key grp => grp.ToList() );
    var emailsByEmployeeId = ( await rdr.ReadAsync<EmailModel>   () ).GroupBy( m => m.EmployeeId ).Dictionary( grp => grp.Key, grp => grp.ToList() );

    foreach( EmployeeModel emp in employees )
    {
        if( phonesByEmployeeId.TryGetValue( emp.EmployeeId, out var phones ) )
        {
            emp.Phones.AddRange( phones );
        }
        
        if( emailsByEmployeeId.TryGetValue( emp.EmployeeId, out var emails ) )
        {
            emp.Emails.AddRange( emails );
        }
    }
}

我承认我对 Dapper 并不十分熟悉 - 和 上面的代码有问题 :它不指示 Dapper 如何阅读包含的 Department , JobTitleModel , 和 EmployeeStatus第一个查询中的数据。我假设 ReadAsync 有一些过载指定其他包含的数据。
如果您发现自己重复执行这种逻辑，则可以定义自己的扩展方法来处理最糟糕的部分(例如 GroupBy().ToDictionary() ，并从已加载实体的字典中填充集合属性)。

如果您有过滤条件，那么您需要存储结果 EmployeeId TVV 中的关键值，或重复 Employees 上的条件作为 INNER JOIN 的右侧在 Phones 的查询中和 Emails .
例如，如果您想添加按姓名查找所有员工(及其电话号码和电子邮件地址)的功能，您可以这样做:

const String EMPLOYEES_PHONES_EMAILS_SQL = @"

-- Query 0: Get EmployeeIds:
DECLARE @empIds TABLE ( EmpId int NOT NULL PRIMARY KEY );
INSERT INTO @empIds ( EmpId )
SELECT
    EmployeeId
FROM
    dbo.Employees
WHERE
    FirstName LIKE @likeFirst
    OR
    LastName LIKE @likeLast;

-- Query 1: Employees, Departments, EmployeeStatuses
SELECT
    e.id,
    e.FirstName,
    e.LastName,
    e.Nickname,
    t.Name AS JobTitleName, -- This is to disambiguate column names. Never rely on column ordinals!
    d.Name AS DepartmentName,
    s.Name AS StatusName
FROM
    dbo.Employees AS e
    INNER JOIN dbo.JobTitles      AS t ON e.JobTitleID   = t.id
    INNER JOIN dbo.Departments    AS d ON e.DepartmentId = d.id
    INNER JOIN dbo.EmployeeStatus AS s ON e.StatusID     = s.id

    INNER JOIN @empIds AS i ON i.EmpId = e.EmployeeId;

-- Query 2: Phones
SELECT
    p.EmployeeId,
    p.Number,
    p.Type
FROM
    dbo.Phones AS p
    INNER JOIN @empIds AS i ON i.EmpId = p.EmployeeId;

-- Query 3: Emails
SELECT
    m.id,
    m.EmployeeId,
    m.Address,
    m.Type
FROM
    dbo.Emails AS m
    INNER JOIN @empIds AS i ON i.EmpId = m.EmployeeId;
";

using( SqlMapper.GridReader rdr = connection.QueryMultiple( EMPLOYEES_PHONES_EMAILS_SQL, new { likeFirst = "%john%", likeLast = "%smith%" } ) )
{
    // same as before
}