Part 22 Many to many relationship in entity framework

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

Entity Framework - All Text Articles

Entity Framework - All Slides

Entity Framework Playlist

Dot Net, SQL, Angular, JavaScript, jQuery and Bootstrap complete courses

In this video we will discuss many-to-many relationship in entity framework with database first approach. We will be using the following 3 tables in this demo. There is a Many-to-Many relationship between Courses and Students. A student can enrol into many courses and a single course can have many students enrolled. The data is stored in the StudentCourses bridge table.

Now if we generate an ADO.NET entity data model based on the above 3 tables, only 2 entities (Student and Course) are generated with a Many-to-Many association between them. Notice that an entity for the bridge table (StudentCourses) is not generated in the entity model. Also, notice that we have navigation properties to navigate from Course to Students and from Student to Courses.

At this point, right click on Many-to-Many association and select Table Mapping option. In the Mapping Details window notice that, the StudentCourses Many-to-Many association is mapped to StudentCourses database table.

Now, let us see how to query the data. We want to display all the students names and the courses they have opted into.

Drag and drop a GridView control on the webform. Copy and paste the following code in the code-behind file.
protected void Page_Load(object sender, EventArgs e)
{
EmployeeDBContext employeeDBContext = new EmployeeDBContext();

GridView1.DataSource = from student in employeeDBContext.Students
from c in student.Courses
select new { StudentName = student.StudentName, CourseName = c.CourseName };

GridView1.DataBind();
}

Turn on SQL Profiler and load the webform. Notice that the generated SQL Query joins all the 3 tables (Students, Courses & StudentCourses)

[kingwoods]

Thank you sir! You've really helped me with an issue that was bugging me for days!

[kenmtb]

Amazing series. I should have come here rather than waste time on stack overflow. Simple to the point explanation found here. Thanks!

[rynoadlam]

How would you go by to add a date or an extra property to the linking table?

[stonovsky]

Great video, everything explained very clearly. Thank you.

[uneekswatian]

Dear Venkat,

I have copied the sql code from your blog and generated the tables, then I follow the video and generate the entity and the query to pull the transactions,  

I am facing a problem for each student in the student table its displaying all courses in the courses table, however in the studentcourses table we have two courses for one student and 3 courses for second student.

Please reply how to resolve the issue.

Thank you

[numero]

And what about if I decided to add a property to that relation for example a date property

[EdgarPaulk]

thank you very much! your videos are very useful!!

[verite]

This does not work it generates an exception with VS 2012 + Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery) is not supported

one solution is to get a list of the linq query, why did it work for you?

protected void Page_Load(object sender, EventArgs e)
{
EmployeeDBContext employeeDBContext = new EmployeeDBContext();

GridView1.DataSource = (from student in employeeDBContext.Students
from course in student.Courses
select new
{
StudentName = student.StudentName,
CourseName = course.CourseName
}).ToList();
GridView1.DataBind();
}

[programminggotchas]

how to implement following scenario
student can rate many courses
course can be rated by many students
??? any idea

[namrathasrinidhi]

Your videos are extremely helpful and educational. Please help me with this query below.
Is it possible to get studentID column values as S1, S2, S3 auto generated and CourseID column as C1, C2, C3 and so on ?

[IcanReadAndSpell]

Thank you very much for all of your videos. Seems like I have got a final place to come and look for my problems. Thanks alot. I have a question related to many many relationships topic. Here, you have written the query using linq to sql approach, can you please tell how to frame same query using linq to entity(using lambda expression)

[shainemaguire]

This make sense, but in the real world, you are not going to know what the IDs are for the entities. I am trying to do something similar to this in MVC using Identity and having a hard time with it.

[aleksandrakosic]

Why you didn't use INCLUDE method?

[AzureusNation]

I know this is 4 year old. But what about Many to Many with TPT from part 21?