You can have three types of relations in EDM as in database. 1) One to One 2) One to Many 3) Many to Many.
Let’s examine database table design before going into relationships in EDM. Following figure is a database diagram of SchoolDB used in this tutorial.
As you can see in the figure, student and StudentAddress have One-to-One relationshionship where each student has zero or one address.
Standard and Teacher has One-to-Many relationship where one standard can have many teacher but one teacher can’t go to many standard.
Student and Course has Many-to-Many relationships by using StudentCourse table. StudentCourse consists primary key of both the tables and thus it makes relation Many-to-Many.
When you create ADO.NET Entity Data Model from SchoolDB database using EF Designer from database option, it will create following entities and relationships into EDM:
As you can see in the above figure, Student and StudentAddress have One to One relationship (zero or one). StudentAddress entity has StudentId property as PK which makes it One-to-One relationship.
Standard and teach have One-to-Many relation marked by multiplicity where 1 is for One and * is for many. Standard entity has navigation property “Teachers” which indicates that one Standard can have collection of teachers and Teacher entity has “Standard” navigation property which indicates that Teacher is associated with one Standard. This makes it One-to-Many relationship.
Student and Course have Many-to-Many relationships marked by * multiplicity, but it doesn’t display entityset for middle table StudentCourse where primary key of both tables will be stored. This is because The EDM represents many-to-many relationships by not having entityset for the joining table in CSDL, instead it manage through mapping. It can do this only when the joining table doesn’t have any columns otherthan PKs of both the table. If the join tables had additional columns, such as DateCreated then the EDM would have created entities for them and you have to manage Many-to-Many relationship entities manually.
Many-to-Many relationship in EDM:
Open EDM in XML view. You can see that SSDL has StudentCourse entityset but CSDL doesn’t have StudentCourse entityset instead it’s being mapped in navigation property of Student and Course entity. In MSL (C-S Mapping), it has a mapping between Student and Course into StudentCourse table in <AssociationSetMapping/>
Thus Many-to-Many relationship is being managed in C-S mapping in EDM. So when you add student in course or Course in Student entity and when you save it then it will insert PK of added student and course in StudentCourse table. So this mapping not only enables a convenient association directly between the two entities, but also manages querying, inserts, and updates across this join.
Note: EDM does this only when joining table has PK columns for both tables. If you have some other columns in joining table then EDM will treat as normal entity and you have to use ‘Join’ in your query to fetch the data.
When an entity has relation with other entities then full object hierarchy is called ‘entity graph’ for example following is a Student entity graph that includes its full hierarchy object Standard, StudentAddress & Course.