.NET 7 🚀 JSON Columns with Entity Framework 7 / EF 7

hey friends thanks so much for dropping by my name is Patrick God and today it's about Json columns in Entity framework 7 so Entity framework core 7 but they ditched the core term now and also the older.net framework and the old energy framework stuff not important anymore now it's entity frame X7 and with that we've got Json Collins isn't that great and now this came with the second release candidate of dotnet 7 and I thought well let's try this out and this is what we are going to do in this tutorial now I will short disclaimer here I will show you four different ways to do this we've got one entity called superhero and another entity called hero details and this is one relationship so one superhero got one hero details model entity relationship you know what I mean one to one and there are well four different ways to do this there's one let's say classic way that I would use most of the time without really configuring anything with the on model creating method hope you already know what I mean mean you can simply and this is the beauty of a code first migration with any framework actually you can just create your entities then your data context and then you say well I want to get this certain database set right like the superheroes for instance and the models then have the relationship in there so let's say we've got the superhero class and the superhero class has then one property called the hero details class then the hero details have some properties and also an ID for the primary key and with that then the engine framework and code first migrations will already know what's going on here and create your tables two tables this is important it will create two tables this is really nice I like that learned it that way so one one table for the superhero and one table for the hero details then but you can change that and this is what we will do today so well if you want to know how Json columns and also other ways of creating these relationships with course migration in framework 7 now works stay tuned stay with me here and keep watching please and if you like this video and learned something as always I would really appreciate it if you click the like button maybe even subscribe to my channel thank you very very much it does make a difference and if you want to learn even more get these videos in your inbox and get early access to my upcoming courses I mean.net7 is around the corner or maybe you while you're watching this it is already there then you can also expect new courses like 2.7 web dev boot camp and as a newsletter subscriber you will be the first to know when these are available so uh thank you very much for also subscribing to my newsletter and now enjoy the tutorial alright so let's start from scratch here I start Visual Studio 2022 and we create a web API because I also want to add a controller where we will see that with the let's say default link statements we can still access all the properties everything from our entities right and I also want to show you well in total four four different ways on how to actually run codeless migrations with Entity framework and how they result then in the database tables right so I hope you got some time here if you're only interested in the Json columns check out the chapters and then just jump to that and you're good to go I guess so asp.net core web API it shall be let's call this ef-7 Json columns maybe yeah I think that's okay dot and 7 preview it is by the time of recording we've got release candidate 2 and also the preview edition of Visual Studio 2022 so but it's just not many days are left is until.net 7 is released so I think you will be fine when you do not want to get the preview edition of the SDK and visual studio and just wait a couple more days so there's our web API typical stuff we've got our programs yes the weather forecast example with the specific or the corresponding weather forecast controller and now what we want to do is we want to get a relationship right so let's just real quick create two entities and I miss my superheroes so let's create a superhero class superhero it is and this guy now gets an ID and also now this is the relationship hero detail and the name no not superhero hero detail we create this class in a sec Visual Studio don't worry and then these are just called detailed can be null and again Rick Wiki I know I could create a models folder bear with me here this is just a real quick and Dirty tutorial I think you want to learn something and not too much a time so now here hero details and this thing now only gets a name and a city all right so we've got a string a name like Spider-Man for instance by default this shall be empty and then also the city place of birth or whatever it could be string empty all right so that's that and here this is called hero details and you're good to go so this is our really really simple relationship we've got superheroes with an ID and then already the details and then details of course this could be something like an address I don't know date of birth custom ID whatever it is and in this case it's just the name and the City of the superhero now with that we can actually already move on with Entity framework itself meaning creating a data context but let's first install Entity framework right so there is this little page here the new again gallery4.net you have we need the.net tool and again if you're watching this when dot Net 7 is already released just type in this thing here.net tool install global.net EF with that you get the latest released version but if you want to try this with the current release candidate or a preview edition of some.net version also for the future maybe maybe there's a preview for Android framework 8 then then just go to versions and make sure to specify the correct version here so you can copy this then and then we open the package manager console here for instance in Visual Studio and as you can see I actually already got this installed here right and when I try to install this now just paste it it's telling me it is already installed right so what you can do is actually you can uninstall everything like that remove the version then in this case now it's uninstalled and here I can again install it and you can also use the update statement here instead of install to get to the latest version but again only with the released version right so if you want to use a risk candidate a preview version then make sure to specify the version as well additionally we need nuget packages so right click the project manage Cricket packages and these are first let's make sure to use the browse tab also include pre-release right and there they are already you could also just search for them Microsoft entity firmware course SQL Server here it's uh also.net7 release candidate two if I would not check this then what do we get loading loading loading here version 6.0.10 right so this is the big difference and here in this case to use Json columns we need.net7 with Entity framework seven please note it is now called Entity framework 7 not Entity framework core 7 anymore made another video about that internally it's still anti-famil core but the official name now is energy framework 7 and the old.net framework the old Entity framework well this is pretty much gone right so we will continue with Entity framework core here and net core so long story short install SQL Server the preview Edition in my case we hit accept and and another package we need is this thing here Microsoft Entity framework design and also here the release candidate2 off.net 7. all right and with that we can go back to the solution Explorer and in here now this time I create a new folder because I'm just used to it and call this data and in here now we create a new class and call this data context this will now be our DB context our database context to access database if you have no idea what I am talking about please check out my other videos about energy framework or just write it down in the comment section and I will do my best then with Entity framework 7 to create some kind of introduction tutorial with any framework maybe this is a good idea if you want this please please please write down in the comments so I know that there are people who want to see this and apart from that you can of course check out my.net jumpstart tutorial it will be updated with net7 of course and there we will do this as well in teeny tiny steps and explain everything but you only need a couple of hours and you pretty much know the most important stuff about web API and any framework and also authentication with Json web tokens but this is just a little commercial sorry about that but if you're interested again please check it out Link in the video description so we we want to have the DB context now DB context Jesus I'm really really tired today the EV context it is we need Microsoft engine framework for that that's already nice and now we need a Constructor here I'm curious no it does not work I have no idea why the build instrument doesn't work properties work but CT or for Constructor not here again tell me in the comments does this work for you CT oh is it Visual Studio what the heck is going on here maybe the latest update I don't know so since this is not working we need to public Constructor data context we need a parameter here DB a context options data context we call this then options and we need the base Constructor as well also with these options here let me again close the solution Explorer this is now our Constructor and now the thing is we first need to configure the connection string right so we can do this either here or for instance in the app settings Json file here and then grab them from there let's just do that in the unconfiguring so we type overwrite on configuring it is and in here now we say options Builder use SQL Server this is now why we needed the provider package the nuget package for SQL Server because we we're using SQL Server here and now also a connection string and in here now I've got SQL Server Express installed on my local machine so in this case this will be simply SQL Express then the database name uh I don't know Json columns superhero test DB are awesome name and then trust the connection set to true and one thing that is new we need trust server certificate also set to true this was new is new with ND framework 7 if you've got other experiences again please tell me that in the comments hope now you can read everything on one screen so seek will Express it is the localhost SQL Express database is superhero test DB trusted underscore connection true and trust a server see that server certificate is also true all right okay now the default way I would do this is I just add a DB set here DB set of type super hero and I call this simply Heroes like that for instance and this is then table name all right that will be created with the migration all right now to make this work we have to make one more change to the hero date details but I will first show you the actual error a message meaning we open the terminal again or the package manager console now we can write.net EF let's just have a look first to see this database TV context migrations these are the comments commands that are available here and first we need migration so dot net EF migrations add and then since this is the initial migration let's just write initial well and make sure to change the directory to because here you can see we've got the solution folder but we have to be in the actual project folder so CD EF and so on and now we can again write.net EF migrations at initial and where is it show me the it even fails okay what the heck is going on here this is not expected actually Instagram card does not exist in the namespace okay this is interesting so let's just reboot this maybe well it's the preview edition of visual studio right maybe I forgot something I don't know let's try this one more time add initial build started will succeed it unable to create an object of data context oh yeah I forgot one more thing let's see that it's late and I'm tired but still I hope you learned something in the programs yes of course we have to register the the DB context maybe I should do this live in this case I'm pretty sure one of you would tell me that right so uh yeah and the the data context of course like that okay so now let's try this for the third time we'll start a bit succeeded and this is what I wanted this is what I wanted to to see hero details requires a primary key all right and this is interesting because this is the only way where you where you need this so what we can do here now pretty simple and ID and now we're talking right so with that now we've got our migration files so this is the first way and the typical way I do this you think the other ways are better again please send me that in the comment section lots of opportunities to write some comments in this video and this file now this is important here in the migrations folder you see that energy framework creates the hero details table with the ID name City and also the heroes table with an ID and a details ID so you know we we don't have it here actually but Entity framework is doing this automatically and this is really great and it adds the primary keys and the foreign keys and so on and and now we can run a.net EF database update with update it will also create the actual database and you can if you want you can have a look at all the commands but let me first open the secret server management studio in my case and then we can actually have a look at the new database and then we will delete it and Implement all the other ways all right so okay there we are databases lots of test databases this is great places e-commerce course by the way and here it is superhero test DB we see the tables hero details and heroes right and again I'm I'm doing this that way because I really want to emphasize the differences that you see here all right so we've got hero details with ID name City and the columns with ID and details ID and now again if that's too much for you you can just skip this I wanna add a controller so with that controller then I want to test what is happening if we uh add superheroes and also try to find Heroes with a specific City for instance right so let's just add a new controller here it's an empty API controller we call this super hero controller again we need a Constructor actually but it's not not that way today so Public Super almost public superhero controller see how rarely I do this that way and here we need the data context call this context and we assign this field here at the underscore I know I can configure this didn't do it so now with that we've got our Constructor and I'd say let's just add two methods one method to add Heroes and one to get Heroes by a certain city all right and this then the first one would be HTTP posts public async task action results so I see the types with swagger UI otherwise I could also just use the I action result but in that case I wouldn't see the types in Swagger and here now again talking about all that in the course and my other tutorials so maybe you want to subscribe this would be really really nice thank you so much for doing that and now here we just say context Heroes this is how we can access them now and range and here we can add the list then and with the weights context save change async yes intellicode that's correct and let's then return all the heroes now from the database so context Heroes to list sync maybe alright so this is really quick the post method and now to filter the heroes by a city we just write this method maybe we can just copy this okay now it's time for copy and paste Eros you get Heroes but here now with the city and actually let's just say for Our Heroes is now awaits context Heroes almost Heroes where the hero details City contains the city okay and we also make a list out of this so to list async it is and he reaches return the heroes and let's just say this is always not not okay now this is really really bad please don't do this at home but for this tutorial it works okay I think that's it first we add Heroes then we get Heroes let's run this and this is one thing I prepared let's just have a look here okay this is our one new application and I've got the payload here so let's just do this right so by the way here you see here now all the types and um let's try this out we can actually add this so we add Spiderman living in New York City Iron Man living in Malibu Daredevil living in New York City and again when we have a look here added up to 100 rows nothing there and at the top 100 rows nothing here here as well we execute and we get all our here we get all our heroes back execute SQL there they are and in the details we also see that stuff and now let's try to filter them by this specific City we can just enter new for instance hit execute and we do not get the details this is interesting let's have a quick look maybe we also need to include the details of course so like that try this one more time and now we've got it okay so this just works great stuff okay 20 minutes and we got uh the old way or my typical way to do this now I want to show you three other ways I want you about that you can skip with the help of the time codes or the chapters so now we can enjoy this little blog post here announcing anything from a core 7 RC to Json columns and Arthur here great blog post by the way is showing us how we can do this with an offer and addresses and so on and I want to do this as well now with our superheroes alright so what we need now is the on model creating and regarding our models we can also make some changes here let me just stop the app and now also very important we have to delete the database so delete this thing make sure to close the existing connections otherwise it would it would just waste a lot of time and then we can now remove the ID again here from the hero details we've got our superhero now the models look like this hero details name and City and superhero ID and details and now regarding the data context we can actually remove the DB set here but again now we override the um on model creating and the first way now you can do this is use everything in just one single table all right and the way you do this is like that so you get the model builder and then entity super hero and to T superhero it is and you just say that one superhero owns one and then sh for superhero that's short Jesus what's going on here I'm not the superhero superhero owns the details okay and now I know why intellicode was doing that stuff because I forgot the parenthesis here so this is everything model builder entity superhero owns one details right so this is the relationship one superhero has one relationship a one-to-one relationship two uh hero details all right so that's it and now we remove the migration we do that with DOT net EF migrations remove uh build failed again okay this is really interesting what's going on here data context or something oh sure of course what the heck am I telling you here we of course need the the uh DB set here in this case if we want to use the controller here but if you don't well maybe there are some cases where you don't and I tested this without so this is why I why I told you this um so sorry about that if you want to access this stuff then of course you need the DB set but the migration would also work without the database set all right so this is just important to to to mention here uh so let's just edit back again the superhero and heroes and DB set so this is the necessary for your controller or for any function where you want to access the heroes from the context and just regarding the migrations this is in this case not necessary the only thing to consider is that the name then of the table would be different I think well you know what since we already got almost half an hour here let's just test this so we remove the the DB set here and in the superhero controller we just remove the methods real quick so now everything is fine and uh again pay attention here now in the where's the data context we've got our or you don't don't have our DB set here but we've got the on model creating where we Define the relationship between a superhero and the details and now when you get your DB set then the name of the table will be heroes but otherwise it's I think it's superhero so let's just remove the migration again this is the old one so uh let's try that hope this works now yeah it seems so and now we can add it back and initial all right and now with that you can already see here that yeah we've got the name superhero now for our table and it's only one table right and it would create an ID field and now details name and detail is City and in the database yeah there you see the name then it would be detailed underscore name and details underscore City but since I also want to show you the controller let's just remove this one more time and uh let's just add the TB set back and also the controller methods and now everything okay yeah and now let's add it again okay now this worked and drumroll Heroes table nice so now you see it is called Heroes and still we've got the ID and the details name and the detail City and now let's update the database here to see that in action.nif database update and we also run the the application one more time so now let's have a look let's refresh refresh close this we've got our superhero test DB with only one table now and here we've got details name and so on and when we um edit the arrows looks like that it's bad right I don't like it that way really but uh let's go back to Swagger one more time there it is alright so let's just refresh here now try this out and again I've got my payload here execute this seems to work right and of course now if you want to filter this for New York City for instance we've got these two and in the database looks like that all right so this is the other way you can do and now if you say oh my gosh only one table for one relationship I I learned something else when I studied computer science so please give me two tables here at least well I got you covered we can do this as well let's just stop the app first there it is stop this remove the database again close the connections and now here we remove the migration again and the changes are really really easy really really easy because in our data context now we've got this relationship here right and what we need is we we have to add a navigations Builder like that so one more option in essence and here now we can say navigations Builder simply to table and here now we can also Define the name of the table like hero details for instance all right close this and that's it this is everything you have to do and to use Jason then this is also really really simple but first let's just do that way we add the migration again with DOT EDF migrations at initial and now in the migration file you will see we've got the heroes table and the hero details table changes here now is that we've got the superhero ID remember the very first way was a little bit different you've got the heroes table and then here a details ID and this is simply now the other way around but still with primary keys and foreign keys so this is nice and again just to show you and now I think you you've got time right so we can update the database and then we run the app to have a look again how this works and if it works so refresh superhero hero details and the hero just the ID it's funny so somehow okay and now here let's see let's get Swagger let's add them again execute there they are and here they are as well and now we can also filter by the city all right so this works as well and now finally Json kudos to you if you did not skip to this chapter here but if you did I totally understand of course so let's just stop the app first again and now let's remove the migration uh and database still here do delete yeah I forgot to delete the database of course so we stopped the app database is deleted and now we can remove it the migration all right and now navigations Builder to Json that's it alright it's that simple really great stuff really and now let's just add the migration and you will see I thought you would see oh yeah this has to be removed and now again it's late it worked so you see that it's even simpler more simple than creating the table somehow because you do not have to specify a name or anything well it's again only one table if you would like to do it that way in production I leave this up to you really I gotta admit I'm not a big fan of Json columns we've got lots and lots of problems at work with that but this is uh this is another topic here maybe so create table we've got the heroes table and now for the details we've got an n Bar jar Max and well the name is detailed because this is this is nice in essence because this is the name of the of the property here as well and we've only got the ID well this is everything right and now let's have a look okay I know this is this is interesting actually this is really interesting and of course awesome stuff because many people wanted that so now let's just update the database.netf data base update and with that okay this worked nice let's close this on refresh and now it's getting exciting right so we've got only two columns look exactly as the as the model nothing here but if you skip to this chapter we also created the the controller to test this so let's run the application and here's Swagger now we had to try it out there's our payloads right so this is how it looks without any IDs or something execute that's the result it looks exactly the same right as the ways before we just get the idea of the superhero and also the details looks awesome you would not know that this is now Json column in the database and this is the most important thing now if you skipped again I wanna I wanna show you this and now here in the in the controller you get this simple statement here we just uh it's it's really the way you know it already we got the heroes by context Heroes so we access the DB set with include details because we want to see them forgot that sorry about that and then where the hero details and then the city contains and so on right and with that now let's try to get the ones that live in New York City bam Works we've got Spider-Man and Daredevil and this now is how it looks in database exactly like that so we really got the Json stuff in there okay so now this took way longer because well I just wanted to to show you every single step without copy and pasting anything except the payloads yeah but now you see with Entity framework seven Json columns are a real deal now you can really use Json columns if you want to do you please tell me that in the comments thanks for watching I will uh I will push this to GitHub now and the link as always in the video description yeah that's it Json columns I know this was way longer than I actually wanted to create to do to you get the idea because I I really wanted to show you every single step and not throw some complete Solutions here at your face and you would not really know okay how the heck did you actually create and build all that Patrick right so this is why I show you every single step hope you liked it and you learned something if so please click the like button maybe subscribe to my channel thank you so so much for that and if you want to get even more.net and Blazer stuff and energy framework and whatnot then maybe you want to subscribe to my newsletter thank you very much for that as well and please again write it down in the comments do you like this stuff would you use Json columns what are the pros what are the cons tell me that in the comments would be really interesting to to get your opinion here on that and if you want to see more now just hang out here just just do nothing just keep watching YouTube will do the rest and show you the next great video about.net and Blazer maybe even by me so maybe you want to hang out on my channel thank you so much check out the videos here on the side this would be really really great thank you very very much for watching again and thank you so much for your time and I hope I see you next time

Channel: Patrick God

Views: 13,883

Keywords:

Id: C5AkBDp_YjI

Length: 37min 40sec (2260 seconds)

Published: Tue Nov 01 2022