Using ViewModel to retrieve data from multiple tables?

Question

I have 3 classes for rules, queries, executions, and assertions.

I want to combine these three tables in the viewModel so that I can get the Requestid, description, statements, and execution status in one view. Here is my first rude attempt, which seems to work, but I believe this is a very wrong way to create 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 a database query inside a model seems wrong to me. There must be a better way to do things.

Answer 1

the composition is pretty good, however you should not have private RequestsContext db = new RequestsContext(); and RequestExecutionViewModel() included in the viewmodel. also you can use IList<> , not List<> .

your db access should be done at the service level or in the action of the controller and probably should be entered through some IOC container.

only my 2 cents

Answer 2

Typically, you will create your model in your Action Manager , not in the ViewModel code. Code combination mapping code, although it may be useful in the ViewModel, but a database query that I don't think about should be there.

Not all pages will necessarily have ViewModels, and this will lead to a scatter of database queries if some of them execute queries in the controller, and some in ViewModel.

Answer 3

In my opinion, this is apparently mostly beautiful. As far as I understand, ViewModel should be used to provide enough data from the model (s) to your view, which does.

The only thing I could suggest is perhaps to use a repository template instead of using RequestsContext directly so that you can do unit testing better.