Many To Many RelationShip – Entity Framework

Many To Many RelationShip – Entity Framework

By default, entity framework supports many to many relationship. In below figure the tblBooks and tblAuthors are having a many-to-many relationship and tblAuthorBook is the junction table. In case of using ADO.Net you don’t have to worry about the junction table and its structure/schema, but in case of entity framework you have make few changes in the junction table for it to work.

Below is the schema generated by entity framework

The above schema will not contain junction table and also entity framework does not create junction table class. Instead of that, it will be maintaining relations in form of properties within the tblAuthor and tblBook classes (highlighted in below figure). Since it is a many-to-many relationship tblAuthors is having ICollection<tblBooks> and tblBooks is having ICollection<tblAuthors> properties.

Below are the classes generated by entity framework

Data in the tables

Let us try to print

1. All the author’s and their books
2. All the book’s and their authors

 

The following is the output for above code

We got the output as expected.

As discussed in the beginning of this article

“In case of using ADO.Net you don’t have to worry about the junction table and its structure/schema, but in case of entity framework you have make few changes in the junction table for it to work”

Let us discuss on what changes we have to make and why they are needed by looking into below code.

What happens when we run the above code?

• New book should be added to the tblBook
• New author should be added to the tblAuthor
• A row with book-author relationship should be added into tblAuthorBook

 

Instead of that we will be getting an error as shown below

In order to make this work, we have to make changes to the junction table. We have to add “composite primary key” in the junction table as shown below.

After making changes to the junction table, update the edmx file. After updating the edmx file you will not find any changes in the entity diagram/schema or else in the classes generated by entity framework. But when we run the same code after adding composite primary key it will run as expected without any exception.

After running the code below data got inserted into the tables

Conclusion

For maintaining many-to-many relationship in entity framework, junction table columns should be set to “Composite Primary Key”

Sample Code

DB script file is included in the solution

https://github.com/KishoreIthadi/Blog-Samples/tree/master/EntityFramework/N2NRelation

Happy Coding 🙂

Please leave a comment below, and let me know what you think!