C# Web API INNER JOIN 和 RETURN 查询值 [英] C# Web API INNER JOIN and RETURN Query value
问题描述
EF 模型图像参考
我打算从数据库中读取数据,然后在C# WebApi控制器中使用INNER JOIN,如下图所示.
I was planned to read data from database and then using INNER JOIN in C# WebApi controller as the picture shown below.
以下查询用于内部连接引用:
Select FirstName, LastName, Gender, Salary, E.Department_id, Department_Name
from Employee E
INNER JOIN Department D on D.department_id = E.department_id
<小时>
更新
答案已被以下代码确认通过DTO方法
public class JoinController: ApiController
{
DepartmentServicesEntities DSE = new DepartmentServicesEntities();
[Route("Api")]
[HttpGet]
public object JoinStatement()
{
using (DSE)
{
var result = (from e in DSE.employee join d
in DSE.department on e.department_id equals d.department_id
select new {
FirstName = e.FirstName,
LastName = e.LastName,
Gender = e.Gender,
Salary = Salary,
Department_id = e.department_id,
Department_Name = d.department_name
}).ToList();
// TODO utilize the above result
return result;
}
}
}
}
关于加入多表,解决方法在这里:
As for joining multiple table, the solution was here:
namespace WebApiJoinData.Controllers
{
[RoutePrefix("Api")]
public class JoinController : ApiController
{
DepartmentServicesEntities DSE = new DepartmentServicesEntities();
[Route("Api")]
[HttpGet]
public object JoinStatement()
{
using (DSE)
{
var result = (from e in DSE.employees
join d in DSE.departments on e.department_id equals d.department_id
join ws in DSE.workingshifts on e.shift_id equals ws.shift_id
select new
{
FirstName = e.FirstName,
LastName = e.LastName,
Gender = e.Gender,
Salary = e.Salary,
Department_id = e.department_id,
Department_Name = d.department_name,
Shift_id = ws.shift_id,
Duration = ws.duration,
}).ToList();
// TODO utilize the above result
string json = Newtonsoft.Json.JsonConvert.SerializeObject(result, Newtonsoft.Json.Formatting.Indented);
return result;
}
}
}
}
输出以下结果显示在此处:
推荐答案
根据上面给出的模型,您应该将查询更改为:
As per the Model given above, you should change your query as something like:
public class JoinController: ApiController
{
DepartmentServicesEntities DSE = new DepartmentServicesEntities();
[Route("Api")]
[HttpGet]
public object JoinStatement()
{
using (DSE)
{
var result = (from e in DSE.employee join d
in DSE.department on e.department_id equals d.department_id
select new {
FirstName = e.FirstName,
LastName = e.LastName,
Gender = e.Gender,
Salary = Salary,
Department_id = e.Department_id,
Department_Name = d.Department_Name
}).ToList();
// TODO utilize the above result
}
}
}
上述代码只有一个问题,因为结果总是匿名类型 对象.因此,当您遇到多实体连接结果以进行正确映射时,建议使用 Data Transfer Object
(DTO).
There is only one issue with the above code as the result will always be an Anonymous Type object. So, it is advisable to use a Data Transfer Object
(DTO) whenever you have a case of multi-entity join result for proper mapping.
这篇关于C# Web API INNER JOIN 和 RETURN 查询值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!