DbContext and Migrations

[00:00:00]
>> Spencer Schneidenbach: In our project, let's set up our DBContext and DBSets. So as we mentioned, we're gonna use SQLite. So I'm not gonna talk a lot about databases necessarily, but SQLite is basically a single file DB. It is super cool. It is actually extremely mature, but not something that you would use in a production system for a SaaS product or anything like that.

[00:00:22]
But for a single purpose, like an application needs to run, it needs to track some data, and you wanna do it in a structured way, SQLite's a good tool to kinda reach for in order to do that. So let's go here. We're going to close this folder. And we are going to open the next part of our exercises, which is, let's see, we are on 4, 3.

[00:00:51]
So setting up. Let's see, querying-sub-resources. So we're gonna sing, setting up your first. Okay, perfect. You can see that this is our current project as it sits. We don't have any DBSet in here yet, but we do have all of our benefits, and we still have our repository.

[00:01:10]
So let's go ahead and change that. So first thing we need to do is install SQLite. This will also install the SQLite package for Entity Framework Core. We'll also install the base library. So you just have to install the one. So we'll go ahead and do that. Hit Cmd+P, angle bracket, and then focus on NuGet.

[00:01:27]
And, there we go. Put that there, paste that in. You can see that this is the one you wanna install. There's the core one, but the one that you wanna install that has all the real goodies is the top one. We'll hit plus sign.
>> Spencer Schneidenbach: And we will see inside of our project file.

[00:01:48]
We will see that our EntityFrameworkCore.SQLite project or package got installed, which is exactly what we want. Okay, so let's create our DBContext and establish our DBSet. Now, lots of .NETers like to put these in separate folders like data for infrastructure. I'm gonna go ahead and just put it in the root of the project.

[00:02:09]
But as I like to say, you do you friend. I'm gonna do this. I'm gonna hit these. I'm just gonna go to the solution explorer. I'm gonna click the new file icon. I'm gonna say I want a new class. I'm gonna call it AppDBContext, hit Enter, and we got ourselves a class.

[00:02:26]
So first things first, we've got MD framework installed, so now we just have to reference it. And the way that we reference that is to inherit from DBContext. This is kinda the base requirement that you need in order to get all of the goodies that you need out of entity framework.

[00:02:39]
So I'm going to do command dot. I'm gonna use my using statement, and then I am going to prop, and then hit Tab to automatically create a property. For me, write it all out. I'm gonna say, DBSet of employee, and I'm gonna call it the employee's property. Hit Enter, Tab away.

[00:02:59]
And if we switch back here, you see that we've almost got it all. Last thing that we're gonna need is just a constructor. So I'm gonna copy and paste that in, and we'll talk about this here in a second. But essentially, this is the options class that we send into the base method for DBContext in order to actually configure it.

[00:03:23]
This is where we set it up to actually use SQLite. We'll get to that in a second. So, inherits from DBContext, as we already mentioned. It has the constructor that takes in a DBContext options, which we'll use to configure it to use SQLite, but we were not gonna do it here.

[00:03:39]
We'll do it in another part of the application. And it has a DBSet employee property. That is, the collection of employee entities that are mapped to the employee's table in the database that we are going to create. So pretty good so far. So let's set up our DBContext in our dependency ejection pipeline.

[00:03:57]
So we're gonna go to our builder.services.AddDBContext. We're gonna give it a generic type because it is a method that takes in a generic type, and it wants the type of the context that we're adding. So we're gonna say, AppDBContext. We're gonna sit here, and then we're gonna see that this is a method.

[00:04:18]
We could just do that, but we're not really giving it enough useful information in order to know that you're targeting a SQLite database. So let's go ahead and configure that. So we're gonna give it a lambda. Somebody yesterday asked about callbacks, like they have in JavaScript, or passing functions to other functions in order to use them.

[00:04:37]
Happens all the time, and this is another example of it. So options goes into, and then we will say, because we've installed our SQLite package, we'll say options.UseSqlite. And then we're just gonna give it the connection string, which, in this case, is just gonna be the name of a database.

[00:04:56]
And we're just gonna use employees.db. And that's gonna create a file for us here in a second with our database. Okay, so good so far. All right, so next part of the project is that now we've defined our data context, our DBContext, we've defined where we want it to go, which we want it to go to SQLite, and use employees.db.

[00:05:23]
But now we actually have to instruct and create the tables for us cuz the Entity Framework isn't gonna do that for us. So we have to create what's called a migration, and here's how you do it. First, we're gonna add the Microsoft Entity Framework Core tools, which will give us some additional add-ins inside of our .NET CLI.

