CODEWITHSUNDEEP
c#asp.net-mvcmodel-view-controllerviewmodel
drieddust
drieddust

Reputation: 193

Using ViewModel to fetch data from multiple tables?

I have 3 classes for rules,requests,executions, and approvers.

I want to combine these three tables into a viewModel so that I can fetch Requestid, description,approvers and execution status in one single view. Here is my first crude try which seems to work but I feel this is very incorrect way of creating a ViewModel. Please suggest a better approach.

public class Rules
{
    [Required]
    public virtual int RulesId { get; set; }
    [Required]
    public virtual string RulesDescription { get; set; }
    [Required]
    public virtual int ApprovalLevels { get; set; } //if 0 then auto approved.
    [Required]
    public virtual string Requestor { get; set; }
}

public class Requests
{
    [Required]
    public virtual int RequestsId { get; set; }
    [Required]
    public virtual DateTime RequestTime { get; set; }
    [Required]
    public virtual bool isCompleted { get; set; }
    [Required]
    public virtual string UserName { get; set; }
    [Required]
    public virtual int RulesId { get; set; }
    public virtual string Description { get; set; }
}

public class ExecutionStatus
{   
    [Required]
    public virtual int ExecutionStatusId { get; set; }
    [Required]
    public virtual int RequestId { get; set; }
    [Required]
    public virtual int CurrentApproverLevel { get; set; } 
    [Required]
    public virtual string ApprovalStatus { get; set; }
}
public class Approvals
{
    [Required]
    public virtual int ApprovalsId { get; set; }
    [Required]
    public virtual int RulesId { get; set; }
    [Required]
    public virtual int ApproverLevel { get; set; }
    [Required]
    public virtual string ApproverName { get; set; }
}



public class RequestExecutionViewModel
{
    private RequestsContext db = new RequestsContext();
    public RequestExecutionViewModel(string username) 
    {
          this.Request = db.Requests.Where(a => a.UserName.Equals(username)).First();
          //aa = db.Approvals.Where(a => a.RulesId.Equals(Request.RulesId));
          this.Approvals = (List<Approvals>) db.Approvals.Where(a => a.RulesId.Equals(Request.RulesId)).ToList();
          this.ExecutionStatus = (List<ExecutionStatus>)db.ExecutionStatus.Where(a => a.RequestId.Equals(Request.RequestsId)).ToList();
    }

    [Required]
    public virtual int RequestExecutionViewModelId { get; set; }
    public Requests Request   {get;set;}
    public List<Approvals> Approvals { get; set; }
    public List<ExecutionStatus> ExecutionStatus { get; set; }
}

Edit: Doing the database query inside model seems wrong to me. There should be a better way of doing things.

Upvotes: 4

Views: 2112

Answers (3)

Paul Tyng
Paul Tyng

Reputation: 7584

Typically you would do your model building in your Controller Action, not in the ViewModel code itself. Boilerplate mapping code though could be useful in the ViewModel, but the querying of the database I don't think should be in there.

Not all pages will have ViewModels necessarily, and it would lead to DB queries scattered around if some do queries in the controller while some do it in the ViewModel.

Upvotes: 2

jim tollan
jim tollan

Reputation: 22485

the composition is fairly good, however, you shouldn't have private RequestsContext db = new RequestsContext(); and RequestExecutionViewModel() included in the viewmodel. also, you might want to use IList<> rather than List<>.

your db access should be performed in the service layer or the controller action and should probably be injected via some IOC container.

just my 2 cents

Upvotes: 4

Peter Monks
Peter Monks

Reputation: 4389

In my opinion this seems to be mostly fine. As far as I understand, a ViewModel should be used to provide just enough data from the Model(s) to your View, which yours does.

The only other thing I could suggest is maybe using the Repository pattern instead of directly using a RequestsContext so you could do unit testing better.

Upvotes: 1

Related Questions