c# - 使用 LINQ 或 Entity Framework 拆分数据表以列出对象 C#

Question

我有一个数据表，其中包含一些行，这是使用以下查询连接两个表的结果:

SELECT 
    e.Id AS EmpId,
    e.Name AS EmpName,
    d.Id AS DeptId,
    d.Name AS DeptName
FROM  
    Departments D
JOIN 
    Employees E ON D.Id = E.DeptId

另一方面，我有这些类(class):

class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
    public int DeptId { get; set; }
}

class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Employee> Employees { get; set; }
}

那么，如何将给定的数据表转换为部门列表，并且每个部门都有自己的员工列表等等...

满足相同需求的基本解决方案，可能如下，但它不是专业的，它可能会影响大量数据的性能，特别是如果我们谈论的是多个连接，而不是像提供的那样简单的例子:

var depts = new List<Department>();
var view = dt.DefaultView.ToTable(true, "DeptId", "DeptName");
foreach (DataRow row in view.Rows)
{
    var dep = new Department();
    var depEmployees = new List<Employee>();
    dep.Id = int.Parse(row["DeptId"].ToString());
    dep.Name = row["DeptName"].ToString();
    foreach (var emp in dt.AsEnumerable()
        .Where(w => int.Parse(w["DeptId"].ToString()) == dep.Id).ToList())
    {
        depEmployees.Add(new Employee
        {
            Id = int.Parse(emp["EmpId"].ToString()),
            Name = emp["EmpName"].ToString(),
            City = emp["City"].ToString(),
            DeptId = dep.Id
        });
        dep.Employees = depEmployees;
    }

    depts.Add(dep);
}

Answer 1

这个解决方案怎么样:

var view = dt.DefaultView.ToTable(true, "DeptId", "DeptName");
var departments = view.Rows.OfType<DataRow>()
    .ToLookup(row => row["DeptId"])
    .Select(group => new Depatment
        {
            Id = group.Key, // convert somehow
            Name = group.First()["DeptName"], // convert somehow
            Employees = group.Selec(row => new Employee
            {
                Id = row["EmpId"], // convert somehow
                Name = row["EmpName"] // convert somehow
            }).ToList();
        }).ToList();