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屋！