MVC Viewmodel to access data from multiple tables

Question

I am new to MVC. In my application I am trying to search the data based on different parameters. The data is stored in the database and am using Entity Framework to access db. As I need to show data from multiple models into one view I decided to use ViewModel to bind the both class together and show the data.

Here is my code:

Models

[Table("User")]
public partial class User
{
    [Key]
    public int UserId { get; set; }

    [StringLength(50)]
    public string FirstName { get; set; }

    [StringLength(50)]
    public string LastName { get; set; }

    [StringLength(50)]
    public string Email { get; set; }
    
    public virtual LicenseTypeMaster LicenseTypeMaster { get; set; }
}

[Table("tblLicenseTypeMaster")]
public partial class LicenseTypeMaster
{
   Key]
    public int LicenseTypeId { get; set; }

    [StringLength(100)]
    public string LicenseTypes { get; set; }

    public int? LicenseCount { get; set; }
}

ViewModel

public class UserLicenseTWRBinder
{
    VSLicenseDb db = new VSLicenseDb();
    

    public IEnumerable<User> GetUserDetail(string searchBy, string searchValue)
    {
        List<User> usr = new List<User>();
        public List<User> usr { get; set; };
        var query = (from u in db.Users
                     join ut in db.UserLicenseTypes
                         on u.UserId equals ut.UserId
                     join tl in db.ToolsLicenseTypes
                     on ut.ToolLicenseTypeId equals tl.ToolLicenseTypeId
                     join tm in db.LicenseTypeMasters
                     on tl.LicenseTypeId equals tm.LicenseTypeId
                     where u.FirstName == searchBy
                     select new 
                     {
                         FirstName = u.FirstName,
                         LastName = u.LastName,
                         Email = u.Email,
                         LicenseAllocated. = tm.LicenseTypes
                     }).ToList();
       return query;
    }
    }

I want the method to return FirstName, LastName & Email from User model and LicenseType from LicenseTypeMaster model. I am not sure about which properties to be included from both model into the ViewModel so that it can only render the desired data. Also the return type of the method so that it can be accessed in the controller and then can be passed to the view.

Answer 1

You need to create a new class Say UserPoco with the all the fields to extract

 public class UserPoco
 {
     public string FirstName {get;set}
     public string LastName {get;set}
     public string Email{get;set}
     public string LicenseAllocated {get;set}
 }

And the method should look like this

public IEnumerable<UserPoco> GetUserDetail(string searchBy, string searchValue)
{
    List<User> usr = new List<User>();
    public List<User> usr { get; set; };
    var query = (from u in db.Users
                 join ut in db.UserLicenseTypes
                     on u.UserId equals ut.UserId
                 join tl in db.ToolsLicenseTypes
                 on ut.ToolLicenseTypeId equals tl.ToolLicenseTypeId
                 join tm in db.LicenseTypeMasters
                 on tl.LicenseTypeId equals tm.LicenseTypeId
                 where u.FirstName == searchBy
                 select new UserPoco
                 {
                     FirstName = u.FirstName,
                     LastName = u.LastName,
                     Email = u.Email,
                     LicenseAllocated. = tm.LicenseTypes
                 }).ToList();
   return query;
}

This is how I would be doing. You can add as many fields as you want to the poco class. Hope that helps.