Join 3 tables in ASP.NET Core MVC

I am trying to learn API development. This is my query:

select 
    companyusers.companyid, companies.companyname,
    companyusers.name, companyusers.id, compnayusers.designation, 
    personalInfo.otherinfo
from
    companyusers, companies, personalInfo
where 
    compnayusers.companyid = 2
    and compnayusers.id = personalInfo.userID
    and compnayusers.companyid = companies.companyid
    and compnayusers.IsRemote = 0
order by 
    compnayusers.name

I have to create an API that would parse the results of the above query in the JSON format.

My model classes:

[Table("companies")]
public class Companies
{
    public int compnayid { get; set; }
    public string companyname { get; set; }
}

[Table("companyusers")]
public class CompanyUsers
{
    public int id { get; set; }
    public string name { get; set; }
    public string designation { get; set; }
    public int IsRemote { get; set; }
    public int companyid { get; set; }
}

[Table("personalInfo")]
public class PersonalInfo
{
    public int userID { get; set; }
    public string otherinfo { get; set; }
}

One view model class because I have to join the data of all these:

public class ViewModel
{
    public Companies companiesvm { get; set; }
    public PersonalInfo personalInfovm { get; set; }
    public CompanyUsers companyUsersvm { get; set; }
}

DbContext class:

public class UserContext : DbContext
{
    public UserContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<ViewModel> viewModels { get; set; }
}

Controller:

[Route("api/[controller]")]
[ApiController]
public class APIController : ControllerBase
{
    private readonly UserContext userContext;
    
    public APIController(UserContext userContext)
    {
        this.userContext = userContext;
    }

    // GET api/<APIController>/5 
    [HttpGet("{id}")]
    public string Get(int id)
    {
         // I am confused here 
        var result = from userlist in userContext.viewModels.  
    }

I know that now to connect with the database we will have to use userContext object. I don’t know how to create a Linq expression for the same. I am stuck here.

And also, am I going the right way by creating the view model class and creating DbSet object of it? Please guide. Thanks.

I referenced these links too but don’t know how to link all the tables with the userContext object:

http://www.codingfusion.com/Post/How-to-Join-tables-and-return-result-into-view-usi

Join models in asp.net mvc application

I wouldn’t put the ViewModel into your DBContext. Make a DBSet for each table, so that you can access them seperate from each other if you need to:

public class UserContext : DbContext
{
    public UserContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<Companies > Companies{ get; set; }
    public DbSet<PersonalInfo> PersonalInfos{ get; set; }
    public DbSet<CompanyUsers> CompanyUsers{ get; set; }
}

For your API create Models that only hold the data your interested in. I would create specific Model foreach endpoint. My naming is provisional here (data according to your sql):

public class GetAPIModel
{
    public int CompanyID {get;set;}
    public string CompanyName {get;set;}
    public string CompanyUserName {get;set;}
    public int CompanyUserID {get;set;}
    public string CompanyUserDesignation {get;set;}
    public string OtherInfo {get;set;}

}

Now you can execute your Query like this:

var data = (from p in userContext.CompanyUsers
                   join c in userContext.Companies
                   on p.companyid equals c.compnayid
                   join pInfo in userContext.PersonalInfos
                   on p.id equals pInfo.userID
                   where p.id == 1 and p.isRemote == 0
                   select new GetAPIModel
                   {
                       CompanyID = p.companyid,
                       CompanyName = c.companyname,
                       CompanyUserName = p.name,
                       CompanyUserID = p.id,
                       CompanyUserDesignation = p.designation,
                       OtherInfo = pInfo.otherinfo
                   })).FirstOrDefault();

Now data should hold your GetAPIModel with your informations needed to create your JSONResponse.

EDIT: Code adapted to fit the models specified by OP