 On today's Visual Studio Toolbox, Phil is going to show us how you can build your model in code and then apply that to a database using migrations. Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green, and joining me today is Filcher Pixie. Hey, Phil. Hey, Robert. How are you? I'm good about yourself. Very good. We are continuing our series on NAD Framework Core. This is, I believe, our fourth part in our in-depth series, and today we're going to talk about. We're going to talk about building your model code first, truly code first, and migration. There's two ways you might go about it. You might have a database and then you can create a model based off the existing database. We saw that in the intro series. Or if you don't have the database yet, you can just write classes and have any framework create the database for you, which is very cool. You can also do a blended approach. You have an existing database, you want to switch over to a true code first model. We talked about how code first is really a terrible name. You can do the initial scaffolding to get your existing database moved over, and then you can switch over to doing a code first. The reason I like doing code first is we get more of an iterative design with our database. I'm a certified DB as well. I remember building databases and you pretty much put in everything you think you're going to need into the tables and then hand it off to the developers. Well, if you're building your classes first, as you need them and adding the fields and the tables and as you need them, you get that emergent architecture, the iterative design, and then you might end up with 20, 30 migrations initially, and then what we do is we just squash them. We will remove the migrations, we've got our classes and we build a bigger migration. Okay. Got it. If you've been doing databases for a while, you might start by creating the database, like SQL Server Management Studio. If you're used to that, that might be the way you start, but if you haven't been doing SQL DBA stuff for a while, it's just way easier to write your classes. Yeah, it is. The one caveat to that is sometimes when you've got some interesting foreign key situations, even as much as I work with the EF Core and I work with it every day, I will sometimes just open up SQL Server Management Studio, create the relationships away, make sure I get the Cascade set right, I'll scaffold that into a temp area, and then just clip or inherit the code. Okay. Cool. Let's see how it all works. Yeah. Let's jump over to my code here. I've got several small projects. Let me make sure I share the right screen. Continuing on where we left off, I've got this project here called Models and Migrations. Let's just talk through the models first of all. One thing that we want to do is, well, two things. We want to make sure that the people following us understand what our code is doing, and we want to build a clean design. There's a bunch of conventions in EF Core. If I have a field or property called ID, or the name of the class ID, for example, if it's customer class and it's customer ID, and it's an int or a grid, EF Core assumes you want that to be the primary key. Well, the problem with assumptions is not everybody's going to know them, right? You always want to imagine the next person looking at your code as an Axe-Wielding psychopath, and we don't want to make Jason angry. So I'm always very, very specific and explicit. So here I am telling Entity Framework Core through these data annotations that I want the ID to be the primary key, and I want it to be an identity. So you're saying that you don't need that Entity Framework, we'll treat that as the primary key and market the primary key and create the index on it automatically? Yes, based on the name. I did not know that. Yeah. Just likewise, if you have a nullable property in Visual Studio or C-Sharp, it will be nullable in the database. If it's a non-nullable property, it will not be nullable in the database. Another key field that I always use in my objects and this will play into a later show is use a SQL Server timestamp. The timestamp is a special SQL Server data type that is a number. It's like clock ticks since January 1, 1970 or something like that. I don't know what the algorithm is, but it's managed by SQL Server. So anytime a record gets updated or added, the timestamp property is updated. So it's totally controlled by SQL Server, plays a key role in concurrency, which we'll talk about later. So I put these two in a base entity. So any framework totally understands class hierarchy. So let's look at our car class. We inherit from base entity, and this then now has the ID as a primary key and a timestamp. Again, some of these are not required to put on here. So for example, I have the required attribute. Well, it's going to be non-nullable anyway. Because it's a non-nullable int. But I put it on for two reasons. One, again, to be explicit, but also for ACP on that core, that comes into the validation scheme. So let's talk about navigation properties. So in our database design, let's look at it real quick. This is the resulting database. So we're looking at a car, which is actually named Inventory in a database. The only reason I changed the name was just to show you that you can. It has a foreign key out to makes. Make has a name, it's got an ID as well. The way we do foreign keys on the one side is, well, this is actually the many side, but we just have a property of the type we want to join to. So in this case, make. A convention that I use and I make my team use is, I'll call that make navigation, merely because if we have a property name, the same as a class name, until a sense gets all confused. So it's not required that you change the name, it's just the best practice. Okay. So I'm decorating it with another attribute saying, hey, the foreign key for this relation is called make ID. Now, again, there's a convention in EF Core that if I have a make property that's a navigation property and there is a field on my class called make ID, it'll assume that is the foreign key. Again, I'm staying away from the assumptions. So the other end, so I have in my make class, a make can have multiple cars, we have an innumerable of cars, and again, this is just helping shape the database and be very clear how we want things to work. The inverse property, in other words, the one side of the one to many is the car.make navigation. Again, this is a little overkill, we don't need all these things in here, but we're doing that again to be very, very specific. We also shape the database with a host of other annotations. For example, I'm setting string length to be 50. If I want to change it to a var char instead of an n var char, I can add in the attributes for the data type. So anything that you want to do to shape your data we can do through attributes. Well, most of it's through attributes. Some of it we have to do through the Fluent API, which I'll also show you shortly. I think one of the nice things about using something like inverse property there, even if it's not needed, is it's assigned to other people that the relationship is taken care of. So nobody has to worry or be confused as to whether or not the relationship was set up in the code. You put that there, which then is the sign that you've done it. Correct. And we always do bi-directional navigation, so it's a w-link list. So here from a make, I can get to all the cars that are related. And from car, I can get to its make. Right. All right, so let's look at some other things. There's a not mapped, and this is something that we want to use as a C-sharp property that does not go into the database. So this is a convenience method. I'm combining make and color together, but I don't want this to be in a database. So we just simply say not mapped, EF-core ignores it. Right, and then if you rebuilt this from the database that you would lose that, right? You would lose that, correct. But what you do in the scaffolding does this for you, when you scaffold out your database, it creates everything as a partial class. Okay. So you would just set up a partial class with your things in it. Like I would lose my override of two string as well. Yes. If I didn't have it in a partial. Right. Okay, so that's the basics of shaping your database. There's also this table attribute where you can say, I want the name to be inventory as opposed to car, and you can assign a schema. Now I normally don't put things in DBL, but again, this is sample code. Right. There's a column attribute as well, where I can change the name of the column, right here, name, let's ignore the order. And I can put it, but I can also put a type name in here. So instead of an N, Varchar, 30, well, it's an int. I could make it a tiny int or I can make it some other type so I can change the name. So you can shape it however you want. Okay. This is convenient if you have some, and I'm going to call them archaic, database naming rules, right? We used to have to do 8.3 names and databases and it made it for really ugly C sharp code. Right. Right, so another thing I want to talk about is owned entities. So if we look at, actually I'm going to do this first, if we look at our customer, customer has their first name and last name. I also have a credit risk table and in there I have a customer ID, but I also am storing their name first and last. Well, that's repeated code. So anytime you've got a block of things repeated, we should look at how we can encapsulate it. So I created this person class and I decorate it as an owned class. So this is going to be owned by another entity. It's not an inheritance chain. It's I owned this thing. And one could argue whether or not to put it in the base folder, but that's neither here nor there. So now let's go back to our customer. We have a person, personal information. Again, I don't like the person, person, you know, naming the property the same as the class. We actually, it is a property, it is a class, right, it's a C sharp class. So we've treated it as such. Now, there's a couple of things, if we don't do anything beyond this, EF Core totally understands how to handle it. And what will happen is we look at the credit risk table here. It actually names the fields, the property name underscore, sorry, property name on the credit risk class underscore property name within the owned property. And I'm not a real fan of that. If you look at the customers table here, I'm actually just, it looks like first name and last name, right? It looks like a normal table. So this is where the Fluent API comes in. And in our DB context, we have this on model creating. And we looked at a little bit, we looked at this a little bit when we were talking about the scaffolding, we did the venture works and we had thousands of lines of code in here. So we're gonna be very, very simple here. We've got our credit risk column. And this is also how we determine or set up cascade delete, right? What the cascade behavior is. So right now I'm just setting up that there is a foreign key, I'm naming the constraint, and this is actually optional because in my classes, I've already done the foreign key and inverse property. So this is a little overkill, but what I like to do is I like to keep the relationships very clear and very close to the code. So I don't have to go into the DB context to figure out what your relationships are. Unfortunately, we can't change cascade rules through the attributes we have to use the Fluent API. So here we have on the make table, make has cars, right? Has many cars. And then you have the inverse property here, what the foreign key is on that. And then here's the cascade behavior. I'm not gonna let you delete a make if it has cars related to it. The other behavior we can say is here's an order. I can delete the order. And on the client side, I don't want to delete the car just cause somebody canceled their order. I'm just going to set that value to null. Let's go back to the owned property. On the customer, it came across as first name and last name as opposed to person information underscore first name, last name. So very simply, we go into the customer entity and when we want to shape it, we say this customer owns one personal information and then now we can go into each individual property and say, what do we want the column name to be? We can't add the column attribute in the owned entity. We have to do it this way because it's an owned entity. So we're just setting the column name to first name and last name. And I used a name of function so I don't worry about magic strings, right? It'll always match the name in the class. So any questions about building and shaping your model? Question about the owned, why are you duplicating the name and the credit risk when you have the customer ID? Because it's a sample from my book. Okay, so- It's not meant to be a clean database design. When might you do that in real life? So let's say that you've got, when might you do it like, anytime you have a repeated chunk of code, let's say address, for example, you don't want to have a separate address table, right? Anytime you have repeated code, right? This is, okay, let's create a story around this because this is the story in the book. We are relating the credit risk to the customer table, but they might have changed their name because they're a credit risk. So we're also storing the name of the credit risk. Because maybe they have an alias. So there we go, there's our story around it. All right, got it. And honestly, I just wanted to show owned and those were repeated columns. So I just wanted to do that. So anytime you have- But when I look at something that I'm thinking, yes, it's often a pain in the butt to have to look that up over and over again. It's convenient to have it also in that table, but that's not always a good reason to do something just because you're lazy. Well, it's a good reason to do something if it allows you to be lazy down the road. This is arguably not a good design. I wouldn't do that, but I wanted to show owned and I was, okay, here's repeated data, right? Cause I could have shown owned on one column or one table, but then I couldn't show you the difference on whether or not you changed the property. But then if the person, the same person changes their name, right? Changes their first or last name. Now you have to change it in multiple places. Well, not necessarily, right? If you want to keep track of your aliases, right? So what was the name at the time that they had a credit risk? Right. So yeah. Okay. All right. So yeah, as we always do. All right, one more thing I want to talk about and we touched on this, but here I have a custom order view model and the properties on this combine first name, last name, color, pet name, and make. So pretty much it's pooling from orders, getting the customer information, get the car information, plus the make name. So it's just a view model. And what I'm doing here is I'm configuring this as a DB set. And this was a change from 2.x. In 2.x we had DB queries. This is a DB set, but it's actually not. And we tell it that it has no key and it should get its data from this view on this schema. And if it's DBL, you can leave schema off. So now I can treat it like anything else. I can't do updates. I can't do inserts, things like that, but I can just say context.customorderviewmodels. Where customer name like Phil and it's gonna get the records back. I don't have to do the from SQL like we did on a prior show. And again, when we say view model here, we're not literally meaning the same view model you use in MVVM. This is a query result. So I'm gonna disagree with your statement. It is the same view model as an MVVM. What a view model is an MVVM is other models squished together for the convenience of a view, right? So this is a custom order view model is other models squished together for the convenience of displaying that data. A view model in my mind, my definition of view model is you take other models and you squish them together into one thing. Okay, granted. But when I think of a view model, I think of the code I use with a view which is my user interface that handles the data, the link between the view and the model, right? So typically one view model per view, often talking to one table, but not necessarily. And here, so at the high level, yes, it's the same, but at another level, it's not because this is the ability to create a query that returns either a subset of the table or data from multiple tables. So it's really related more to a query, no? We'll disagree to disagree. And it's not a show about MVVM. So let's get, if we can get back to EF Core, that would be great. I'm just trying to clarify what we mean by view model. So let's go. That's fine, view model. So I'll just say my definition of view model is data squished together from other models. Okay. So it's, I guess it doesn't have to be a squished. You're right, it could be a subset, but I very rarely will make a view model just for a subset as we'll display those fields. All right, so now we gotta figure out how do we get the database updated? And one thing that I always do in my projects is that I always have a migrations.text file. And here's all the migrations that we have run. It just makes it easy to roll back to prior versions. If you look in the EF migration history table, here's all the migrations that have been run on my little database. There's the product version, name of the migration and the order that they go in. When we look at our migrations folder, these match up. Now, there's a couple of things I wanna talk about before we get into how we create migrations. First thing is if you ever go in here and start colluding files, I will slap your hand. It will mess you up beyond belief because each of these migrations has this initial designer underneath it. And this is the shape of the model at the end of this migration. So this is going from zero to my very first migration and it's all C-sharp code, but it has the state of the model. So let's go down a few and here is the state after the adding the owned table or the owned property. If I delete this, right, it also deletes the state of this. So when we move migrations up and down, there's an up method in a migration and this is applying the changes from the prior migration. There's a down method which reverts those changes from the prior migration, but we can't revert a migration if it's been applied to the database. There's a safety check, right? If I try and revert, sorry, if I try and remove a migration, not revert, I can always revert. But if I try and remove a migration and it's been applied, E of core comes back and says, nope, you can't do that. It's been applied. So then I can apply, for example, this migration and it will roll back all of these other migrations to the state that is shown here. Okay, so if I start deleting things, it doesn't know how to roll back and things get really ugly. Right. There's also this application DB context model snapshot, which is updated every time you run or remove a migration. And this is the final version of your model as defined in C sharp. So I just wanna get it out there that's, and it's quite often like when I'm doing workshops, people go, well, I just deleted it because I did it wrong. I'm like, no, now you gotta start with it, start over. First thing I wanna talk about with all of the EF stuff is that the .NET CLI tool is not installed by default anymore. So you have to run this .NET tool install global .NET EF and that allows you to run the .NET tools. I think we talked about this in the first episode of the last series, but I just wanna be very clear. There's a scaffold which we're not using. And then so the syntax is very simple .NET EF migrations. I'm adding a migration. I apply a name, and by convention, I always call the first one initial. I want the migrations themselves go into this folder path. And then the context that I'm using is this. Now, I only have one context in this project that would automatically find it. But just to be clear, I always put the context in there. Then when I want to run that migration against a database, I actually have to apply it by calling database update. I can just put the name, the context is overkill, but I think you know me by now that I'm all for overkill for clarity. There's also, you can drop to database and you can revert all migration. So if I do .NET EF database update zero, everything goes away. Doesn't kill the database, it just removes all my migrations from the database. They're still here in code. If I want to remove a migration, this is very different from EF six, and EF six, you can just delete stuff willy nilly, it didn't matter. You have to unapply it and then use remove to start rolling back. So if I wanted to remove this migration, I would apply this migration. So this is going to update the database to this migration. So now I could go in and call remove and it will delete this migration from here. It's already been removed from the table. Let me update today's back to where it needs to be. .NET EF database update. And if you just do update, sorry I was on the wrong screen, then it'll just update to the latest migration. You don't have to put the name migration in there. So that's how simple the migration process is. There's one more thing that I want to show you before we jump off is dealing with SQL Server-specific things. So I have a store procedure in my database. And what I always want is to have a home or Simpson button so that anybody joining the team or wanting to work on the project can download all the code from Git and then just hit F5 and everything automatically appears. I don't want to have a support document, a bunch of SQL scripts and say, run this, run this, run this. So what I do is I create a migration that starts off empty and then I run SQL code and I just put this here in a helper so I don't delete it if I roll back. And here's my view, right? Oh, it says create sprocket. Should we say create view? There we go. And so my up method will call the create view. My down method in the migration will call drop view. And so all of my SQL Server things, whether they're views, store procedures, user design functions, I use migrations to build all of them because then it's part of the entire process. Two more things to talk about. Again, real quick, if I want to run all the migrations through code, I just call context.database.migrate and it'll run all the migrations. If I want to delete the database through code, I just say context.database.ensuredeleted. If it's already been deleted, it does nothing, doesn't throw an error. If it exists, they'll just delete it. So this code here drops and recreates the database. Okay. So in tests, I will do this. I always run dropping recreate and then I initialize the data by adding a bunch of records so I have test data to work with. So any questions on building out your model? Nope. The things that I didn't talk about a one-to-one relationship, we do the same thing that we did here, right, where we, instead of with many, if you're doing a one-to-one, you have to do this construct in here to tell it which one is the parent. E, of course, SQL Server needs to know which one's the parent. Typically, you'll have a one-to-one relationship. For example, if you have a text field that you don't want that page allocation in SQL Server with every single record, the only record that has data. So you can do a one-to-one that way. The other thing that you can't do through data annotations anymore, but you can very easily do through the full-in API, is here I created a unique index on the order table based on customer ID and car ID. So the same customer can't order the same car twice. Right. Okay. And the unique, this is in the order of the complex variable. Or complex type. Any questions on? No. It's good. Good overview. All right, so that was migrations and building out the model. Very good stuff. And of course, we will have a link to this code in the show notes so people can play around with this themselves. Good stuff. Thanks so much. Yep, thank you. All right, hope you guys enjoyed this and we will see you next time on Visual Studio Toolbox. See you soon.