How to use SQL Joins in Web API

Question

I need to pass SQL Query(with Joins) as parameter to my API and retrun the results. But the result is dependent on the using the below code. I there any way to get this fixed. Please advise.

Expected results should be all the columns from the query into var users variable.

public IHttpActionResult retData()
{
    using (var context = new DBModel())
    {
        var users = context.Database.SqlQuery<portal_testcase_scn>("SELECT *,Portal_Version.build FROM [portal_testcase_scn] inner join Portal_Version on [portal_testcase_scn].row_num=Portal_Version.row_num").ToList();
        return Ok(users);
    }
}

Answer 1

You can use Linq.

using (var context = new DBModel())
{
   List<object> users = (from c in context.portal_testcase_scn
     join d in context.Portal_Version
     on c.row_num equals d.row_num
     select new {
        build=d.build,
     }).ToList();
   return Ok(users);
}