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
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?
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
For maintaining many-to-many relationship in entity framework, junction table columns should be set to “Composite Primary Key”
DB script file is included in the solution
Happy Coding 🙂
Please leave a comment below, and let me know what you think!