.NET 7 & Entity Framework 7: Mastering ALL Database Relationships (1:1, 1:n, n:n) with SQL Server 🚀

in today's video we are going to explore one-to-one one-to-many and many to many relationships in a fun and engaging way so that by the end of this tutorial you'll be able to create and manage database relationships like a pro so let's Dive Right In now first let me pull up this little presentation here promise it is really really short where I'm going to show you the different types of relationships in a relational database and also what entities we're going to use in this little example we've got one to one one to many many to many and the first example here is the one to one so we've got a character entity and one character can only have one single backpack and vice versa right so one character only one backpack and one backpack can only belong to one character so this will be our one-to-one relationship and the next one already is the one to many and let's say we are in a universe like a game or a series for instance where a character can have many different weapons so this would be our one to many relationships so one character can have many weapons but one weapon only belongs to one character and with that we can already move on to the many-to-many relationship and this would look like that for instance we've got many characters and these characters can belong to many different factions alright so many characters many factions and the factions can have many characters that's already it so now let's have a look at the code so I created a simple dot Net 7 web API project here right with the default implementations and when we have a look this is what we get we've got Swagger with this example weather forecast controller I think you know the drill here and what we first have to do before we can actually do all this stuff with Entity framework and all the relationships is of course install everything we need for Entity framework seven now if you know this already check out the timestamps below in the video description and then you can already move on and just skip this but for everyone who needs to also install entity framework.net EF tools and so on for the migrations then because we're going to use code first migrations then please keep watching and maybe code along or click along with me together with me and one more tip if you want to manage your database a little bit easier then maybe you want to install SQL Server management studio and of course you want to install SQL Server because this is what I like to use to do that you can simply Google for a SQL server and then you find the SQL Server download notes and there is this Express Express Edition where is it there it is so you can simply go to download now I already have this one available on my machine and then there's also the again SQL Server management Studio again you can simply Google for this thing SQL Server management Studio download it and great thing also is that with There's the link SQL Server management Studio you also get Azure data studio so if you decide to move this database then to an Azure SQL database then you can also use Azure data Studio to manage this database all right that would be it for SQL server but now Entity framework so what do we have to do first thing you get packages so right click your project whatever your project is and then go to browse and in here now we first can already install Microsoft there see it already Entity framework core if you start with the implementations meaning creating a database context maybe you already know this then this might be suggested in the Quick Fix menu for you so you can also do it in another way but this is maybe the the more straightforward way so you just manage your you get a packages and then install entity Microsoft Entity framework core so install this thing and when this is done then additionally we need a provider for SQL Server as you can see here you can also do this with SQL Lite or Cosmos whatever you want to do or use but in my opinion SQL Server makes sense it is free so why not use it see this here SQL Server database provider for Entity framework core so we installed this thing and then to be able to use code first migrations we also need one more package and this then would be the design a package here share design time components for energy frame core tools and after that we've got two options we can actually use the tools package here there it is and as you can see here it enables these commonly used commands like add migration bundle migration drop database and so on but typically what I do is I use the dot net EF core tools now what's that I'm assuming you've already got.net7 installed on your machine so what you can do then is simply enter dot net tool install and then dash dash Global and then.net EF and as you can see in my case it is already installed so what I can do just for you guys I uninstall it uninstall could also use the command update this would also work but let me just uninstall this thing and now I think I have a new version now available so instead of uninstall I use install 705 is now there great that I'm recording this video and with net EF I can simply double check and there you see it entry framework core.net command line tools 705 are installed great so this is what we need and the next thing now is to actually implement the data context all right so that we're able to access our database so how's this done you just right click our project add a new folder call this thing data and in here we add a new item and this thing now let me just call this a data context and this derives from DB context and as you can see here already this is part of Microsoft ND framework core new reference is added here automatically and here now we need a new Constructor and this Constructor gets also an argument of type DB context options with our data context type we call this thing options and we are also using our base Constructor here also with the options and that would be that and then we are for now done with the data context but when we're adding some entities some models then we also need to add database sets but this in a couple of minutes the next thing now is the actual connection string for our database right so we can save this already and here in the app settings Json file we can add a new section for instance call this connection strings you see it's already suggested here and then here we say we just call this default connection and now again if you're doing this with me the same way I do it you so I installed SQL Server Express on my local machine here just for development purposes learning purposes whatever you want to call it so then the connection string should look like this you say it server is localhost backslash backslash and then SQL Express semicolon and then the data base equals this is then the name of this database let me just give this a strange name like tlou database maybe now you know what I'm talking about if not maybe you would just have a look in the comments would be great if the community is telling everybody what I'm doing here after that trusted connection set to true and now since.net7 we also for development purposes have to add one more thing and that would be trust server certificate also equals true again just for development purposes for production maybe you want to set this to false and then provide a real certificate but this is something for another video maybe so server is localhost SQL Express database the tlou database trust connection is true and Trust server certificate also true awesome and now the last thing regarding configurations is we go back to the program Cs and here we have to tell our API that we actually want to use this data data context and also use the SQL Server database for that so what we can do now is maybe up here we say Builder services and then add DB context here we need our data context type again and now here this again gets options and regarding these options we're telling this thing that we want to use SQL Server all right and SQL server needs a connection string or this configuration it's a connection string and with that we can just use our Builder configuration that was already maybe I can do this one more time so we see it suggested here configuration a collection of configuration providers for the application to compose this is useful for adding new configuration sources and providers all right so this is what we want to use and with that we actually can access now our connection string section as you can see here there's a function already built in for us thank you.net team this is just shorthand for get Section collection strings so let's use this function here and the name of our connection string is default connection all right so that should be it I hope we rebuild and apply the changes and visual studio is really giving me a hard time sometimes regarding hard reload so you will see me restarting the application manually okay so config is done and now finally the entities first let's create another folder and we can call this models entities whatever you like so let's call this models for this example and the first one again would be the character we will start with the one to one relationship so now we also add the backpack and now first let's have a look at the character so this character first ID should be a first ID First Property should be an ID and maybe the name was already suggested here that's great so let's also add a name I don't care about the warnings for now all these null warnings here and here now what we can also do is another property for our backpack all right so this means that this character has one backpack and we can actually also add the ID for the backpack maybe so backpack ID it is and pretty similar will look the backpack so here in the backpack now we again add an ID and regarding one more property maybe a description and now the other way around we add the character id and then here also character character all right that's it so we have our character here and with ID a name and then these properties here and also the backpack with an ID description and then the character stuff that's it with that we Define our one-to-one relationship now let's use code first migrations to create a database with tables out of this but first what we have to do is add these two entities as database sets here to our data context so the first thing now first DB set is of type character all right and we call this thing characters and this then will be the name of the table that represents our character so usually you would just pluralize the name here of the entity so character is then characters and pretty much the same thing now for the backpack so here now we've got our backpacks and I hope that's it I haven't tested this before so let's see this is kinda live now the very first thing we have to do is we uh make sure when we have a look where we are here we're in the solutions folder now this won't work we have to go to our actual project folder so now the name here is ND framework seven relationships again and when you have a look now you see we are actually in the program program in the project folder and now here we can start a migration so when we have a look at the commands that are available database DB context and migrations the first thing we want to do is dot net EF migrations and then add and then let's say initial almost initial create because this is our very first migration is this spelled correctly in initial yeah I think so and when this is done and the builds hopefully succeeds maybe with some warnings then we will see a migration file but this takes way too long maybe I should stop the app first so let's try this one more time this looks better now that's a bummer of course we get an error message here dependent side could not be determined well what you can do here it suggests that we have to fix this with the on model creating there are different ways to fix this actually on model creating would mean a bit more code we could also use foreign foreign key attribute actually um in in the character class here so telling our API here in essence that the backpack ID is the foreign key but what we can also do if you don't need it we can just just remove the backpack ID save everything and try this one more time and all of a sudden now there's no problem any more so dependent side is now determined all right so let's have a look at the migrations file there it is new folder here you see migrations and then we've got this single file here and now we've got two methods here up and down maybe you already know this but now regarding the relationships we will see that when we now run this migration and update the database or create the database then we get a table called characters with an ID and the name the primary key here is then the ID and then the dependent side here is the backpacks table or the backpack entity because here now we see we've got a character id all right so an ID of course as always for one entity a description and then also the character id and here we see the foreign key then that the column character id is the foreign key and then and the principal table then is the characters table with the ID as the principle column and on delete is set to referential action Cascade meaning that if the character data will be removed then the backpack will also be removed all right so that's that and in the down case so if we would roll back this whole migration and then we would just drop the backpacks and characters tables I think this is clear so now we can simply update everything with dotnet EF database and then updates don't get confused by the update command here this just means that it will also create the database if it does not exist as you can see here create database plou database all right and now I would say let's have a look at SQL Server management Studio there we are let's have a look at the databases you can ignore the time trigger database this is from my dotted Web Academy here but now our tlu database let's have a look at the tables great backpacks awesome characters Let's test this here real quick so for instance no first the character we just add one so for instance we call this character here Ellie and now here we say this is for instance Alice backpack and the character id now is one so here we have this connection right so we've got again Ellie with id1 the backpack also with id1 and now if you try to add another backpack so at least second backpack Maybe something something like that we try to edit one more time it's telling us cannot insert duplicate key Row in objects backpacks with unity and so on duplicate key value is one statement has been terminated great so you can really see only one backpack is possible here in this case and when we now delete Ellie yes and refresh this then the backpack is also gone awesome one-to-one already works all right now the next type of relationship would be the one to many the most common one really and this was again a character our character here can have a list of weapons so first let's create our weapon entity right click our models folder and then weapon [Music] like that this thing again gets an ID then also again a name and here now again the character can have a list of weapons but one weapon only belongs to one character so this means similar to The Backpack we have a character id and also uh a character here like that all right so that would be the weapon and now here in our character we add the list of weapons all right that's it maybe we can you know I just wanna wanted to initialize an empty list here but let's ignore that and simply add another database set here it's not just to mention that it's not always necessary to add the DB set here when you add relationships then any framework will know what's going on and we'll add the corresponding tables but with that you do not have the option to give the proper name maybe to their table so the weapons table would maybe just be called weapon and I don't like that so this is why I always pretty much always add the database sets here so we've got everything ready here let's try to add another migration and call this one now weapons or maybe character weapons relationship all right let's have a look at the migration there it is in the app method now we create the weapons table and again similar to The Backpack ID name and the character id we've got a foreign key here and that's it isn't it great so now and again please note on delete if a character gets deleted then all the weapons are also gone so again IDF database update all right you can see new table will be created here that's nice right again we have a look at SQL Server mentioned Studio refresh the data base and now we should see our weapons table all right characters did not change at all here's Noah weapons table and then let's edit this and again add Ali here and now let's say please note ID now is 2 because the first L was deleted you know here say in the weapons table maybe you've got a pistol for instance and we can now also add a shotgun with this ID here so Ellie now has two weapons a pistol and a shotgun but if we remove Ellie again nope delete Jesus that's what I wanted to do and have a look at the weapons table gone great and now let's move on with the many too many relationship so for that again I wanted to add factions all right or call it teams Clans whatever it is and here now we know we add a new item for that called faction and in here again we just add an ID again also just a name and here now we add a list of characters like that all right and with that now we know that factions can have several characters and vice versa in here we again add another list but this time of course also of factions now let's also add the database set here in the data context for the faction we now say factions save everything and hopefully the last migration right so in this scenario now this would be character factions relationship this is done and now you will see something very very interesting because in the up method you will see the factions table nothing really interesting here but also another new table a junction table so-called the character faction table with a character's ID and a factions ID meaning to enable or make a many-to-many relationship possible you need a junction table where these IDs ID mappings are found now just side note B4 Entity framework core five so with Entity framework Core 5 and after that version so now with the entry framework server in anyways before that version you had to create this Junction entity by yourself or do some magic with the fluent API but now this is the past you do not have to do this any more but maybe again just as a side note this is an interesting fact but here now you see this table will be created for us and you can see also with the primary key here this is a composite primary key so there can only be one record in the database table with the same characters ID and factions i d combination all right and with that you can also see that this is a foreign key the character's ID is a foreign key with the principle table characters column ID and same thing for the factions and now the delete actions here mean that this is this is in essence an or so if a character gets deleted or a faction is deleted then this entry here in this Junction table will also be deleted all right let's do this with again.df database updates again we should now get the factions table and the junction table there is character faction and also the factions table let's go back here refresh this thing and now we see our factions edit up to hundreds and now also character faction and now how would that work again let's go to the characters table and here now we again say we've got Ellie for instance and also Joel and for the factions where is it there it is we can say we've got the fireflies for instance and the hunters and now in here let me just have a look at the IDS three and four all right and one and two so now here for the distraction table we can say Ellie for instance belongs to the fireflies but also to the hunters and Joel same thing doesn't have to be right okay but just for demonstration purposes here you see that this works and now again when we delete Joel for instance delete this entry then in the character factions this is removed and now for instance when we remove the hunters just double check this is an entry here three for Ellie and two for the hunters and now we just remove the hunters this entry is gone and now again same thing for the character of course this is not I have to refresh this of course now it's gone and let me just delete this thing as well because now we've got our relationships but let's play around with that with code so we will next now create a controller and just add some actions here we can do with all these relationships all right now for that let me just create a general controller okay so right click the controllers folder API and then empty empty one like that because I have not too much code generated here and let me just call this the tlou controller this will be a fat controller I know you shouldn't do it like that you should use a repository probably and a service I do this in my courses so please bear with me here I know that but again just for learning purposes and to save you lots of time so the first thing we need in our TLO you controller is a Constructor and in this Constructor we inject the data context so we can also start with the data context so private read-only and then data context call this context and now with the Quick Fix menu we can generate our t-look controller and inject this now the very first method I would like to add here is a method where we create a character with the backpack already but since our entities are a bit complicated with all these relationships we should probably use some dtos here some data transfer objects right so let me real quick add another folder here call this a DT DT yeah why not DDOS maybe and here now let's first create a character create in dto and also a backpack create dto and I hope this will work so this thing only gets a description string with this encryption and actually we could also make a record struct out of this so public records tracked backpack create dto with only again the description like that and similarly click records tracked character create dto with a name and also the backpack create DDO with the backpack like that yeah actually I started it earlier and was hoping I stopped it but I don't know Visual Studio at its best today so we've got our dtos and now let's try to use them in our method here so this would be a post method HTTP post to create a character with the backpack already so public async task action result yeah action result where we yeah maybe return list of all characters already let's see how this will work create character now with the DDO create no character create dto character create dto this is our requests and in here now we say new character is a new character and the name is now here the request name we also create a backpacks of our backpack is a new backpack where we set the description to the requests backpack description and now here actually just could have used one dto for that and not two but anyways and the character now is our new character and here now we say new character uh backpack is this backpack and now we just access our characters table here with context characters say at new character we save all our changes I have to do that and then we return okay status code 200 uh for weights contexts characters and now this is a bit much for beginners maybe I also want to include the backpack so nope actually includes and then see for character backpack to list async and there is actually brace missing well let's see okay there it is let's just try this with Ellie and Elise backpack hit execute okay possible cycle character backpack character backpack yeah let me just have a look this is because of that BS here so now backpack and and what we can do is actually just set this to Json ignore save this again restart the app where is it there it is let's try this out all right nice this worked [Laughter] not much but this works and we've got two allies here all right great now maybe we can now do one more thing and add weapons so for instance here we add a new dto or maybe you can just copy this so here for the backpack you know we've got a say weapon create in dto weapon create dto and the weapon has a name and here now in our character create dto we also say uh actually a list a list of weapon created EOS call this weapons maybe we can format this a little bit automatic formatting not working all right then we use it like that she's a special Studio come on and now here we also say that quick look at the weapons looks similar to The Backpack right so maybe we can just copy this and say weapons is this is requests weapons select and then for each weapon we say new weapon where the name is also the request not the request the weapon name [Music] and the character is now the new character like that so we've got this new list and the new character weapons is now simply the weapons and we have to set this to a list so to lists and no this should work all right and in here now I not only want to get the backpack but actually also the weapons are right save that and now here we could say something like this is now Joel With the Jones backpack and this guy now has a pistol [Music] and also a shotgun we hit execute of course there is the cycle error again because maybe I should have used the video here as well would make definitely sense Jason ignore and let's also add this here all right restart one more time okay looks great so now we've got double entries here so maybe we can have a look on the database uh here the characters execute SQL maybe we can delete Ellie here first one and the first Joel you see Ellie we see double this is already nice you see the backpacks yep that's nice and we also see the weapons nice so this works and the last thing the factions so let's just do the same thing in essence it's really just exactly the same we add a new dto again foreign for the factions that would be a faction create dto with a name and now here we also add the list of faction create dtos factions and then here nope in the controller we do pretty much the same thing factions factions and for every faction we say this is the name maybe here now set this to f here as well but the character list is now actually a new list of characters and here we already add our new character this one all right I will also push this to get up so don't worry about that and here now we say factions is factions like that restart this there we are let me again just remove one character Joel for instance ah come on delete this one okay and you see it here now we've got the factions as well so you know we just say instead of weapons or additionally to the weapons we also add factions and this would be something like the fireflies and the hunters what's the issue here okay let's see if this works um yeah looks good okay nice and I'll double check in the database you've got Joel now with id9 same here and here and now in the factions table we've got the two factions with uh ID3 and 4 and now in character faction we see the mapping here great so this works as well of course I know this is just creating no updating or anything is done here and actually we have to include the factions here as well so maybe we can just add one more method just to get method to get one specific character so that'll be then HTTP and get public async task action result character gets character by ID and let me also add the ID here as a route not necessary actually but definitely a better practice and here now we just say character is a weight context characters and you know you want to include first the backpack then we want to include the weapons and now also the factions and return this um nope not return we want to get the first or default where the ID is the given ID and here we just return this thing and we want to call the async one all right that should be it and let's have a look try this and the IDS for the characters are actually where are they five and nine so when we go to Ellie we see weapons infections are empty but we got Ellie's backpack that's nice and nine is Joel with his backpack the pistol and the shotgun belonging to the fireflies in the hunters again if that's correct I leave this up to you and that's pretty much it all right so I hope this was not too complicated especially here in the end with the controller again I just wanted to play around with that stuff show you something there was no script Nothing prepared really so we can now create characters one more tip do this with details really create data transfer objects for transferring data to the server and or to the service and then also to returning data back to the client you see it here this was this gave us some trouble this is why we had to use the Json ignore attributes not the best practice really so DDOS make a lot of sense here but still I hope you learned something if so I'd really appreciate it if you could give me a like and maybe even subscribe to my channel and just write a comment in the comment section below thank you very much for that and thank you very much for watching and I hope I see you next time take care

Channel: Patrick God

Views: 18,743

Keywords:

Id: V0UF4vEMlhQ

Length: 47min 31sec (2851 seconds)

Published: Tue Apr 25 2023