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

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 recor开发者_开发百科ds 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.

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.

this could be also helpful;

video from chris pels

It is simple just do what I say step by step.

1. add connection string into web.config file

2. select models from solution explorer and add 4 classes as following

   • 1st class for first table "i have employ table which have 3 columns

     public class Employ { [Key] public int Emp_id { get; set; } public string Emp_name { get; set; } public string Emp_city { get; set; } }

   • 2nd class for my tempo table

     public class tempo { [Key] public int ID { get; set; } public int Emp_Id { get; set; } public string subject { get; set; } public string hobby { get; set; } }

   Now I create a third class in model folder which contain value that i want from employ table and tempo table

   public class Alladd
   {
       public int ID { get; set; }
       public int Emp_Id { get; set; }
       public string subject { get; set; }
       public string hobby { get; set; }
       public string Emp_name { get; set; }
       public string Emp_city { get; set; }
   }  
   

   and the final class is datacontext class

   public class DataContext:DbContext
   {
       public DataContext() : base("DefaultConn")//connection string
       {
       }
   
       public DbSet<Employ> Empdata { get; set; }
       public DbSet<tempo> Tempdata { get; set; }
   }
   

3. now go to the Home controller and add code as below

   public ActionResult file()
   {
       // IList<tempo> tempi=new List<tempo>();
   
       IEnumerable<Alladd> model = null;
   
       // model = getVerifydetails(id);
       // return View(objcpModel);
       List<Alladd> verify = new List<Alladd>();
   
       cn.Open();
   
       if (cn.State == ConnectionState.Open)
       {
           string query = "select Employ.Emp_name,Employ.Emp_id,Employ.Emp_city,tempo.hobby,tempo.id,tempo.subject from Employ inner join tempo on Employ.Emp_id=tempo.Emp_id;";//joining two table 
           SqlCommand cmd=new SqlCommand(query,cn);
           SqlDataReader dr = cmd.ExecuteReader();
   
           while (dr.Read())
           {
               verify.Add(new Alladd { Emp_name = dr[0].ToString(), Emp_Id= Convert.ToInt32(dr[1].ToString()), Emp_city = dr[2].ToString(), hobby = dr[3].ToString(),ID = Convert.ToInt32(dr[1].ToString()),subject= dr[4].ToString()});//filling values into Alladd class
           }
   
           cn.Close();
       }
   
       return View(verify);
   }
   

4. now the final step is so simple

   1. go to solution explorer
   2. select views folder and left click on it and select add view
   3. now name it as "file" which we give it into controller
   4. check on create strongly type view
   5. select model class from dropdown-> Alladd
   6. select scaffold templet ->List
   7. hit Add button

Now you're done

Happy coding...