Introduction to Entity Framework Core and approaches
Hey guys. Today I'm going to talk about Entity Framework Core. Entity Framework is .NET's ORM framework which lets you map your C# models to database tables and it makes it easier to query and update your database tables. There are two approaches. The first approach is Code First approach where you design your C# models and apply migration to create your database. And the other approach is Database First approach where you have database and you run scaffolding to create models and DbContext in your dotnet project. I'm gonna show you demo on Database First approach, because most of the projects start they have database team and they design database and they give the database work on it.
Project architecture and demo database
So for this project I'm going to follow this architecture where I'm going to store my database in SQL Server Express and then I'm going to create a Web API to show results in JSON. The reason why I'm going to create a Web API is because it takes JSON and returns JSON as an input and output and it gives me freedom to create my UI in any of these technologies because these technologies handle JSON or any technology that you desire which can handle JSON. You can work with Web API. OK. So after I create Web API I'm going to install Entity Framework in my Web API and then run scaffolding to create DbContext and models. For my demo I'm going to use bookstore as a database and these books have publishers and they have authors and then we'll maintain sales of these books in our store. And to get into the system we have user table and it has user ID and password. If you do not have a database you can totally find this database in my GitHub link here. I'm gonna put this link in the video description so if you want to practice with me you can totally take this backup and attach in your SQL Express and follow this demo.
Connecting Visual Studio Server Explorer to SQL Express
So let's go ahead and connect to our database. So I'm going to go to Server Explorer and add a connection and here I would like to connect to my SQL Express so I'm gonna say .\SQLEXPRESS and I'm going to look in my drop down here for selecting the database and you can see that I have a BookStore database here and I'm gonna connect to my BookStore here. So I'm going to open and see all the tables. You can see all the tables are listed down here. Then I want to look up some of the details in my Authors table here. You can see Author has FirstName, LastName, Phone, Address, City, State, and Zip. These are the columns and some of the data. This is all of the data in Authors table. So alright, so I would like to show this Authors table in JSON format. So let's go ahead and create a Web API project. I'm gonna say it's a public project and I'm gonna put it in public folder so that you guys can have access to and I'm gonna say it's a BookStores Web API project. Alright and create. It's gonna ask me what template I want to create. It has an empty template and it has web application MVC format, Angular, React. So I'm gonna select API here because I want to show the results in JSON format. This will create BookStores Web API and out-of-the-box it gives me a WeatherForecast controller here which returns weather for the next five days. Let's run this and see how it looks like.
Running the default API and planning to show authors
So when I run you can see that it's returning WeatherForecast for the next five days in JSON format. I would like to show my Authors data in JSON format here. So the first thing that I'll have to do in order to connect to my database, the first thing I'm going to need is Entity Framework Core SQL Server package. And another package I'm going to need is Entity Framework Core Tools so that I can scaffold my database and create models in my .NET project here. So let's go ahead and browse these packages. And as I'm using SQL Server I'm going to install the SQL Server. Let's select proper version here and I'm gonna install Microsoft.EntityFrameworkCore.SqlServer so that I can connect to my database. A package that I'm gonna need is Entity Framework Tools.
Installing EF Core packages and scaffolding the database
Which is EntityFrameworkCore.Tools, and again let's select proper version. I'm creating the Web API in ASP.NET Core 3, that's the reason I'm selecting this version and let's accept this. Alright so now we have both of these dependencies. You can see that we have SQL Server and Tools. Now let's go ahead and scaffold our database or Authors database so that it creates models and DbContext. I'm gonna copy this piece of command here and paste it here. You can see that it's a pretty simple command. I'm gonna paste this in the video description. What it's doing is it's using the command Scaffold-DbContext. I'm passing the connection string of the database. You can get the connection string of the database from here. You can go to Properties and get connection string from here. And then I'm passing the DB provider how you can connect to the database and then I'm saying that output directories models. So what it did is it created the DbContext and models, all the models for my database tables in this models folder here. So I'm just saying the output directory is Models. Alright let's look at this DbContext. This DbContext has created some properties. These properties are same as my database tables so that if I want to update anything or select anything then I can totally use these DbContext and then here on configuring it is using the connection string here.
Creating the Authors controller and fetching data
Alright so let's go ahead and show the database table Authors data in JSON format. To do that I'm gonna copy this WeatherForecast and create another controller here and I'm gonna name it as AuthorController. AuthorController and let's go ahead and change that name here. I do not need all these so I'm gonna get rid of that. I'm gonna get rid of all this here. Nice. So now instead of showing Weather I would like to show Authors here. When I return JSON format here let's add the namespace for this. Cool. So to get data from the database the only thing that you have to do is you have to create an instance of your context, your BookStoreDbContext. Then you can use the DbSets in it to pull the data from it. So what I'm gonna do here I'm gonna say using and create a context instance of BookStoreDbContext and then return context.Authors.ToList. See that's the only thing that I have to do to get all authors. That's the only thing I have to do. So I opened a connection, I created DbContext and I use one of the DbSets from the context to return all the authors from my database. Sweet. Let's run this and see how it looks like. So instead of going to WeatherForecast I would like to go to Authors and this will connect to my database and list down all the authors for my database. Awesome.
Querying single author with LINQ and changing launch URL
So what if I would like to return only one author? What if I want to get author by ID? To do that I'm gonna comment this piece of code. These DbSets are like collections so you can put where condition on it. You can use LINQ to get author who has AuthorId == 1 and let's return ToList because it's set is enumerable as this property. So let's just return ToList. Alright so when I run this you can see that it will return only one author here. Now alright what I'm gonna do I'm going to go in my launchSettings here and instead of making WeatherForecast as default launch URL I would like to make it as Author so next time when I run it we'll go to AuthorController instead of going to WeatherForecastController. Also so what we did here we returned all authors and then we returned author by ID.
Creating (POST) a new author in the database
What I would like to do now I would like to add an author in my database. To add an author in my database I should create an instance of Author first, a new Author because we are adding an author. Then I'm gonna say this author has FirstName John and author has LastName as Smith. And to add this instance in my database what I'm going to do is I'm going to say context.Authors.Add and then add this author and to insert this record in my database I'm just gonna say SaveChanges. So what I'm doing here I created a model then I added that in my DbSet and to persist the changes in the database I'm gonna say SaveChanges. And if I now I would like to see this John Smith in JSON format. Cool. So what I'm doing here I created author, added that in the database and now I'm pulling that author from my database to show it on the screen. Sweet. Let's run this and see how it looks like. So now it's going to Authors link here. Now instead of going to WeatherForecast you can see that it created an AuthorId 28 John Smith. The phone numbers unknown and other fields are null. That's because we did not assign these fields when we added the author in my database.
Updating an existing author (PUT pattern)
Now that we added this author, I would like to update this author saying that this author has a phone number. First thing I will have to do, I will have to get this author. I will have to get this author in my model instance Author and then you can make changes to your author instance which you got it from the database. I'm gonna say this author has phone number. The phone number is 777, the luckiest number ever. And the only thing that I have to do now I just have to call SaveChanges in order to update my author in my database. Sweet. Let's run this and see if this works. So when I run it it will pull author from the database because it's the same author. You can see 28 and John Smith and now you can see it updated the phone number in database and now it's showing on the screen. Awesome.
Deleting an author (DELETE) and verifying removal
So now that we added and updated let's go ahead and remove this, delete this author from the database. Before I delete it I would like to show this author in my database. So when I go to show data you can see this John Smith in my database who has phone number 777 and the rest of the fields are NULL. Alright now let's go ahead and remove this author from my database. Here we updated. Now I would like to remove author and I would like to remove the same author. The only thing that I'll have to do, I will have to say context.Authors.Remove author which we just pulled from database. So what I'm doing here is I'm removing this author and to persist the changes I will just have to call SaveChanges and that will remove the author from the database. And then I'm again showing, I'm trying to pull the same person and show it in the JSON format and this should not return anything because we just removed the same author that we are pulling to show it in JSON format. Alright let's run this and see the switch. So no it shouldn't return anything because we just deleted John from our database. Sweet.
Post-scaffold cleanup and next topics
So this is how you can do the CRUD operations Create, Read, Update, Delete. There are a few things that I do not like when we scaffolded the database. One is it's creating the DbSet, its name is not plural because it should be plural because you know it's a DbSet and it's holding a lot of Authors so it should be named as Authors. And another thing is it's storing the connection string in my DbContext. That's not safe so I'm going to save this connection string somewhere in the configuration manager and pull it from that. I'm gonna talk about how you can do that in my next video. I'm gonna talk about how you can delete rows, if you delete a column or update your database then how you can update your models in your dotnet project. So yeah, stay tuned. Thanks for watching. See you soon.
