Working with Many-to-Many Data Relationships in Dynamic Data

This topic describes how data fields in tables that have a many-to-many relationship are managed in Dynamic Data. Many-to-many relationships are established in the underlying database. A typical example is a school registration site in which classes can have multiple students and students can have multiple classes. In general, many-to-many relationships require a join table (also known as the junction table) that stores keys from tables on both sides of the relationship. In the school example, a join table would store class ID and student ID.

In some cases, many-to-many relationships can be represented in the data model in ASP.NET. LINQ to SQL does not support many-to-many relationships. ADO.Net Entity Framework (Entity Framework) creates many-to-many relationships in the data model when the join table that represents a many-to-many relationship contains only keys. This is referred to as a pure join table (PJT).

When Entity Framework creates a data model, it does not represent the PJT table directly in the data model. Instead, Entity Framework creates a direct-navigation relationship between the related tables, known as a many-to-many association.

If the join table contains fields that are not keys, the table is not a PJT and therefore Entity Framework cannot create a direct-navigation (many-to-many) association between the tables. (Join tables with non-key fields are also known as join tables with payload.) For example, the SpecialOfferProduct table in the AdventureWorks database is a join table, but not an Entity Framework PJT, because it contains the rowguid and ModifiedDate fields. Because of this, the EDM cannot generate a many-to-many association and therefore Dynamic Data cannot use scaffolding to directly connect the SpecialOffer and Product tables.

Working with Many-to-Many Relationships in Dynamic Data

To experiment with many-to-many relationships in Dynamic Data, you can use the School Sample Database, which you can download from the CodePlex Web site. The sample database is a model of a school enrollment application. In the sample database, the CourseInstructor table is a join table that represents a many-to-many relationship between the Person and Course tables.

A Visual Studio project with source code is available to accompany this topic: Dynamic Data Entity Framework.

The following illustration shows the Microsoft SQL Sever Management Studio relationship diagram for the Person, CourseInstructor, and Course tables of the School Sample database.

The following illustration shows the same relationship from the ADO.NET Entity Data Model Designer (EDM Designer).

The CourseInstructor table is a PJT and therefore the Entity Framework can move the join capability into the connected entities, eliminating the CourseInstructor entity. A person entity object can directly navigate to a course entity object without using a join entity. The join capability is implemented as navigation properties, which replace the capabilities implemented in the database by using the join table. The Person and Course entities have navigation properties that describe the navigation path between the two tables. The following illustration shows the EDM Designer properties dialog box that is used to configure the association between the course and person entities.

Displaying and Editing Data in Many-to-Many Relationships

Dynamic Data can display direct-navigation many-to-many entities. If the Entity Data Model (EDM) contains direct-navigation many-to-many entities, Dynamic Data will use the many-to-many field templates to display and modify the data.

For example, imagine that you create a Dynamic Data Web site that uses the School Sample database and that uses the Entity Framework. In that case, the data model can represent the many-to-many relationship that was illustrated previously.

When you run the Web site, the Default.aspx page is displayed, which displays a list that contains the table entities from the data model. You can select a table that is involved in a direct-navigation many-to-many relationship, such as the Course or Person table. The following illustration shows a portion of the Person table.

When you select a Person entity, Person data is displayed. The Course field is rendered using the Dynamic Data ManyToMany_Edit.aspx field template. The Course field displays a check box for every field in the Course table. The illustration shows the result if you select the person entity for Kim Abercrombie:

The ManyToMany_Edit.aspx field template lets users directly add or delete data in the referenced table. For example, you might add Calculus and Physics to the courses taught by Kim Abercrombie, and then click update. When you do, the Person table is redisplayed and shows Calculus, Chemistry, and Physics in the course field for the person entity that you edited. The following illustration shows the rendering of the list page for the Person table. The course column is displayed with the ManyToMany.ascx field template.

See Also

Concepts

ASP.NET Dynamic Data Scaffolding

Other Resources

Navigation Properties