How can I create Has-Many-Through relation with Entity Framework 6?

Question

I have an application written using c# on the top of the ASP.NET MVC 5 and Entity Framework 6 using Database-First approach.

I have a Student model, a ClassRoom model and a relational model to link the two relations together called StudentToClassRoom.

I want to be able to select all students and for each student I want to get all the ClassRoom that the student has relation too.

Here are my models

public class Student
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<ClassRoom> ClassRoomRelations { get; set; }
}


public class StudentToClassRoom
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [ForeignKey("Student")]
    [InverseProperty("Id")]
    public int StudentId { get; set; }

    [ForeignKey("ClassRoom")]
    [InverseProperty("Id")]
    public int ClassRoomId { get; set; }

    public virtual Student Student { get; set; }

    public virtual ClassRoom ClassRoom { get; set; }
}

public class ClassRoom
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
}

Here is what I tried

var students = DbContext.Students.Include(x => x.ClassRoomRelations)
                                 .ToList();

However, that gives me the relation collection for each student. But I want to be able to get the ClassRoom information for each student. So I want to create a Has-Many-Through between Student and ClassRoom. In the end results, I don't really care about the ClassRoomRelations, I only care about the Student and theClassRoom objects.

How can I get a list of Student and a collection of all Class-Rooms for each student using Entity Framework?

Don't waste your time searching. EF does not support such relationship. — Ivan Stoev, Aug 21 '17 at 18:43
@IvanStoev thank you :) Is there a workaround to get the same dataset? — Junior, Aug 21 '17 at 18:44
Only if you actually need `many-to-many` with implicit junction table. Although even this is problematic if you are bound to existing database (`StudentToClassRoom` is not eligible for auto junction table). — Ivan Stoev, Aug 21 '17 at 18:49
@IvanStoev I am kinds of lost here. what is "implicit junction table"? are you saying that there is no way to do this other that writing two queries and they join then in memory? — Junior, Aug 21 '17 at 18:54
The implicit junction table defines the relation. It contains only the 2 FKs which also form the composite PK. See the answer by @Jonny Piazzi and the provided link. Or [Many-to-many mapping table](https://stackoverflow.com/questions/11382783/many-to-many-mapping-table) — Ivan Stoev, Aug 21 '17 at 19:03

Steve Greene · Answer 1 · 2017-08-21T19:58:28.377

1

Since you've exposed the bridge table, you could go with:

var studentRooms = DbContext.StudentToClassRoom
                            .Include(x => x.Student)
                            .Include(x => x.ClassRoom)
                            .ToList();

See here

Also, you don't really need the [Inverse] annotations - EF knows you are linking to Id with the FK.

EDIT: A student and their classrooms

First you will need to fix your student model:

public virtual ICollection<StudentToClassRoom> ClassRoomRelations { get; set; }

Then you can run

var studentAndRooms = DbContext.Students
                      .Select(s => new 
                      {
                         student = s,
                         classrooms = s.ClassRoomRelations.Select(r => r.ClassRoom)
                      }).ToList();

edited Aug 21 '17 at 19:58

answered Aug 21 '17 at 18:55

Steve Greene

12,029
1
33
54

This is a good start, but is there a way to select one Student and Many ClassRooms so the end results are accesses student.ClassRooms? – Junior Aug 21 '17 at 19:08
Thank you so much for your help. I think @Jaylen answer is very similar to this approach but worked. – Junior Aug 21 '17 at 20:19

canmustu · Answer 2 · 2017-08-21T18:57:24.437

Why don't you use simply ? You can get student's classroom information already.

public class Student
{
    public int Id { get; set; }

    public string Name { get; set; }

    public Guid ClassRoomId { get; set; }

    // public virtual ClassRoom ClassRoom { get; set; }
}

public class ClassRoom
{
    public int Id { get; set; }

    public string Name { get; set; }

    // public virtual ICollection<Student> Students{ get; set; }
}

public class StudentToClassRoom
{
    public int Id { get; set; }

    public Guid StudentId { get; set; }

    public virtual Student Student { get; set; }

    public Guid ClassRoomId { get; set; }

    public virtual ClassRoom ClassRoom { get; set; }
}

// var students = DbContext.Students.Include(x => x.ClassRoom).ToList();

var mergedRecords = DbContext.StudentToClassRoom
                             .Include(x => x.Student)
                             .Include(x => x.ClassRoom)
                             .ToList()

My `ClassRoom` does not have a `StudentId` property/column so I can create a navigation property from Student directly to `ClassRoom`. The model `StudentToClassRoom` is needed to be able to identify the relation between the two. — Junior, Aug 21 '17 at 18:52

Jonny Piazzi · Answer 3 · 2017-08-21T20:27:59.307

Entity Framework has a better aproach to handle many to many relationships.

The EF's way to think this is Student has classrooms and ClassRoom has students:

public class Student
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<ClassRoom> ClassRooms { get; set; }
}

