Databases
StackOverflow2013
Postsdbo
POASP.NET MVC 4, EF, & LINQ: Creating a specific LINQ query for a many-to-many relationship
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POASP.NET MVC 4, EF, & LINQ: Creating a specific LINQ query for a many-to-many relationship
    text
    copied!<p>In my application, I am trying to write a ListStudents ActionResult that will only display a list of students whose <strong>ClassroomID</strong> is equal one of the ClassroomID's that the currently logged on teacher has created.</p> <p>I believe that I need to use a few LINQ queries to get the desired result but I am stuck because I do not quite know how to handle it when the teacher entity has an ICollection of classrooms.</p> <p><strong>ListStudents &amp; CreateClassroom in my TeacherController</strong></p> <pre><code>public ActionResult ListStudents() { //This gets the current teachers UserProfile as only users with the teacher role have access to this controller var teacherProfile = db.UserProfiles.Local.SingleOrDefault(u =&gt; u.UserName == User.Identity.Name) ?? db.UserProfiles.SingleOrDefault(u =&gt; u.UserName == User.Identity.Name); var teacher = db.Teachers.Find(teacherProfile.UserId); //Need query where students ClassroomID needs to be one of the current teachers ClassroomID's return View(students); } public ActionResult CreateClassroom(Classroom model) { if (ModelState.IsValid) { try { var userProfile = db.UserProfiles.Local.SingleOrDefault(u =&gt; u.UserName == User.Identity.Name) ?? db.UserProfiles.SingleOrDefault(u =&gt; u.UserName == User.Identity.Name); var classroom = new Classroom { ClassroomName = model.ClassroomName, TeacherID = userProfile.UserId }; db.Classrooms.Add(classroom); db.SaveChanges(); } catch (DataException) { ModelState.AddModelError("", "Something went wrong, try again."); } } return RedirectToAction("ListClassrooms"); } </code></pre> <p><strong>Models For User, Student, Teacher, and Classroom</strong></p> <pre><code> public class User { [Key] [Editable(false)] [ForeignKey("UserProfile")] [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)] public int UserId { get; set; } public virtual UserProfile UserProfile { get; set; } [Display(Name = "First Name")] [Required(ErrorMessage = "First name is required.")] [MaxLength(50)] public virtual string FirstName { get; set; } [Display(Name = "Last Name")] [Required(ErrorMessage = "Last name is required.")] [MaxLength(50)] public virtual string LastName { get; set; } } </code></pre> <p><strong>Student</strong></p> <pre><code> public class Student : User { [ForeignKey("Account")] [Editable(false)] public virtual int AccountID { get; set; } public virtual Account Account { get; set; } [Column("ClassroomID")] [ForeignKey("Classroom")] public virtual int ClassroomID { get; set; } public virtual Classroom Classroom { get; set; } } </code></pre> <p><strong>Teacher</strong></p> <pre><code> public class Teacher : User { public virtual ICollection&lt;Classroom&gt; Classrooms { get; set; } } </code></pre> <p><strong>Classroom</strong></p> <pre><code> public class Classroom { [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] public virtual int ClassroomID { get; set; } [Display(Name = "Classroom Name")] [MaxLength(50)] public virtual string ClassroomName { get; set; } public virtual ICollection&lt;Student&gt; Students { get; set; } [Column("TeacherID")] [ForeignKey("Teacher")] public virtual int TeacherID { get; set; } public virtual Teacher Teacher { get; set; } } </code></pre> <p>Thank you for your time to help me solve this, I am very new to MVC and am trying to learn as much as I can.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload