C# Web API INNER JOIN and RETURN Query value

问题:

EF Model Image References

I was planned to read data from database and then using INNER JOIN in C# WebApi controller as the picture shown below.

Below query is for Inner Join references:

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

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:

回答1:

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.

回答2:

This is the completed answer that I sorted out, via DTO concept thanks to @vikscool contribution

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;
            }
        }
    }
}

It showed the result as follows:

[{"FirstName":"Peter","LastName":"Joe","Gender":"Male","Salary":1234,"Department_id":1,"Department_Name":"RND","Shift_id":"A","Duration":"morning"},{"FirstName":"John","LastName":"Doe","Gender":"Male","Salary":1234,"Department_id":2,"Department_Name":"Account","Shift_id":"B","Duration":"afternoon"},{"FirstName":"Mary","LastName":"Jones","Gender":"Female","Salary":5566,"Department_id":3,"Department_Name":"HR","Shift_id":"A","Duration":"morning"},{"FirstName":"Elizabeth","LastName":"Tan","Gender":"Female","Salary":9999,"Department_id":1,"Department_Name":"RND","Shift_id":"C","Duration":"night"},{"FirstName":"gg","LastName":"wp","Gender":"NoGender","Salary":8,"Department_id":1,"Department_Name":"RND","Shift_id":"B","Duration":"afternoon"}]

Thanks everyone, the problem had been solved

回答3:

Hopefully your join works ?!

If so, you can run your query through EF and get the results like below :

namespace WebApiJoinData.Controllers
{
[RoutePrefix("Api")]
public class JoinController : ApiController
{
    DepartmentServicesEntities DSE = new DepartmentServicesEntities();
    [Route("Api")]


        [HttpGet]
        public object JoinStatement()
        {
            string Msg = String.Empty;
            string sql = String.Format("Select FirstName, LastName, Gender, Salary, E.Department_id, Department_Name from Employee E INNER JOIN Department D on D.department_id = E.department_id");

            using (DSE)
            {
                //proceed the query and return Msg
                var results = DSE.Database.SqlQuery<object>(sql).ToList();                    
                Msg = Newtonsoft.Json.JsonConvert.SerializeObject(results);                    
                return results;
            }
        }
    }
}

I would suggest you create a DTO class instead of using object as this will help when you have large amounts of data.

Another way could be you return the data as json string