Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
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)
