C# Web API INNER JOIN and RETURN Query value

Question

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:

if the `DepartmentServicesEntities` is related to `entity-framework`, then i would suggest making the [Join in LIND and Enityframework](https://stackoverflow.com/questions/33523974/join-in-linq-and-entity-framework). — vikscool, Feb 26 '20 at 04:24
I assume that you are using entity-framework, If so could you provide your EF model as well please? — Selim Yildiz, Feb 26 '20 at 05:40
Hi @vikscool thanks for your suggestion, however I can't perform the code given at [link]https://stackoverflow.com/questions/33523974/join-in-linq-and-entity-framework When I tried to perform ` var result = (from e in employee join d in department on e.department_id equals d.department_id select e.col1).Distinct();` the error was on my department(second line after join), it said 'department' is a type not a valid content @SelimYıldız I forget to mentioned you in the previous comment so I did it here — Lawraoke, Feb 26 '20 at 07:04
@Lawraoke i think you are getting the error is because of the entities(*`Department` and `Employee`*) being part of your context `DSE`. So. it should be something like:`var result = (from e in DSE.employee join d in DSE.department on e.department_id equals d.department_id select // your column names here).Distinct();` — vikscool, Feb 26 '20 at 10:08

MDT · Answer 1 · 2020-02-27T09:42:45.837

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

Hi @Manti_Core Thanks for your answer! However I had faced the error on (obj) It said the name 'obj' does not exist in the current context, while I change from 'obj' to 'object' it said Invalid expression term 'object' Do I need to generate another variable obj? I think the return data must be json string if I am not wrong(?) — Lawraoke, Feb 26 '20 at 07:38
my bad: obj to be replace with results, i have edited the answer. — MDT, Feb 26 '20 at 10:10
Hi @Manti_Core that was a great help from you! Thank you sooooo much!!! XD — Lawraoke, Feb 27 '20 at 00:52

score 1 · Accepted Answer · answered Feb 26 '20 at 10:21

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.

answered Feb 26 '20 at 10:21

vikscool

1,293
1
10
24

Hi @vikscool after I edited, your code was working for me, thank you so much! – Lawraoke Feb 27 '20 at 00:58
Hi @vikscool, here is the follow up question, if I want to further join the third table (such as workingshift as shift_id), should I perform another sql query (**join ws in DSE.workingshifts** after the first query) or I store the first result joined result in a temp table1? And which method you think was better for this solution? – Lawraoke Feb 27 '20 at 01:14
@Lawraoke that's good, glad we were of any help. Just a small suggestion, try to avoid creating another answer for your question(*with the resolution*) as it helps others in not to confuse. What you can do is edit your main question and post your completed solution as an **Update**. – vikscool Feb 28 '20 at 03:35
Hi @vikscool Opps Sorry for doing that, I am a new guy here, I going to make the correction – Lawraoke Feb 28 '20 at 03:58

score 1 · Answer 3 · answered Feb 27 '20 at 03:38

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

C# Web API INNER JOIN and RETURN Query value

UPDATE

3 Answers3

Linked