[RESOLVED]MVC 4 generate ViewModel from multiple tables

I want to generate ViewModel (contains Teacher Name and list of student’s Names) from multiple tables. Teachers table and Student table.

   Teacher’s model contains  public virtual ICollection<Student> Students { get; set; }

var model = (from x in db.Teachers where x.Email = email select new TeacherVM { Name = x.Name, StudentNames = x.Student.ToList() }).FirstOrDefault()

How do I retrieve all students into the TeacherVM in a format ("Bob","Mike","Dan")

Thanks,

Can you post your Teacher and Student models?

 public class Teachers
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public virtual ICollection<Student> Students { get; set; }
      …..
   }

    public class Student
    {
        public int Id { get; set; }
        public string Name{ get; set; }
        public virtual ICollection<Teachers> Teachers{ get; set; }
        …..
    }

And you want to list all students assigned to a specific teacher?  Or you just want to list all teachers and all students?

all students assigned to a specific teacher

so the viewmodel would contain 2 fields Teacher Name and list of all students

I tried this but I think I am missing something.

var model = (from x in db.Teachers where x.Email = email select new TeacherVM { Name = x.Name, StudentNames = x.Student.ToList() }).FirstOrDefault()

In the view I want to display 2 textboxes first textbox "Teacher Name" and another textbox all students comma separated
Bob,Mike,Dan

Thanks,

Do you have a relationship mapping between Teacher and Student?  Typically when you have a many-to-many relationship schema, you need a 3rd class/model/table that maps the relationship between those tables.  With your current two tables, you don’t have any
way of knowing which student belongs to which teacher, and vice versa.

Example:

public class Enrollment
{
  public int EnrollmentID { get; set; }
  public int TeacherID { get; set; }
  public int StudentID { get; set; }
}

and then you’d need primary keys in your Teacher and Student tables:

    public class Teachers
    {
        public int TeacherID { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public virtual ICollection<Enrollment> Enrollments { get; set; }
      .....
    }

    public class Student
    {
        public int StudentID { get; set; }
        public string Name{ get; set; }
        public virtual ICollection<Enrollment> Enrollments{ get; set; }
        .....
    }

Your Enrollments table would then have data like this:

EnrollmentID TeacherID StudentID
1 1 2
2 1 3
3 2 1
4 2 3

As you can see in the table above, Teacher #1 has Students #2 and #3, while Teacher #2 has Students #1 and #3.  Student #3 has teachers #1 and #2.  This is critical information that we need to have for many-to-many relationships.

Once you have this schema, you can create your Teacher-Students list

Teacher Details action:

public ActionResult TeacherDetails(int id)
{
  var teacher = db.Teachers.Find(id);

  return View(teacher);
}

View:

@model AppName.Models.Teacher

Teacher Name: @Model.Name
Teacher Email: @Model.Email

<h3>Students assigned to Teacher</h3>

@foreach (var item in Model.Enrollments)
{
  @Html.DisplayFor(modelItem => item.Student.Name) <br />
}

And that’s it.  Pretty standard solution for many-to-many relationships.

Thanks John for the explanation all makes sense. I was wondering instead of 

  var teacher = db.Teachers.Find(id);

to add teacher’s record and the student names into viewmodel in the controller

 public class TeacherVM 
    {
       public string Name { get; set; }
       public string StudentNames { get; set; } 
    }

something like this:

var model = (from x in db.Teachers 
             where x.Email = email 
             select new TeacherVM { 
                Name = x.Name,
                StudentNames = ??  <-- not sure how to get the list
             }).FirstOrDefault()
return View("teacher", model)

and then pass the viewmodel into the view so instead of doing a foreach to retrieve each single student.name I would just pass a string that would contain 
Bob,Mike,Dan into the textbox so the that the teacher can update the textbox right away instead of having edit button and another page. I hope it makes sense what I want to do. thanks for your help!

Sorry for the delayed response.  Let’s take a look at why you can’t do it the way you want to.

Yes, you could totally use a view model if you wanted, however you still need a relationship mapping between teacher(s) and student(s).  This is why I suggested a 3rd table (Enrollments) to establish which student has which teacher, and vice versa.

StudentNames = ??  <-- not sure how to get the list

With your current solution (Teacher and Student tables only), you have no way of getting this list.  There’s no association between the two.  Hence, you need that Enrollments table.  If you had that table, you could do something like this:

var teacher = db.Teachers.Single(t => t.Email == email);
var students = db.Students.Where(s => s.Enrollments.TeacherID == teacher.TeacherID); var model = new TeacherVM(); model.Name = teacher.Name; model.StudentNames = string.Join(",", students); return View(model);

As you can see, we use the Enrollments model to track down the students that have the teacher’s ID associated to their record in the Enrollments table.  How ever you want to handle this solution, you need to have a table that manages your many-to-many relationships.

You are absolutely correct. Sorry it took me a while to get this, new to mvc. But you made it all clear. Appreciate your help.

Leave a Reply