.NET 6 🚀 EF Core & SQLite with Code First Migrations

hey friends patrick god here welcome to this video and the new studio yes we moved and we are still arranging everything in our new place here because as you can hear maybe the sound is not the best yet so please bear with me still uh yeah arranging everything ordered some stuff to hopefully improve the audio quality so i'm really sorry for that but still there has to be a new video this week right and it's actually a simple one anthony framacore with sql lite now if you already followed my channel and my videos you pretty much know how to do this i guess usually i use sql server for all the web api and energy firmware core stuff but now we use sql lite and the most important thing here is well for one the package you need to install the new get package and then also the connection string and what a coincidence when i edit the connection string well i did a little mistake so i'm tired i'm really really tired because of all the moving and still i managed to fix this error of course and then you can use what you will learn and then you can use entity from a core with sql lite to well build your web api and then use a sqlite database i think that's pretty much everything i wanted to tell you if you learned something and like this video as always guys thank you very much for clicking the like button maybe even subscribing to my channel i promise the sound is getting better it really annoys me but i hope it's okay for you and the other thing that might be interesting for you is my newsletter for all these videos here earlier in your inbox and also the upcoming.net bootcamp the web development bootcamp and also other courses so if you're interested in this stuff then maybe the newsletter is something for you and that's it that's it for this intro and now just enjoy the tutorial all right so we start with a simple new project and we create an asp.net core web api project here it is just click next let's call this ef6 sqlite tutorial perfect name dotnet six it is no authentication configure for https yes please use controllers and enable open api so we can test our controllers in a minute or in a couple of minutes let's just create this and again i'm really really sorry for the sound lots of echo but it will get better soon when the room is treated well for now hope it's not too bad so this is our project now and since we want to focus on entity framework core with sqlite first let's just install ef core for that we open the package manager console here and we have to install the the tools the net tools for dot net entity framework to do that we enter dotnet tool and then install dash dash global and then dot net ef and my case it is telling us that it is already installed now what i can do is i can either update it or so i mean enter update or uninstall it first and then install it again and let's see what's the current version [Music] we've got [Music] 6.0.8 so this is new and now i can double check with dotnet ef and here's now the installed version of the entityframercore.net command line tools so this is the very first thing we have to do and the next then is install some you get packages now there are different ways to do this when we want to use the the db context class for instance because this is what we have to do when we create a data context but more about that in a couple of minutes then we get the option to install a certain you get package automatically or we just install them manually now there are three we have to install so right click the project manage nuget packages and then the very first one well entity framework not core sorry we have to open the browse tab and then it's just entity framework but wait a second no it's energy from a core of course and if microsoft entity framework was confused about the uh because of the installed tab but well maybe you experienced this little mistake as well so please make sure to choose or to select the browse tab and then microsoft entity firmware core it is almost 500 million downloads so install this one and then you can also see the version 6.0.8 so that's the one and this one here is the old entity framework framework uh entry framework framework yeah oh gosh yeah the the the i'm so tired because of all the moving and this is the result now okay i hope next week it's getting better so this is the old entity framework stuff and now uh the the new engine framework core one so we've got this covered and the next one would be then anti-framework core design that's the one shared design time components for energy framework tools we need this for the migration for the code first migrations and so when this is installed then you need the the right provider for your database now i think usually we use sql server most of the time but now it's about sql lite and this is actually the the most important difference i guess that we choose um entity framework and then sqlite there it is microsoft dot entity frame record dot sql lite with 80 million downloads for now and the other one would then be microsoft entry firmware core sql server and for the other for any other database then there is probably a provider for this certain database then but now for sql light you need microsoft enterprima core sqlite just install this one and when these three are installed and then you can use the code first migrations for a specific sqlite database right okay so i think this is an important part and with that everything is installed you can double check in the project file there it is and you frame a core core design and also sql lights perfect let's just save that and then the next part is well there's not a perfect order actually but i would like to add a connection string because with that connection string then android thermocore knows where the database is and the connection string for sqlite database is pretty simple it's just the file and of course the place of the file so let's just add a new section here call this connection strings for instance and then default connection and we simply enter for instance c [Music] colon backslash and then let's call this just i don't know sqlitedb for instance or dot db that's the correct file name and that should be it with backslash two two backslashes and then this should be it i hope it it works we'll see okay now after that um we could start with the actual data context but what i like to do is well create a class a model that you want to store in the database and since i still like games haven't played games for a long time but in for instance the dot net six jumps of course we create a little text-based role-playing game with rpg characters with role-playing game characters so let's let's just add it an rpg character class a simple one a really simple one and this then will be used for our database so rpg character it is and this guy here gets or these guys and guards get an id and then the next thing of course maybe a name and this is string empty by default then we could also add an rpg class so let's call this rpg class for instance at night something like that also empty by default and then let's just add some stats for instance well hit points would be interesting right yeah and let's just leave it at that okay so and by default we set this to a hundred and then of course we could add some attributes like the strength the defense intelligence and so on but we do that in the course and i think for this tutorial this is totally sufficient and now we can start with the data context so this thing that it's necessary for entity framework core to communicate between our web api here and then actually the database so for that i like to add a folder call this data and then in here we create a new class and we call this class then data context and in here now we need to use another class and this one is called db a context and you've seen it here microsoft any frame record this is where this comes from and there's a lot of text you can read to to to see what this actually is and it says that it represents a session with the database and can be used to query and save instances of your entities it's a combination of the united work and repository pattern great stuff because with that you actually don't have to worry about the actual database that is used any relational database should work here mysql for instance should should work as well and the only thing here for the only thing that's interesting for you is the to use antifema core with link for instance and then access the data with that help then all right now the next thing is also a constructor and this will look like that we've got a data context but we also need a parameter and this is a db context options parameter we give this thing our own class here the data contacts call this options and we also need the base constructor here and then the last thing then this is also very important if you wanna see a representation of a certain model of a certain entity in your database you have to add a db set here so for instance we can say db set of our rpg a character class and then the name of this db set is the name of the table so usually you would just pluralize the entity name in our case that would be rpg characters already suggested here and we can also initialize this thing as a set of rpg character and i think this should be it then all right then the warning is also gone but the other way would also work with the get under and the setter it's just this little warning the green lines synths.net six and i think that's it with that we've got our constructor we've got the connection string we've got our class and the next step now is to register our data context we do that in our program cs file and down here what we can do is we say builder services and then add db a context our data context and then here again we need some options but let's first add the reference the data folder all right and now options and in here now we say options and then use and this is on the provider use sql lite all right and if you want to use sql server it would be something like use sql server but we don't have we didn't install the nuget package so it's sql lite then and here now comes the connection string and we access this thing with builder configuration get connection string and then default connection all right one parenthesis is missing and this should be it let's just double check app settings default connection this is now our database all right and with that we can actually run our migrations with code first migration so let's open the package manager console again let's get some space and now we have to pay attention here because we need to be in the proper folder so that would be ef6 and so on and now that's the correct one and now what we can do is let's have a look at the ef command again and you see we've got three commands database db context and my gradations and we actually only need two of them migrations and the database the first one is the migrations command to add a new migration and after that we want to update or create the database so then we need the database command so first let's just enter dotnet ef migrations and initial for instance because it's the well the very first migration that will also create the database and i hope this works now build succeeded all right this looks good and now we see a new folder here migrations so let's have a quick look what is happening here we've got two methods up and down and as you can see here in this case the rpg characters table will be created when we now really run this migration or apply it we've got the columns id name rpg class and hit points and the primary key will also be the id column here and if you would like to roll back this migration then the down method would be called and in this case the table would be dropped that's it so now we can say dotnet ef database and then update and don't be worried about the update command this also creates the the database if it does not exist but something is wrong here yeah and i think this is the issue here of course so i forgot to add data source and i think that this won't work as well because there might be some uh right issues here you're unable to open database file now let's not use the see partition let's just use the file name here so in then in the root folder of this project this file will be created sqlite db uh yeah so let's try this one more time the ldf database update and yeah this looks way better you see all the commands for instance create table rpg characters and what else well that's no great table you have migrations history but this is not interesting for us these are just the the current migration states so the actual rpg characters table is the important one right okay so that's it this is how we create our sqlite database and now of course we want to have a look right so right click this thing and open the containing folder there it is but how would you open this one well the thing is what you can do is simply download the the light brother db browser for sql lights that's the one sqlitebrowser.org that's where you can find this thing and download it of course there's also a portable version here for windows there's also one for mac os and for linux so just choose the one for your operating system and when you open it it looks like that all right so this is the db browser for sql lite and now let's just open the database there it is open and we see the rpg characters table also you have migrations history and then with browse data we see there's nothing in here but this is our table right in essence we're already done but if you still want to do some more stuff meaning create characters for instance just fill this uh this table here with data then keep watching we will create a controller now with a post in the get method where we then create new rpg characters and then get them back again so right click the controllers folder and then add a new controller now and that would be an api controller an empty one is what i choose and we call this thing now rpg character okay almost character controller and in here now the first thing is the constructor where we want to use our data context call it context and we create and assign the field and we can also add the underscore here and now down there we well let's just create the post and the get method already so maybe first the post method to create a character that'll be in public async task action results with uh well let's just return the complete list of characters then rpg character all right add character with an rpg character call it character maybe and then what we can do is simply call contexts so this is now our database actually and then you see rpg characters so this is not a table and yes we just want to add this character here and then after that we have to save these changes because with add alone these changes changes are not really written it's not stored persistently this change but with safe changes async then it is and then what you want to return is an okay so status code 200 and then awaits context and then rpg characters to list async for instance all right with that then we get the complete list back and this is what we actually can do also with the get method so http and get it is this time and again public async task action results list rpg character and by the way i use the actual result list rpg character because then i see these entities in swagger ui what i like to use to test this api then when i would just use the i action result interface but this actually also works but then i wouldn't see the actual entities and default requests and result objects then just try that by yourself and then i think you will see what i mean so simply call this get characters all characters for instance all right and with that then we just return okay oh wait and then context uh superheroes yeah that's what i that's what i usually do right i create superhero uh web apis rpg characters rpg characters to list async and then maybe also the last one to get a single character for that also which is http get but we also add a route here which is the id as a parameter and then here public async task action results and then rpg character no list this time get character with the id please note that the id here and this id here so this string here and the name of this parameter they have to match and now here we just say var character is awaits context rpg characters find async id for instance now if the character is null then we say return bat request character not found and otherwise we just return okay and then the character all right that should be it's let's try this there we are worked so far that's nice so you see we've got our post method and the two get methods also this the default with the forecast controller not interesting for us and now here you also see the rpg character entity and also here for instance we see an example value for this request let's try this out actually we don't need an id because it's sqlite will do this automatically and the name here let's say frodo well he's a knight got a hundred hit points we hit execute and we get a list of our heroes back let's try one more sam for instance hit execute all right this looks nice we can get all our characters we hit execute works and also a single one [Music] with an id we try this for instance id oh let's try id three first then we see character not found that's what we expect and with one we get frodo two we get sam and in our database now we can simply refresh and here we see frodo and sam and let's just say frodo now has 200 hit points we hit apply and also right changes it's important and now let's get frodo one more time and we see the 200 hit points perfect that's it and with this quick implementation you can use entity framework core 6 from.net 6 with a sql lite database and of course i will push this to get up just check the the video description below for the link and then you can play around with android phone core and sql lite yeah that's it again check out the video description for the link to the github repository i hope you learned something if so please click the like button and maybe even subscribe to my channel again i have to apologize for this sound it will get better promise you that and the next thing might be the newsletter for you because if you learn something and you want to see more learn more then you can follow me here on my channel watch these videos here on the site and you can also as always subscribe to my newsletter because then you get these videos in your inbox and also information about upcoming online courses but either way thank you very much for watching thank you very much for your time and i hope i see you next time take care

Channel: Patrick God

Views: 26,363

Keywords:

Id: PDiRDNNc2tw

Length: 27min 6sec (1626 seconds)

Published: Tue Aug 30 2022