Create MVC ViewModel with Multiple Queries using Entity Framework

Question

I am fairly new to MVC and trying to explore more into ViewModels. I have an product category page that I would like to display product categories as well as products and their associated images. I am going to simplify some of these tables and just focus on the logic of returning this data to the view. I have the view working with the populated dropdownlist, but I am not sure how to populate the ProductViewModel within the CategoryViewModel.

Database

Category Table

CategoryId
CategoryName
CategoryDescription

Product Table

ProductId
ProductName
ProductDescription
ProductPrice
CategoryId

ProductImage Table

ProductId
ProductImage1
ProductImage2
ProductImage3
ProductImage4
ProductImage5
ProductImage6
ProductImage7
ProductImage8
ProductImage9
ProductImage10

ViewModel

public class ProductViewModel
{
  public Product ProductVM { get; set; }
  public ProductImage ProductImageVM { get; set; }
}

public class CategoryViewModel
{
  public List<Category> Category { get; set; }
  public List<ProductViewModel> Products { set;get;} 
}

Controller

public ActionResult Index()
{
    var model = new CategoryViewModel();
    model.Category = db.Categories.OrderBy(d => d.CategoryName).ToList();
    model.Products = from p in db.Products
                             join pi in db.ProductImages on p.ProductId equals pi.ProductId
                             orderby p.ProductPrice descending

    return View(model);
}

View

@model CategoryViewModel

@Html.DropDownListFor(x => x.CategoryId, new SelectList(Model.Category, "CategoryId", "CategoryName"), "View all Categories")

<table>
@foreach (var product in Model.Products)
    {
        <tr>
            <td>@item.ProductImage.ProductImage1</td>
            <td>@item.Product.ProductName</td>
            <td>@item.Product.ProductPrice</td>
            <td>@item.Product.ProductDescription</td>
        </tr>
    }
</table

Answer 1

Whenever I'm using ViewModels, I also develop a Service class to populate it. This helps keep my controller clean and my logic isolated.

First, create a folder named "Services" to contain these classes. If using Areas, create the folder at the same level in the project hierarchy as the Controller using it.

Then, create a "Service" class in that folder. For example, I would create a class named CategoryService since the ViewModel is named CategoryViewModel.

In this class, I would put the code to intialize the ViewModel:

public class CategoryServices
{
    private MyDbContext db = new MyDbContext();

    internal CategoryViewModel GetCategoryViewModel(){
        return new CategoryViewModel(){
            Category = GetCategories(),
            Products = GetProductViewModel()
        };
    }

    internal List<Category> GetCategories(){
        return db.Categories.OrderBy(d => d.CategoryName).ToList();
    }

    internal List<ProductViewModel> GetProductViewModel(){
        return db.Products.Select(x => new ProductViewModel()
        {
            ProductVM = x,
            ProductImageVM = x.ProductImage
        });
    }
}

Now, you can easily retrieve the ViewModel from your controller:

public ActionResult Index()
{
    CategoryService service = new CategoryService();
    return View(service.GetCategoryViewModel());
}

On your view, you'll have to update your model references to handle what is in the ViewModel.

@model CategoryViewModel

@Html.DropDownListFor(x => x.CategoryId, new SelectList(Model.Category, "CategoryId", "CategoryName"), "View all Categories")

<table>
@foreach (var item in Model.ProductViewModels)
    {
        <tr>
            <td>@item.ProductImage.ProductImage1</td>
            <td>@item.Product.ProductName</td>
            <td>@item.Product.ProductPrice</td>
            <td>@item.Product.ProductDescription</td>
        </tr>
    }
</table

This should set you in the general direction that you need to go. Feel free to leave if a comment if you have a question on anything above, and I'll try to clarify.

EDIT: Also, I would recommend breaking down the functions in your service class even further. I avoided that here because I didn't want to provide you with a 10+ function class.

EDIT2: Updated GetProductViewModel() function. Since there is a 1-to-1 relationship between the Product and ProductImage models, and ProductImage has a foreign key on ProductId referencing Product's ProductId, ProductImage should be available as a child entity on the Product model.

Because of this, you can use a handy lambda expression to generate the list of ProductViewModels in one database trip. I've used this lambda expression to generate many lists, but you may need to modify it to work correctly.