public class ClassRoom
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Student> Students { get; set; }
}

The relationship table is completely ignored in EF map. Give a look in this tutorial.

EDIT:
Below a few queries to simplify how to use this map scenario:

Get a list of all class rooms of a specific student:

var classRoomsOfSpecificStudent = DbContext
    .Students
    .First(s => s.Id == studentId)
    .ClassRooms
    .ToList();

Get a list of all class rooms of students that has a name containing "a".

var classRooms = DbContext
    .Students
    .Where(s => s.Name.Contains("a"))
    .SelectMany(s => s.ClassRooms)
    .ToList();

Get all students that has name containing "a" and the class rooms' name containing "2b".

var students = DbContext
    .Students
    .Where(s => s.Name.Contains("a"))
    .Where(s => s.ClassRooms.Any(c => c.Name.Contains("2b")))
    .ToList();

I hope I have clarified a little.

I am confused. My `ClassRoom` does not have a `StudentId` property/column so I can create a navigation property from `Student` directly to `ClassRoom`. The model `StudentToClassRoom` is needed to be able to identify the relation between the two. — Junior, Aug 21 '17 at 18:51
I edited the answer with a few queries, I think will clarify a little about how to use this map. — Jonny Piazzi, Aug 21 '17 at 20:29

David Browne - Microsoft · Answer 4 · 2017-08-21T19:29:20.567

If you want to use an explicit bridge table, it should generally not have an artificial key. The Foreign Key columns (StudentId,ClassRoomId) on the bridge table need to be a key, and so having an extra key is useless overhead.

And querying across the M2M relationship looks like this:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ef6Test
{

    public class Student
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<StudentToClassRoom> StudentToClassRoom { get; set; } = new HashSet<StudentToClassRoom>();
    }


    public class StudentToClassRoom
    {

        [ForeignKey("Student"), Column(Order = 0), Key()]
        public int StudentId { get; set; }

        [ForeignKey("ClassRoom"), Column(Order = 1), Key()]
        public int ClassRoomId { get; set; }

        public virtual Student Student { get; set; }

        public virtual ClassRoom ClassRoom { get; set; }
    }

    public class ClassRoom
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
    }

    class Db: DbContext
    {

        public DbSet<Student> Students { get; set; }
        public DbSet<ClassRoom> Classrooms { get; set; }

        public DbSet<StudentToClassRoom> StudentToClassRoom { get; set; }

    }
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseIfModelChanges<Db>());

            using (var db = new Db())

            {

                var students = Enumerable.Range(1, 150).Select(i => new Student() { Name = $"Student{i}" }).ToList();
                var classRooms = Enumerable.Range(1, 20).Select(i => new ClassRoom() { Name = $"ClassRoom{i}" }).ToList();

                var rand = new Random();
                foreach( var s in students)
                {
                    var classRoomId = rand.Next(0, classRooms.Count - 10);
                    s.StudentToClassRoom.Add(new StudentToClassRoom() { Student = s, ClassRoom = classRooms[classRoomId] });
                    s.StudentToClassRoom.Add(new StudentToClassRoom() { Student = s, ClassRoom = classRooms[classRoomId+1] });
                    s.StudentToClassRoom.Add(new StudentToClassRoom() { Student = s, ClassRoom = classRooms[classRoomId+2] });

                }

                db.Students.AddRange(students);
                db.Classrooms.AddRange(classRooms);
                db.SaveChanges();

            }
            using (var db = new Db())
            {
                db.Configuration.LazyLoadingEnabled = false;
                var q = db.Students.Include("StudentToClassRoom.ClassRoom");

                var results = q.ToList();
                Console.WriteLine(q.ToString());


                Console.ReadKey();
            }

        }
    }
}

How can I create Has-Many-Through relation with Entity Framework 6?

4 Answers4