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. — vikscool
– vikscool, Commented Feb 26, 2020 at 4:24
I assume that you are using entity-framework, If so could you provide your EF model as well please? — Selim Yildiz
– Selim Yildiz, Commented Feb 26, 2020 at 5:40
Hi @vikscool thanks for your suggestion, however I can't perform the code given at [link]stackoverflow.com/questions/33523974/… 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
– Lawraoke, Commented Feb 26, 2020 at 7: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
– vikscool, Commented Feb 26, 2020 at 10:08

vikscool · Accepted Answer · 2020-02-26 10:21:13Z

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, 2020 at 10:21

vikscool

1,3131 gold badge11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lawraoke Over a year ago

Hi @vikscool after I edited, your code was working for me, thank you so much!

Lawraoke Over a year ago

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?

vikscool Over a year ago

@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.

Lawraoke Over a year ago

Hi @vikscool Opps Sorry for doing that, I am a new guy here, I going to make the correction

Lawraoke · Accepted Answer · 2020-02-27 03:38:35Z

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

MDT · Accepted Answer · 2020-02-27 09:42:45Z

1

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

edited Feb 27, 2020 at 9:42

answered Feb 26, 2020 at 7:03

MDT

1,7054 gold badges20 silver badges31 bronze badges

3 Comments

Lawraoke Over a year ago

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(?)

MDT Over a year ago

my bad: obj to be replace with results, i have edited the answer.

Lawraoke Over a year ago

Hi @Manti_Core that was a great help from you! Thank you sooooo much!!! XD

Collectives™ on Stack Overflow

C# Web API INNER JOIN and RETURN Query value

UPDATE

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related