如何将数据从Sql对象复制到C＃模型属性

I have two tables:

我有两张桌子：

Employee: Id, Name, DepartmentId
员工：Id，Name，DepartmentId
Department: Id, Name
部门：Id，姓名

Employee.cs:

Employee.cs：

public int Id {get;set;}
public string Name {get;set;}
public int DepartmentId {get;set;}

Department.cs:

Department.cs：

public int Id {get;set;}
public string Name {get;set;}

ViewModel: EmployeeDepartmentVM:

ViewModel：EmployeeDepartmentVM：

public Department department {get;set;}
public List<Employee> employees {get;set;}

To Join these two tables I have written this code:

要加入这两个表我写了这段代码：

    SELECT E.* , D.Id as DId , D.Name as DName 
     from [Employee] as E
     LEFT OUTER JOIN [Department] as D
     ON E.DepartmentId = D.Id
     where D.Id = 1

How do I get EmployeeDepartmentVM type from the above query?

如何从上面的查询中获取EmployeeDepartmentVM类型？

I know if I write a model like my problem will be solved:

我知道如果我写一个像我的问题的模型将被解决：

public int Id {get;set;}
public string Name {get;set;}
public int DepartmentId {get;set;}
public int DId {get;set;}
public string Name {get;set;}

But I don't want to write extra model. Simply want bind query data into EmployeeDepartmentVM type.

但我不想写额外的模型。只需将绑定查询数据绑定到EmployeeDepartmentVM类型即可。

1 个解决方案

#1

I really don't see what's the challenge. The EmployeeDepartmentVM definition implies that you need to group the result set by the Department. Assuming the result set is unordered, it can be achieved by simply maintaining a dictionary for locating the view models of the already added departments during the read.

我真的不明白什么是挑战。 EmployeeDepartmentVM定义意味着您需要对Department的结果集进行分组。假设结果集是无序的，可以通过简单地维护字典来实现，该字典用于在读取期间定位已经添加的部门的视图模型。

Which leads to something like this:

这导致了这样的事情：

static List<EmployeeDepartmentVM> GetEmployeeDepartmentVMList(DbCommand command)
{
    var resultById = new Dictionary<int, EmployeeDepartmentVM>();
    using (var reader = command.ExecuteReader())
    {
        var employeeIdCol = reader.GetOrdinal("Id");
        var employeeNameCol = reader.GetOrdinal("Name");
        var departmentIdCol = reader.GetOrdinal("DId");
        var departmentNameCol = reader.GetOrdinal("DName");
        while (reader.Read())
        {
            var departmentId = reader.GetInt32(departmentIdCol);
            EmployeeDepartmentVM result;
            if (!resultById.TryGetValue(departmentId, out result))
            {
                result = new EmployeeDepartmentVM
                {
                    department = new Department(),
                    employees = new List<Employee>()
                };
                result.department.Id = departmentId;
                result.department.Name = reader.GetString(departmentNameCol);
                resultById.Add(departmentId, result);
            }
            var employee = new Employee();
            employee.Id = reader.GetInt32(employeeIdCol);
            employee.Name = reader.GetString(employeeNameCol);
            employee.DepartmentId = departmentId;
            result.employees.Add(employee);
        }
    }
    return resultById.Values.ToList();
}

Some things to note. The way written, your SQL query implies that Department related fields can be null (LEFT OUTER JOIN). However, the WHERE clause and also the Employee model (DepartmentId field non nullable) implies that it cannot happen. If the intent is to include the departments with no employees, then better change the join to RIGHT OUTER and use something like this:

有些事情需要注意。编写的方式，您的SQL查询意味着Department相关字段可以为null（LEFT OUTER JOIN）。但是，WHERE子句以及Employee模型（DepartmentId字段不可为空）意味着它不会发生。如果目的是包括没有员工的部门，那么最好将连接更改为RIGHT OUTER并使用如下内容：

// ...
if (reader.IsDBNull(employeeIdCol)) continue;
var employee = new Employee();
// ...

EDIT: For completeness, here is another approach. It's similar to the way EF materializes similar queries and does not need temporary dictionary, but requires the input set to be ordered by the PK of the master table, so you need to add

编辑：为了完整性，这是另一种方法。它与EF实现类似查询的方式类似，不需要临时字典，但需要输入集由主表的PK排序，因此需要添加

ORDER BY D.Id

at the end of your SQL. Databases can easily and efficiently provide such ordering, and the benefit of this solution is that it allows deferred execution and does not require processing the whole set in order to start returning results. It's not essential if you want to just get a list, but can be useful in other scenarios.

在SQL的最后。数据库可以轻松有效地提供此类排序，此解决方案的好处是它允许延迟执行，并且不需要处理整个集合以便开始返回结果。如果你想获得一个列表，这不是必需的，但在其他场景中可能很有用。

static IEnumerable<EmployeeDepartmentVM> GetEmployeeDepartmentVMs(DbCommand command)
{
    using (var reader = command.ExecuteReader())
    {
        var employeeIdCol = reader.GetOrdinal("Id");
        var employeeNameCol = reader.GetOrdinal("Name");
        var departmentIdCol = reader.GetOrdinal("DId");
        var departmentNameCol = reader.GetOrdinal("DName");
        for (bool more = reader.Read(); more;)
        {
            var result = new EmployeeDepartmentVM
            {
                department = new Department(),
                employees = new List<Employee>()
            };
            result.department.Id = reader.GetInt32(departmentIdCol);
            result.department.Name = reader.GetString(departmentNameCol);
            do
            {
                if (reader.IsDBNull(employeeIdCol)) continue;
                var employee = new Employee();
                employee.Id = reader.GetInt32(employeeIdCol);
                employee.Name = reader.GetString(employeeNameCol);
                employee.DepartmentId = result.department.Id;
                result.employees.Add(employee);
            }
            while ((more = reader.Read()) && reader.GetInt32(departmentIdCol) == result.department.Id);
            Debug.Assert(!more || reader.GetInt32(departmentIdCol) > result.department.Id); // Sanity check
            yield return result;
        }
    }
}

To get a list as in the first approach, just add ToList() after the call, e.g.

要获得第一种方法中的列表，只需在调用后添加ToList（），例如

var result = GetEmployeeDepartmentVMs(command).ToList();

#1