EF Model Image References
![enter image description here](https://i.stack.imgur.com/k6UHP.png)
我计划从数据库读取数据,然后使用内部联接 in C# WebApi控制器如下图所示。
以下查询适用于内部联接参考:
Select FirstName, LastName, Gender, Salary, E.Department_id, Department_Name
from Employee E
INNER JOIN Department D on D.department_id = E.department_id
UPDATE
答案已通过以下代码确认通过连接数据的解决方案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;
}
}
}
}
至于加盟多表,解决方案在这里:
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;
}
}
}
}
输出结果如下所示here: