Programming Entity Framework

Originally posted on datadeveloper.net

This tutorial will explain how many-to-many relationships appear in the Entity Data Model.

What is a Many-to-Many relationship?

A many-to-many relationship is one where two data tables or entities have multiple rows that are connected to one or more rows in the other table.

A typical example is the EmployeeAddress join table in the AdventureWorks sample database. One employee could have multiple addresses (home, vacation, etc.) and it's possible for one address to apply to multiple employees. In databases, a join table is generally used to create the relationships.

Note that there are additional properties in the join table to track the address type (a customization of the original table), when the joins were created/modified and a unique identifier for each row.

Another example of this is Students and Classes in a database for managing a school. One student can be in many classes and one class can have many students.

Many to Many Relationships in the Entity Data Model

The Entity Data Model Wizard handles these two sets of tables very differently when they are imported into an EDM.

The Employee/Addresses entities mirror the tables -- two entities with a join entity in between. There are navigation properties to traverse between the three entities, Employee.EmployeeAddresses gets you from the Employee to the join entity and from there you can use EmployeesAddresses.Addresses to get the addresses for the Employee you began with.

Using this data in code requires building join queries or queries that drill through multiple navigation properties (eg Employee.EmployeeAddress.Addresses), for example:

Private Sub employeeaddress()
   Dim aw As New AdventureWorksTModel.AdventureWorksTEntities
   Dim empaddQuery = From e In aw.Employees, ea In e.EmployeeAddresses _
         Select e.EmployeeID, e.HireDate, ea.AddressType, ea.Addresses.City
   For Each emp In empaddQuery.OrderBy(Function(e) e.EmployeeID)
      Console.WriteLine(String.Format("ID {0}:: Hire Date: {1:d}, Address Type: {2}, City {3}", _
      emp.EmployeeID, emp.HireDate, emp.AddressType, emp.City))
   Next
End Sub 

Which results with:

ID 1:: Hire Date: 7/31/1996, Address Type: Home      , City Monroe
ID 1:: Hire Date: 7/31/1996, Address Type: Vacation  , City Seattle
ID 2:: Hire Date: 2/26/1997, Address Type: Home      , City Bellevue
ID 2:: Hire Date: 2/26/1997, Address Type: Weekend   , City Everett
etc...

The student/class entities however, have no join table. Instead they have navigation properties to get to their related data. You can query class.students and you can query for student.classes.

This is a special benefit that the Entity Framework provides when join tables contain only the keys and no additional columns.

Because of this it is possible to traverse directly into the related data without the use of a join entity. Here's an example that uses a nested from clause to create an anoymous type based on the related data.

Private Sub studentclasses()
  Dim school As New SchoolSampleModel.SchoolSampleEntities
  Dim studclassQuery = From student In school.students From c In student.classes _
                       Select student.firstname, student.lastname, c.classname
    For Each student In studclassQuery.OrderBy(Function(s) s.lastname & s.firstname)
      Console.WriteLine(String.Format("Student {0},{1}: {2}", _
                                      student.lastname.TrimEnd, student.firstname.Trim, student.classname))
    Next
End Sub 

Which results with:

Student Doe,John: European History
Student Doe,John: Art History
Student Robin,Stacey: American History
Student Smith,Jane: American History
Student Smith,Jane: French
Student Williams,Bill: European History
Student Williams,Bill: French
Student Williams,Bill: Computers 101