[00:05:44]
We'll go ahead and get the .NET EF CLI tools, and we're gonna install them globally. So I'm gonna go back to my command line. I'm going to clear it up and then add them here. All right, so now we should be able to say, dotnet ef, and see the unicorn of awesomeness.

[00:06:03]
And now we're gonna say, when we go ls and we see TheEmployeeAPI, we're gonna be able to say dotnet ef database update. You're gonna see that it's actually gonna build the project. So it's gonna gather that metadata from the dependency injection pipeline that we've created in ASP NET Core.

[00:06:21]
Check this out, look at all the goodies it did. It went through, and it actually created our database. And you'll see here that we actually have a database file now called employees.db. It wasn't there before. Let's delete it and do it again, just for fun, if you missed it.

[00:06:41]
So watch, at the end of this command, you'll see it appear. And there it is. So we've created a database file in SQLite. I will point out one thing, which is that the only thing that it will create at this point is an EF migrations history table. This is where it keeps track of what migrations have run.

[00:07:00]
What the heck's a migration? Let's talk about migrations. A migration is a file that is used to alter the database based on the structure of the DBContext, as you haven't defined. So let's go through a few examples of that. So let's say, dotnet ef migrations add init. We'll go ahead and add a migration called init.

[00:07:22]
You will see a folder pop up over here called migrations. And you're gonna see that your initial migration, our init migration, is created here. And you're gonna see that because we've defined our DBSet as employee right here, that first thing it's gonna do is create an employee's table.

[00:07:43]
However, it's also done something else for us, which is, it's automatically looked and saw that on our employee, right on our employee file here, right there, you'll see that it also detected this list of benefits. So it automatically knew that, okay, this is an employee with a list of employee benefits, that one-to-many relationship, which is important and meaningful to databases.

[00:08:12]
So Entity Framework automatically went in and detected that and said, yes, not only are we going to create employee benefits table, but we're also going to add what's called a foreign key. And it basically says that the employee ID that's in this employee benefits table needs to match the employee ID in the other table.

[00:08:31]
And it won't allow that record to be created without a valid employee ID. So this really gets deep into SQL, which I can't cover in this course, that could be an entire, it's five courses on its own. But just know that under the hood, what Entity Framework is doing is it's looking through your entities and deciding from there, what tables need to be created based on the structure of the entity as it exists?

[00:08:55]
So, very neat. One thing I will say about migrations, I treat them with a lot of respect. And what I mean by that is that I will make changes to a data model, I will look at a migration, and I will scan that file like a hawk. And that's because you will occasionally, and I do, make a mistake where you've altered a data structure in a way that you didn't intend to make some kind of Entity Framework change.

[00:09:20]
But Entity Framework has happily said, well, I think you meant this, and that gives you the opportunity. Reviewing the migration really gives you the opportunity to review for mistakes. So I highly recommend you always look at your migration files before you run them. This one looks pretty standard, and this is not a production system, of course.

[00:09:38]
So a couple of other parts of the system. We have two, a migration that is created by our tooling. Automatically, I didn't write a line of code, all I did was run a command. It inherits from migration and it has two methods. It has an up method and a down method.

[00:09:56]
Up means upgrade, and down means downgrade, in case you need to back out or undo those changes. And you can see our down method here simply drops the tables that are being created. So we talked about navigation properties, which is this, but I don't think I named it.

[00:10:13]
So this benefit here is what's called a navigation property. And what that means is is that this, in EF Core parlance, a navigation property simply means that they can use this to navigate down to the employee benefits table. Correspondingly, you don't have to have a navigation property be a collection.

[00:10:32]
A navigation property could also be the entity of which it's pointing to. So for instance, if I add an employee property here, this nav property can be used to refer back to the employee. And we know, based on our data structure, that an employee benefit in the database cannot exist without an employee.

[00:10:56]
Now, can it in C Sharp? Of course it can, but we're modeling our DBContext. We're modeling the entities in our DBContext as if they're real database tables. But you notice the squiggly lines. You can't help but see them, right? Check that out. So it says, non-nullable property must contain a non-null value.

[00:11:14]
Well, you could fix that by giving a question mark here. But that doesn't really fix the problem because that's gonna instruct Entity Framework Core to say, this must be nullable. Is this nullable? And actually, you have the ID property is not nullable. So why is this nullable? My guess is that I'm not exactly sure what the behavior would be.

[00:11:31]
It might actually confuse it to be framework. Well, then you could call required here, and get rid of your compiler error. It's kind of a little bit weird as well, because when you're actually constructing this employee benefits object, you may not have the full employee object there. You may just have the employee ID.

[00:11:50]
So you wanna be able to instantiate it with just the employee ID, which DBContext and Entity Framework will happily try to insert that object as is. So the best way, I guess the lesser of all the evils is to set this equal to null with an exclamation point.

