我有两张桌子
>员工：Id,Name,DepartmentId
>部门：Id,Name
Employee.cs：
public int Id {get;set;}
public string Name {get;set;}
public int DepartmentId {get;set;}
Department.cs：
public int Id {get;set;}
public string Name {get;set;}
viewmodel：EmployeeDepartmentVM：
public Department department {get;set;}
public List<Employee> employees {get;set;}
要加入这两个表,我写了这个代码：
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
如何从上述查询获取EmployeeDepartmentVM类型？
我知道如果我写一个像我的问题的模型将会解决：
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;}
但我不想写额外的模型.只需要将查询数据绑定到EmployeeDepartmentVM类型中.
解决方法
我真的看不到什么是挑战. EmployeeDepartmentVM定义意味着您需要对部门的结果集进行分组.假设结果集是无序的,可以通过在读取期间简单地维护用于定位已经添加的部门的视图模型的字典来实现.
这导致如下：
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();
}
有些事情要注意写入的方式,您的SQL查询意味着Department相关的字段可以为null(LEFT OUTER JOIN).但是,WHERE子句和Employee模型(DepartmentId字段不可为空)意味着它不会发生.如果意图包括没有员工的部门,则更好地将联接更改为RIGHT OUTER,并使用以下内容：
// ... if (reader.IsDBNull(employeeIdCol)) continue; var employee = new Employee(); // ...
编辑：为了完整,这里是另一种方法.它类似于EF实现类似查询的方式,不需要临时字典,但要求输入集由主表的PK排序,因此您需要添加
ORDER BY D.Id
在你的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;
}
}
}
要按照第一种方法获得列表,只需在调用后添加ToList()即可.
var result = GetEmployeeDepartmentVMs(command).ToList();
