C#Web API INNER JOIN和RETURN查询值 [英] C# Web API INNER JOIN and RETURN Query value
问题描述
EF模型图像参考
EF Model Image References
我计划从数据库中读取数据,然后在 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方法
UPDATE
The answer had been confirmed by the following code Solution for joining data via DTO method
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;
}
}
}
}
在此处显示了以下结果的输出:
The output following result was shown here:
推荐答案
根据上面给出的模型,您应该将查询更改为:
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
}
}
}
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屋!