[00:12:08]
Now this is really odd. It's a quirk. It is an odd quirk and I can't say that I love it. But I think of all of the things that I have to do, it's the one that I dislike the least. It's basically saying that I am setting it to a value, and I am asserting, I, the developer, I'm asserting that this thing is not null.

[00:12:27]
Well, you can see that that is obviously inherently ridiculous because we are saying that null is not null, but it does get rid of the compiler error. And if you didn't have warnings as errors is on, it wouldn't even show up at all. They would just show a squiggly and to say, hey, this doesn't look right to me.

[00:12:43]
I prefer warnings as errors when I can, so I'll just do this to get rid of it. All right, soapbox over. Okay, so we added our employee navigation property to our employee benefits. So if we're querying this table, we could then join and get some properties from this table, if we wanted to.

[00:13:02]
And I wanna point out that I didn't make any technical changes, I didn't make any technical changes per se in a way that's significant to Entity Framework. So I should be able to add a migration file that just has blankness. So I'm gonna do that real quick. I do this a lot in practice because I do want to assert that I have not made a change to Entity Framework in a way that I, as a developer, didn't notice, but Entity Framework will happily notice for me.

[00:13:28]
So I'll do Ctrl+Shift this. I will create a new migration, so dotnet ef migrations add meow. I don't expect this thing to live for long. We'll see into the employee API, and we add our meowgration. Got it added, we see it here. We see that it's blank, which is exactly what we want.

[00:13:53]
And actually, Microsoft doesn't even like it. It says, hey, this violates naming rules. Well, that's okay, we're just gonna remove it anyways. You see this little command, it says, to undo this action, use ef migrations remove, ef migrations remove. Dotnet ef migrations remove. Dotnet build to see the error.

[00:14:14]
It's probably complaining about this. It's complaining about this. So this thing that I've created doesn't even compile. So I can just capitalize that and then rerun this. Or I could straight up, well, it's not even letting me do that, which is okay. We're gonna go ahead and just delete this migration, which is perfectly valid.

[00:14:31]
I will say that if there are things in here that this snapshot file may get updated. And so you may not want to just delete it directly. But if you don't have warnings as errors on, it probably won't matter anyways. So we've removed the migration file. It's all good, it's gone.

[00:14:50]
And.
>> Spencer Schneidenbach: Everything should build as expected, which is what we want. Very good, okay. So, what have we done? We connected, we created a DBContext. We connected it to SQLite, we connected it to our ASP.Net Core project. And we created a migration file to actually create our database and add the tables that we would expect to be there.

[00:15:23]
We're gonna do one more thing, two more things. We're gonna say dotnet ef database update. And what that's going to do is it's gonna read our connection string, and it's going to go ahead and run all of our active migrations. So you can see that we have our Create Table Employee, Create Table Employee Benefits, and we have everything there.

[00:15:43]
So I mentioned early on at the beginning of the course that you can install this thing called SQL tools. And that was the most convenient thing that I could find that worked with Visual Studio Code. So we are going to install it. I installed the SQLite provider. So what I'm gonna do is I'm just gonna call it TheEmployeeAPI.

[00:16:00]
What I'm gonna do is add a connection. I really just wanna show you that those tables there are there inside of this file. So I'm gonna hit SELECT FILE. I'm gonna go here. I'm gonna double-click and go on to my employees.db, boom, boom. I'm gonna say TEST CONNECTION.

[00:16:17]
Let's see. I got a, VS Code engine is not supported, you should enable. Use node runtime, and then you must reload VS Code to take effect. So we'll go ahead and do that. What's the general rule of thumb when it comes to deleting migration files? I know sometimes when you're seeding data and doing migrations, or might be a dependency with primary, so I'm just kinda curious what you might have seen.

[00:16:50]
I will cover that a little bit more, that's an excellent question. And the reason that question is relevant is because if you're moving from one database table to another, for instance, maybe you've expanded it, or maybe you've changed it all together. I think you're talking about a migration where you're literally having to move data over and make sure that the system doesn't break.

[00:17:08]
Am I correct? Yeah, that has to be handled on a case by case basis. We're actually gonna kinda talk about that a little bit later because our employee benefits table, it's gonna get a little upheaval. But that is an amazing question. The best answer is, is that it has to be done on a case by case basis, yeah.

[00:17:28]
But typically, and it's gonna require meetings with people, lots of eyes to make sure that everybody's on the same page. Because when you make a database change in production, it's sometimes difficult, or sometimes straight impossible to go back. And if you cause problems, then you have to stop and fix them.

[00:17:46]
Then your Friday nights are ruined, unless you don't deploy on Friday, which might be a good rule.