How do I display data from multiple tables in a single MVC view

Question

I am having a hard time solving the following with an MVC view.

My goal is to display data from multiple tables in a single MVC view. The bulk of the data comes from a table called Retailers. I also have another table called RetailerCategories which stores the retailerid from the Retailers table and also a categoryid linking to a Category table.

Note that there are multiple records for each retailerid in the RetailerCategories table.

In the view I want to show a list of retailers and with each retailer I want to show the list of categories applicable to them.

What would be the best way to accomplish this? Some of the things I have tried are covered in Can you help with this MVC ViewModel issue?

This however does not appear to be the right approach.

Answer 1

You need a view model specifically tailored to the needs of this view. When defining your view models you shouldn't be thinking in terms of tables. SQL tables have absolutely no meaning in a view. Think in terms of what information you need to show and define your view models accordingly. Then you could use AutoMapper to convert between your real models and the view model you have defined.

So forget about all you said about tables and focus on the following sentence:

In the view I want to show a list of retailers and with each retailer I want to show the list of categories applicable to them.

This sentence is actually very good as it explains exactly what you need. So once you know what you need go ahead and modelize it:

public class CategoryViewModel
{
    public string Name { get; set; }
}

public class RetailerViewModel
{
    public IEnumerable<CategoryViewModel> Categories { get; set; }
}

Now you strongly type your view to IEnumerable<RetailerViewModel>. From here it is easy-peasy to do what you want in the view:

showing a list of retailers with each retail having a list of associated categories.