20. Many-to-Many Relationships in Entity Framework Core

on this part you learn how to add a man to many relationship using the Entity framework core for that you'll add the author model because we have said that an author can publish multiple books and a book can be published by multiple authors to add a many to many relationship you will first add the author model now since the relation between the author and the book is a many to many relationship you need to add the joint table in the database but to add the joint table in the database you need to add the join entity in your web API for that you'll add a new model named book author and I'll use the underscore just so I can distinguish the join models or tables from the normal models or tables then next you'll add the navigation properties add a migration and update the data database for that let us go to visual studio in here let us go to the solution Explorer then inside the models folder we are going to add a new model so add a class and we are going to add the author model so author. Cs and then click the add button now here we are going to keep the author model simple as well because the focus is the book model so for that I'll just add an identifier prop then double tap I'll leave the type integer the name will be ID then let us also add the full name for the author so prop the type will be a string and the property name will be full name now down here we are going to add the navigation properties but since we said that the relation between the the author and a book is a many to many we need to add a join model so for that let us go to the solution Explorer then add a new model so add class then in here book uncore author. Cs and then click the add button for the book author we are going to have an identifier because the book author will a table in our database so it needs to have a unique identifier or a primary key for that just prop I'll leave the type as integer and just ID then down here we are going to add the navigation properties for the book and the author so for that I'll just type in here prop then int book ID and prop of type book the name will be book then we have the author so prop type integer author ID and then prop type author just author let us sa if the changes in here now let us go to the author model since the relationship between the author and the book model is a man to man relationship in the navigation properties you'll not add the book as a navigation property but you'll add the book author and the reason for that is because the relation between The Book Table and the author table will be stored in the book uncore author table so for example let us say on The Book Table you have three books book one two three and on the right side you have three authors now if the book one for example is written just by the author one you would have a row in the bookor author table with the book ID one and the author ID one if let's say the second book with the ID two is written by the author two and three then in the bookor author table you would have two rows with the book ID two and the author ID two and three so we can see in here that the relation is really divided from the book author now we have the book table which has a one to many relationship to the book author and the author table has also a want to man relationship to the book author table so in here we are going to add a new property so prop and it will be a list of book uncore author because as we could see from the schema the book now has a list of book authors and the property name will be just book uncore authors let us copy this line and save the changes in here go to the solution Explorer then in here go to the book model then scroll down to the navigation properties and just paste that line in here now we added the relationship of the book and the author model to the new model book author but this relationship needs to also be configured using the fluent API for The Entity framework ore to be able to map it successfully for that let us go to the solution Explorer then in here go to the FDB context file and here we are going to override the on model creating method so for that I'll just type protected override void on model creating it takes as a parameter the model builder so model builder let us just name it model builder and then inside here we are going to Define this relation the model build Builder or the relation will be defined over the book author so for that I'll just type in here that the model builder for the entity book author it has one book so has one then let us say B that goes to B do book and the book has many book authors so with many ba goes to ba book authors and then you can also Define the foreign key so that has foreign key and the foreign key in here will be the book ID so bi bi do book ID so what we just did in here is that we Define the relation between book and the book authors now we need to do the same for the author and the book authors so don't forget the semic column at the end now I can just copy these lines contrl C and then just paste them down here and here we are going to change the has one from book to author and also the forign key will be the author ID now down here let us add all the necessary DB sets so we can access all the data from the database so for example on the last part we added the publisher table but since we didn't add a DB set publisher in the FDB context we cannot add or send data to the publisher table for that we need to add a property of type DB set in here so we have the books now let us just type in here prop then double tab this will be of type DB set the author and this property name will be authors then in here let us also add the book author which is the join table or the join model so prop double tab dbet book underscore author and this will be named books uncore authors and then the last one will be prop dbet the publisher and the property name will be Publishers now let us save the changes and go to the package manager console and here we are going to add a new migration so add Das migration the migration name will be let's say many to many edit then press enter so we see that the build succeeded and in here you can see all the changes that the Entity framework will use to update our database one change for example is that the name for the publisher table will be changed from publisher to Publishers and that's because in the abdb context file we Define the name of the DB set publisher to be Publishers and by default it used to be just publisher because that was the model name now if you go back to the migration you scroll down in here you'll see that the table authors will be created it has a column ID of type integer and is also set as the server identity then down here you have the book authors and the book authors has three columns the book ID the author ID and the ID which is the server identity the book ID and author ID are both both type integer and nullable false and on the constraints section you can see that the primary key for the books authors is the ID but the author ID is the foreign key for the author table ID column the same goes for the books table where the book ID from the books authors is the foreign key for the books table now let us go back to the package manager console and here I'll just type update Das database and then press enter so we see that everything succeeded now if we go to the server Explorer and refresh the tables you'll see that we have two more tables the book authors and the author table now I'll use the SQL Server management Studio to see the database schema so for that I'll just connect to the server then in the databases I'll go to my database which is the my- books DB then on the database diagrams just right click new database diagram in here I'll select all the tables so the authors then books then books authors the Publishers and close now in here you can see the relations between all these tables like for example the relation between Publishers and the books is one to many a publisher can have multiple books but a book a single publisher the relationship between books and authors is many to many and for that we addit a joint table the books authors and we can see that because we added the joint table now we have a relationship one to many from books to books authors and also from authors to books authors

Channel: Ervis Trupja

Views: 37,969

Keywords: asp.net core web api, many-to-many relationship sql, many-to-many relationship entity framework code first, many-to-many relationship between two tables, many-to-many relationship entity framework core, many-to-many relationship access example, entity framework many to many relationship code first, entity framework code first tutorial c#, entity framework core tutorial, entity framework core code first tutorial c#, entity framework tutorial c#, entity framework core kudvenkat

Id: Qh2hgIc90y0

Length: 12min 19sec (739 seconds)

Published: Tue Feb 09 2021