Deep Dive With Entity Framework Core 5

1. Introduction: Welcome to this new course Entity Framework Core, a full Tour. My name is Trevor Williams, and I am a software engineer and lecturer with over ten years experience in developing applications, as well as teaching people how to do this. In this course, you're going to learn about Entity Framework Core, and this is Microsoft's flagship Data Access framework or ORM for dot t. Now in this course, we're going to go through a few things that will set you above and beyond your peers and your colleagues in terms of how you interact with your databases while building.net applications. We're going to explore entity framework and how it helps you to build applications faster in.net, as well as how it helps you to manage database creation and changes using simply C sharp syntax. That's right. Using entity Framework Core. You don't necessarily have to go into SQL or going to the management studio to manipulate your database, and by the end of this course, you're going to have a good understanding of how all of this is managed through migrations, the database context, and database models. You're going to understand how to write link queries to interact with your database objects and tables, as well as explore many other advanced features of entity Framework core. Now, by the end of this course, you're supposed to be comfortable with entity framework and how it allows you to write link queries, and interact with your data. A little history on entity framework. It has been in development since 2008 in DottFramework, but it has evolved and evolved and evolved and right now we're at D five or Dt COR five. And so the library, which is currently in use in a stable state is EF Core five, which is what we will be exploring. However, soon there will be EFC six, and most of what we'll be learning here will be transferable knowledge. Now, a little bit more about what EF COR is. It's an object relational mapper. I used the acronym ORM earlier. That's what it stands for. Object relational mapper. So it's an open source library with APIs that make it easy for you to interact with the database. While we'll be using SQL server in this course, a lot of FCRs capabilities can be extended to other databases such as post resca and cuaide and my ca. So there are a lot of benefits to come from learning C because it increases your productivity. It helps you to reduce repeating code, and it allows you to focus on other things in your application while keeping the code based nice and consistent throughout. It is also enterprise ready. That's right. If you're building a big application and you're worried about having too many queries and having mesic code, FCR is perfect for you because it's nice, it's compact. It allows you to keep the syntax in C sharp, and it is stable enough to work in extreme conditions. I know you're looking at this course and wondering, is this course really for me? Well, if you want to learn about FCR, you're at the right place. If you know some C sharp and you have some database knowledge, you are at the right place. If you want to learn modern ways of interacting with your database using technology, then you're definitely in the right place. At the end of this course, you'll have no weaknesses and there are no threats. You can only get better and better. So what are you waiting for? I'm happy to have you in this course, and I'll see you in the next lecture. 2. Setup Development Environment: Hey, guys, welcome back. So in this lesson, I'm just going to walk you through what you need to set up your environment for this course. So I'm already here on the Visual Studio website, you can get there by saying sutudo.microsoft.com. And what we're going to be using is the Visual Studio IDE community edition. No, this is the flagship IDE given to us by Microsoft for.net development. So it is perfect for this course. However, if you're not able to use that community edition, on a Windows machine and you're using a MAC, then they have the MAC version. If you're not using a MAC, then you can use Visual Studio code, which is also open source completely free and available across every one of the operating system. Now, things I will be doing in this course will differ if you're using Visual Studio code, but I'll do my best to make notes along the way to try and make sure that you are able to carry out the operations. Now, after downloading Visual Studio, you'll be given well, at least downloading the installer, after launching this installer, you'll be given a screen similar to this. Now, I already have it on my machine, and what you're seeing are the different workloads that I have already installed. You don't need this many for this particular course. If you already have the ones that I'm about to show you, then you can go ahead and skip this step. But if you are here, and you need to know what to get. You need this.net cross platform development workload, which gives us the actual you know, the basic libraries for.net core development. And we'll be doing a little web activity. So it would be a good idea to just get the AP net and web development workload also. So at the bare minimum, you need those two. The more you click, of course, the more that will be installed, so you don't have to click as many as you see me having ticked here. So AP net and web development as well as.net cross platform development. So you can go ahead and install those Now, you need to complete this step, especially if you're not using the Visual Studio and a Windows machine, because if you're going to be using Visual Studio code, then you need to install the SDK in the background. That comes with Visual Studio, right? But then if you're not using Visual Studio once again, you can go ahead and download that SDK, so you can just get here by going to Microsoft co. And from that landing page, you'll be able to navigate the.t5 downloads page where you're going to be getting that SDK. You can also go ahead and get the run times because we need them for hosting, but at the very least you need that SDK. Okay. Now, after everything has been installed and set up, you just want to make sure that everything is installed. So you can go to your command prompt, whichever OS are on, and then you can just type.net H. All right? So when you do that, you will bring up if you're seeing a printo to the screen looking something like this, then you're on the right path. So it will tell you the SDK version that you have, and it will just show you all of the different commands that you can use. So you can actually use these commands when creating a new.net core application, and especially once again, if you're using Visual Studio code, then you're definitely going to be using the.net commands a bit more. All right. So that's really it for setting up the environment. This is a five minute video, but it will take probably a few hours or a few more minutes rather than this if you are setting up from scratch, but it won't take too long. So as soon as you're done and you can verify that you have everything installed, then you can move on to the next lesson. Okay. 3. Setting up the Solution: Hey, guys, welcome back in this lesson, we're going to start setting up our solution for the project or for this course. And what we're going to do is start off with a blank solution. So I'm going to go ahead and click Create New Project, and I'm just going to search for the blank solution template. And that is the one that we're going to select. So go ahead, hit next. And I'm going to call the solution named entity framework net five, right, indicative of the course that we're doing. And have my location set I'll go ahead and click create. And just like that, we have Visual Studio with our blank solution file. What we're going to do is start off with adding a new project to the solution. So I'm going to add new project, and then I'm going to be adding a console application because we're going to be using a console app to go through the fundamentals of entity framework core. Right? Before we get into all of the excitement of web and this and that, I want us to understand what FCR can do, because if we understand it from a console app, then the possibilities are limitless when we have to build more complicated applications. So we're going to go ahead, click Console Application, hit Next, and we're going to call this one entity framework net five console app. So go ahead, hit next again. We're targeting t five as our target framework, and then go ahead and create. Okay. Right now that our console app is created. We're going to create two more projects, and these are really going to be class libraries. Now, in enterprise development, one of the fundamental principles is separation of concerns, meaning we have the application, which is going to probably be doing pulling the strings, talking to the user. But then we need other projects where we're going to be storing database related objects and database related configurations are separated. So that's why we're adding two more projects in the form of class library. So I have class library to the left here. You can always scroll, but you can do search if you need to and always remember that we're dealing with C sharp. All right? So for the class library, we're going to call this first one entity framework net five dot data. And then it's still the target framework is still t five, and then we hit Create. And last, but by no means least, we're going to go ahead and add one more class library, and this one is going to be dot domain. So the same naming convention that we have entity framework net five dot domain, right? And it is still a.t5 target framework, and then we go ahead and add. So that is really it for our solution setup. So once again, we're going to be operating with three projects, at least right now, three different projects. This one, the console app listing of it as the UI, you can see here it as an output type of executable. So it knows that it should execute or it should do something. That's so do core configuration or project files are set up because if you look at this one different from the class library, you'll see that this one only has the target framework. This one knows it's target framework, and the fact that it's an executable. That's only material difference between them two, right? So this one will represent where we are going to be writing out our practical code, but then we'll be setting up our domain and data objects inside here. Well, domain will have our data models for classes, and then data will have our data configurations and files. So when we get we'll start some fun and start modeling some of the tables that we will be using for this course. 4. Creating the Data Models with EF Core: You guys welcome back. So in this lesson, we're going to start modeling our database tables. Now, what I have on screen is a database diagram or ERD entity relationship diagram. It goes by a few names, and there are a few representations of it. But the fundamental concept is that it is a diagram showing the different entities or tables inside of a database and what the primary key column is, what the other fields are, and then depicting that there is what we call a foreign key relationship between two tables. Here, it is showing that we have teams and we have leagues. Those are two tables. The league has an ID, which we're going to make an auto incrementing integer and it has a name. And then a team also has an ID, also as a name, but then it has a league ID. This league ID is what we call a foreign key to the league. A team belongs to a league. That's the relationship. If you're familiar with database, then you already know what's going on. If you're not so familiar, then that's no problem. I hope you understand at least the concept behind this diagram. All right. What we want to do though is set up tables like this inside of EFC. We want to use FCO to model out a database that will generate these tables. So you have different conventions when it comes to developing on top of a database. So we can do it database first, meaning somebody went in designed the database and everything and then we start building the application afterwards, or you have code first, which even though it implies that the code comes first, what it really means is that we are going to write the code that is then turned into the database. That way, if we need to change something down the line, we actually just change the code, and then we can let that make incremental changes to set database. And EFC supports either one. That is the beauty of it. So for the majority of this course, we're going to be looking at code first and how we can make marginal changes or incremental changes as we go along modifying the code. But we will look at how to reverse engineer an existing database into an EFC set of files, right? So enough talk, let's get into that. So my data models or domain models are going to end up going in the domain project, right? So I'm going to get rid of this default class that we got with it, and I'm going to go ahead and add a new class, and I'm going to call this class league. Now, generally speaking, I always advise my students, whenever you're creating a database, try to start off with the ones with the fewest dependencies and then go on because you don't want to build a table that has two or three foreign keys relating to other tables and those tables don't exist yet. Since in this situation, League is a standalone table, meaning league is there. A league is going to be there at least right now without any teams, but the team depends on the league. I want league to exist first. Create that class, I'm going to make it public so that other projects can access it. And then inside of this, I'm going to have the two properties, ID and name. So we're going to say public int ID, public string name. You see here, especially if you're familiar with the data types in SQL, you'll see that we're not seeing Var char here. We're saying string. FCR will be able to translate the native C sharp of what data types. Into the equivalent SQL data types. We'll see that later on. That is one of the benefits. We can use our native C sharp inside of our FCO application, and the translation to the database will happen anonymous to us. Another important thing to point out is the naming conventions and the support that you get from FCO when you follow certain conventions. Generally speaking, an ID column is going to be called ID. Sometimes you might qualify and say league ID or the table name ID. Now, COR will identify either one of these conventions, and it will automatically infer that this is intended to be your primary key and auto incrementing QL identity column, right? So it'll automatically see that naming convention and tell SQL that this is the primary key column. So if you wear off the path of that general naming convention, then you're going to have to do additional configurations to get that to work. So to me, it's easier to just work with FCR than to force F cord on another pass. It's not the hardest thing to do, but why give yourself more work when it can be that easy. Just by saying ID, you will know that this is the primary key and fit up everything else for me. Now, I'm going to create the next table or the next class represent the next table, which is team. I'm just adding that to the domain project. Team is the name, and then team once again, public will also have ID and name. As we saw, but then it has a third column, which is the league ID. That represents a foreign key. Now, what we're going to do when we have a foreign key, we have to add two properties. One, being the table name or the name of the table that this is a foreign key to and ID. Once again, naming convention. Second would be a navigation property and most times you'll see them making this virtual. Let just add that. Public virtual league league being data type of the class that we just created with the same name. That's fine. But the combination of these will leave F to infer that this matching the table name and the word ID means that this is a foreign key to that table. All right. So if you've had to set up a foreign key in SQL server, you know that you have to go in and you have to choose the columns, and you have to do this, and you have to do that. And there are a few steps. This is all it takes. Follow the naming convention and have that navigation property. And FCO will just say, Okay, I know that this is a foreign key, and I know that I can automatically include or automatically present the related details to this record through this property. Later on, we'll see it at one to show too much information. But I'm just trying to show you that from the get go, once you follow these fundamental naming conventions, FCR is going to do a lot of the work for you. Okay. So that's actually it for now. We have created the classes that are supposed to represent the tables in the database. Of course, the columnames match the data types are conceptually the same as what we would expect them to be in the database. And later on when we come back, we're going to look at how we set up the DB context, the provider and the actual connection to the database because none of that exists right now. There's no database. We're just modeling it out and saying, Well, these represent what I want to store. So when we come back, we're going to see how we actually set up the link to the database. Okay. 5. Specifying the Data Provider and Connection String: Our guys were back and the objective of this lesson is to set up our data class so that it knows that it needs to connect to our database. All right. So our data class, we're going to have to add a few libraries to this to let it actually interact with that net framework. Sorry, with entity framework. Because right now, nobody knows anything. We're just talking about entity framework. It's named entity framework, but it doesn't know anything about entity framework. So let me just start off by deleting this default class, and I'm going to jump into the new gate packages and let us go to Bros and we're going to be searching for entity Framework core SQL server. So before I go ahead and install that at the jump, let me just explain. You have Microsoft entity Framework Core. This is the base library for everybody. I think every the variation has a dependency on this one. If you look at it, it's a modern object based mapper for.it supports link queries, change tracking, and it works with a number of databases, CQL server, Azure, CQight postres and others. What you realize is that from UGT, you can actually get different libraries to support the different databases. In our case, we're using Microsoft k server. But if I was to search for entity framework core and just look through the different variations we see our k server, we see in memory, we see CQight. You see Postgres, you're going to see MCL. I'm not going to go through all of them, but the point is that whatever database it is that you have under the hood, there is more than likely a library to support it and you can get that from New gt. All right. As I said, today we're only working with SQL server, so let us go ahead and install that one, so I'm just going to download it. And in doing so, it's going to show you all of the other dependencies that it has, right? But that's fine. We can just go ahead and click Okay, accept any license agreements and let you get installed it in our project. So now that it is installed in our project. If I look back in the CSP file, just by clicking the project file for the data for the data project. You'll see here that now we have that package reference installed. Alternatively, you could have also gone ahead and put that line similar to this. It could say node package reference, include the name of the library, the version that you're interested in, and then just to build and it will automatically go and get the dependencies for you. So it could have done that also, but let us proceed using newG. So I can close all of that off. And the next thing on the agenda would be to create what we call the DB context. So I want to create another class and then I'm going to call this class football league football league context or football league DB context. We tend to put that in, DB context just means that it is the context or the connection. Let's just say context equals connection. DB context means DB connection file, so to speak. And then this is just letting you know what database it is. It's not a naming convention per se. That's how I am doing it. I'm just explaining to you why I'm naming it like that. So I'm going to make this public public class DB context, and then every DB context is going to inherit from the default DB context coming from EF core. So you have different DB contexts, you have DB context, you have identity DB context if you want to user authentication. But for now, we'll just stick to the basic DB context, control and dot and then that will let me know that I need a using statement for entity framework. Core. Now that is satisfied. I can tell it about the tables. Remember, this is going to represent the connection to the database. So whatever is in the database needs to be represented here. We have the models that represent the tables in the database. Now we have the link to the database, we need to let this link know about the tables. So I want to say public DB set. And this DB set DB set just means that the set of rows or records are really a table in the database. You are modeled of team All right. And your name is teams. So you can kind of read it like that just say understand exactly what you're typing as you go along. No, I need to have a dependency. I have to add a reference to the domain project here. And so I'm just going to you could click that, but I've had problems with that before, so I'm just going to go ahead and do it manually click dependencies, add project reference, and then add the domain project, click, and then we can just go ahead and use our using statements. Then I'm just going to duplicate that line and do the same thing for a league the DB set for league, we're calling it leagues in the database. I have a DB set for team objects, we're calling that teams. This, once again, will generate or interact with a table called teams, and anything that is in the teams table will be serialized or converted to native team class that we have defined. The next thing I want to do is actually let it know about a database, and we need to do what we call a connection string for that. So connection strings generally, like in a web application, they would be passed on, so you have the connection string in the web application and it's passed down through configuration settings into the context. We don't have that luxury right now, so we'll just do it manually, so I'm going to write override. And then the thing is that the DB context, which is our EFC class comes with a number of functions that we can actually overwrite and do our own thing with. But the one I'm interested in is configuring. This means whenever you're configuring the DB context, what do you want me to do? I'm going to override it. I don't need to override anything on the base or interact with the base, but I am going to tell it that the options builder UCL server. You see that is courtesy of our dot server, FCR library, right? So UeQL server. And we're going to put in our connection string right here. Because this is a demo app in the early stages, we're doing all of this. But of course, I'm walking you through it so you understand why we're doing it this way. So a connection string comprises a few parts. We're going to say, data source is equal to, and then we would specify the server. So I'll be using local DB, which is a local server built into Visual Studio, and you can access local DB databases via the SQL server object explorer, which you can go to view, and there it is. All right. Local D B, backslash, backslash, and then we're going to say SQL local D B. Get that spelling right. Just take some time and get it just like how you see it here, even with the parentheses and everything, the double backslash and all of that. Next up, we have the initial catalog and this is basically the name of the database. What are we calling the database? I'm going to call it Football League underscore FCR. That is our connection string. All right. And with that, we have actually completed setting up the data provider and setting up the link to the database. Now, of course, this database does not yet exist, right? So I've set up a link to something that's not there pretty much. And I've said these tables need to exist in set database, which, like we just said doesn't exist. So I'm just going to look in the SGO server explorer real quickly just to show you that it really does not exist in there. I have a few test databases, and as you can see, it is not there. So when we come back, what we're going to be doing using FCO to generate this database so that we can start interacting with it. 6. Migrations and Database Creation: Hey, guys, welcome back. So in the last lesson we had set up our DV context, which, like I said, is our connection to the database. In this lesson, we're going to start looking at migrations, which are our instructions to the database. Our first migration will be to create the database because a migration will always say, what existed, what exists now? What is different? Let me give instructions to make those changes. At this point, there is nothing. So what is there now is nothing, and then the instructions will generate what should be there. Right? So in order to do our migrations, we need another tool from our new gate packages, and that library is literally tools, right? We need FC two. Go over to New Gate, I'm going to go ahead and hit tools and download that one and that automatically comes with design and some other libraries, but I'm just going to go ahead and let that install. After installing that, I'm going to have to set a project reference between the console and our data project because when we run a command to update the database, we have to have it run against the startup project or the main project, and the main project needs to know about the other projects with, you know, the connection and the DB context and so on. So I'm just going to go ahead and add a project reference to console up for both of these because we we'll need to be able to access the domain objects. We also need to be able to access the domain the DB context, right? So I'll just go ahead and add that And then know that all of that is done, we can run a migration. To run migrations using Visual Studio, we want to go to the package manager console. So you can go to tools, you get package manager and you see the package manager console. I have mine available to me already down here in this panel, and of course, you can move the panels all up. Let me just redo this one. Now, before we start the migrations, I just want us to take a look at the extent of the options available to us. In this package manager console, I can say get hip and help and then type in entity framework, and then give that a few seconds, and then it will generate a nice document showing us all of what is possible through the entity framework. It starts off with a unicorn and it tells us about the library, It gives us all the command lets and what they're used for. The same list that I just pointed out in GT, it's available here with documentation. So like I said, we'll be adding a migration, and then we'll have to update the database after every migration. I mentioned earlier that you have code first versus database first. So if you're doing code first, well, that's the add migration update database. If you're doing database first, then you would want to sca FO DB context, which basically says, I'm looking at the database and generating the class models based on what I see in the database. So you see that they have quite a few options available. You can script the DV context, Scripto the migrations. If you're still old fashioned to generate the migration, you want SQL script. You can do all of that using these tools. So let us go ahead. I'm just going to clear this CLS, clear that console, and then I'm going to know add a migration. So the default project, once again, needs to be the executing project in the solution. And I'm going to say add migration. And then I'm going to give it a name. So the name, typically, you want to use a sensible enough name that you or somebody else. Well, let's start with somebody else. Somebody else can come along and say, Okay, that is what that was a general idea of that migration. You also want to use a sensible name because after you go on vacation and come back, you want to look back at this migration and understand why it was done and what it was for. So don't name the migration X or just something silly, give it some meaning. So like there's nothing here. I have no migrations, no database. Yet. I want to call this one initial migration. I also like to camel case my names. When you use spaces, then you have to take extra precautions when you have to, you know, reuse the names and so on. So I just use camel casing. You can use underscores, whatever, but I just don't like spaces inside of these names. So I'm going to go ahead add that migration. It's going to build the project, and the build is successful, but I have an error. So let's read this error. And in this course, I'm not going to shy away from errors because I know that a lot of people hate the errors that they get in EFC. Sometimes they're not very clear, and it's hard to troubleshoot some of these errors. So I'm not going to shy away from errors. If anything, I'll deliberately conjure some errors so that we can go through them together. But this one is just it's simply saying that the startup project, which is the console app, doesn't reference the design project, right? So, in other words, I need to install a package in the console app. So an easy way to get that done. Of course, you can go to New gate, but I'm going to try and do it through the project file. I'm going to create a new item group and give it a package reference to include microsoft dot entity framework core design. And I'm just setting the version that I know I'm using through the rest project. This version may vary based on when you're doing the course. So if you're taking this path, then that's fine. If not, you can always go to New gate and add the entity framework design package like we've done for all the other packages. Next very important step is to make sure that we have our data project selected. I just did see a list to clear the noise and the errors in the section, but I just want to point out that we need the default project here to be whatever project you have the DB context in, for us, that's data. I'm going to go ahead select data, and then I'm going to just press up arch which brings me about the add migration initial migration command. Go ahead and try that again and this time, we have our migration created. Now, let us take a look at what we got here. We got our folder called migrations, and it has at least one file in there with the name of the migration that we gave it. Notice there's a timestamp on it. We also have this other one that is the context snapshot. So basically, at this point in time, this is what the database will look like, and we have our migration file. Let us take some time to appreciate exactly what is in a migration file. So typically in SQL, you would have written SQL statements, create table, create, well, sorry, create database, then create table. Then you put in all the fields and the constraints. If you were writing a script. If you were using the management studio, you would have been clicking and all that would have been generated in the background. Well, a migration file is pretty much going to be a representation of all of those actions, but with C sharp context and built on top of what we called the builder pattern, right? So if you just take a look at it, it looks complicated, but just sit back, take a deep breath, and look at it as a developer, you'll notice that everything it is saying to you makes sense, right? The migration builder, that's an object being passed in here, create table. Name off the table, leaks, right? And then it's automatically going to make sure that it creates. So even though in the DV context, I had suggested, sorry, I'm just trying to say a lot here. I had suggested initially that when we're creating the class models, we start with the ones with the least dependency. Because if we started with team and team would have a dependency on this navigational property for league and league didn't exist then, this would have been a little error. So it would have been prudent of us to create league. Then league is already there by the time we're creating things that need league. Okay. Now, in the DB context, I didn't necessarily follow that order. I just basically randomly listed them. So I didn't necessarily list them in the order that I wanted them to be created because obviously, I would have wanted leagues to be created before teams. However, if you look in the migration, it already went through and saw that leagues has no foreign keys, Teams does. So leagues has to be created before teams does. That's pretty much what FCR just did. So it's creating the league table. It's giving it the columns ID and name. And if you look closely once again, following name conventions, ID is automatically going to be an int, not nullable, and it is a CQL server identity auto incrementing. All of that was generated because we used the word or the name ID for this column. FOR inferred that this is what we wanted as our primary key. And then you'll see here where it's saying the column type is string. That's a C sharp type, but then in the database, we're making it NTR. All right? Just let that soak in a little. Then we go ahead and add the constraints that actually make this the primary key, which is I the ID column. Then it repeats those actions. Migration builder, create table, create teams, create the columns, once again, inferring what the primary key is, and then adding league ID as an int, But then it goes a step further with the constraints where it creates the primary key and it creates the foreign key, which is linking the column league ID to the principal table leagues and the principal column ID. So you see all of that is inferred when we use our proper naming conventions. There might be times when you have to, you know, go off course, and that's understandable, but don't let it be a case where you're always going to be doing your own thing and fighting against QR, and then you end up doing twice as much work when QoR is more than happy to do all the work for you. So after creating all of that, it goes ahead and creates an index on that foreign key column. Now you'll also notice that there are two Sorry about that, there are two methods inside of this migration file. We have up and we have done up pretty much means when I am updating the database or upgrading the database, this is what I want to be done. D means that if I am to undo this migration from the database, this is what should be done. D just like up is opposite of D, the code that is in up is going to be doing something entirely contrary to the code that is in do because there are times when you make a database change, and then you say, Oh, That's not really what I wanted to do, then you make a change. But then when you want to make that change, sometimes it's easier said than done. So at least FCR is saying, well, I am I am telling the database what to do when the migration is being done, and I'm also going to tell you what not to do or what to do when this migration is being undone. So all of that is automated within this file. Okay. All right, so now that we have the migration. We're going to finish off this lesson by running the most magical and probably the most important command of them all, which is to update the database. So up until now, we still don't have our database. We go ahead and type update hyphen database. Press Enter, let it build. And what that command does is it looks at the migrations that it probably knows about and then looks at the migrations it doesn't know about, and then just picks up from that point and executes the migrations going forward. Here you see it say applying migration, and then it's telling me which migration it applied if there were five would have applied five, but there's only one, so it's letting me know it has done it successfully. So when I look back in my database list, I will now see the database by the name that I had defined in that connection string, football league EF core. When I expand the tables, I'm going to see EF migration history, which is basically a table in the database, keeping track of the migration. So you can tell up to which version your database is. Right? And then you have leagues and we have teams. And if you look, you see keys, Okay, so that is the primary key. And you see here that League ID is a foreign key. All of that was set up in the database for us courtesy of this migration. Now, in the next lesson, we're going to look at how we could have scripted this migration because you may not necessarily want to run update database and that and let FCR have complete dominion over everything in the database. But there is a way that we could have generated an SCO script based on these instructions, and then you would be able to execute it on your own. So when we come back, we'll look at that. 7. Generate Migration Scripts: You guys, welcome back. So in this lesson, we're going to talk about scripting our migration. So the case study for why you'd want to script as opposed to, you know, generate a migration file and do it from the package manager would be maybe just separation of controls. Maybe there's a database administrator who is in charge of database changes. So you the developer, wouldn't be making these database changes, but you've done the migration against your local. You need to hand it off to him so he can do it in the environment. You know, there are different situations that might determine whether or not you can do it the way that we just did it in the previous video. Or you would have to script the migration and hand it off. Nonetheless, we're going to learn how to script the migration, and it's a very simple procedure. So we have only one migration, so the script won't be the most complicated one. As the script grows, there are particular behaviors that get added accordingly, and the script grows with the different migrations, right? So later on, we can revisit that, but right now, we just want to see how we would script this migration. So in the package manager console, it's pretty simple. You just say script hyphen migration. What it's going to do is the regular build, and then it's going to generate the SQL file that corresponds with all of these commands. There we go. You'll see here that it's checking if the migration history table is null, then go ahead and create that table. Then we start the transaction, we create the tables accordingly. We get the index, we insert into the migration history, the migration that has just been executed, right? That's it. So once again, this is probably going to be done when you want to hand off the responsibility of the database changes or database creation, whatever it is, to somebody else in your team in the organization, and that is what FCO allows. 8. Reverse Engineer Existing Database: Hey, guys, welcome back. So in this lesson, we're going to be looking at how we can reverse engineer a database that exists. So once again, we have code first where we can write the code that generates the database. But in some situations, you might already have a database that you wish to scaffold out into your class models like what we have been doing up until this point. So in this lesson, we'll be looking at that, and you would have taken note that I have already created a new project. So you can do this if you want to follow along, but it's not absolutely required because we're just going to look at the command and see exactly what it does. So I've created this new console up project, which is solely for the purpose of scaffolding the DB, so you can see exactly what happens. So I'm going to make it my start up project, and we're going to go over to the package manager. And as a refresher, I'm going to say get help, and we're going to take a look at the command to scaffold the database. So here's the list of the commands, and we see here that we can say scaffold DB context. So that's the one that we're going to be paying attention to in this lesson. So when we want to scaffold, I'm just going to copy it and paste it as the next command I'm going to run, and we're going to use the same database that we're working on in this entire course, which is our football league database. I'm going to scaffold it into the models, right? So I need to specify the provider. So hyfen provider. So this is a parameter. Provider, and our provider will be microsoft dot tit framework core dot SQL server. All right? So that's the name of our package, and that is our provider. So that just goes to show that this scaffold DB context command could be used for other databases that are not necessarily SQL server. All right. So we specify the provider, and then we're going to have to give it the connection, all the connection, here would be the same connection that we're pretty much using in our B context. So you just have to formulate a connection string. Since I already have one, I'm not going to type it from scratch. I'm just going to copy it over because like I said, we're using the same database. But whatever database it is, you write a connection string. Data source is equal to the server, it's on initial catalog is equal to the name of the database, because it might be on a server somewhere else. It might not necessarily be on the same server. So you just want to take note of the fact that you write that connection string according to where that database is. So I'm going to open quotation marks, put in my connection string. Close quotation marks. So one more thing that we want to do before we try to execute this command. We want to make sure that our project references the same libraries that we had to set up when we were doing our initial scaffolding or our initial migrations, right? So if we take a look back in the data, we see that we had to reference the kill server, and we had to reference the tools and tools references design. All of these are needed. I'm just going to take this entire item group. Copy and then go over to our new project and paste and then do a quick build. And then after that has been successful, we're going to come back to the package manager console. I'm just going to double check that the provider is correct and the connection is correct. I'm going to make sure that the default project or the target project is the project that we want to do the scaffolding to. And then I'm going to press enter. All right. And after pressing Enter, did the building give me a t warning and then a very deadly exception. Now, like I said, I'm not going to shy away from errors in this course. I think seeing the errors and working away through them is very important. So this error is saying that a network related error has occurred when trying to connect to the server. So that means something is wrong with my connection string. And I think I know what it is. It's the double slash because we have to use the double slash in writing the connection string in the literal context here. But then instead of the package manager, I don't need that double slash. I'm going to remove it, and then I'm going to try that again. And this time, I have no error. I got the same warning, but I have no error. And if you take a look behind my package monitor console, and inside the project, you're going to see a bunch of files appearing. All right? So let's take a look at what each one of them represents. So I'm just going to collapse anything that's not absolutely necessary. And then let's take a look at the context. So it generated a context file. The same context file that we created manually, it just to generated it. It gave it the name based on the database. I gave it the inheritance of DB context, it has two constructors, one parameter s, one with the parameter accepting options from somewhere from some other application, that's fine. We have our DB sets both created as virtual, and then we have the same unconfiguring method, right? So if I did a side by side comparison, you kind of see that while mine is much more or always is much more simplified. It's really the same thing, right? We have the unconfiguring, and then it says, if there are no options configured, then it puts in that connection string just like we did. However, it does give us this warning, which is the same warning that we saw in the package manager console. It's just saying that you want to avoid putting your connection string directly inside of the context like this. And like I said, we're doing this provisionally because it's only a console app, so we're just putting it right there just to get it working. All right. But in a bigger application, it would live in another file and be passed on demand. You'll also take note of another method, which we haven't quite looked at yet, but like I said, you can get to override most if not all of the methods that come standard from the DB context class, and this other method is on model creating. So this one is basically just saying that we have the anation the relational collation setting the character sets that we're going to be using for this database, and then it goes on to say model builder dot entity team has index, and it has one league with many teams and has a foreign remember that just by following the naming convention, when we were creating the database, it kind of implied all of this for us or did it in the migration. In this situation, it's looking at an existing database, and then it is trying to formulate the rules in creating this existing database or interacting it with it that need to be adhered to. All right. In the same way if we were to change this database name and then tell you to do a migration, it would actually look at all these rules to know how to do a migration for a brand new database that looks identical to the one it's scaffolded. So it's a very I'm telling you, FCR is very, very intelligent and it's doing a lot. It's pulling a lot of strings in the background for you. All right, so that is what the DB context really looks like. And then if we take a look at our classes that were generated, you see that we get a partial class, we get hash set that represents the collection of team. So I'll get to that in a minute. But we get that construct and we get the same properties. And if we're looking team, it looks virtually just like how we did our own. So that means we're on the right track, right? We still have that foreign key and the navigational property towards the league, which is also virtual. Now, just backtracking to what the league class has, We see here that we have an additional property that we didn't put in our own. So I'm just going to bring up the original league. This is the one we created, right? And then the one that was scaffolded has this additional property, which is a collection of teams. Now, the concept here is that it's one too many. We know that. We created it, and we'll go over one too many later on, but I just want to highlight that what it is doing here is it is saying that one league will have access to many teams. One team, belongs to one league. But a league has many teams. That's the one to many. So with this navigational property set to be a collection or it could have been a list or well, it used collection by default. When we were creating, we could have said list, we could have said I numerable. But the point is that it is the collection of related items to this entity. So just by getting one league, I could include all of the teams, and that would save me the heartache of having to do two separate queries, sorry, to get the teams and the league that they're related to. All right, so it's very powerful stuff doing it like this. So we can later on add that navigational property to our own, but I just wanted to highlight what exactly was being scaffolded and what exactly is being done. And pretty much that's it to scaffold in the database. There are other little things that probably you can do, but at the most basic level, if there's a database, maybe started building sc server, or it's a part of a legacy project or a legacy system and you want to rework it, you just want to start working on it without starting from scratch. Then you can use this command, bring in or that database in the form of these models. And then through the context, you can just start working with it out of the gate. Okay. Okay. 9. View Diagram with Entity Framework Core Tools: Hey, guys, welcome back in this lesson, we're going to take a look at the FCO power tools and how they can help us to visualize our database through Visual Studio. Now, this is coming off the heels of me showing a database diagram earlier where we were in the SQL Server Management Studio and I generated that diagram zero just to depict the database that we were trying to build at the time. You might not necessarily have that luxury, and I'm not saying you need to go and get the management studio, but using the EFC power tools through Visual Studio, you are able to generate a similar diagram and see how FCO visualizes your database or whatever database it is connecting to. So let's get started. If you have Visual Studio, that's good. You just go to extensions, you go to manage extensions. And when that dialogue comes up, you just search for FCO power tools, and when you see in the search results, you just go ahead and hit download. Okay. After that, we'll need to restart Visual Studio, so you can go ahead and do that quickly. Now, after closing Visual Studio, the extension manager is going to come up and finish the installation. If you don't already have this feature DG ML editor installed, which probably was installed with Visual Studio, so you might not have to go through this step. But if you're going through the step, then it's no problem. Just go ahead and hit modify allow it to bring in the dependencies. And once that procedure is done, you can re open Visual Studio and then What we're going to notice is when we right click a project, we're going to see a new menu item in the form of FQ power tools. So you'll see a number of options, some of them looking just like what we've been discussing up until this point with the different migrations and scaffolding and forward engineering, all of those things are actually possible through these FQ power tools without the need to write the commands. However, for this particular lesson, what I want to focus on is adding the context diagram. So of course, once again, we want to make sure we're in a project that has the DB context. We're going to right click, go to FC Power Tools, and then we'll say Add DB context diagram. And then it's going to generate a new file with an extension dot dGML and we're going to get our diagram. So I thought I would be able to select, but it's actually just dragging all around. So you see here, it's giving us this DB context in the form of a diagram, giving us the team, the different properties, and we can click or hover over the property to get a bit more information on it. It's the primary key, and it's not notable. You see all of those annotations or all of the meta data is available right here on hover. And if we hover over the table, it's pretty much the same thing. So from visual studio, we can use this tool to visualize exactly what is happening in our database at any given point. Now, given that we only have two tables right. Now this diagram might not seem as exciting as it probably could be. But you can just take some time to look at it. You can hold on control and scroll to zoom in a bit or you can just change the zoom up top here, and you can take a look at the legend where it points on what the different symbols or the different colors are, you see here, red represents a navigation property. The What's that purple or bluish, I'm sorry, I'm slightly color blind, but this purplish color represents the foreign keys, and then the primary keys highlighted, and then the different properties are. So you can take some time to just take you know, take it in, you can see the different kind of relationships. But as our database grows, we will revisit this and take a look at how FCR infers, what kind of relationships and different annotations on different properties as we go along, and then we can see this diagram evolve with our database. Okay. 10. Adding Verbose Logging to EF Core’s Workload: Hey, guys, welcome back. So in this less, we're going to be setting up FCR with some additional options to spit out more details about what it's doing. So COR is really quite by default, but we wanted to make some noise because when we're executing our commands, I want us to have a visual representation of the Q that's being orchestrated and the different operations being carried out in the background while we're doing our thing. So what we're going to do here is extend the options builder to know that it needs to log to And I'm going to let it log to console that right line. So because we're using a console up it's on the right. So we log to console that right line. And I'm going to add to the configuration or let's say, the pipeline of what it should be logging. I want it to log the DB logger category command name so that we can see a visor representation of what exactly is being done. And I'm going to extend that to let it know that we want a log level. I was going to say a log level. Dot information. And I think I'll have to include they will go include a library for Microsoft dot extensions dot logging. I think I got ahead of myself just now. So it's new DB logger category, and then we close a curly brace here. And then we have the log level information, and then we close the parenthesis there. So take a look at that line. Apologize for that. So we log to console dot right line, new array, and then we're just passing in the command name line right there, DV logger category database command name, and we're letting it know that we want a log level of information. So that means everything it's doing we want to be seeing spit to the console. Another thing I'm going to include is enable sensitive data logging. That enables us to see or it tells FCR that everything that is happening in the background that you probably wouldn't want your front end user to see, we want to see it. It's our app we're learning, so we can at least do this with zero risk, but you wouldn't necessarily want to do that on a production server, but it does help with debugging in certain situations. So now that we have all of this wired up, I'm going to just jump over to the console. You don't necessarily have to do this step. I just kind of wrote some code so that we can start interacting with F course, so you can see exactly what all of that would amount to. But in the next few videos, we will be going through all of this code together. But for now, I just want you to see a sample of the additional logging. So on screen, you'll see the console application. And if you take a close look, you'll see the logs being spit out. So we have info at that time stamp. We did this command, and then it's showing you that we execute a DB command, the parameter was. So what would happen naturally if we didn't enable the sensitive logging is that these parameters, you would not see the values. So enabling sensitive logging, like I said, will show some details that would have been left out for, you know, security reasons, hence the title sensitive logging, right? So we can see exactly what values are being passed in, and the size of the value, the type, the command time out, and then we see the actual SQL being generated insert into that table, the values, and then it's just taking that parameter. So it's not putting that value in directly. So that just goes to show that FCR is actively doing parameterization, which is a key component to the fight against SQL injection as a security loophole that many people try to exploit, right? So it's parameterizing the simple query, and then it just goes ahead and selects the ID from there and updates the scope identity. But right now, I'm not going to get into too many details about what the script is doing. I just want to highlight that we are now in a position that when we're going to be writing our FC commands, we can see actively the SQL that is being generated in the background. We also see the run time this to 61 milliseconds. But there might be times when maybe a query is running too long and you're wondering why you probably need to tweak it. All of those things you can do when you have this kind of logging at your disposal. Okay. 11. Simple Insert Operations: Okay. Hey, guys, welcome back. So in this lesson, we're going to take a look at how we can perform simple insert and select res using EF COR. Now, coming off of our previous lesson where we looked at the verbose logging and adding additional information to the console. You'd have seen that I had this bit of code that we are to explore and try to understand. So the first line that I want to point out to you, and this is once again, the program dot CS file in our console app. All right. So the first line that I want to point out to you is where I'm instantiating our DB context. So I have private static football league DB context. And just in case you're wondering where that name is coming from, that is the name of our DB context here. Remember that I said that this file represents the connection to the database. It has the connection string, and well, it knows about the elements or the entities rather in the database. So this entire class as our database grows our contracts, this class is what gives us that gateway access into the database and allows us to interact with the different entities. So we have to have an object instantiated of it. Now, in a regular dot nic core, well, let me not say regular dot nic core. In a bigger dot nic core application like a web application or even a blazer application, you wouldn't see it being done this way. You would see it being injected in. But once again, we're here to learn about the syntax of F core. So I'm trying not to focus on those other aspects of development in this particular course. All right. So we're instantiating or football league DB context. I'm calling the object context. You would have seen different suggestions given to you, so you can choose whichever one you think is more indicative of what this object needs to represent. But context is almost like the universal word to mean the file that connects me to the database, right? Hence's using it in the name of the class and even the object name. Once again, naming is more subjective. So that's fine if you have other ideas. So we just instantiate it here. And then we move on to the main. So like how we did it in the class, this is global to any other function that we're going to be using inside of this class file. Now, to add something to a table, the syntax would be context. Once again, that object that represents a connection to the database. So we say context, dot. The table we hope to interact with, and this this word leagues is really relating to whatever we called the tables here. All right? So that is leagues. So if I just retype this from scratch, I'm going to say context, dot, I want to see a bunch of options. I can add, I can add a sync. I can do a number of things, but then you'll see the different properties, and some of the properties would include the table names. There's leagues, and if I scroll down a little bit more, there's teams. So as many DB sets as we have defined in the context file, we get to access them directly right here whenever we need to interact with said tables. So context leagues, and then what do I want to do with the league table. In this situation, I want to add. Now, if we take a look at the ad function, you'll see here that the overload is expecting the well, not the overload. The parameter that is expected by the ad function rather is of type league, and it's just saying it expects a league entity. So in other words, it expects some object of the type league. So that's the beauty of F court allows us to stay in C sharp because in Q syntax, we would have had to write something like insert into leagues, and then values and then list out the values and that's if we're not going to specify the columns and then the values. And yeah, that syntax would be it is purposeful, but having to put it into the C sharp and then convert it and then do all of that, FC is just saying stay in C sharp, I have the object context. I get the table you want to add, and then you give me the data you want to add. So I added a football league from Jamaica here, but then we can change this and let us try English Premier League. All right. So new League instantiating an object, and we're passing in the properties, and League really only had ID names. So because ID is already an incrementing primarily, we don't have to provide a value for the ID. We can access it sure, but we don't need to provide a value when we're adding. All right. So let us go ahead and run this command and see what happens. Logs here is showing us that it has successfully executed this command, it took 112 milliseconds and it put in the parameters and everything. And then here is the CQL syntax that was generated. So insert into leagues, then the column names, then the values, and then the value, and then it just updated that object accordingly. Now, another line that I want to point out is line 14, which is the ultimate line that you need to call. No matter what you do, thinking you're interacting with the database. Once you are manipulating data, meaning you're adding or you are updating data or deleting data, you have to call safe changes because all this method does and the like methods for the update and the delete. All they really do is track what changes need to happen, but they're tracking them in memory. Right? So for as long as the application is processing this particular operation, it is just tracking it in memory that, okay, I know I need to add this. I need to update this. I need to delete that. But then until you save changes, save changes actually says, generate the SQL, send to the database and attempt this, and then we'll roll back if anything fails and let the user know what happened. All right? So that's really what the save changes is for. And then we called save changes A sync, but then there is also the save changes without the AC that's fine. I'll use it without the AC since this is not an asynchronous function. I will just keep it simple. But once you're using asynchronous programming, you can always use the A sync version of these methods. Which, of course, have to be placed in an asynchronous method. So I just swapped out all of my methods into the A sync versions, and I change the main function into an A sync task. And then because of that, now I can I have to say a weight add a sync, weight, save change is a sync. Now, another thing, I just want to break this out a bit more. Because right here I am adding the object directly into the parameter. That might not always be the case, because especially in bigger applications, like when a user is submitting the form, then in a web application, they submit the form, you would get all of the data from the form in an object that you need to pass into the database. You wouldn't necessarily want to get it from the form and then have to write new league and then try and put each each property with each value again. So you can always just initialize Subq Sevar League is equal to, and then I can initialize a new instance of league. Let me just do this to cut down any long typing. There we go. And then I can give this one liga. You can tell I'm a football fan, right? So La Liga. So this would be the Spanish league. Now that I have my object that represents the league that I wish to add to the database, I can now say add AC and pass in that object. So it's really that simple. You can build your object elsewhere, then you add it, and then when you save changes, that is when you get it committed to the database. Now, what I want to point out to you also is that once we save changes, this object is going to automatically get updated with its new value. So let me add a breakpoint and run. And what I'm going to do here is initiate a watch window, and I'm putting the league object there so we can track its values as we step line by line. All right? Let me just pin this, make it a little bigger so we can see it. So first line, nothing has happened yet. League is null. Then I'm going to step And then you'd see g has an ID of zero and the value Liga. And then it's going to save the changes, and then we step one more time. And if you look at the object, you'll see it now has the ID value. So this comes in handy when you have operations that are chained, right? Because you might have an operation where you need to add something to the database, and then you need that value, that new ID value in order to maybe display the details of this record on the next page. So I submit a form, you edit to the database, then you bring me to the page to see the data. I just submitted what it's reading from the database. Well, this is where that comes in handy because then I can query the database directly on that record ID right after the operation has completed. I'm just going to press F five to continue with the execution. And once again, we see our very friendly message here saying that it has added that data to the database. No, just for depiction sake, as I said, we want to see some of the errors that we might get. What I'm going to do is try to add an ID value to this object before it gets added to the database. So I know that the ID just now was seven. So I'm going to put in an ID. It really doesn't matter what value because if anything other than zero, when it goes to the add operation, it's going to give an error. So I just wanted to see the kind of feedback that we're going to be getting from EFC, right? So just running it and allowing it to do what it needs to do. And then you see here it is failing, and we're getting this error here. It is saying an error occurred. CQ exception cannot insert explicit value for identity column when identity insert is set off. So you see we're getting literally one of those errors that we would have seen if we tried to do this in SQL directly. If it's not the same error, then it is worded very similarly to it. All right. So that's the kind of feedback that EF COR will give us whenever we try that. And we'll see here in our console log that it just failed. It just tells you failed and insert into that, that just failed. All right? So if I continue, then the execution will end accordingly. All right. So we're winding do, and I just want to do one more example of how entity framework work can make life easier. So in this situation, I want to add a new league, and we're calling this one Syria, that's Italian league, and we add the league like we know we have to do, then we save changes. But then I need information from this league in order to conduct another operation. In this situation, I want to add teams. So obviously, a team needs to exist in a league, right? So I need the information from the league that was just created in order to create these teams. So what I'm going to be doing is passing this league object over to this method. And then notice that we'll have to save changes because every time we're carrying out some operation against the context, we have to save changes in order for it to take effect in the database. So, I already created that method I made a static AC task, and I call it add teams with league ID. Well, I call it with league ID. Let me just take off league Let me take off the ID because I am showing two different operations in this example, and you'll see why, and it's taking a parameter of type league, which is the league. All right. So you can just go ahead and replicate that. And then in this method, I'm trying something different. Instead of one, object to then add, I am doing a list of objects, and then I'm going to add range. So this is now going to take advantage of FCRs ability to do bulk operations. Now, a little tit bit bulk operations, it is a feature that allows FC to kind of batch multiple like add operations and so on into one Q statement and shoot out. But then the team kind of decided that, well, it would be more efficient at smaller numbers of records to just do individual SQL statements. However, at a certain threshold, you'll start seeing one SQL statement with all the information. That's just a little bit about how these bulk operations work in the background. But in this method, what I'm doing is defining a list of teams, and each one, I'm giving its name. So this is vents, and this league ID is the ID coming over from our league object. Right? And then the next team would be a sim following the same format. But then the third one, which is Aroma, I'm doing something different. Instead of using the foreign key, I am now using the actual navigation object and reusing the object that has been passed in. So we're going to see exactly how that works. So, these would be the traditional way. You have the foreign key, you put in the foreign key value, and it's satisfied, right? But then in this team, instead of using the foreign key, I'm using the actual object to pass in. So let's see what this would do. So I'm going to put a break point at the first save changes, and then we can just step through and see the operation step by step. So I've added the watch for the teams also, right? So let me just step through the league hasn't been created as yet. So step use F ten, so I don't have to go over to the context. I know we have our ID of eight. If we look in our league object, we see it updated accordingly. Then I'm going to use F 11 to go into this method where teams is now defined. I'm just going to step through its creation. And then if I look in the list of teams, I see I have all of them with their respective IDs, and then Roma is here with the Actual navigation object instead of the league ID. All right? So league ID for Roma is zero, but the league ID for everybody else is eight as expected. However, the navigation property for everyone else is null, and for Soma, it has its navigation property. Now, let us see what happens after we save changes. I'm just going to press five so we can see the logs that gets spit out. And you see the first insert operation happening right here with the league. Oh, no, that. This one is with the league. Sorry, right? Then we see another one happening for the first team, and we see that the ID, the league ID of eight and the name that's being passed in league ID name, right? And then for the last one, you notice that it's the same SQL statement. It gets the eight, it knows it's Aroma, and it does the same kind of insert. So that's just going to show you that you can put in the actual foreign key value and it will, of course, do what it needs to do with the insert, or you could put in that whole object and it will still infer that well, the object has its primary key. So obviously, this is a related object, so the foreign key would be the primary key offset object and whatever other data. So FCR is doing that for you in the background. I just wanted to highlight the different ways that you can insert a record that has a foreign key dependency. Now, there are a few other things that you can do, but sometimes they only come with experience, but at least if you understand the basics of how to add something to the database. And notice I had to add here, save changes and then add again and save changes because I needed this to be added for the key to be generated so that I could carry out this operation. All right? On the flip side, if I had done something like this and just take the time to look at it. I just kind of reworked it so I didn't have to sit and watch me type. But what I'm doing is still initializing a new league, and then I have a team This new team has a name Bern Munich, and then I'm passing in this league object. Now note, I'm not doing a I'm not saving changes in between these two lines, right? So first league, fresh team, and it's getting that fresh league object. And then all I'm doing is adding the team and saving changes. And notice the difference between this ad and the previous ad ACC lines. You have the option, of course, to highlight the table that you wish to interact with. So if I said context dot leagues add a sync, and then passed in a team object, it's going to be an automatic error. Why? Because once again, the ad operation requires a type of league, right? So I can't be passing in a team object. I could of course have said that teams and everything would be okay. But then if I didn't specify the table, entity framework would automatically know that, okay, this is a team object or it's a list of team objects like we did down here, where I just said context dot add range passed in the list of team. So it knows that clearly it's going to look for the corresponding table for that data type. So you don't necessarily have to at least when dealing with the context, you don't have to specify the table all the time. All right. But what I want to point out is that when I do this and then do one save changes, it's actually going to create the dependent property and then automatically create the team with that foreign key. So let's take a look at that operation. So when you look at what it generated, you'll see that it executed the command to create the league first. There it is creating the Bundesliga, and then it gets that ID, and then it goes ahead and executes the one to create the team passing in the new foreign key into that new team. SCEF CR is just pulling all of these strings for you. All because I told it, I want a new team, and this team is a part of a league that I don't know the ID of. So it said, no problem. I have this. It goes ahead, creates the league, gets the ID, creates the team, and then just lets you know everything is done. So if you took a look at that team object, you'd see all the data relating to the team and the league. So when we come back, we'll start looking at doing some select queries because up until now, we've only been putting in data, putting in data, putting in data. Now, let's look at how we can read the data from the database and display it in our application. Okay. 12. Simple Select Operations: Guys in this lesson, we're going to be talking about simple select operations. Now, before I move on, I just want to point out that we did quite a bit from the simple insert stuff, and I have extracted them into individual methods. So I've got rid of the code, and I've kind of commented them so that when you look back at it, you can see what is happening where. All right? So I just have them commented on because we don't want to keep on adding and adding and adding the same old data. So now it's time for us to look at selecting, so I'm just going to part them, put them to the side, and then we can continue to work around them. All right. So when we talk about selecting, this would be the R in CRD. So I don't know if I use that acronym prior to speaking on this topic, but CRD create read update delete, right? That's an acronym that is widely used in database development, and it represents basically the four operations that you'll always carry out on a database in any application. You're creating data, which is what we just looked at when we look at inserting, that's creating you are going to read data, meaning you want to retrieve the data that is in the database. That's what we'll be doing now, and then later on we'll look at the U and the D, which stand for update and delete. Now, when we want to retrieve data from our database, we start thinking about formulating what we call link queries or link statements. Link, which is short for language integrated query. It's basically a dialect. I call it a dialect of C sharp. It's a way that allows you to write a query using C sharp, and then of course, FC is going to translate that into QL. Let's get started. The first and simplest thing that you can do. The simplest link query to retrieve data from the database would be and I'm just going to define a variable. So I want all the leagues to come back, and I'm going to say Regus is equal to context, which is connection to the database table I'm interested in, which is leagues. And this is telling it that look in the DB set of leagues. But then I want it in a list. So I'm going to say dot to list. Then it's going to ask me to include a certain library. I'm just going to control do you see it's telling me that I need the library system dot link in order to do this. I'm just going to go ahead and include that, that error goes away. And just like that, I have told it, select star from table called Leagues. That's pretty much it. Right? Then what's going to happen is that context creates connection to the database. It goes to the league table and then this list says, extract the data, bring it back in a tula form, but then materialize them into a list of league objects. Just the same way that we had created the list of teams, where is it, here's my list of teams. The same way we created the list of teams, it's the same way it's going to materialize into the list of leagues for us. So what I'm going to do is do a for each, and I'm going to say for each league in leagues. I want to console dot right line. So I'm just hyphen using a hyphen to separate the ID from the name. All right. So let us take a look at that. All right. And when we look at the output, we see here the statement that was executed, select ID and name from leagues as L, right? So that's basically that SQL statement, which you could just copy from the console, go over to your management studio and execute. So that comes in really handy when you're troubleshooting, maybe you wrote a query and you're not getting back the results you want. You can always get that SQL statement and try decrypt why this cle is being malformed. Once again, that's a very powerful tool. However, when we take a look at the objects being printed out, we see everything that is in the database. So while testing, we probably entered the Earth strap Premier League a few, too many times, also the Premier League, but then we see all the other leagues as well. So that is what we get when we just simply say context, table name, give me them as a list. Now, some important things to note when it comes to the syntax, this is what we call the executing statement. So without this, that query wouldn't be run. This would just say, Well, legs is now just the hash set or the DB set off the table called legs, right? So when we put on the to list, that's when it actually says, I will go and execute that query and enumerate them and send them back as objects. So if we were supposed to do something like leave off the to list and then still execute this. This query would not get executed until it actually starts the four each loop. So it would just be like in a state of stasis here. And then when we start to four each through, then it would say, Okay, okay, let me go and get them so I can enumerate through. Now, the danger of doing it this way is that the connection will remain open for the duration of this four each loop. Now, with only maybe 15 records, that doesn't seem like much of a big deal. I mean, okay, fine. But then when you're going through a bigger database, you have that connection open. And let's just think of every connection to the database as an expensive operation in any system, right? So you want to reduce that as much as possible. And you also don't want a connection to be open for too long or longer than it needs to be. So when we do the statement like this and we could even get rid of that leagues part of it and just say for each VR league in context leagues. The point is that this would actually kind of create a lock and a very inefficient one at that. As a matter of fact, the more operations you have doing in that for reach, the time between going through each item increases, and then of course, the connection stays open, and it becomes even more expensive. So I'm just pointing that out, yes, you might try this and it works, but it is not the most efficient way to do it. The most efficient way and the smartest way to do this, once again, would be to Put on that executing operation, let it iterate through and get the list, and then that list is no stored in memory. That connection is no close to database, and then you can do all of your operations and manipulations against the data afterward. All right. So that's really it for doing the simple select queries. I've kind of extracted all of that code and put it in this method, which is a static void method. So this one doesn't use any asynchronous operations. So we don't need to make it an async task, right? So it's a simple void. And what I've done is to highlight which one is good and which one is pretty much bad. So not everything that works, even though it works, it has some underlying ramifications that you might not be aware of, but that's why I'm here to kind of point out what is the best way to kind of gobble your operations. Okay. 13. Filtering Records: Hey, guys, welcome back in this lesson, we'll start looking at how we can filter our queries. Now, the case study for filtering is obvious, right? What we've looked at in the simple select is that we are selecting all and then we're iterating through all. There are situations where you don't want all, you want specific records, and that's what we'll be looking at first. So I've created a method query filters. And before I move forward, I want to highlight that I did say that we made this one void because nothing synchronous was happening in this method. Well, thanks to entity Framework Core, we do have to list A sync. And when I control that, I just have to add that using statement for entity Framework Core. And then thanks to that, I can now make this synchronous. As well as add the weight before the AC call. So let us move on to our query filters. So when we want to add a query filter, and I'm going to say leagues is equal to or context, dot, the table we're interested in, which is leagues, then dot, and I have access to a number of methods on this side. So if I wanted to say, give me all the leagues where the name is equal to some value, then I can say dot, where and then this where function takes a parameter that looks like a predicate or it is a predicate or a Lambda expression. So the format for a Lambda expression is you have some token. I'm going to call it Q. Most examples on the Internet, you'll see them use Q. However, there is no stipulation as to what this token must be. Of course, you treat it like a variable name. So if you want to use Q, if you want to use league, you know, whatever it is. So if I said Q and then it's followed by this Lambda arrow, Then I can now use Q to represent any one record in the table. So that's the format of Lambda expression. If I used x's the same thing. If I wrote out the word league I'm just highlighting that it doesn't really matter what you call it, they all will function just the same way. Maybe this one would read better because once again, it's saying database, give me the table call leagues and give me the records where any one league has a name that is equivalent to some value. If we wanted to find say Siri, then that's what that would look like. Once again, this token could have been Q, it could have been x, it could have been y, it could have been Z. So it doesn't really matter what you use as that token. Now, if I do this, it will not execute until, once again, we end with our to list. Since we have the A sync version of the to list, we can say to list A sync, and then I'm going to await that and of course, make the method an A sync task. Then after doing all of that, I'm going to repeat this operation where I'm just going to print them all out to the console. I await this function call and of course, and I wait goes before everything else. I'm just going to fix these things before I move forward. So when we call that, it's going to go ahead look in the table where that condition is met. So this is going to be true or false, so it's either meeting the condition or not. If it has met the condition, it will be added to the list and eventually returned here. So let us run this query and see what we get, right? And then we see it is coming back. We have our one record coming back. If we take a look at the SQ well, we see it's just a simple select query, but it adds on that were clause with that condition. All right. Now, I hard coded. Notice that unlike the other times when you saw the parameters, it actually just put the actual value inside of the query, and that's because it was kind of hard coded. So I did mention that parameterization is good protection against CQL injection. So the reason FCR has not used parameterization in this situation is that it sees that I am the one who hard coded the value from the code. So I wouldn't put in a SQL injection directly into my own code. So of course, like, okay, it's safe because my master is the one who did this, so it must be fine, right? But then the reality is that in normal situations, you'd probably be getting whatever it is you're searching for from somewhere else, right? So let us get a bit creative here. So I'm going to tweak this function a bit, and I'm going to say console that right line interleague name. So I'm prompting the user this time. So once again, this is a console app but in a web application or so on. Usually, you allow the user to depict or determine what it is that they need, right? And you carry out the filtering accordingly. So I'm prompting the user, and then we're going to store the response in this variable. And then that is the variable that I'm going to use to execute the query. So I'm going to say where The Lambda expression, Q dot name is equivalent to league name. All right? We could also even say dot equals, because we could just rely on the C sharp functions that we probably would use in a regular statement, but you notice that that looks just like a statement, right? It's the same kind of logical operator that you would use in a condition. Because once again, this is just a condition that needs to be true or well, it needs to be true in order to be included in the list. Right? So now we kind of boasted it. Now we can interact with it a bit more. So I'm going to run this, and we're going to see the prompts. I've already entered Syria I press Enter. And then it took 83 milliseconds to go out and look at it, now it parameterized league name. And then it is executing with the parameter. So you see, once again, this is automatically kind of safeguarding against CQL injection through the parameterization, we're getting back our seria. Now, we only have one league with that name, but we did have multiple red stripe premier leagues. So I'm going to try this again with something that I know is going to return multiple records. We're trying again with red stripe Premier League, and the filtering will now bring back all of the records. You see it is working. It's bringing back every single one that has that exact name. And remember that this time we didn't use the double equal sign. We used the C sharp because this is a string, so we said dot equals league name. So it allows us to write that C sharp syntax that we are familiar with in other parts of our code right into the query, and it just handles all of the rest for us. Now, let us do one more experiment here. There are situations where your filter might not be as exact as a true or false. It might be a kind of fuzzy logic where you want to know if it contains, especially when we're dealing with words, right? So in a typical search, you would be able to type in a part of the word or part of the expression you're looking for, and then you would see matching results. So I've extended the prompt to say interleague name or part of, and then what we're going to do is do two queries. So I'm going to take all of this and duplicate it And what I'm going to do is called one exact matches and the other one partial matches. So for the partial matches, I'm not going to say dot equals, but I'm going to say dot contains. Because in C sharp, if we were to try to figure out if a string has another string in it to say dot contains. So that is what I'm going to pass over in this were expression. So let us take a look at this. So I'm just going to use I'm going to enter one bit of string to search for. I'm going to enter premier right? I know I don't have any league that is an exact match for premier. So we already saw the exact matches work. But I'm just going to type in premiere. And then what you're going to notice is that it's going to execute both queries. So it's going to say, executed this, it's looking for premier. So this one has the wear clause for the league name premier. Of course, there are no. But then this one has the league name like or chart index L dot name is greater than zero. So it just chart out a whole nice query to say, I'm looking for something like what is in the parameter. All right? And then that is when we see all of our leagues that have the word premier coming back. All right. So that's another way that you can go about filtering your queries. Now, when it comes to the, you can use the content, sorry, or you can use EF functions. So I'm just going to duplicate this, and I'll commend that part out, so that's option one. Another option would be to use the function. So I still need the token, still need to initialize this, but I'm going to say EF functions. Dot. And then you see here, I can do contains, I can do and a bunch of other functions that would be available to me if I was doing straight SQL. Some of them are right here for access for us. So I'm going to do the like because that is more like what we're looking for. I'm going to say like and then I'm going to say q dot name. So the method takes, what is the expression or what is the string? What is the database column that I am comparing on. And then the second parameter would be the string pattern. If I wanted to put in a specific pattern, if you're familiar with colority then you know that when you're dealing with like, you have that wild card. So I could say premier modulus, which means it starts with the word premiere. If we put the modulus in front, it ends with the word premier. So whatever you would have done in SQL for that modulus for that wild card bit, you can do it inside of the string and it will just aggregate it and create that expression exactly like what we would expect. So in our situation, because we're dealing with this variable, I have to get a bit more fancy. I don't want to hard code what we're looking for. I'm just going to use some interpolation here, and I'm just going to put in the value that is coming in from the input. So we're saying, get me all the leagues where The name is like this search pattern. All right. So let me just do that one more time so we can see what that will spit up. This time, I'm going to type in La. I don't know. I don't have a Liga. I don't know which other league in the database might have the letters LA in it, but we see La Liga being returned. And if we look at that query once again, we see that it is giving us the format and then it's saying where the name is like. That parameter. So you see that kind of looks a bit cleaner than what the contains query generated, but at the end of the day, they're both going to give us very similar, if not the same results. So that's it for a little experiment with filtering our queries. When we come back, we'll look at how we can go about aggregating data, maybe one, the first one and list, the last one and the list, we one the sum of everything in the list, those kinds of things. So when we come back, we'll take a look at that. 14. Additional Execution Methods: Hey, guys, welcome back. So the objective of this lesson is to understand or have an appreciation for some of the other methods that we have available to us via Lincoln FCR and how they work exactly. So I've already gone ahead and created a new method, and this is for additional execution methods. So I have a statement here that looks just like a select statement coming from our previous activities where we have context that leads where, and I'm just saying contains A. Now, the case study for an aggregate function or one of these additional execution methods would be that you probably don't want the whole list. Probably you want something done against the list or you want to cherry pick the list? So when we talk about aggregate functions in C, we talk about things like Min max, count, some, those kinds of operations, all of those are available to us through these additional aggregate functions. Now, another thing that you'd probably want to do is get the first or the last in a list, those kinds of things, right? So I'm just going to use this as a quick example. And what if I wanted the very first league that has the letter A in its name? Then after seeing all of this statement and adding the filter, I would then end it with a first or default. You'll see here that we have, of course, the Async methods and for every AC there's a non AC version. But it's not necessarily the other way around. So not every method has an Async counterpart, but you'll see that later on. Okay. So here, I can say I want a first or default or I can say the first. The difference between first and first or default is that first we'll always expect to see a list and it will get the first. So if nothing is returned, then it will throw an exception. It will end the execution with an error. All right? First or default over will say, I will attempt to get the first and if there is nothing to get, then I will return null without ending the execution at that point. All right, so first or default is probably a safer bit. And then what that will do is just get back the one league that is at the top of the list. All right. Now, we could actually simplify this because what we're doing here is saying give me the work clause and then put in the condition and then get me the first. I could have actually just done this. I could have said context do leagues do first star default, this condition. That is an executing method. Remember going all the way back to our simple select. Sing context dot leg doesn't really do anything, but then once we put on that method, it executes. We saw the same thing with the to list and the ware. But in this situation, I can just say first star default and then that condition and then it will execute the query accordingly. So I'm just going to kind of break out and show you some other commonly used execution methods. So I'm going to replace that line with a simple statement that is getting our DB set here, right? And of course, from we have the DB set, we can access of the execution methods. So let's just go through them kind of in order. So we already looked at the tost. We know that the tolls is going to execute the Selcti statement. We also have first and first R default, and we just discussed the difference between the two of them, so we can say leaks dot first a sync or first R default a sync and once again, they do have non asynchronous versions to these methods. Based on your situation, you may be using the A sync, you may not be using the A sync, but I'll just keep it consistent and stay with the A sync. We also have the single or single default. Single it does what the first would do except first is seeing a list, and it's just going to pick the first, literally first from the list. Single is going to operate similar to the first where if it is looking for one, first is expecting a list and it's going to take the first. If no list is returned, then this throws an error. Single is expecting only one record to be returned. So whatever qu you is expecting only one record to be returned. If it sees more than one, it will also throw an exception. Single will just not throw the exception but return default under similar circumstances. You can always read up more when you just hover over the method, it will give you some education as to what it does. Now, when it comes to some of the traditional aggregate functions that we would probably know from SQL, you see count. So you can say leagues dot count sink you will execute the count query. You have long count, you have minimum, you have maximum, you have S, you have a number of other ones. Like I said, you can always just say leagues dot or context dot table dot, and then you can scroll through and you'll see the plethora of methods available to you. You can choose to use them when you need to use them. But sitting down and trying to go through all of them at once might be exhausting, but At any point you think you have an idea in mind. You can always just do the dot control space and just look through and see which one of these meats you need at that point in time. Now, another execution method, which is not necessarily a query method like one of these, but it will execute against a DB set is to find a sync. This one will actually just go out and find based on a value, so that value is going to be the key value, right? So finds an entity with a given primary key. So when you say find a sync, you're expected to put in whatever unique identifier is therefore that table, and then it will bring back that record. It will either bring it back or bring back null, right? Or it will return the record or return null. So that is how that find a sync work. So let us try executing and see exactly how this would work out for us. So I've set a break point at the start of this method, and I'm just going to go through step by step so we can see the C quel being generated. I believe when it gets to the single and single or default or at least the first, it will probably throw an error on one of these, but let us step through and see exactly what is happening. So I've put my windows side by side so we can see every step of the way. So when we say to list, we'll go ahead and execute that one, And then we see here that we're getting that select query that we're familiar with already. So I'm going to go and do the first. So you see it's getting the first, it's select top one. From league. All right. So that's what the first does. And the first or default, if I'm not mistaken, we generate pretty much the same SQL as the first. But like I said, if first doesn't see anything, then it will throw an exception. Now, single is definitely going to throw an exception because it is only expecting one record, but it's getting the list and saying one single. So when we step with that one, then it throws that exception. There we go. So the exception is that sequence contains more than one element because it was expecting only one element to be returned, right? So I'm just going to kill that execution, and I'm going to comment on that line just so that we can continue with the rest of the execution. So I continued the execution against all the other methods, and you'll see to the right, some of them were successful, some were not. So single singular default and pretty much all of these math ones through errors, Really, because, well, I'm not really doing anything mathematical here. I have to make sure that I'm finding the mean of something, finding the max of something. Obviously, that's not really happening when I just say leagues, right? But the point is that these are some additional methods that you can use when you're doing your more complex operations. And if you look to the right, you'll see that up to the cot when we asked when we asked for count and we asked for a long cont, the difference between the two was that we had select cont star for the cot and then select cont big star for the long cone. The only material difference really is that count returns an integer and this one returns a big integer. So, you know, based on the size of the database, you may never end up using long count or big in SQL, but the method is there regardless. So that is really it for the execution methods. As we go along, we might find use for them. And we may need to interact with them otherwise. But right now, we don't. I'm going to comment out all of those that probably gave errors, and I just want us to see the q that gets generated when we try to find. So we see here the execution for the find is that it finds a top one where that condition is met. So all of that once again is generated for us. And more than likely we're not going to hard code that ID anyway, that ID would be coming from somewhere, you know, like when somebody clicks on edit in your user interface. You have to know what record they intend to edit. You have to go and find that record, return it, and then present it to the user. So that's a case study for when you would use that find. So that's it for us looking at additional execution methods. As you saw, there's a much longer list than what we have gone through here. But you have these four reference, and I'm sure you'll find good use for them in the future. 15. Alternative LINQ Syntax: Hey, guys, welcome back. In this lesson, we're going to be looking at alternative link syntax. So up until now, we've been looking at link in the form of these execution methods and different Lambda expressions that we can place in some of them to kind of get our operations unique to our needs. Now, That is fine, using the Lambda expression method is perfectly fine, and as you can see, it can be written in one line, nice and clean. However, there is an alternative syntax that some developers may find a bit more intuitive because it looks a bit more similar to traditional SQL, but it is still C sharp. So this is actually the first way that I used to write link before I discovered or got comfortable enough with Lambda expressions. And I have the code prepared here for you, so I'm just going to walk you through it. So this time we're looking at the teams, right? And then our syntax here is saying from some token. So this could be, you know, we used Q in our Lambda expressions. This represents something just like that Q. We could say team, we could say Q. I'm saying I. All right. So from I in, and then we say the table that we're interested in looking in. So context that teams, then select. So like I said, it looks a bit more like Q because it actually has some of those SQL keywords, right? It's just a little bit backwards because it would have been select star from table. And this one is from record in table, select record, or we could say select something specific. But we'll look at that later on. So when I do this query, it is going to give me queriable of teams, right? So that means I need to convert that into a list. But let us execute and see what we get, and then we can refine as we go along. If you look at that, it actually executed this query quite perfectly, right? It gave us our select statement, and it's giving us back our teams. So we didn't have to explicitly say to list and try to convert it. Now, one of the benefits, however, of having it as a list, is that when it is in the form of quarable, our ability to manipulate it to a certain extent is limited, right? So if I looked at teams, there are certain functions here that are unique to i quarable and there are certain functions that are unique to lists that you probably would want to use later on in your coding that you can't really get when you're dealing with an i quarable. So from time to time, I actually just wrap this all in parentheses and then say to list, and then that just gives me back my list, which is a bit more native to what I would want to be doing in my C sharp code at the end of the day. Of course, to do that, I have to change my method from void to AC task, right? So then we are getting our list, and as you can see, the list, the collection types work, fairly similarly, but like I said, there are certain things that the list affords us that an querable enumerable, and the other type of list collections or collection types in C sharp, they just don't. But of course, everybody's situation is different. Use the one that is appropriate to your context. Now, let us extend this. We also looked at querying, or sorry. We also looked at filtering our queries. So right now, this is just doing a standard select star. Right? But what if we wanted aware clause? Well, we would say from I in table, and I'm just going to break the line here. So it looks a bit more readable. Where, and you'll see that it kind of highlights those keywords for us. So where I which represents expression or any one record in the database. So I can say where I name is equivalent to, and then put in my records. So the same way that we were able to inject in or, you know, a variable or a static name or any of those things, we can do all of that right here. Let me see if this would even work. I'm going to try to say, where I name or let me say where EF functions like. And then this would be I don't name. And then we have the league name, which I'm just going to do a quick prompting this method for also, this would be team name. So Enter team name. Let me just change the verb name. And there we go. So we're prompting for the team name and part of. And then we're saying from the team table where dot functions like dot name, right? Our expression, and then we want to select, and then we want to put all of that in a list to return to our variable call teams. So this is going to work, of course. I'm just highlighting the fact that we can use the alternative syntax in the very same way that we use our Lambda expression. So here I'm testing it, and I'm typing in B A Y as part of the team name. We see our select statement with the statement being generated for us, and we're getting back Bar and Municx since it's the only team that we have that has BAY in it. So once again, this is our alternative syntax. Whatever you can do in the Lambda expression, you can also do in this kind of syntax. Personally, however, I find the Lambda expressions to just be a bit easier. So ever since I got comfortable with them, actually abandon writing my queries like this. I haven't written queries like this in years to be honest, but it's good to have the knowledge of all of your options and alternatives, if need be. Okay. 16. Simple Update Query: Hey, guys, welcome back in this lesson, we're going to look at performing an update operation. Now, the general workflow for an update would be that you retrieve the record, that you intend to modify, then you make the modification, and then you save the changes. So that's a typical workflow. In any application that you use, that is what is happening behind the scenes. You indicate you want to editor record, it goes and finds that record, presents it to you, make it changes, and then when you click Save, it Well, you would have made the record changes and then the save would commit those changes to the database. All right. So let us first look at retrieving the records. So what if we wanted to modify? And we have some let me just go and look at our leagues. We have some duplicate values in here. I think we have three red stripe football league records. And yes, we do. So we have IDs, two, three, and four being the same. So what I'm going to do is just change the names on some of these, right? So I'm going to change the name of three. It's a duplicate but then in retrospect, we realize, oh, that really should have been the Scottish premiership, right? So we know that we want to modify record with ID three. So what I'm going to do to retrieve the record is SVR League, z equal to context, leagues do find, and I'll just use a sync. Of course, if I'm using a sync, I have to transform my method into an A sync task, and then I wait it here. So we're going to say, find a sync, and then remember that all we have to pass in is the primary key, so we know we want record with ID three. Now, when we get that league, the change I want to make is league dot name, I want that name to now be Scottish premiereship. Then we save our changes. So we already looked at the save changes from when we're inserting the same save changes. Anytime you are going to augment data in the database, you have to call this to commit the command. Then I want to print out the result of this or I want to print back to screen this record. I'm just going to create a method called get record, which is specifically just to go and find this record. I have it defined up top here, or I'm just going to say Varg, go and find the same record. So after the save changes, then it would have closed the connection to the database. I'm just going to await get record, which is just going to go and retrieve the record and then print it back to the screen just to show that the update procedure was successful. So let us give that one a goal. So when I execute, we see here that it is doing the select, that's where it is doing the find, so it retrieves the record, and then it is carrying out that update, so it retains the primary key and the new value for this record. And then it carries out that update statement, set the name to be whatever values in that parameter, where the ID is in that parameter, and then it goes ahead and updates. And then, of course, our get record is just re opening that select query, that's a selquy and giving us back that record. All right. So let us try that again, and I'm not going to change anything. I'm going to leave the same record. I'm going to make the same change and everything is going to remain the same. And then let's see what happened. This time, we don't have an update statement. Take note of that, right? We don't have an update statement because it notices that whatever change I'm making, it's not really a change. That is the value in the database already. So it's not going to bother to waste its time to say, Oh, I need to make a change. CEFCO is intelligently choosing not to open a connection to the database and carry out a command that it realizes it doesn't need to. Now, what enables this kind of change to happen, meaning that FCR is seeing that the data that we're presenting for that field is different, so then it knows to save changes. This is called tracking, right? We look at tracking and no tracking later on. But just as a quick preview, whenever it is that we are modifying objects, FCR is actually tracking in the memory what objects it has if there are any changes made to them so that when we say save changes, it will literally say, Okay, I can see a change was made here, I have to commit this change, et cetera, et cetera. So that is what tracking allows us to do. Now, there are other things that are other ways that we can carry out an update because an update may never be as straightforward as finding that exact value and just changing one property and then just saving changes. A lot of the times when people, especially have user interfaces and they're interacting with a form, when we allow them to edit the data, we don't know what has changed. So we can't for sure, know that, okay, we're only going to update name, and we're only going to update this and that. We don't know if maybe in editing a team, they change the league and the name. We don't know. So For this other example, I'm going to use team. So I renamed our method from update record to simple Update league record. So it's clear that we're dealing with the league and it was a simple update, and then now we have simple update team record. So let us take a look at the alternative. Now, in the case where somebody using a web interface would have submitted the form, that means that we already have all the information about the record that we need at that point in time, meaning we don't have to go and find it before the update because we already found it before showing it to the user. Now the user has submitted, we have the new data. All right. So I'm going to simulate this by creating a brand new well, an object of type team, right? And then what I'm going to do is specify an ID this time. So up until now, we haven't really given any of our objects any IDs because those are auto incrementing. However, what we need to realize is that when an ID is present, of course is going to look through the database tables for the record with that ID. So I'm going to go over to the team table. And what I'm going to do is manually insert just so we can get through this activity team that is local. It is in the Red strip Premier League, but I'm going to put it in with a few spelling errors, right? It's Tivoli. This is Tivoli, and I'm going to put it in with the wrong league ID, 'cause it is not in league eight, and based on the teams in league eight, we can surmise that Sri. It's not in Syria, so we have to do that kind of update, right? So let us say that this was erroneously entered by some user, and then another user picks up on that error, and they are setting out to write that wrong. So they would have been presented with the form and then they put in the corrections, and then they submitted the form. So the object that they would have submitted would look something like this where the ID is seven, that is the ID for this team, yes. And the name having been corrected is Tivoli Gardens FC with an O instead of AW, that's a correct spelling. And then the league ID would now be the Red Stripe premier league, right? So let's use League ID two. We see that we have another duplicate, but that's fine. Let's just use league with ID two. So this is the update that this user is submitting. Now, how do we get this new record into the database? Well, we can say context dot league teams. We're dealing with teams dot update. So we have an update method, which just like the ad is going to take an object of the data type that corresponds with the table. So we have our team object. I note there's no asynchronous there's no asynchronous version of this. We have the single and we have the batch version, and we already looked at the batch range means if we have a bunch of them to update, we just handed the list and it will deal with all of them for us. Right now, we're just looking at the single. And then after that, we have to call our context dot save changes. And we'll use the A sync for that one. And of course, I have to make this a sync and then all our problems are solved. Let us take a look at what gets done when we pass in this kind of update. You'll see that it is a similar kind of operation. No one, it doesn't update once again because it sees the ID. So it knows that there's a team with ID seven, and then it will go ahead find it automatic well, it doesn't have to find it because the update statement says league ID change, name change where the ID is equivalent to the ID that it saw come in on the record. So it's doing all of that automatically once that record ID is there. So a number of let's go through a number of permutations. If we did not specify this ID, what would actually happen? And I'm just going to use another football team, let's Ciba United, and that is also in that red stripe Premier League. So let us try that one and note the difference. This had no ID. We took the ID out, and it took that as a que to do an insert. Take note of that. So the update command or the update function was saying that, I don't see a primary key on this, this clearly doesn't exist in the database yet, so I'll go ahead and insert it. Now, I'm not touting this as an alternative to the insert. I'd like to keep them very separate. If I'm inserting something, I use the add function. If I'm updating, I use the update function, or I just track it accordingly, but tracking is not always an option like we're seeing in our applications. But one thing to note, if that ID is not present, it will go ahead and add it. So that's why we always make sure to include the ID information on a form so that when it is submitted that ID is present in the record so that we can properly carry out that update. So that is a very important point to note. And one more experiment is putting an ID that does not exist. So if that ID value is completely incorrect. I have ten, and we know that we only have well, no eight teams, right, or teams with ID eight. So ID ten is completely wrong. It will try to do the update, but then it's throwing an exception on the save changes, saying that the operation failed, well, it expected to affect at least one row, but affected zero. There might have been some modification to the data therein. So you can see here it's not really telling you exactly what's wrong. We know that this is wrong because the ID value does not exist. And it is saying that it thought it would do at least one change, but nothing happened. So it's not really sure what might have happened. You need to go and do some reading on the documentation. But like I said, I want to see what can cause these errors so that when we get slightly vague errors like this, we can surmise that, okay, there must be something wrong with some of the data that I provided somewhere here. So that's really it for the update. As you can see, it's a fairly straightforward operation. You have two options once again. You can go and find the record, make it changes, and then save the changes, and then tracking will allow COR to know that, okay, this record was modified, so I have to create the update statement for it. Because even if we've got a list of them, if we made changes to only one, it will know that only one needs to be updated, right? And the alternative know or another situation might be where tracking is not necessarily an option. And the record that we get to update is not being tracked by FCR at that point in time, so we can use that update method, which will go ahead and look for the record and generate the update statement automatically. If there is no ID present, then the update method will go ahead and add the new record. Okay. 17. Simple Delete Query: And we're back. So up until now, we have looked at all of the letters in CRD except the D, which is delete. So we've looked at how to create. We spent some time looking at how we can retrieve and the different ways. We just looked at how we update and now we want to know how we delete or remove data from our database. So I've already set up two methods, one to show a simple delete example and then another one where we show a delete when there's related data. All right? So the simple delete. Let's take a look at the options that are available to us. And I'm going to spend some time with the league table because we have to do some cleanup. One, we have duplicates here and here. And then we also probably want to remove Bundesliga. So that's what we're going to be doing today. All right. So when I say context dot and choose a table. So in this case, it's leagues dot, and then start typing Delete, or rather remove my bad, we'll see that we have two options. We have removed and we have removed range. So remove like add and update deals with one record at a time, and then the range operations are for bulk. All right? So when I say remove and just take a look at what it requires to do a removal, we'll see that it needs the whole entity to be removed. All right. So typically when you're writing an q statement, you would say delete from table we maybe ID is equal to one. All right. And then it will just go for that go for whatever record matches the condition I was given and delete that. And without that condition, it will actually just wipe the table. So at least, there's a safety net here where we have to either provide the entity or the list of entities to be removed, which means that we have to make a very deliberate effort to know that this is what we want to remove during runtime. So the risk of wiping the database is far reduced in this situation. All right. So, of course, if we need to provide the entity that is to be removed. And if you just look at the documentation, it says that the remove puts the entity in a state called deleted. So it marks it to be deleted, but as you know, nothing happens until we call save changes. So if I need to provide an entity to this record, then clearly I need to go and find what I need to delete. So I'm going to put in a line right above that where I'm finding the league with the ID four four because that is one of my duplicates, right? And this one doesn't have any related records. So this is a simple delete. Just finding the one with the ID four, when we get it, then we can say, that is the one I want to remove. And then as we know, we say save changes. Of course, using the ASIC to add a s two, the method declaration, All right, and there we go. So when I call that one, and I'm just going to comment on that second call for now. When I run this operation, we see where teared up the select query to retrieve the record, and then it went out and said, delete from leagues the same query that we know and love. Of course, with that were clause make sure we don't wipe the tables in the database. All right. That is how a simple delete really works. Now, the reason I'm differentiating between a simple delete and delete with relationships is that when we have related records, a delete operation becomes a bit more sensitive. Meaning, well, there's a setting called cascade delete, which means that if I remove the record with a primary key, then every other record that has a foreign key to this one will also be deleted. All right, so it would almost be like delete you from a database. So all the cards you've ever owned and all the information relating to you will also be wiped out from various parts of the database. While that might be good in some situations that might be very dangerous in others. All right. So in this situation, if I'm going to go and delete Bundesliga with ID nine, so I'm just repeating all the same code from the symbol delete. What makes a difference is that I know that I have at least one team related to the Bundes liga, right? So that means if I removed Bonds Liga, then that team would also get removed. Just the same way if I removed Syria, then one, two, three teams would be removed. Now, going back to our migration file just so that we can understand the constraint rules when the table four teams was created and the foreign key constraint was put in, by default, it gave referential action do cascade. There are other options that we could set here. So it could say restrict, meaning you cannot carry out a deletion on any record that has related or dependent records. Set Null means that it will set all of the other records, all of the related records, the foreign key value will now become null, right? Say we'll delete the parent and just set all the foreign key values to null. And then you have no action, which is, well, it says, ignore the constraint, and then another one that says default, which would just set a default value afterwards, right? So I'm going to leave it on the default cascade, but once again, this might not always be the best situation. And there are times when entity framework core in generating the migration will actually indicate to you that If I put cascade there, it can run into certain kinds of errors because, you know, this table might have a dependency on this and that that and you get a circular reference kind of situation or different tables relying on the same data. So CO will kind of warn you in situations where your database design may not be optimal for this kind of constraint rule. All right. So with all that said, let us try and carry out our deletion on Bundesliga, which we know has a related team and see what happens. So when we take a look at the qua that's generated. It doesn't look any different from what we saw with the previous one. So you're probably wondering, okay, so where is the second delete for the related record. Let's verify that that happened. So if I refresh teams, Bundesliga is gone, sorry, leagues, and then if I refresh teams, that means Burn Munich is also gone. So the cascade did occur, but our logs only show one deletion. That is because a cascade rule is really in the database engineer. It's not necessarily an ca statement that will get generated. All right. So that migration rule was really set in the database when we affected the migration. The database itself knows that the rule is delete all related records. So that has nothing to do with FCR at that point. So that is one of the dangerous situations that potentially dangerous situations that you need to be aware of when you're designing a database, setting up your migrations, and carrying out your delete operations. Just know that by default, it will say cascade, but you can always override that to say restrict by default or anything like that through configurations. So that's really it for our delete operations, as you can see, it's fairly straightforward. We just need to go and retrieve the record. More than likely we would have the ID for the record that we need to delete anyway, we go and retrieve it and then just tell it that's the one we want to remove, save our changes, and then that's it. 18. Tracking Vs. No Tracking: Hey, guys, welcome back. In this lesson, we're going to take a look at tracking versus no tracking so that we have a better appreciation of what is happening in the background. I've already written some code can pause right here, replicate it, but I'm going to walk you through what each line is doing. We start off with the method called tracking versus no tracking. In this method, I have two statements, one, where we're getting value from the database, and I'm just calling it with tracking, and then another one where I'm getting it without tracking. If you take a keen look at the two lines, the difference that you'll note is that I have dot as tracking in the statement. The first one is what we're used to context dot teams. I want the first record that has the ID two. Right? That could almost have easily been a find except the An tracking does not work when we're using a find. All right? So if we were to try and find like we would have delete and update and so on, we can put An tracking in this kind of statement. Hence my writing of the first or default. And then for consistency I did it both times, right? So with no tracking, I can say, give me the team So don't track after you've given me, please don't track it in memory, but I want the first or default with ID eight. Now, this seems not intuitive the way it's written because you'll probably say, why don't I say, give me the first team with as tracking. The reality is that after the first or default statement, All of this pretty much becomes the object that we're looking for. So, in other words, the only thing I can do after a first star default and well, we're using the acing. So I'm just going to wrap that inside of parentheses. So after this statement executes, right? And I only have to do that because it's acc if it wasn't acing, then I wouldn't have had to do that, but at the end of a first default operation, we're going to start interacting with the actual fields that are in the object. So that is why that Ano tracking just can't come after that statement. So we have to tell the DB context that look into the teams table, don't track the items, but I want the first one that meets this condition. That is the statement. Now, the real advantage to not tracking is that it actually releases memory a bit more and speeds up performance because you can imagine that if you are retrieving 100 records all with tracking, then FCR is going to have to be monitoring 100 records, and that's just on one request. What happens to all the hundreds of records that you might be juggling in the system, right? So then the FCR engine has to work over time to be tracking all of these all the time. So in a simple operation like maybe you're just doing a read on the list, like you're listing things in the database to your user. You can always just say no tracking because you don't have to track. Things that are in a simple list, right? At the time, however, when you're about to make a change and you do a statement like this, then yeah, the tracking will be there. You go ahead and remove it or even in the update, the tracking would be available after the find so that we could sorry, that's a wrong method so that we could, here we go, made the change, and then it is being tracked in that moment to be saved, right? But then for large read operations, you can always use the Ano tracking to kind of reduce the attention that FCR has to pay to each record being retrieved. Now, what I've done after retrieving the with and with no tracking, I've made changes to their respective names. Then I'm going to show you that we can actually look at the entries. So we have this thing called a change tracker as part of the context, which is basically just going to show us information about which entity is being tracked, what the state of it is right before the save changes, and then we're going to take another look at it after the save changes. I only did this as a precaution, but I'm sure that this will get updated afterwards anyway. But we will see All right. So let's go ahead and execute. I've set a break point at the save changes line, and I have the variables in the watch. So I have the entries before save and after save. So if I take a look in the entries before save, it will give me the results view, and it's showing me that only one of the records it is tracking as modified, right? So if I expand, you'll see that the entity state here state is modified. We actually have some enums given to us by FCR, where we can say entity state dot and you have modified added deleted. Anything that pretty much would be able to do in a crude context, we can tell what change is about to be saved or what state it is in right before it is about to be saved. We see here that record with ID two, is in a modified state. Record with ID two was the one that we retrieved with trucking. We made the change, and then right before we save, it sees that it is the only one it needs to save because we were not tracking the record with ID eight. So no matter what change we made to it, it just doesn't care. So that would be the scenario that gave us this situation where we would have the record, we know everything about the record, but then we have to tell the context that this team or this record needs to be updated, at which point it will start tracking it. So while it was here, it was not being tracked. This object is in the same state as this object, just not being tracked by EFC. However, if we were to put in the manual update statement for with no tracking, then by the time it got here, it would also be listed as a modified record to be saved. All right. So I'm going to just step past that one and then do two more steps so we can see entries after save, and entries after save. Now, it only has it's still tracking the fact that it had that entry before the save. No, it is in an unchanged state. So after we save the changes, they move from whatever state they were in added deleted or modified, and they moved to an unchanged state. So that means entity framework is still tracking it right? Once it's listed here in the entries, that means it is being tracked. So there are times when you might run into some concurrency issues when maybe you're carrying out an operation and then you save the changes to the record, and then you probably try to manipulate it again right afterwards, and then you might get an error saying that this is already being tracked by EF course. So that is one of these situations. So sometimes you need to release it from being tracked, but we won't get into that level of complication, at least not right now. For now, we just want to focus on what tracking does differently from no tracking. So like I said, in a scenario where you only need data for read only purposes, then as no tracking creates a very efficient scenario for you. Okay. 19. Review One-to-Many Relationships: Hey, guys, welcome back in this lesson. We want to quickly review our one to many relationship and how FCR makes life easier in defining this kind of relationship and allowing us to interact with related records. So just as a recap, we have our league table defined as this data model, and we have our team table defined to the right. So we know that by following our naming convention, first of all, FCO was able to infer that a foreign key relationship existed between these two. What's that naming convention? Well, firstly, I would have indicated that the field name is league ID. All right. So we have the table called League and the foreign key. Just by calling it League ID, it actually inferred that there's a foreign key relationship. So just as an example, if I did not include that virtual navigation property, this would still know that there is a foreign key just because of the naming convention that I have employed at this point. Now, because of the data type being used, I'm using t and t by default cannot be null. So you'd realize that that migration that was generated would have a nullable. Here it is League ID was the column, and a ullase falls, meaning it cannot be null in the database. Well, C sharp supports nullable data types. If I said in question mark, then that would automatically be nullable. So I'm just going to run a migration just to show. So this is our migration add migration made league ID nullable. And then in this new migration file, it kind of looks different from the previous one, and we will be going through more migration. So I'm not focusing on what we're looking at just yet. I just want to highlight that the alter column is now seeing that nullable is equal to true, all because we put that question mark. I C sharp, if you make the data type nullable, then that is how FCR will know that it's null or nullable in SQL should be true, pretty much, right? So it's saying that the old type new type is TS, but it is now nullable. So that is one of the ways that we can make a foreign key nullable. So why would you want to make a foreign key able? In a situation like this, maybe you can have a team without a league. So later on we're adding more tables, one of the tables that we're going to add is a coach, a coach can be a coach. Well, technically, a coach can be a coach without a team because my profession is I am a coach, but I don't have a team coaching right now. So I'm in the coaches table, but I just don't have a team. So at that point, that team ID would have to be null if I am not employed to a team at that moment in time. So that's just a quick example which we'll look at later on. But for now, I just want to focus on the fact that we can make that foreign key nullable. Now, going back to our team, data model, I've made a slight adjustment where I've removed the integer for the foreign key and I've replaced it with only the navigation property. That is also going to try and generate a leable foreign key field. The only problem here while the foreign key field will be generated in the database without having the property in the class, there's no way to actually get that integer value or interact with that ID value, right? So that is why for me to make life easy, it's just best to interact with them both. Now, I've made that knowable, but I don't necessarily want to keep that chain. So what I'm going to do in the package manager console is remove migration. So we probably saw that one earlier when we're looking at all of the options or all of the commands that we can run. So to do this action, it says it's right here, remove migration. So remove migration will always try to remove the last or the most recent migration done. So there's our most recent migration. I can just say remove migration, and it will kill that file. Please note, however, that if and sees they're saying it reverted and it's doing everything that it knows it needed to. So the only thing though, is that if you have already committed that migration to the database, and I removed migration becomes a tad bit more difficult. But that is something that we will also look at, so don't worry about that just yet. Now, one more thing I want to point out about these foreign key relationships is the fact that on the league side, I can add a property that is a collection of items that I know are related. So when we scaffolded the database, you probably took note of the fact that the league had a collection, and collection. So this can be an collection. It can be enumerable, it could be a list. It really is up to you, but we'll just infer that a collection of team right. And I'm just going to call it teams means that league can access automatically the list of teams related to it. So think about it. In general, qua, if you wanted the league and then you wanted all the teams in the league, you'd have to find the league maybe by ID and then go and curate the teams table to say, get me all the teams with the league ID, right? Or you just get all the teams with the league ID, but then you have to interjoin on the league table to get the details of the league they're in. So we're already kind of doing that because one with team, I can get the details of the league that team is involved in. We'll look at that later on. Right? But just by putting this collection type here, I can say, get me the league with ID one and include all the teams. So automatically, I'm getting the league, I'm getting the name, and I'm getting of 2030 teams that are associated with the league all in one object. So that's another advantage. So I'm going to add this property, and I'm going to leave it there. You can do the same in your model. And the final thing that I want to point out really is I want to reiterate the importance of following the naming conventions. When you don't follow the naming conventions, you're actually fighting against a system that is designed to help you do things better. So in this particular situation, I'm referring to say the foreign key. Creation because you might have other ideas as to what you want to name this foreign key column. And I'm not going to say no. Your business rules may require you to use another column name. But then it becomes difficult because if I wanted to name this league FK, and I know this is going to break some other code that I have in other places. But if I did this, what would happen is that when I run the migration, it is still going to generate a column called League ID because FC is following its own convention, and then this is going to be a random column. A random column called league FK that has absolutely no affiliation with the foreign key. So once again, I just want to reiterate, follow these naming conventions, and life will be much easier for you. Now, in the next lesson, we're going to look at the many to many relationship, and I'm going to delve some more into some of the awesome things that FCR can do for you. 20. Adding Many-To-Many Relationships: Hey, guys, welcome back. In this lesson, we want to start looking at many to many relationships. Now, the case to def many to many relationship would be when you have many well, many records relating to many records. Within our context of our football application, or football database, we have to take into account the fact that they're going to be many matches between many teams. So many teams will play against many other teams over the duration of a season. I have on screen a nice useful app called raw dot IO. It's a web app, and it's completely free for use. And what we're going to be doing is visualizing our data structure. So I'm going to just use rectangles, nice and simple, and I'm going to call this one league and we're going to call this one team. So we know that we have leagues related to teams. Nice and simple just using an r to connect them. All right? So we have league and we have team. No, I need a new entity in the mix, and I'm going to call this one Mach. Now, a match is going to comprise, and I'm just going to write out the fields and the entities. We already know what's in league and team, but this one is going to have an ID. Of course. It's also going to have home team and away team. We can say no, I'm kind of veering away from the naming convention, right? So home team away team, and we're going to probably have time. Like I said, we're going to end up with the same home team, the same team being home team multiple times and the same team being away team multiple times, but they're being pit against each other. So this many to many relationship really needs what we call a linker table, which is a table that's going to sit in between the two related tables. In this particular situation, though, the many too many is really between many teams and many teams. So that means we have we have team being related to this table twice, So, forgive me if you're new to database development, but that's just how it goes. Sometimes you have two foreign keys to the same table from another table, right? So one table has two foreign keys here. We have a foreign key for the home team, however, a foreign key for the way team. But then this is really saying that many teams are related to many teams, many to many. All right. All right. So now that I have visualized what this database structure needs to look like and this new table, we can go over and create the models for this. So I've gone ahead and created a brand new class that I'm going to call match. And just like it's counterparts, it's going to have certain name conventions. Now, before I move on, I just want to point out that every single table or every model basically has this ID. And then there are times when you're going to have of multiple fields that probably repeat across all tables, like maybe when you're doing auditing or, you know, you have date created, those kinds of fields that maybe everybody needs to have, because clearly, all of our fields, following the same convention, all of our tables, sorry, we have a field called ID. Now, I don't want to keep on repeating this in every single because, you know, if we have 20 tables, then that's 20 copies of the same line of code. So what I tend to do is add what I call, let me call it come on, and then I'm going to add a class inside of come on that I call base domain object. This is not a naming convention either. I just call it base domain object. People call it based data object, people call it base object, whatever it is. But this is really just going to be a public abstract class. I'm only making it abstract because when it's abstract, I can't instantiate it by itself. It's really a failsafe for me, but it doesn't necessarily have to be. But I'm going to take this ID property, place it inside of base domain object, and then every other entity can inherit from the base domain object. So that way, I don't necessarily have to repeat the fields. If the field name changes, at least the common fields across them all, I only have to make the update one place, but everybody is inheriting from that one place, so everybody gets the field. All right, so I'm just going to do that for much. So no match. By default has an ID field team. And I'm not going to remove it from team, but I'm going to let team inherit. And then what you're going to notice now is that it's going to start complaining that it's seeing ID both here and in the inherited class. So that means I can safely remove ID from that class. So that's just a little trick that if you're not already practicing that you can put in to make sure that you don't repeat code too much. All right? So let's move on. We have the Match ID. Now I need property that represents my home team. I'm going to call it home team ID. Once again, this is breaking away from the naming convention. In the previous video, I would have mentioned how important naming conventions are. But there are situations where you really just can't follow the naming convention because when I call home team and away team ID, CR doesn't know or it's not going to infer that this means I'm referring to team and this means I'm referring to team. So we'll see how to navigate that in a moment. All right. So just adding the rest of the properties, I've added the navigation properties that will correspond with each foreign key. So we have foreign key, we have foreign key, and then I have the date time. So I changed to date instead of time because date can capture date and time. So we'll know the date and the time of the match as opposed to my initial design, which only said time. Now that we have this new class or model defined. We know that we have to add to the DB context. We have to let it know. I'm just going to duplicate that and add the new model to the DB set and I'm calling it matches. But I have to do something extra. So once again, I have broken away from the recommended naming conventions. I have to do some extra work to let it know that hey, you are supposed to be a foreign key. So let's go ahead and adjust the team model to let it know that it should have two lists, a list called home matches and a list called away matches. Now, remember that we did something similar for a league. We have team referencing the league table. So we know that a team belongs to a league. However, a league has multiple teams. So in the same way, a match can have a home team and an away team. So one, one, one at a time for any one record or row. But then a team, can have many away matches and home matches. So that is why we have to make sure that we put in these list navigation properties. Now, building on this, we have to let entity framework know that all of that wiring up means that there is a foreign key relationship between team and match. Up until now, of course, still oblivious to all of that. So what I'm going to start by doing is overriding our model create method. So we have configuring. This means whenever you're setting up the context, This is what you should do. Well, this one is saying, whenever you're going to create the model or do a migration, the next time you're doing a migration, make sure you have these rules in place. All right? So not to say every migration will repeat the code for these, but it just means whenever you're building up the database and dealing with the database, this is what I wanted to do. So that's what this method is really there for. So I'm going to remove that default line, and then we're going to have to let the model builder know that our entity. So we're using fluent API at this point to define certain rules, and you'll see why it's called fluent API. So our entity called team, And then just I'm just breaking line so we don't go too far out. And we're going to say dot, and then you're going to see a bunch of options here. Now, I've gotten carried away and tried every combination, but not every combination works really. So you just have to kind of know what you're doing. So in this situation, I want to say that my team has many All right. And then we just define a Lambda expression, dot, and I'm going to say home matches. A team will have many home matches. That is true. Then we go to the next line with one. What's going to infer at this point, no, is that you are going to have many home matches with one dot, and then we are seeing properties from the home matches or from the match entity, see that? So that's why it's fluent because each line is based on the line before. So a team has many home matches, and then the whole matches or a whole match a match only has one, and I'm going to say, if I'm dealing with home matches, then it only has one home team. Right? And then I'm going to have to tell it that and it has a foreign key Lambda expression again, M dot, and then I can specify which foreign key facilitates this has many with one relationship that I'm telling it about. I'm going to say the foreign key is home team ID. And I'm hoping that you're starting to see that you know, naming your columns properly. If it can't help FCR to figure out what you want, it helps you to figure out what you need to do later on. I'm also going to add a constraint to say that it is required, and to finalize, I'm going to define the undelete behavior. We already discussed the undelete behavior where we know that it's a configuration for the database. I'm going to say cascade. That means if I delete a team, I want all the matches to go with it. That may or may not be what you want to do because maybe for the archives, you will want to keep the team and all the match data around. That's up to you and your business rules, of course. So I'm going to just repeat all of this for the Away team. And you'll see that both are identical. The only difference really is that I put away matches and away based columns where they were the home columns. And that's pretty much it for defining the rules around this many to many relationships. So there are quite a few times you might have to get your hands dirty like this. In this situation, it's a very unique one because one we're not following naming convention with our foreign keys, and two, it's the same table relating to another table twice. In another situation, and let's say, I'm just going to give you an open scenario here. We had a table to store the products. We had a table to store customers, and then we have another table to store the products that customers have ordered. So many customers can order many products. So that middle table needs to have the customer ID and the product ID. All right? Think about that scenario. Now, in that scenario, it's a nice clean relationship. It's an easier relationship to define than what we had to do here. And then in that situation, really, you just need to put the list navigation property on either table. And then FCR would just infer that there is a customer product table to be generated. Sorry, I started that just now, but there should be a customer product order table, for instance, to be generated because you just told it that customer would have a list of products and products would have a list of customers. So it will just automatically know, I need to create a middle table for this many to many, and literally, it would be many being the list too many. So there are different scenarios and each scenario may come with its own quirks. This is the quirk of scenario, and I think it's good to do the harder scenario because at least you get to see what might have to be in place should you run into difficulty and your scenario is not as easy as it could be. Now, with all that said and done, let's go ahead and run our migration and see what we get. So this is our migration file. It's creating the table called Matches. We have our columns being defined, and then we have the constraints being put on the team which indicate our foreign key relationships, right? So foreign key number one is between is on the column AA team and it's between teams and that ID and deletes cascade, and it's just the same for the home team. All right. So with all of that done, let us update our database. All right. So if you updated your database just like I did just now, then you would have gotten this error. If you didn't, then shoot me a message and let me know that you didn't. But this message just came up, and like I said before, I'm not going to shun errors because these are the kinds of things that make people afraid of this kind of technology. So It is saying to us that it failed to execute the DB command, create the table. And if I scroll all the way down, it's going to say, I'm introducing a constraint or introducing this constraint, it may cause multiple it causes cycles or multiple cascade paths. So in other words, what it is saying is that having this referential the delete action cascade, on these two foreign keys can be problematic to the database structure. So maybe in designing the database manually, you probably could get around it because I have never been warned by SQL server directly about something like this, but FCR is just letting you know that that can be problematic and it has an error trying to do that for you. So What we can do and what we will do is just remove this migration for no, and following EFC suggestion that this delete behavior is problematic, I'm just going to do a restrict. So in other words, you cannot remove a team unless you have removed all of the matches before. All right. And I actually see where that is reasonable because when I said it earlier that you delete a team and it automatically wipes out the matches that could be problematic, right? And especially where a team could be on the home team ID or the A team ID. And then, you know, that is going to end up deleting data for teams that are still in the system. So that was a poor design choice on my part. No problem. I R has warned us, but once again, I just want to help you to understand what that error really means. So if you see that kind of error coming up, it is maybe because we need to change that delete behavior. Remember that it's going to be cascade by default. So even if we didn't define cascade here, you probably would have still gotten that error. So it's important to understand how to mitigate against that error should you get it. So let us try and add that migration again. And this time you see it saying that it is restricted. That's fine. Let us update database. And this time, I get done and looking at it in our SQL object server explorer, we see that we have our ID and we have our two foreign key columns that are not leable. That is one way of setting up a minute to mini relationship. Like I said, it may differ based on your situation. Given the structure of the database, this is what we had to do to accomplish all of this. These are general guidelines, of course. When you are creating your many to many relationship, you can basically follow these guidelines and you would be able to create it no matter how complicated or simple it might be. 21. Adding One-To-One Relationships: Hey, guys, we'll come back in this lesson, we'll be talking about one to one table mappings or one to one relationships. So I've already gone ahead and created a new entity model, and we're calling a coach, and a coach will be used to depict the one to one with a team because in our situation, in our database, a coach can only belong to one team at a time, and of course, a team. Has a coach, right? But then if the coach gets fired tomorrow, then let's just say that team might not have a coach and this coach might not have a team. So, you know, those kind of unique business rules or constraints can drive the rules that we put in or our database design. So let us go ahead. We already have coach inheriting from base domain object, like we said, base domain object is providing that ID property by default, so we can go ahead and focus on all the other properties that are more unique to the coach. So the first property for a coach, as you probably would have guessed would be the name. Now, I'm not getting too complicated with the database, but we know we have a coach, a coach is going to have a name. At this point, I'm not going to include any more details about this coach. But I will include the fact that this coach has a property called Team ID. So this team ID as the name suggests, will be the foreign key to the team table. Now, our team or team's table needs to have some representation of a coach. And what I'm going to do here keeping it very simple is just reference the coach. So just like that, the coach is going to be a part of a team or, you know, related to a team, and the coach also the coach table also knows that it is related to a team. I'm going to make this nullable. Now, when I was talking about the rules and so on, I went through the scenario that the coach can exist in the table without having a team. So If I don't make this nullable, then it's going to be required. It's not always required because if he gets fired, he's still a coach just without a team. So I'm making it leable so that the migration will know that in the database, this can be null. At the same time, in our team table, just by doing this, it knows that, okay, this can be nullable, so it won't have to make any additional effort for it. Now, navigational properties once again, help you to get the details of the related entities. So when I get a team, I can get the details of the league it's in. I can get the details of the coach. And I can get all the matches if I need to. From the coaches perspective, if I'm looking at the coach, all I have to go on is a team ID. So if I wanted to I could include a navigational property, I'm just going to copy and paste to move along more quickly. I could just include that team navigational property right here so that if I get a coach, I can also include the details of the team and all the matches and everything, right? So let us take a look at the migration that we can generate from this. All right, so my migration added coach team one to one. So our migration is generated, and we can just take a quick look and see that we are getting the new table called coach. And if you've noticed, we've actually skipped one of the more vital steps. I don't know if you noticed, but we skipped what I have taught it as a vital step towards creating a table, and that is to include it in our DB context. We did not include our new table called coach or coaches in our DB context. So let's look at that now. The fact that I have told a table that is in the DB context, that it references this class or some class, the migration or of course, is just going to go ahead and create a table that represents that table name. So that name is being generated based on the property name, which is coach. So this might not be the best approach if we're going to be standard with our naming convention because up until now, we've always pluralized every table name. So I'm just pointing out though that by adding that navigation property, EFCR is automatically going to insert or create that whole migration around creating a table for that navigation property. So I'm just going to remove the migration, however, because we want to kind of keep standard. And I'm going to add it to the DB context. So we have a table called Coach. Redo the migration, and then we can feel a bit better about what is being generated. So I'm just pointing out certain things that FCR will kind of do behind the scenes even if you miss a step. So you want to be kind of deliberate, but FCR is going to make certain assumptions for you based on how it knows it needs to operate. Let us go ahead and update the and that is done. So I just want to point out also that the create index kind of looks a bit different from what we might have seen up until this point, and it has a filter. Team ID is not null. So just looking at it, you're probably wondering, okay, what does that mean, especially since we told the table that team ID is nullable. Well, if we look at the statement that was generated for the create index, we are creating the unique index on that column when it is not null. So once there's a value there, that means you can't repeat that value on any other coach. That's pretty much what it is saying, but it is allowed to be null regardless. Okay. So that's really it for setting up a one to one relationship. Once again, the scenario for that would be when you know you only want an entity to be associated one time with another entity. And there are different scenarios when you have one to one relationships. It could also be that one is entirely dependent on another, meaning, it could be a scenario where we only want to have a coach in the system when he's associated with a team. Outside of being associated with a team, he shouldn't be in the database. So you do have that scenario also, but once again, your business rules and your requirements will drive the decisions you make during your design. 22. Generate New Entity Diagram: Hey, guys, welcome back. This is a quick video. I just want to show you how you can update your database diagram. So we've been making quite a few changes. We've added new tables, added new relationships, and I just want to show you how you can update the visual representation of your database. And it's pretty simple. The same way it did the first time, you just repeat that step, and it will create a new diagram and change it for you. So you just right click your project. You go ahead and go to FCR Power Tools, add DB context diagram. It will just go ahead and generate a brand new one for you, and you will see it here. So we have our new entities in the form of coach, and you'll notice that the arrows are showing you the cardinality of these relationships, so you can tell off the bat that this is being seen as a one to one by FCR. This is a one too many, and this is also a one too many. You'll also notice that the navigation properties have been updated where a match has an away team and a home team. However, team has lists of away matches. And of course, if you hover over any one of these blocks or properties, they will show you what category is navigation collection, dependent on match, and its type is list of match. So this is a great reference diagram to give somebody who might not necessarily understand or want to comb through each class to see exactly what's happening. This nice overview diagram is a great way to get up to speed with what reference is what and how these relationships are formed. 23. Inserting Related Data: Hey, guys, welcome back in this lesson. We will be going through some examples of when we will need to add records that have relationships. So earlier when we were looking at our insert scenarios, we actually looked at one and I've repeated it below, which is to add new teams with league. In that scenario, we had a league that didn't yet exist, and then we had a team, which also didn't yet exist, hence the create, right? And then we added this team while passing in the league object. And then we noticed that when we called add and save changes, what happened is that it created the league that didn't yet exist, and then it automatically inserted that foreign key relationship with the team. So you can revisit that insert video and review what happened when we did this operation. Now, I have a few other scenarios that we would want to go through. One is when we add a new team with the league ID. And I think this one is probably more indicative scenario of what would happen in a software situation. Now, when we're adding a new team, add new team with league ID, this is probably what's going to happen maybe from a web interface, right? Somebody is typing in a team name, and then they would probably indicate which league this team belongs to through a dropdown list. So you would have this scenario if you required the user to enter both at the same time, enter the team name and the league name. Then, yes, you could go ahead and manually create the objects and then do one save changes and both get committed. Another more practical scenario, though would be that you have the list of leagues and when they select from that list, they would have you know, sent over the ID of the league that was selected as well as the name of the team that they're entering. So, we already have some leagues in our database. Let me just go and fetch them quickly. So we can easily simulate what IDs would be the potential IDs for a user to select from. So let's say they wanted to add a new Serre football team. Then our code would look something more like this. We wouldn't have to create that league object, but we would create the team, and then we would pass in the league ID of eight. So in this scenario, the league already exists. We know it's ID. We're adding it to the team. We're adding the name to this brand new team, and this team is going to Fiorentina, and then we can go ahead and add it, and then that relationship will be strong. Now, one of the benefits of having relationships and well, relational databases are designed to kind of enforce this consistency in its data. We lessen the risk drastically if it even still exists of having a league that doesn't exist being associated with a team. So I already looked at the fact that we do a cascade delete. If we delete the league, then all the teams would get deleted, right? But then I cannot add a team that is related to league with ID 50 when we saw that eight was the max. CO, well, the database itself will reject that COR will attempt it, the database will give an error and then it bounces it back. So that's one of the benefits of having this kind of strong referential integrity enforced in your tables. There are people who don't like it, but you're using a relational database. Use it to your advantage. All right, so that scenario one. When Orwell two, scenario one was when we could just put the whole object in and add both and the relationship would have been created or that relating data would have been created in the background. Our other scenario, which is more practical would be when we get the ID of the related record and then we pass it up into the record that we intend to create. Now, our next scenario would be when we want to add a new league with teams. Once again, that might be a scenario where you're creating the league and, you know, you give the user the opportunity to add a new league add all teams and then they submit one time. So that means in this situation, you need to create the object for the league and you need to tell the teams that it has, and much like how the ad context would have added both objects new and then set up the relational values, it would be the same scenario here. So I've already done that function where I'm adding a new league with teams, and then a R teams is equal to a new list of teams. So let's just say that this is what the user has submitted, as well as the name for this new league. So you can tell that I copy and paste it, right? So This new league is CFA, short for man Island Football Association. Let's work with that. And these are some of the teams that would be in CFA. So when we add this new league, which is an object of type league and it has its name and its list of teams, entity framework will do the rest when we save changes. In this next scenario, we're looking at creating records for our many to many tables. So remember that matches represents our main too many table where many teams are going to play against many teams, right? So this function simply defines a list of type match, and it has a few matches with the Away team ID, home team ID, and the date of the match. All right? Now, I'm kind of doing a two for one here because we're looking at one how to create the record for the many to many. And remember that our match or our many many table generally would have the ID as well as the navigation property. So the same way that we could put in the object for the navigation property and have it added to the database, it's the same way here. But in a more practical scenario, these teams would already exist by the time we're adding a match. So what we need to do is make sure that we're referencing the IDs properly. With a user interface, of course, you restrict the values that the user can enter to values that are more than likely going to be valid values to reduce any mishaps by the database trying to enter the records. All right? So that is part one of the 241 in this scenario. The next part is the fact that I'm using this add range. So Ponto, we've always just been doing ad. Right? Add represents one. We're only passing in one object when we say add or add a sync. However, when we say add range or a range a sync, then we can pass in a collection of values, and all of them will just be added once we save changes. So, traditionally, in older versions, you'll probably put this in a for each loop and for each one in the list, you add add add add then save changes. Now, Now, in our final scenario, there's nothing really special happening here. We already know how to add one record. And the fact that we have a one to one relationship in this scenario does not change the fact that it's the same bit of code to just add one record. So we have a coach and this coach's name is Joseph Marino, and he is going to be coaching team ID three. Now, remember that this is actually a leable field. So let us say we had two coaches and one did not have a team. So I'm going to say Antonio Conte, and this coach does not have a team, right? There's the same way that I can add Joseph Marino with the team ID, I can add Conte without the team. The only thing is that he will kind of be orphan he'll exist in the table, but he has no relationship to a team. So as soon as he gets hired, then we simply update his record and he'll have that related record for the team. So as it stands, if I execute this We see both coaches being entered with no errors anywhere. Antonio without his team ID, as well as Joseph Marino with his own. So I had commented the other functions. Let me uncomment, and then we're going to just execute all of them and see exactly what's happening in the code. So let's try that again. All right, so everything has executed without any Earth. So we see we're adding Bundesliga at the top. We're adding Barn Munich, we're adding Florentina, we're adding CFA. So you can see that even when we clumped the objects, the FCR, once again, knew intelligently, which one is the dependency, Insert that get its scope identity, and then use that to insert whatever else is appended to it. So here we see we're adding Rivoli United alongside CFA. So CFA has an ID of 11, and that's why Rivoli and Waterhouse both have 11. That scenario was we're adding one league with the list of teams. For the one with the matches where we added range, you see that it just went through and added each one individually. You're probably wondering why didn't it just create one insert statement and do it line by line. Well, that goes back to the bulk operations. The FCT decided that unless you're dealing with a certain number of records, that batching those commands is probably not worth it. So that is why for smaller commands or smaller number of records, you'll see individual SQL statements being executed for them. So that's really it for how we can go about inserting related data. Once again, this might seem unintuitive because we're hard coding the data and we're using a console application. But in a web application scenario, just remember that you would have given your user form, which would limit or allow you to restrict them to insert only data values that you know you need to carry out the save operations. So when they fill out those forms and submit, you would extract that data. If it is that they were creating a new league with a list of teams, well, we see how we can accomplish that quite easily. Right? These are just guidelines as to what can happen in the back end, once you have the data that the form or the user rather would have submitted via form, you know how to build it and then send it over to FCR and let FCR do the rest. Okay 24. (Eager Loading) Including Related Data: Hey, guys, welcome back in this lesson, we're going to be looking at how we can retrieve data from multiple tables using one command. Now, a scenario for that would be if you have a report or some display of data that you need to accomplish, but then the data that you need to display is spread across multiple tables. Now, one, this is where referential integrity comes in because you know that you can always go and get the related data from another table. But then if you're familiar with traditional qua, you would also know that you have to do either some form of join. It could be right, it could be left, it could be ineric but you have to do some form of join in that qua query to retrieve the related data. It is not very different from what we would have seen with our simple select queries, except we're going to be looking at a few more things. So I have the simple select query here for reference, and we know that we execute with that to list, and that is how we get our data. Let's take a look at some scenarios that I have put together to see how we can play around with the whole concept of including related data or eager loading. So firstly, we're going to look at how we can get many related records. And the scenario here is that what if we wanted to get all the leagues and all the teams? And just think about displaying this data to a user, right? You have the list of leagues and maybe when you click on the league, you see the teams. All right? So you want to get back all the leagues and all the teams related to them, maybe all in one call, for whatever reason, your scenario may determine why you would need to write this kind of core. There's no problem. Entity Framework Core allows us to do it all. So what we would do is just like a simple select, we're going to say leagues is equal to, and then we await our context, which is going to call our leagues, and then we're going to simply say to list. So that would be what we do to get the leagues. Now, we want the teams that are associated with the leagues, right? So before our to list, we have another function that we can use called include. Include allows us to put in a Lambda expression. So you see how Lambda expressions work. No, you see that they're not unique to filters and so on. There are certain functions that use Lambda expressions. And you can always tell a Lambda expression is going to be used based on the data type, which is expression funk, and then you'd see the league or the object of whichever table you're on. All right? So we're basically saying, What do you want me to include? That's what this is asking right now, and I want to include Q dot, and then I would say teams. So right off the bat, we're going to see this quarry being run to give us back basically select star from the teams with the inner joins on the team IDs matching the league ID or the league ID or in the team table, matching the league ID from the league table. So let's just quickly take a look at that generated SQL statement. And there we see select, and then it lists out all the columns from both tables. So leagues is L, and teams is T, so it selects all of the columns between L and T, and then it's going to left join teams on the league ID matching the team league ID. So because of that referential integrity that we have enforced, F C clearly knows how to formulate that query to know which columns should map to each other in that left join. Notice it's using a left join. That means that if there's a league in the database that has no teams because we're querying the league table, it's going to bring back that league. However, the team's object will be. So let me just put a break point there at the end of that execution. So you can see what that will look like. So this is the data coming back in that league object. And if I expand, then you'll see ID two, Red stra Premier League and the teams that are in that Reds Premier League and all of the details. So this team has no coach. It has the ID seven. You see that it is related to League with ID two, and you can get the name. So right there, you can say league object dot teams dot and it just access anything you want from the leagues object. So that's the power of our eager loading. So we have some other examples that we want to go through just to show you how you can mix and match and the different things you can do based on your situation. So, we looked at getting many records with their many related records, right? So we're getting all leagues and the list of teams per league. That might not be the case. Maybe you only want to get one record and a related record or, you know, a list of related records, but only one record. So in this situation, we want to get one team and the coach details. So for this one, I'm going to say our team is equal to, and we await or context. As it calls our methods here. So if you look at it context dot teams do include. But then as we established, even when we say context dot teams, it does nothing until we put on that executing command. Now, I did say I only wanted one. And if you remember, the way to get one would be either single or default or first or default. In this case, I'm going to put first or default. So I want team with the ID, let's say, two, and the coach for the team with the ID two. Or let's say three. I think I put in the coach with ID three. We can just go back and check. So team with ID three. There we go. So I'm going to say dot include then put on my executing command, which is, in this case, first star default because that is the executing. I only want one. So first star default takes the Lambda expression where I'm going to specify that I want the team with ID being equal to. Three. All right. So that's how you chain these commands along. I'm just breaking the line so you can see where each function really starts, right? So context dot teams, get me all the teams. Please include the coach, but then I only want the first or default one where the ID is equivalent to three. So let's take a look at what we get when we execute that one. All right, so Q that gets generated is pretty straightforward. Select top, and we see the same kind of join that's going on. So because of the first default, we're selecting the top one, and then we have that were clause to filter down to where the team ID must be three. So I hope you're seeing the common theme going through. So I'm back in the code or in the watch window. And you see here that coach, the navigation property has all the details of the coach. So that is the power of our include. And one thing I want to point out, which is something that can be frustrated if you don't realize what's happening. Where you place the first or default has a lot to do with if the statement will work or not. It will just give you an error. So if I put context dot teams then first or default, I cannot do an include after a first or default because a first or default really turns this into an object of type team, and then all I can access are the properties. Because I'm using the A sync that is not being shown, clearly, so let me take off the ASIC and show you. That when I say dot, I'm really just getting the properties, home matches ID, right? So if that's what coming if that's what's coming after first or default, then clearly include is not in this list. And so we end up with that syntax error at that point. Now, in my early days, I did not appreciate this much, and I used to think that entity framework was buggy, but really and truly the order matters, right? So you want to do all of the database related things, and then you leave your executing statement for last. All right. So once again, we're going to go get the teams, include the coach, and then first or default, and those red lines are because I need the A sync, and there we go. Alright, guys. So for our next scenario, we're going to be looking at grandchildren inclusion. So when we talk about grandchildren, it's just a matter of the hierarchy. The first table that we're querying, let's say that's the parent. Then the first table or the next table that we include is the child. But then we can have multiple includes. So all the children are ones with direct foreign key relationships to the main table being queried. So those are children. So you can have multiple includes for just the children. However, there might come a time where you need additional data from the child, and then you'll have to look into another table that is related to that child. So in this situation, we're going to have to get team then matches. And then when we get the matches, you know, we either get home, or away matches. But then we need the details of the opposing team. So if I'm the home team, I need the details of the away team. If I'm the away team, I need the details of the home team. So I'm going to have to go into the grandchildren because matches will have the navigation properties for the home team as well as their away team. So once again, we leave our executing method for last, and we do all of our includes before we call that final part of it. So I'm going to start off with an include four, four our away matches. All right. So I am team number one. We want to see all the AA matches. And then we can see that. But then when we include the Away matches, all we're seeing are the details for the very team that we got, but I don't know anything other than the ID of the home team. So I can chain and say, then include then include gives me access to the properties of the child. So include gave me access to the navigation property of the table or the object that represents the table. I gave me access to that navigation property. Now, I want to include a navigation property in that child. So I can say then include and use the Lambda expression once again and say Q dot, and I can look for the home team. There we go. So I have Day matches and from the Away matches, which is of type match. I want to include details on the home team. Okay. On the flip side, and I did say earlier that all the children can be included side by side. So I do have this include, and then I have this then include, right? So, what I'm going to do, I don't want to confuse the issue. Let me show you multiple includes for multiple navigation properties before I show you the grandchildren. So we just saw an example of the grandchild, right? But I'm just taking a step back and showing you that you can say include as many times as you need to include directly related properties. So I can say, get me the teams and for every team you get me get their way matches and all their home matches. Once again, though, when it's a home match when it's an away match, I need the details of the home team. When it's a home match, I need the details of the Away team. So then I can say, after you include the home matches, I want you to include the details of the home team. And the same way, when it's a home match, I want you to then include the details of the Away team. And then we can call our executing. So whether we call it first or default or to list, but the point is that is our query to get our grandchildren. And then the thing is you can actually chain this across because, you know, based on your database setup, you may have multiple tables with children upon children or foreign keys upon foreign keys. As long as you have a foreign key involved, you can say then include, you can include. But then remember that the include allows you to directly reference the foreign keys related to the main table. And then for every include, you can say, then include, and then you can continue to then include and include. And notice that they then include does not stop me from doing an include afterwards, because even if I did all of that in one line, it's a little less readable, which is why I broke it into two lines. But you see I'm saying, get me the teams. Include the way matches. Then include this. Then I'm going back to include. But obviously, the then include can only follow the include because I can't then include after a table that doesn't have the navigation property I'm looking for and expect to find it, right? So I include the way matches, then I say, when you get the way matches, then include the home team for the way matches objects. Then I go ahead and say, also include pretty much the home matches. And when you include that, I want you to then include the Away team details. After you've formulated all of that, I only want the one with ID of one. So let us take that for a spin and see what we get. So firstly, let us pay attention to the SQL being generated. We have all the tables that we're getting. So you see, select T zero, T four, T two, all of those are there. And then we have from, but then it's using a sub query. So we query the teams where the ID is equal to one, and we call that t zero. Then we left join that on, and then we're selecting from matches, and then we're interjoining it back onto teams. Right? So the complexity of the SQL will vary based on how the relationships are really set up. In this situation, it's almost like a circular reference because I'm looking at one team, and then I'm saying, you know, when I'm looking at matches, please go back and bring back the team. So it's kind of going back to the team table, which it just queried. But that's just a situation that we have to work with when we're doing this kind of thing. All right? But then once again, you don't have to hurt your head over trying to work this out because FCO generated it for you. Now, what it returns is the team with matches and opponents, and that should have been team apologies. But then we have the AA matches, which is only one. So this team with ID one, which is Juvento only has one AA match and one home match. Okay, fine. The Away match, we know the way team. That's implied because we included the Way matches. So we know we are the Away team. Jovents is the way team. We don't need that one. However, we did need the details of the home team, and we got that. So here we see that it is InterMilan. And I think Jvento stands a good chance because they don't even have a coach, right? So then Javent may just win that away match. If we look at the home matches, it's the same situation. We are the home team. This is the home matches. So obviously Javent is the home team. But if we look at the Away team, we see that the Away team is Asma, and the coach is null, but then if we had included the coach. So what if we wanted to include the coach alongside this? So that's another let's just do that one. So what if for every team that we're including, we want to see who the coach is. We want the coach details. So I can say, once again, do then include So when you include the home team, I want you to then include Q, and then you see it's just going down the chain and giving me the navigation properties according to what is next in line. So I can just say then include the coach. And then this will bring back the details of that team's coach. So let me just do that again. And we're in the middle of looking at the home matches, so we see that we are the home team. A A team is A Roma, and the coach for ASRma is Joseph Moreno. So that's a good coach. We're going to have to bring out our A game to win that match at home. So that is how you can go ahead and chain your includes to make sure that you're getting all the data from all the related tables. And this is another strength of having relational integrity properly implemented and working with FQ. Of course is trying to make your life as easy as possible. So this just cut down a whole bunch of joins and inner joins for you because if we look at that SQL, it just got a bit bigger because now we have to inner join or left join the coach on the table. I notice that it's saying inner form and left for sum. So it's automatically knowing that if it's nullable, then it's a left join because that means there might not be anything on that side of the table. However, when it is not nullable, it will just do an inner because it knows that they have to be an absolute match for me to bring back something. I had these two other examples earmarked, but I think we kind of exhausted them all with these three because these three are quite comprehensive in terms of what they are depicting that you can do when it comes to includes. This one says with filters, we already filtered here. But the main idea behind showing you one with filters would be that if I was to say get all teams with home matches. So it's a simple of query. We await context dot teams, and I'm going to add the where clause for my filter, and then here I'm just saying home matches, which we know is a list dot count is greater than zero. So that means, get me all the teams that have at least one home match, and I want to include their coach. And of course, it's A. So that is the SQL statement that gets generated for us. You can sit down and take a look at it, of course, if you need to. But then we see that we have three teams coming back that have no home matches scheduled. So you might be tempted when you look at the data, you might be tempted to say, Well, why didn't we just check if home matches is null. And I mean, I would understand because we're saying home matches.com is greater than zero. What if I had said, ho much is equivalent to null, since that is the data that we see, but how exactly would FCO be able to translate this list object into null? Because remember that SQL doesn't know anything about lists and lists being null. So if we look at the SQL statement being generated for this, well, firstly, there is no data coming back, right? So that query, whatever query is generated, is not bringing back data that we expected. No, if we look at the SQL query that was generated, we see it's a regular select query with the left join, but then this filter is just throwing it all the way up. So that's of course, way of saying, I can't I can't make out what you want me to compare in this situation, right? So, while we may not have gotten a syntax error or any form of warning, we just want to be careful when we're adding our filters, and, you know, we might get a little over zealous with how we're doing it, and that's fine. It's good to experiment, but just be careful. So that's really for us exploring how to query related records. And as usual, I'm going to just leave that method there so you can reference it later on. I'll just clean up what we didn't go through, and you have those examples to review afterwards. 25. Projections and Anonymous Data Types: Hey, guys, welcome back in this lesson. We'll be taking a look at projections and anonymous data types. Now, you're probably looking at this topic and wondering, okay, what exactly are we talking about here? A case study for this would be that you have all of your models sure, but then you want specific data from a query, and even more so that is the only bit of data you want to return. All right? So you want to have a custom object with only bits of data from all of the dataset being returned, and that is all you really want to tax it across in your system. So let us go through some examples of, you know, when you would probably need to do something like this. All right, I'm going to show you three scenarios when you need to know how to handle a select. The first one is when we want to select one property. Like we said, maybe, we're querying the entire team table. So let's say teams. And whether it's one team or many teams, the principle would remain fairly the same, where we have to say context, dot and get the teams. And then let's work with a list in this situation, right, which of course we have to await. No, I don't want everything out of the team, because we know that what we're going to be getting is a list of objects with name, league ID. And then if we include, then the other things, right? I don't want all of that. What if all I wanted were the names? I just wanted the list of names. I didn't want any complex objects or anything else, right? So in that scenario, what I would have to do is add a select. So I would say context do teams dot select And then when I do that, I can use my Lambda expression to specify which property I would want to select. So if I select name and then execute the two list, then this is just a list of string. All right. Because name is a string, and I said I want the list of all the names of the team. So I get the list of type string. And that goes for pretty much anyone that you do. So if I wanted all the league IDs from the team table, then I would be getting a list of integers k on disappearing. There we go. A list of integers because league ID is an integer. And that's just how it would go for anything. List of coaches. All right. So that is how you go about selecting one property. Now, the scenario might be that you don't only want one property. You want multiple properties, and even more so you probably want multiple properties from multiple tables. All right. So, let us look at anonymous projection. What if I wanted to select all the teams and I wanted all the coaches included? And all I really wanted to return, however, was the list of Well, I list containing the team name and the coach name. That's all I really want. So we know from our previous experience with the includes that would end up doing something like this, let me say dot include. And then I'm going to say, make sure you give me the coach details or the coach object along with the team. But then I want to select multiple things. I can't separate this. This doesn't work. I can't say q dot. So we know in SQL when you want specific columns. All you have to do is say column, column one, come column two, et cetera, et cetera, all before the fm. That's not really an option here. So that's why we talk about projection into another data type. So on the fly, what you are allowed to do is something like new, and then you can open up a brand new object directly in that select statement. All right. And then if you hover over it here, it says it's an anonymous type. We know that C sharp is strongly typed, right? So everything in C Sharp is strongly typed. It's either string, it's an int or something. But in this situation, I'm just saying new, there's no there's nothing after the new to say a knew what, right? So it's just a new blank. But then notice there are no errors, and it is assigning it an anonymous type. So it doesn't know what data type it is. It just knows that it's some type that I want to define, and it has a property of type string called name. So it's already inheriting the name given. By the field. So in this situation, Q represents the team. So if I wanted to be specific, I would have to say team name is equal to Q dot name. And then if I hover over it again, you see that it evolved. It says, This anonymous type has a property called team name. All right. So, what if I wanted the coach name also. So I can come or separate because no this is an object. So let me just break this out into a new line so that you can see where everything is happening, where everything starts and ends. All right. So here's our new object being defined. So we have Lambda. Then we're projecting into this new anonymous type, and we are defining on the goal, which field names it has and what values it gets. So team name gets que name, and then coach name, And don't be intimidated by the fact that what you're typing doesn't come in intelligence because once again, we're doing this on the fly. And it's just going to work with us, dot, and then can say dot coach dot name. So, there is going to have two properties, team name and coach name. If I hover over teams, then you see just a list of this anonymous data type, all right? And then we're just projecting it into a list. So then after this, I can say four each, and I'll just do a four each here to print them out, for each item in the list of teams. I can console dot right line. And print team and put item dot team name, and I'll just do a pie as a delimter coach and item dot coach name. So even after this projection and this very random and spontaneous creation of this anonymous object, we can easily access the properties that were defined. So if I modify its definition, then item loses sight of the team name was a property, right? So I guess that's as flexible as C sharp gets, and it can be very convenient to do at times. All right? Now, There is a case study for this, and it can be very useful, like I just said, but personally, I prefer to have a strongly typed projection, meaning I always know the object types that I am interacting with anytime I run a query. So like I said, there might be a case where you need a custom object, and this is good for an on the fly scenario. However, in a bigger project, you want to have a bit more control than just having these new anonymous objects all over the place. So what you would want to do is have a strongly typed class, and what I'm going to do is I'm going to create a new folder inside of domain. It shouldn't go inside of domain, but let us just work with it. I'll just call it models. By right, you'd probably want to create a new project, but this is a very small demo project. I'm not going to go to Wild West with a number of projects. But in a bigger project, you don't want to have this in a dedicated space not mixing with your domain objects. But I'm calling the models because really they're models of the data. So this is a model of the database data, but then what I'm going to be creating is a model of probably like custom data that I know I expect. So let's say a model would be a cla called Team detail. All right? So Team detail is going to have a few properties. Let's say team detail has the name of the team. It's going to have the coach's name, and it's going to have the league name. Those are three different data points than we would ever be able to get just by running a regular query. We'd have to include everything about the coach and we'd have to include everything about the league name. And then it can get kind of annoying on the receiving when we have to say team coach teams. So you want to just have one object where everything is just there for you. So that's why I call it a model. All right? So we can project into this strongly typed query. So I'm just going to copy this initial query. Let me just state the entire thing. And I'm going to add and include for the team. So remember, it's your query, whatever data you need, you go get it. So I need team sorry, the league team. So we're looking at the team table and we're getting the coach details included, and we're getting the league details included. And then I'm going to select into a new instance of team detail. So we were anonymous up top, but now we know what we're all about below. Team detail has name. It also has coach name, and then it has league name. I'm going to say Q league dot name. Of the bat, when we look at what is being returned, we know for sure that we're getting objects of type team detail. So we don't have to guess and spell and say, what data type can I expect this time. Or we don't have to necessarily go to the definition to see what were all the fields in the anonymous type at the time that developer did it because when we have a strongly typed class, we can always just look at this class definition. And once we know that we're getting this data type, then we just know how to work with it off the bat. All right. So I'm just going to wire all of these up and then we're going to take a look at the different QO statements being executed for each and what data is coming back. All right. So looking in our console, we can just go through this slowly. Our first command, which was selecting one property. We see that it's saying select T name from the team. All right? Straightforward. You want one property or one column. That's how it's done. No problem. In the next one, we said that we wanted name and the name of the team and the name of the coach. All we did was to say, select into this anonymous type, and we specified the columns that we wanted. FCR generated exactly the statement it needed to get exactly that data. So it selected t name as team name, which is the alias, right? So we called the field name differently inside of our or anonymous type. Well, it gave the alias to the column inside of the SQL, and then it's left join accordingly. We're familiar with the include already. So I'm just really pointing out the targeting of the columns that we want. And then these are the teams that came back. We see Juventos has Conte, Roma has Joseph Marina, and all the others are blank. That's fine. Now, for the next one with a strongly typed projection, it's pretty much the same thing. We're selecting T name, C name as coach name, and led that name as league name. So notice I said name, name, right? So it didn't have to give this one an alias because that's already the name of the column. So the SQL didn't give it an alias, but then the alias was in place for the other two, and then it went ahead and did the joints. And then when we printed it out, we see team name, coach and Syria, Syria, and we're seeing all the league details. So here we're targeting the columns that we want. So it's going to be a much smaller query, a much smaller payload than just getting every team and every detail of every include to just bring back one time. It is just going to say, what are the fields that I am selecting or I'm interested in. Let me get those and then bring them back as a list. So once again, while this is a good facility, in a project, especially when you're working with others, I would recommend that you stick to the strongly typed models. You maybe make a different model per type of data you'd want to display on the page so that when you run the query, you extract it directly into that model, and that page is modeled off that data. And if you need to extend it to you simply extend your model and extend your query accordingly. 26. Filtering on Related Records: Alright, guys, so we're back, and we're looking at another topic, and this is going to be a fairly short lesson where we're going to be talking about filtering with related data. Now, what is a scenario for filtering with related data? I have a query here, and my variable name is wrong, so let me correct it to leagues. And I am going to query the leagues table, but I am querying the league's table on something that the team might have. So think of a scenario where you're showing the leagues, you're showing the user the list of leagues, but then you allow them to filter based on the team name. So I know the team name or part of a team name, and I want to see which league this team is in. So that means when I click submit, you need to go and get me the list of leagues where there might be any team that has a name containing whatever search term. So just imagine that this was a search term coming in from the user, by short for Barn Munich, maybe. And then we're saying here. So let me just take the time to rewrite this query just so it doesn't look as as intimidating as it probably does right. Now, we say it context dot legs where. We are all familiar with that. We know that we have the Lambda expression. And then I'm going to say where some property meets some criterion. In this case, the criterion is against the navigation property, which is teams. So I'm going to say Q dot teams And then it's a list. So I can't very well just say teams that name. It's a list of teams. So I'm going to use any because any returns a boolean based on some condition. So I'm going to say because I'm already inside of a Lambda expression, I can't re use the same Lambda token. So Q here is already tied up representing a league record. So I can't use Q in the again. So that's why I have the X. It could be S. Once again, the Lambda expression, the token doesn't really matter, but I'm just showing you why I have X in one and Q in the other because this one is a subset of the larger Lambda expression. So x dot and then no I can access the properties of the team table. So I'm going to say where the name, and then we know the dot contains already, and then the search term, and then we have our executing statement. So that's really all there is to query against related records, right? So once again, teams is not the main table. This is the main table. However, operation requires us going into the child table to match some condition. So if we look at this, our SQL statement that gets generated is just going to give us the league in for where exists and it's just going to try and select the team based on the search term or based on the criterion that we have specified. And then that would give us back one league, which, if I'm not mistaken, would be Bundes Llega. 27. Adding Views and Other Data Objects: You guys welcome back in this lesson, we'll be looking at how we can add SQL objects that are not tables to our database through migrations. Now, a case study for this, we have been using migrations up until this point to control most of what goes on in our database. We wouldn't necessarily want to have two separate operations, one where we're going to manually script in the reviews and another one where we're scripting in the tables. So it would be good if we just had a central, like focal point to our database so that we can always roll back and know that everything that was done in the previous migration can be undone through this particular procedure. Remember that migrations kind of act like a source control for your database. So in this particular lesson we'll be looking at how we can add a function, how we can add a And then by extension, the same technique that we're going to be using to do all of this would kind of apply to stored procedures and every type of function. I'm not going to get into the details of what the scripts are. At this point, I'm assuming that you're already familiar with what function is, the scalar function versus the table valued function, as well as how our views are constructed. So I'm just going to focus on how we get this Qule into the migration and by extension into our database. Now, as with any journey into anything with the database, it starts off with us adding a migration. So we're going to come to our package manager console, add a migration, and then I'm just going to call it adding team details view and early match function. As I always say, you want to be clear with your migration messages. Don't be vague at all. So I'll just go ahead add that migration. No, I haven't made any change to anything with any of the database related elements. I haven't changed the context. I haven't changed any of the classes. So you notice that these are going to be empty because I made a migration. I didn't say anything to do and it didn't say anything to undo. So we have to manually put in the code for it to know what to do for the up and what to do for the dom. So let's look at adding the function. So I'm going to get that functions SQL. And I'm going to say migration builder. So what would have happened really and truly is that I went into SQL and made this. I went into the Management Studio, made the function manually. That's fine. But like I said, I don't want to add it there because I want one point of entry to all data base based modifications going forward, right? So I'm going to say migration builder dot, and then I can say SQL. Which is going to take a parameter of type string. So it's expecting the cual command as type string right there. So what I'm going to do is use my a sign to turn this string into a literal string, and then I just paste that qual statement right in there. So that is what that is going to look like. So migration builder Q and then you pass in any Q. So although it's Q to create a function. It's Q to create a view like we're about to do or a stored procedure, whatever it is, that's all you really need to do. So I'm just going to copy that, and I'm going to repeat that step with the view. So in this particular migration, I expect to create that function and create this view. Now, I did say that and I said this from before, so we should be familiar with the fact that the up means the change I'm about to make. The down means the changes that I undo or the things that I do whenever this migration is being rolled back. So if I have the create statements inside of the up, that means I need to put the drop statements inside the down. So I'll just do that migration builder dot SQL drop view, and then I give it the name and then drop function with that name. So this is our first time getting our hands, you know, dirt here inside of the migration files. Let's go ahead and do an update database and watch the magic happen. And my experience was not very magical because I have an error. And as I'm looking at it, I see that I have an error in that line. So let me just correct it. I had gotten overzealous and repeated the word matches. So let's try that again. You probably detected it and didn't get that error that's perfect. And at that point, everything is done. And if we look in our database real quickly and look at our views, then you'll see the view appearing there as well as on the programmability and functions and the scalar functions there we have our function appearing. So this migration was a success. Now, when we come back, we're going to review how we can interact with a view. Later on we'll look at how we interact with functions and other scalar operations where we make a call and expect data, but then with a view, it's not really a command, we're querying, and it's going to be slightly different from how we query our tables, and there are certain rules that we have to know about. So when we come back, we'll look at the modifications needed for that. 28. Querying Keyless Entities (Like Views): Hey, guys, welcome back. In this lesson, we are going to be building on what we did in our previous episode where we created two non table qual objects in the form of a function and a view. So we're going to continue with interacting with the view because a view in practical terms is really like a read only table from the qual side. So that means we would like our application to be able to query those views similar to how we can query regular tables. So what we're going to have to do is create a new data class that corresponds with the view and add it to the DB set. So I've already kind of done this where I created a new class. I put it in the domain project. I call it teams, coaches, leagues, and I just appended the word view so that you know, at first glance, we can tell, this one is a view versus the others. Whatever prefix postfix, that's up to you. I'm not being prescriptive. I'm just saying that this is my convention to know the tables different from the views. All right? You could also create a whole new folder and put in a call it view, sorry, and put all of the view related models in there. However, you want to separate them, that's entirely up to you as long as it's clean and easily identifiable. Now, after creating that class to represent the view and the data going back from the view, what we want to do is let the DB context know about this view. So I'm going to add a new line in this DB context where I'm going to say DB set, give it the data type, and I'm going to just call it the same name as the view in the database. In our program dot CS, we're going to follow suit with what we've been doing up until now, and I just created a method query review, which is just going to have the sole purpose of calling context, the teams, coaches, leagues, and putting it to list. However, we're going to see if we will really get back results and if we will get any errors along the way. So let us take this for a spin. Now, as soon as it hits the code, I'm greeted with this exception, and it's saying the entity type requires a primary key to be defined. If you intended to use a keyless entity type, you have to explicitly you have to explicitly let it know, sorry, that it has no key, right? So that is expected, and that's an error that I wanted us to see together, because sometimes you're blindsided by that error, and you're not entirely sure why you're getting that error. So FCR, like we've seen thrives of relational integrity. Primary keys being defined as primary keys, foreign keys being defined as foreign keys. And with that, it knows exactly how to make the queries efficiently, how to track, if anything is being changed, and how to just monitor everything happening in the context during our request. So by that standard, our new table or new entity rather. It doesn't know if it's a table. It doesn't know if it's a view because we added it to the DB context, much like how we added every other table. So as far as entity framework is concerned, it's going to treat it as though it's a table. However, that exception was saying, I don't see a key on this table. We can't put a key on it. It's not a table. It's a view, and yes, it has no primary key. So the exception that in the model builder, we have to let it know that it has no key. So I have to say model builder dot entity with the data type, and then we just specify has no key. So when it's creating it knows that, all right, I'm aware that I shouldn't try to track this. If I find a table or anything that matches this, then I know exactly what to do. Now, another thing that we would want to do at this point is say two view and two view basically allows us to specify the name of the view in the database that it should look for. So this is like an extra precaution to make sure that it doesn't see any of this as any new elements or car subjects or new work, it has to do the next time we do a migration. I'll just know that, Okay, well, this data type, this class type, or this model directly maps to the view in our database by that name. And it has no key, so don't do any tracking. So what I'm going to do is execute that a bit of code, again, and I'm going to leave that break point so we can look at what's happening in the change tracker. So this time it hits the breakpoint. When we look in details, we would see we're getting back details because the view is executing correctly, and we're getting back coach, we're getting the league, and we're getting the name as we expect. And then the context really doesn't know anything about anything to track, right? As far as it's concerned, it did its job and it's done, and it's moving on with life. So that is how we handle situations where there's no primary key present because you might you might end up with a table without a primary key or an ID element like that. Perhaps you're forced to deal with a legacy database where those things aren't being enforced, like how we are encouraging you to enforce them when you're using entity framework core from scratch, because those situations may exist. But then when you have that has no key, battle to fight, then this is the remedy. You put it in the model builder as soon as I can find a context, you put it in the model builder, let it know it has no key, and then you can directly map to the view, or if it's a table, you could actually say dot to table and specify the name. So there might be times when you have a mismatch between the table name given in the DB context and the actual table, you can always say to table and then give it that name, much like we did to view. Okay. 29. Querying with Raw SQL: Hey, guys, welcome back in this lesson. We'll be looking at how we can run queries using raw SQL. Now, up until now, we've been writing everything using our link, syntax, and our C sharp and that's all been perfect. But there might just be a situation where you need to write some raw QL, and especially now that we're dealing with non table related objects, and we start to see that it becomes a bit more complicated. You know, how do you execute that stored procedure or that function? How exactly do we do this, but maintaining the whole use of entity framework core. So here we're going to look at two examples as we work our way through these different scenarios. Now, we have two functions that allow us to execute raw cual commands, and that's from Q R and from Q interpolated. Now, note the syntax, it context do teams. So we still have to specify our table, and then we say from QL R, then we can put in our raw cual statement and then to list. Now, I must point out some of the limitations here and the dangers associated with this raw QL. Once you are using this function, you actually open up yourself to the potential of SQL injection if you're not being very careful. So that's why they gave you from QL raw and from SQL interpolated, right? So we'll see the difference in a few moments. So let's look at what we get back with from Q R. I just comment out the one that we're not using just yet. And from SQL raw, we have to say something like select star from teams kind of counter intuitive, right? We just said context do teams, and then I have to say select star from teams again. But let's see what we get back when we test this. Now when we check our results, we see that we do get back the list of teams and we're getting back every single thing. We did say select star, so we are getting them back and everything is all good. Now the thing with the raw SQL is that it has to return columns that exactly match the DB set or the entity behind the DB set. So in other words, if I just wanted but the names of the teams and I said, select name from teams, let me keep it simple. ID not simple. Let me use ID common name. I only want two columns from the teams table. If I attempt that, then we end up with this exception saying that there are certain columns not represented in the query set because it tried to query only two columns, but it's expecting. So that's one of the limitations with this raw SQL command. So, number one, the query or the result set must return all the properties of the entity type. Another thing is that the column names must match. So even if I was to list all the column names, I can't give them aliases that don't map back to the original entity type in our class called team. And then another thing is that we can't inter join, can't directly have related data. However, I can, at this point, say, include Okay. So after writing out the raw SQL, I can still do my include statements, and it would work just like how we know that it will work regardless, right? So if I do that and include the coach, then we'll see for our teams, and I think team number three has a coach and there we go, we'll see the coach entity being included in the form of Joseph Moreno. All right. So that is one of the things that you can do. If you ever need to run this from EQ raw and need related data, just know that you can do include just the same way. Now, you're already familiar with SQL at this point. So you know that if we wanted to add a filter, for instance, if I wanted to find the football club where the name is equal to this variable, and we've done it in the past where we actually accepted user input and then use that user input for the filter, right? So let's just say that we want to use this variable for our filtering. We can always just say, where name is equal to, and then we pass in our variable. So interpolation would have us put this dollar sign before the string, and then we can just do our curly braces and then place name in that area. Now, if we take a look at the qL that gets generated for that, we will see which we didn't necessarily look at the last two times That it is generating our SQL statement, and it's doing the left join automatically because of the includes. But then take note of this now. What we're doing here is passing in select star from teams where name is equal to. And notice that it is passing in the value, but it's not passing it in with quotation marks, because in SQL, we would have had to put where name is equal to. Open maybe single the value and close single quote when we're dealing with a string. As a result, it is complaining that it cannot find the keyword as because A S roma is being seen as AS command in ques. So to remedy that, in our interpolated string we have to put our single quotation marks around the values. So let's try that again. And this time we are hitting our breakpoints. Our query was successful. But once again, if you look at this query compared to previous queries where we did filtering, You notice that there are no parameters. It's passing the literal value directly into the query, A K a bad practice, QL injection. All right? So if you ever have to end up taking some parameter and using a raw QL command to execute that, then this is what's going to happen, and you have to be very careful because if somebody passes in some malicious command as their input, then you would have gone back to square one before the days that entity framework was developed to help you prevent Qual injection. In a situation like we employ the services of its close cousin from qual interpolated. If you look at this one, it's asking for a formatable string. From qual is only asking for a string, but then from sc interpolated is asking for a formatable string, which means that it will only accept one interpolated string. And two, we don't have to treat this car statement as literal as we did just now because it is going to interpolate it. So it's automatically going to handle the parameterization of the command, so we don't have to put in the single coats around the interpolated or the injected value into the string. So let's take a look at that qual and compare them. So you see, we looked at this one just now, where it passed in the literal value. Whereas the second one is actually going to say select star from teams where name is equal to parameter zero, and it has defined the parameter up top. And that is the difference between from raw from scle raw and from escule interpolated. So I would always recommend that if you have to mix and match with variables to pass in a raw cual statement, use the qua interpolated for your own protection and peace of mind. 30. Add and Query Using Stored Procedures: Hey, guys, welcome back. In this lesson, we'll be taking a look at interacting with stored procedures using entity framework core. Now, the case study for why you would need to interact with a stored procedure comes from the fact that maybe you're dealing with legacy systems where a lot of the logic is in stored procedures in the database. So in the rebuild, you don't want to double the work, rewrite them, you just reuse them. So in that situation, you probably need to just know how to make stored procedure calls from FCR. So I have a very simple one that I've constructed for the context of or database where I just have SPG team coach, and it gets a team ID, and then it returns from everything from the coach's table. Now remember that when we're dealing with RwQUL we have to return everything that corresponds with the matching entity type. All right, so you can't just say name. You have to if you're going to list them out, you have to list them out in the order of the column names with the same column names, no Ss and anything. So like we did with our views and functions, the first thing that we're going to do is create a new migration. I added a migration saying added SPG coach name. I messed up the name of the migration, but we'll continue. And then I put in the migration builder to create the procedure and similarly to drop afterwards. So you can go ahead and do the migration and do those steps, and then we can just update our database. And then that allows us to start interacting with our stored procedure. Now, in our program CS file. I've already gone ahead and written some sample code, and the new method that we're working with is stored procedure. So in store procedure, I just hard coded a team ID, and then I'm going to pass it into the raw SQL statement and await the result. Now, let's take a look at the syntax. I say await context dot coaches do fc R then I call my execution to get me SPG team coach, and then I have a placeholder. Now note that I'm using from SQULRw In the previous lesson, I stopped short of condemning it saying that it was bad because it opens you up for cual injection. Reality is that it's based on how you use it. So I showed you the bad way to use it initially where I used an interpolated string and pass valid directly in. So all of this got translated into literal QL, which is the worst thing that you can have between user input and going to the database. Parameterization is always best, hence our preference for the interpolated. But then let's take a step back and not condemn from SQL raw too much because if we take a look at its overload, it really says, give me the string SQL and then give me a list of parameters. So that is exactly what we've done here. I say DBO dot SP get team coach, and then I use a placeholder. And then after that placeholder after that string, I have now included the parameter. So when you do it in this manner, it will actually handle the parameterization for you just the same way that we saw from Equal Interpolateed do it. So it's not the worst method and it's not completely usess because you probably left the last lesson wondering. So why did they put it if it's so dangerous? There's a good way to use it and there's a bad way to use it. So, I showed you the bad way. Here is the better or more recommended way to use it. So let's take a look at that. Now, when we take a look at what we get back, we see that our query is being generated and parameterized, like we said, or expected it to be. So there it is taking that parameter of three, executing a stird procedure. And then our result is Joseph Marina's information as the team coach for team with ID three. So that is how you can execute a stored procedure. And pretty much that's how we execute commands against a database that are well, query commands. We're asking the database for something and we see getting returned. So in that situation, that's why we have to execute these raw QL commands against a table. In each situation. Now, what happens when we need to execute a stored procedure that is not associated with a table or query is something that's not directly associated with any entity type. So we need raw QL against some object that is not associated with an entity type. That's what we're going to look at in our next lesson. Okay. 31. Executing Non-Query Raw SQL: You guys will come back in this lesson, we'll be taking a look at how we can execute non query commands against the database. So an example of this would be when we want to delete or update something that manipulates or augments the data, but it doesn't necessarily return anything to us because we're not selecting. So up until now, we've been selecting and selecting and selecting. Let us look at what we do when we're not selecting, but we have to execute this kind of command. I have on screen a new migration delete team by IDP, SP short for stored procedure, and I have the code to create the stored procedure called delete team by ID, which takes a team ID as the parameter, and then it deletes the team accordingly. We have the up and down methods I can pause, replicate those, and then go ahead and update the data. Now, after you've completed that successfully, you can head over to the program dot CS and you see already created the method execute non query command. Now, when we're going to execute the non query command, there's a few things that are going to be different between this and when we know that we're expecting a result set and I'm trying to bring up both sets of code on the screen so we can make a comparative analysis. Number one, we are only going to be told how many roles have been affected. Unlike when we looked for the teams, we knew that we were getting back a result set of teams. In this situation, we're only going to get back some variable that says, number of rows affected. All right. So that's all we're getting. That's one. Two, when we were doing the other ones where we knew we were getting back data, we knew which entity type or DB set to execute it against because you wouldn't expect to be querying select star from coaches but context that team. We know that you would just get an error when it tries to execute that method altogether. However, in this situation, since we don't know what the stored procedure is going to be doing, we don't know which table it's directly interacting with. We say context dot database instead of context dot DB set name. So context dot database, and then the methods that we get are going to be slightly different. In the case of the DB set, we get from CLR and from Q interpolated. In this situation, we get database dot execute SQL raw and it has an asynchronous version to it. So we have raw and we have R A sync. Hence the wit, where is in the Async one. And then similarly, we have executes our qual interpolated Async. So we have the interpolated version and we have the raw version. Now, we already explored the bad use of the raw method and the more acceptable, the safer use of the raw method. The same thing applies here. We don't know what the stored procedure we'll be doing, but we do know that we need to pass in some value, which is more than likely going to be coming over from our user. So we want to protect ourselves and use a formatted string with a placeholder, not the interpolated string when we're using the execute Q R A sync. And then, of course, if you don't want that additional responsibility of thinking so much, no problem. That's why they gave you the alternative for you just pass in the interpolated string, and it does the same for you. So let us take a look at what we get when we execute these methods. All right. So I'm looking at the kill that has been generated, and I see here it has been parameterized with team ID two, and everything looks like what I expected. However, it's telling me that it failed. Why did it fail? That's because of that foreign key constraint that I have on the record. So I have related matches to team with ID two. So I can use that one. That's my bad data. Let me try again. All right, so I'm adjusting my values because I just want to look for some teams that I know don't have any matches. So we shouldn't have any foreign key constraint errors again. Let me try that again. And we hit the break point this time, which means that everything got executed properly. Now, if I look at affected roles LC, one. If I look down here, I see one. So it only tells you, that we executed this successfully, and this is the number of rows that got affected. So if you wanted some flag to say, was it successful or not, then you could always say, is the number of affected rows greater than one, then we can say it was successful. All right? So that is really it for executing non query QL command statements using raw QL. Okay. 32. Seeding Data: Hey, guys, we'll come back in this lesson. We're going to take a quick look at one of the roads less traveled, but essential to know about, which is how to seed data. Now, data seeding, if you're not so familiar with what I'm talking about, is the act of putting data into the database at the beginning. So as soon as your application is installed, there might be default data that you need in there, maybe like a list of countries or certain roles or like a default user, things like that, you probably just want those in the system at the time of creation. As soon as the database is created, these things must be in there. COR allows us to hardcode that so that whenever our database is being generated with this scripted or we're running the update database to get the database up with all of the migrations, we can actually put in code that will be seen as a migration so that when that migration is executed, that data is automatically put into the database from the get code. So that is what we want to take a quick look at in this lesson. Now, the simplest way to get seeding done is to do it from the model creating method. Under all of this, we can say model builder dot, and then we specify an entity. Let us say we have teams, we have leagues, we have matches, we have coaches. Let's say I wanted to seed some coaches into the system. I'm going to say entity coach and that entity coach has data, and then this has data allows me to specify As many coaches as I need. That's not ci brass that's parentheses. And then in these parentheses, I would now start giving it new coach objects. So I can say new coach. I can specify the ID from the get go. I'm going to use some IDs that I know won't clash with the existing ones. ID 20 name is equal to I'll use my name in this situation. And the team ID. Well, I can leave those as ll because I'm seating the coach. Now, that brings another important point. If you have hierarchical data, then you need to make sure that you're seating according to the order or the level of dependency, the same way that we want to create the tables with the dependencies the same way that we need to seed the data with those dependencies. Because I can't be expecting to put in Trevor Williams, the coach of Team ID five, but then I'm defining the team with ID five after all of that. All right? So let me show exactly what I mean. If I say team, I want a new team, I'm going to be seeding a new team and this team is going to have let's say ID 20. The name of the team is Trevor Williams Sample team. All right. I can't be telling this coach that its team ID is ID 20 because the order in which I have defined these seed commands is the order in which the car statement is going to be generated. So I can't be creating coach and inserting coach with team ID 20 when team ID 20 doesn't yet exist. So I have to make sure that I maintain my ordering according to the levels of dependency. No, like I said, you can put in as many as you want. So I have new coach here, and then I can just separate as many objects of new coach as I need. So if I wanted three coaches in the system initially, of course, the IDs can clash. Then let's just keep that name, sample one and sample two. And then for the teams, well, I can't be the coach for the same team over and over and over. Because we know the constraints. So whatever constraints exist on the database, of course, will govern and the limitations you have when you are doing your seating, right? You just want to be mindful of all of that. Of course, once again, this is probably when the system is just installed. So I'm using IDs 2021, and 22 because I have teams and coaches in the system already. I don't want to clash. However, in a brand new system and a brand new paradigm, you would probably start off with one, two, three, four as your IDs because those are the IDs that you definitely want in before anything else. Now, If you were to do seeding for multiple tables, you can see that it can get quite cumbersome and Honestly, I don't like seeing all of that in the model creating. That's too much code. I want to keep it a bit cleaner. So what I do or I would recommend you do in that situation is extract these into seed configuration classes. So what I tend to do is I create a folder and I'm going to do it in the data project near the migrations. So I call it configurations, and then I have another Folder in there because there might be multiple configuration types that we might want to do. I'm going to call that one entities. And then inside of that, I'm going to have the different configuration classes per entity type. So for instance, I want a coach CD configuration. Now, in this class, which I'm going to make public, I'm going to inherit from entity type configuration. So I'll just go ahead and grab that reference, and I'm going to tell it it's for the type coach. And then include anything that is missing. So for the type coach, I want that. So now I'm going to have to implement this interface, which gives me this method configure and a local builder. So now that I have this builder, this builder basically resembles the same purpose as this model builder object, right? So I can just say has data. I'm just going to take this part as for team, let me just take the has data part of it. I'm going to cut I'm going to erase all of this from the DV context. And then over here, I want to say builder, dot and then put on has data. So everything I had has data and beyond fits in perfectly in this method. So Builder dot has data, and then you can put in as many see records in this dedicated class. Now, of course, if I'm moving into a dedicated class, I need a way to make it reference or make reference to it in the DB context. So back in the DB context, I'm going to say model builder dot, apply configuration, and then new and just pass in the name of this method or sorry, this new class that we created, which is a new configuration class. So I just pass in a new instance of this, and that is it. So as many configuration classes as you create, you just need to pass in these lines. Once again, maintaining that order. So I did that for coach or coach seating. Let's do the same thing for the teams, right? So I'm just going to kind of play lazy here and just copy and paste the existing file and just change the name to team seat, configuration. And then I'm going to update the references in the file. So this is four type team. This is for team. And then our has data section is going to come directly from this. I'm just going to cut and paste over in its dedicated configuration file. And back in the DV context, I can just duplicate this and say team seed configuration, and there we go. So that looks much cleaner to me, and it is just as effective as having everything in the file. But of course, this method keeps a bit more kosher and looks a bit better when we do it like this. So let us take a look at what we get when we try to add a migration. And when I do that, I call it added default teams and coaches. I'm now seeing that the migration builder is doing something different. We've never seen it do that before. So it's always create table or alter table. Now, it's insert data into the table teams with these columns with these values, and it's doing that for everything. And once again, that order matters. A see it took care of all the teams. And then it's going to take care of all the coaches which have dependencies on the teams, right? So imagine if these were mixed up, we'll be trying to insert a coach with a team ID 21 before the team with ID 21 was even created recipe for disaster. So just remember that ordering matters. And in the D, the delete data is just reversing all of those inserts with that delete statement. So if I do an update data base, I'm going to get this error, all right? I'm getting this error saying that I have a conflict with the team and the league ID column. All right. That's fine. So in that situation, I have to modify what's happening with the team because I didn't complete the data, so you have to be aware of that. You have to be mindful of the fact that your constraints will govern if the data can go in or not. So league ID cannot be null. That is a constraint that is on the database. A team has to be in a league. So I can't be seating teams with no leagues. So I'm just going to quickly seed a sample league. So following the same steps, and I'm going to encourage you to pause and try it yourself. But what I've done is to create a new class that I'm calling league seed configuration, following the same steps inheriting from entity type configuration of type league. And then we're just building one league with the ID 20, once again, to avoid any clashes in the database, and the name is sample league. Then I retroactively go and update my team seed configuration to add that league ID to each of these teams, and by extension, the DB context where I now have that league seed configuration happening before everything else. Now, I made a mistake, all right? I generated this migration when the data was incomplete. Now we see that this migration is failing. How do we roll back? Well, the first step is to remove the migration. So that will always remove the most recent migration. And then I can just go ahead and generate it again. And this time, if we look, we see it's creating the league first, then it's going to create the teams and then the coaches. So let us take another stab at the update database. And after levels of logging and being very verbo, we see done, and we know now that we have this data. So once again, this is now a migration. So it is expected to happen along the chain from initial migration to the very last migration that you might have for your database. So there might be times when you have to introduce different lookups and different tables along the way, and you need these values in as defaults from day one. Well, this is perfect for that. You just go ahead add in the configurations, and then you can just put them in the DB context. So the model builder will know that when I'm doing my build when I'm creating the model, I need to be aware of these configurations for seating. 33. Rolling Back Migrations: Hey, guys, welcome back in this lesson, we'll take a look at rolling back and managing migrations in general. So we've done quite a few activities where we've made a few changes to our database, and with each change, we made a migration and then updated the database. But what happens when you make a migration that you don't quite want or in retrospect, you want to roll it back, make an adjustment, and redo it. Now, in this example, I've already made a migration. I'm going to show you the changes I made to trigger this migration. I changed the base domain object to have these four fields. And these fields are usually used for auditing purposes. You know, when you have people entering data into a database, you want to know when it was created when it was last modified and created by whom and modified by whom. Now typically you'd want these to be kind of clear. So I deliberately kind of name them badly, just to show you like a mistake that probably would warrant a correction or a rollback or something like that. So in this situation, if you just look at the column names, they're not really very cohesive, right? Date created last modified. If I'm just looking at that name, I don't know if this is last modified date or this is last modified by whom. I don't know if this modified means modified date or person who modified it. I don't know. You understand what I'm saying. So these are the things that if, you know, if you are obsessive with these details, then these things will kind of look ugly to you. However, The point is that I've made these adjustments to the base domain object, which, of course, is being inherited by every other domain object. So when I generated that migration, which I just called added audit fields, you can go ahead and do that so, you know, you can work alongside me. But when I did that, you noticed that it just added those columns to every single table that exists. However, up and down. And then I went ahead and updated the database, which was a successful operation. So as of now, every field in the database has these audit columns. Now, what happens when I am no longer satisfied with this? So we have seen already that when we generate a migration and we realize that there is maybe a mistake with it, we can always remove the migration. But look at what happens when I try to remove the And if I go to the command and say, remove migration, everything builds and is successful. But then I get this error saying that the migration with that name has already been applied to the database. Revert it and try again, and if it has been applied to other databases, consider reverting its changes. You see all of that. In other words, this migration has already been applied. So it cannot just remove migration because remove migration actually deletes the reference or the record of this migration file from the folder. So it is a fail safe it's saying, I've already made note of this change to the database. So I cannot just go and delete this from the history without you modifying the database. So that's what we're here to do to understand how we handle that rollback. And it's a pretty simple procedure. I don't know, maybe this a message could have given us a bigger hint or a better hint as to how we could revert the database, but let us do that together. So reverting the database really is an update database activity, right? Kind of sounds contradictory, but just work with me here. So to revert the database to an earlier point in time, what we need to do is run our update database command and actually tell it which migration in time we want to update to, which if it is in the past, it's really revert to. So I want to revert to the migration that I did where I added the default teams and coaches right before I added the audit fields. So I'm going to simply just double the file very slowly, or you can rightly can say rename, and I'm just going to copy that file name. I don't need the CS. I just need the name because this is the name of the migration that is actually stored, that timestamp underscore and whatever verbiage you put behind it. So in package manager console, I'll say update database again. And then I will just pass in the name of the migration in quotation marks and press enter, And then you see that it has actually done what was in the done. All right? So that's all we talk about when you have the up and you have the do. So the commands that executed just now were to remove from everything there. It's altering and it's dropping the column modified. If you look in the do portion, well, the migration file, sorry, if you look in the done portion of this migration file, that's all it does, it tells it to drop the columns. So that is a practical example of what happens, we one revert and two when this down actually gets called into action, it actually undoes everything that the up did. So now that we have reverted or updated the database to a previous version, I can actually go ahead now and safely say remove migration. It will do it willingly happily and without any queries or qualms, and there we go. It has removed that previous migration. I'm just going to make adjustments to my based on main object. There we go. I've updated these column names to be a bit more descriptive of what is going to be in them. Create a date, modified date, created by, modified by. Now after doing all of that, we're just going to add our migration again add the date audit feels. Okay. And when that's done, I just want us to take a look at what happens with our date column. So these are date time columns and they're not nullable. What happens is that when you have a nollable date time field in the database, or it has to put in a value. So that's why we get that default value everywhere that this date time column is going to be added, right? So we can't have no date in a nullable date field. So it's putting in that default date, which in the database, you'd probably see some 01010001. It's just a default date, I guess, since the beginning of time. All right. So with all of that done, we can go ahead and update our database. And I'm going to run that command without specifying any migration, and it is going to be a successful operation. There we go. So now, our database is equipped with at the very basic level some auditing fields. So when people are entering, of course, we want to know when did they create that record? When was it last modified, and then who created it and who last modified it. 34. Manipulate Entries Before Saving Changes: Hey, guys, welcome back in this lesson. We'll be looking at how we can manipulate entries before we save changes. Now, a little background to why you would want to do something like that, would be coming from maybe the fact that we just added audit fields for all tables, right? So we just added all the audit fields in the base domain object. And evidently, this is a bit more work to do because we've seen that when we're adding data, we have to formulate the objects and then add them and add them to the context and then save changes. Now, that means that every time somebody adds a team or a league or a match or a coach or anything to the database or modifies it, we're putting extra responsibility on ourselves or our developers to say, always put in the created date, always putting a modified date, at least if you want the data to be standard. Now, doing that in a bigger system can get very cumbersome because we are only working with four tables, and I'm annoyed already just thinking about it. Imagine when you have 2030 and more tables dealing with. So at this point, it's good to understand how the context actually gives you access to everything that is about to be saved, and you can actually manipulate what you need to manipulate before you save the changes. I'm going to hop over to our data context here, and it's going to be a quite interesting experience now because we can override the save changes, right? So we see quite a few options of save changes to be overridden. We have the default one, which is when we call context dot save changes. We can override it. And then we can carry out some operations here before we say actually save the changes. You'll notice here that save changes really returns an integer, which is usually more than one when it is a successful save and less than one or zero when it wasn't successful. So if you need to, you can probably incorporate that into your checks to see if it was a successful save operation or not. But for now, we're not going to focus on that what we want to do is to know how we can intercept everything that is about to be saved and manipulate of these values before they get saved to the database. There are quite a few things to be aware of when you're in this area. Firstly, there is an object given to us called change tracker. I think we looked at this earlier, but let's just look at what it allows us to do. So we have changed tracker and then we can say dot entries. This actually gives us the list of entries going in to the save changes are being tracked by the context in memory. I can say VR entries is equal to change tracker dot entries. Okay. Another cool feature with being able to see the entries is to interrogate what we call the entity state. So that's an enum given to us. A enum is just a constant, and this constant has different states that generally represent what state your entry would be in by the time it hits the save changes. So we have unchanged. Meaning maybe you did a query, the tracking was on, so it's tracking it, but then you didn't update it, you didn't do anything to it, so it's in an unchanged state. Added, I think that's self explanatory. You're about to add something to the database. So anytime you create an object and say context dot add and then put in the object, it's now in an added state. Detached means that it is not being tracked by the context. All right? Modified means that well, you took it, you changed something in it, and then you said, here it is. So it was either being tracked when it was changed. So now, it sees that it's different from what it was tracking initially, or it wasn't being tracked, and you explicitly called context dot update and passed it in. And then there's actually another way to update where you just mark it as modified, right? So you can actually just put it in the entity state modified so that the tracker will know that, I should be tracking this as a modified object. Deleted self explanatory. Anytime we say context do delete and give it the object, it is now in a deleted state. So those are really the entity states available to us, right? But then in certain situations, we may need to track some and not track some. So with the entries, I don't want to see every single entry. I don't need anything that hasn't been modified or hasn't been added, at least for my auditing purposes. Now, once again, I'm just giving you a blanket concept that you can probably adopt for different reasons, right? So you might have some complex audit logging or a second database that you need to write logs to. You may need to Track, when was this added. When was this modified? Is this are to be deleted and write that to a separate data store, whatever it is, the change tracker allows you to intercept these entries, do what you need to do all before the save changes gets run. So let's take a look at that. What we want to do is get all the entries that are about to be modified or added and then update the respective columns, right now, we're just going to focus on the dates. Okay. Right? And then we can go ahead and save the changes afterwards. So I can extend this enumerable because this function is just returning an enumerable, so I can just extend that and say where, and we can use our Lambda expression right here. So I can say where Q or my Lambda expression, and then I'm going to look for the state is equal to, and then I can use that num to filter on added or Q state is equivalent to entity state that I just did added, so this would now be modified. Right here, we're filtering out and getting all the entries that are either about to be added or modified. Okay. Next, I'm going to have a four each lobe going through these entries, and I'm going to be converting them into the base domain objects. So take a look at what's happening here. Entry in entries. Now, if we look at entry, entries of type entity entry. All right? So when I'm converting it, I can just convert entry, but I have to convert entry dot entity. So entry has a few objects, a few properties, you can see the current values in the object, you can see the original values. So even for audit logging purposes, you can see where the properties before or the values on the properties rather before and what are the values now. You can look at the context, and the entity, of course, embodies the actual entity type that the context knows about. Now, because we use the base domain object and every other entity type inherits from this, I can bring it down to this level so that I can modify the object fields, because at this point, I don't know if I'm saving a football team. I don't know if I'm saving a match. I don't know what entity type is really coming over in the entry. I'm just casting it into the base domain object so that I can start interacting with the fields. For each one, I'm going to say auditable objects do create a date, nice and simple is equal to date time dot because I'm about to save the changes, it must be know that modification sorry was done. Modified date, it's the same time. But then think about this. I am looking for added and modified, but every time something is added. Of course, I want to have the created date, but I don't want to have the created date every time something is modified. You see how sensitive that is. So I'm looking at both added and modified, but I am putting in the modified date every time that something is going to get modified. Or, sorry, I'm putting in the created date every time something is getting modified, which is wrong. All right. I'm going to take that one out. So every time we hit save changes, I want to say that, it was modified because it's true. Whether it was being created or it was being modified, it's modified date is no. However, I only want to set the created date if The entry dot state is equivalent to entity state dot created or added, rather, right? So when it's going through, it will say, for this entry, I'll set the modified date. So whether it's being created or actually being modified, we're setting the modified date. However, if it is being added, then set the created date. Otherwise, it will just skip this. It won't do anything. We'll just go to the next entry and do all of that until it's done. Now, when all of this auditing is finished, the last thing that we want to do is finally save the changes. This is like a final Hurrah. Whatever data came over, we don't know, we don't know what entity DB set type it is. We just know all of these have base domain object in common. And once that is in place, we can go down to the audit fields, regardless of the higher level type, I'm going to say, and then make the adjustments and then save the changes. Now, before I moved on, I just noticed that there are zero references to this method. In my program do CS, I have dusted off a few of the old methods that we would have used, and each of these methods does call save changes, but then look at that, save changes a sync. However, we have overridden just save changes. That's my bad slip of mine from me, right? So the override method that we're really looking for is save changes. So what I'm going to do is rewrite this override because I think that there are several. I just want to make sure we get the right one. So we have save changes, save changes, and save changes a sync that takes two parameters, and then this one that takes one parameter with a default. So I think that's the one that we want because if we use the other one, then we have to provide a bullion, and well, that cancellation token is there by default, but then we want the one with the default where we know we don't have to provide any parameters. I'm going to use that one and just organize my code here, right? There we go. So we're public override task in Save change is a sync, and that is the parameter. You can go ahead and make that adjustment, and then in addition to that we need to make this syn because well, it's a syn, so we need to make it an asynchronous method. And then if it's calling a synchronous, it's an asynchronous method, which means that we want to use the asynchronous method down here also. I can just change that one to save changes a sin and it requires an weight. So I can just pass that a weight, and that should be it. So I have consistency. I'm going to use the same cancellation token in the parameter right there. All right. So with that adjustment, no, I'm seeing all 12 references being made to the save changes. So that's a little better now. All right. So that means when from our program.cs when we call the context, it's really going to hit our new Custom one, and then it's going to do all of this before it calls the base. All right? So that's the power of overriding all of this. So let's go ahead and take this for a spin. All right. And what I didn't point out which methods I dusted off, apologize. So we're using the simple insert operation methods where we're adding new league and teams with league, and I'm doing the simple update league record, we're updating the league record and the team record. So Taking a look in the console and the SQL statements being generated. You'll see here that I have P three, and I'm getting that date time value right there, right? And when it's being inserted, create date, modified date, P two and P three are going in. Accordingly, here's P one. There's P one, which is our created date. And then we have P two being null because that would be created by or modified by So we see that our dates are going in into our SQL statement properly, right? So once again, that SQL statement gets generated at the time you cause save changes. Now, we've overwritten it to do some additional things before that Q statement. So that is a nice little way to inject your own little logic and a little consistency or cleanup or anything like that you need to do before the data actually gets committed to the database, then you can always override the context methods like that. Now, there is a way that you can extend the DB context even further to facilitate certain things. So in context, we want to put in maybe modified by, like a user name or something to say, who modified it. Obviously, we can't do that here because there's no way for me to pass in a string that represents the user name as or as any form of value here because safe changes is only looking for this. So what we can do is extend our context to accept additional data that the default context would not, and then we can massage the data before we call the base context. So we can look at that later on, and I think that will be a fun activity. Okay. 35. Extending DbContext: Hey, guys, welcome back. Now the last time we were here, we were extending RDB context to handle a bit of auditing work. In other words, we extended the save changes or overrode it actually to be able to massage our data a little before we get to save it in the database. Now, we saw the benefits of this because now we see that we're getting the audit dates for the modified and created date. However, a limitation is that we don't know the person who has created or modified the record at any point in time. So that is one of the limitations we're just overriding because we're still kind of confined to what the default safe changes has in store for us. I cannot override this parameter account ask for a user name. So for context or program dot says would represent the application that the user is using, right? When somebody is using the application web or desktop, they add a new lead I would like to log who did that. I want their user name from the system. I don't want to put their responsibility on them, the user to tell me who they are. I want to know who was logged in when this action was taken against this record. So It would be nice if I was able to pass over a user name into the save changes so that we could use that in the auditing before saving. So with all of that limitation that we understand the limitation and what we want to accomplish, I'm going to get right into it. What we're going to do is extend the DB context to have another version quote and quote of it that is going to take over the save changes role or take over the overriding of the save changes and then hand it over. So that sounds a bit more complicated than it really is, and actually started the work already. So I've created a brand new class, which I created right in the same data project as the original DB context. I'm just calling it a auditable, sorry, the football league DB context. Now, this audit football league DB context file, it's one abstract, so it wouldn't get, you know, instantiated on its own. And it inherits from the DB context. What we're going to do is let the original DB context or football league DB context inherit from our football league DB context. Now, the benefit of this is that just by using this one, we get all the functionality available in the auditable version, but then the auditable version allows us a bit more flexibility because we're not confined to the actual DB context and overriding it in that regard. So with all of that said and done, I'm just going to cut this save changes AC method from our football league DB context, and I'm going to place it in the abstract version, and I'm going to make a few modifications at this point. One, I'm going to take out this cancellation token because I don't really need that I'm going to take that out altogether, and I'm going to allow it to call the base saving changes because that's the DB context, right? But I'm also going to extend this method to take a string parameter called user name. Now, what this is doing is letting every calling method know that they must pass over a user name in order to call this method properly. And I'm going to remove this overide because we're no longer overriding. We only have a method called save changes, which is just going to call the base. So it's just like an extension or a method before the real method. Now that we have this user name parameter coming in, I can now say auditable object dot at this point would be modified by, and then the modified by would be the user name. And similarly, the created by would be that user name. Now, zero references. Why is that? That's because in our program, it's still calling the default save changes. Let me find one of these methods. There it is. It's still calling save changes async, that is the default one for the context. However, if I look at the overloads available to me, I will see that there is a new overload, which allows me to pass in that string user name. There we go. So I can pass in a user name. Let's just say test team management user. I'm sorry. I'm just trying to be explicit enough so that we can see in the database query where that save changes is going in. This is a simple update. So we can look for that or test up date user. Let me keep it simple. Test update user. This record. We don't need that. I'm going to leave this one without any user name. This is modifying the league. This one is going to not have a user name. This one is audit. So I can change my league name. So you don't have to do that. You can use your old data or put in your own data, but that's fine. But I'm just going to put in some overrides test audit create user, and then I'll leave this one without any user name, right? So I'm just going to run this at this point and let's see exactly what happens. So when I'm looking at some of the statements, I'm seeing here in the save changes that I have that test audit create user. There we go. So that test audit create user was the one who created that audit testing league. All right. There we go. So this person, this user is going in as P zero and P two. And if we look P zero is the created by and P two is the modified by. So that is how we can get those users into the system. I note, while I may have hard quoted it here, once again, in a real application where you have people logging in and so on there are ways to get the user names, and just plug it in in the save changes. In.net core web applications, you can even inject the CTP context along the way and forego the whole grabbing it and sending it over, but that's not for discourse. I'm just trying to show you how you can extend your DB context and manipulate your data in more and more creative ways to kind of make sure that you have that data integrity by the time it hits the database. Okay. 36. Implement Full Database Auditing: All right guys. So the last time we were here, we were just concretizing how we do our auditing. All right. So upon to know auditing is just a modified date and modified by creating the attend created by. But then you may have another necessity where you may need to audit the entire roll. So you could be on separate database or it's a separate tables, or at least that's always traditionally done. Sometimes what people do is they put triggers in the table so that every time something is saved, edited, or deleted, it will automatically writes are required to another table that is trucking. What activity happened on a particular table is take a copy of the table. So we're going to take a copy of the original values prior to the operation, the new values after the operation, and serialize them into a string and store that inside of the database. How it can be read. That's up to the application and the developer at a time. But I'm just showing you how flexible EF Core is in accessing data before and after the fact and how we can manipulate it during. So let us start off by creating a new class and I'm going to put it in the domain. You could probably put it in common, but it really is going to be a domain class. Creates a new class. I don't want to call it IT audit. So audit is going to have a few fields, is going to have the standard id, a string for the table name. So we make it public. And then we put in the fields. So it's going to have ID table name, datetime, meaning that the time, of course it was entered, the key value. So whatever primary key is off the record being audited, the old values of the record and the new values of the record. So after you've replicated that audit class, then you can go over to our auditable DB context. And we're going to add that DB set, right? So it's going to be in the auditable DB context. Do you need set audit? And I'm just calling it audits. So then we're going to go into deceive changes. And I want to actually do some stuff before save changes, because before we save the changes, we need to take a copy of the data that is coming in, right? So right above where we get our truck changes and know whatever happened and so on. I'm going to see on before save changes, right? This method doesn't exist, so I'll just control dot and generate that method stub. And there we go. So this is going to return a list of another class that I'm about to create called audit entry. So if I do Control dot, Let's see what Visual Studio can do for us. It says it can generate a type called audit entry in its own file. So we'll just go ahead and create it in its own file. No harm, no foul. Jump over to audit entry. So an audit entry is basically going to be an obstruction of what exactly we need, right? So let's settle this class for a bit. So I'm going to say public audit entry. So that's basically the constructor. And I want entity entry. So we know what entity entry is. This is coming from EF Core. And we'll just call it entity entry and sure. And then we are going to use that constructor to initialize our property. So I can control that and say Create understand property called entity entry. And it does that for me automatically, right? Then I'm going to have other fields that kind of much what we had in audit, right? So I'm going to have string table name. I'm also going to have well, old valleys and new values and well Key Vault is all about isn't involves what I'm going to change the datatypes of those. So we have the table name, we have the key value which is really going to be like a dictionary. Someone to change the datatype to dictionary of string and object. So if you've never worked with dictionaries is just like a key value pair so that I can be able to solve JSON. And that's what a dictionary is. A string would be the key and the objects would be the data that's going in alongside the key. So control dot include that. And I'm going to just initialize it so it's never know. So just take this and c is equal to a new dictionary of string, an object. And pretty much that's all we're going to do for old values and new values. And we do the same initializations and you see all that kind of looks consistent and little scarier bullet soon you'll see what we're seeking to accomplish. The next line is going to be for temporary properties. So I'm going to say a public list of property entry. Which is also from coming from entity entries library of freeware trends change tracking, right? So you see a list of property entry, temporary properties is equal to a new list. I'm going to have a quick method That's just returning our property rather that's returning a Boolean to see has temporary properties. So as soon explain what temper properties really means or will be used for. But then we're going to have another method that's going to actually build out or audit required on I'm going to call it to audit. All right. I would just have to include any namespace there. There we go to audit. And then we can start building all the functionalities. So when we talk about to audit, we're talking about building up the actual entity, her record. So var audits is equal to new audit. Initialize a new object of type audits. And then we'll start filling out all of the fields of the audit. So I can use this object initializer. And then I can say, okay, Did time we know that you are datetime dot nl no-brainer, right? So this is the time dots. No. Right? Then table name, that's easy enough and there shouldn't be using a semicolon, apologies. Table name is equal to the table name coming from the local. Alright? Then we have the key values, which would be a serialization of the key values coming in, right? So I'm just seeing key values is equal to JSON convert, see serialize or object and key values, right? So pretty much remember that this is a dictionary. So we're going to have the key, which is going to be like ID. We're going to have the value is stored as an object, so it's a string ID, or in our case we're using int ids. Whatever it is, is just going to have that key value pair and we're just going to be storing it as JSON. And frankly, that will be the same thing for the old values and the new values. But then there's sort of a twist with those. So we're going to be seeing something like all the values is equal to all values dot cones being 0. If it is 0, then we can store null. Otherwise we want to convert the volume coming over from the key value appear. Know the relevance of that. If we are doing certain operations like inserting our record, then there is no old value for that record because it's a brand new record. So there's no way to get the old values. So it will be logging to see were there any old values to be stored in this record if not then null because then we can surmise that it was an add operation. And then we can go ahead and serialize the values if in case it was I didn't eat or an update. So the same way we did all the same, we will do new values. So I'll just duplicate this line and changes so two new values. And wherever there was a split, new buddies. So if there are no new values, store null, although eyes stored in the new values. I think I'm going to add one more field and that is the axon. So I'm going to, in the audit table add actions so that we know what kind of operation was being carried out, what's right. So action, and I'll do the same here, called you action. And then in order to audit, I'll say action is equal to our local action. I heard. So we know what was happening now after we've built all this whole audit object, this method is supposed to be returning that audit object. So we just return audit. That's it for our audit. Enter at least for null as we go along, we might see all the adjustments that are needed. No, we'll jump back over to our auditable DB context. And to me this easier, I'm just going to take all of this out. I'm going to cut it and I'm going to put it inside of our new method. So on before save changes, these are things we wanted to do anyway, right? Alright, so I'm going to refactor this a bit instead of trying to get only these entries on Wednesday, instead go by a process of elimination. So I went to see if they get the entries from the instructor where the state is not equal to detached and it is not equal to on cheaters way would be capturing more scenarios in between, right? So get me the entries that were not detached or unchanged and then those are the interests that we're iterating through. You see here that's it's complaining about username. Let's see what controller.js for us, we can generate a property that's fine. Okay, this just add the parameter. Generated perimeter username. There were all sorts I was looking for. So now we have the username i, we can Boston that username on before, see if changes and everybody's happy. All right, So nowhere getting all the interests are not touched, not unchanged onto our setting those audits columns for them. Now before we get into that for each, I want a new list of audits entries and we will be compiling this list as we go through. So for each entry, after it does pass through these initial tests, I am going to try and see I want a new instance of audit entry policy in that entry coming over from our THE trucker. Right. So remember that's when we had the constructor and audit to ensure that Tolkien entry as its parameter. Then I'm going to get the table name through seeing audit entry dot table name is equal to and then you just hurry up and sit that it's equal to entry.metadata. So I'm just showing a whole much data. You can actually get a boat, something that's a bolt to be saved to the database. I can get the metadata, not relation. Let me see your relational event need to include missing reference or just spell it properly. So dot relational or actually I think that's a dotnet Core three-point one. If core 3.1, here it is, get table name. There we go. So in court dotnet Core 5, it's even easier to get a table name. I just see GET table name. All right. And then we're going to add this new audit entry to our list. So I'm just going to say Add and audit entry as ensemble fact. There's another field that I would want to sit from this point on, and that would be the action. Alright, so we need to know what action is being carried out. And then I could just see the entry dot, state, dot to string, that is the auction. Know that we have that part of the way and let us focus on the properties. So we need to evaluate certain properties, one, to copy their values, whether they're old valleys or new fathers or the primary key equals and remote. That's our audit record, does have a specific field for the primary key. So what we can do easily is start off with a foreach and we can say var property in entry, Thought properties. So this is using what we'll call reflections and all which is one of those great allowance is given to us by more recent versions of C sharp. So we can see if property, sorry, if property is temporary. So this would allow us to evaluate if there is prettiest hold of value inside of the property. So back when newer, experimenting with adding records and so on, you'd see that the id value has a minus, I think that's int Min minus 2 billion and something value in it. That's actually a temporary value until the value is saved. Right. So we want to see if property is temporary than audit dots. Audit entries are temporary properties at this property. Alright. So this is all we can know if this is going to be added or not. You'll see later on why this is so important. So after that we can continue. We don't need to do it any other operation on this property. If it is a temporary property, we just add it to the record and then we just continue. No need to worry about it. No other things that we may need to do if it is not falling into that category is get the property name. Which once again, I'll have to seize property.metadata and we just use my clipboard property dot data dot name. So I'm getting inflammation and that's what metadata is. If the property.me is primary key. And that's a method. So we're seeing if we're dealing with a primary key, then we want to store the key buddies. So it's going to look similar to what we did here. We're always seeing IS audit introduced key values and then we get the property name as the key or the subscript off the IRI. And we're storing the value. So we're going to just go ahead and store that. So this is all we get that key value appear, right? So that's string in the dictionary, that's as a property name. And then the value or the object would be the current value of the primary key. Then we go on to a switch where we see Get me the entries state. So switch entry state. And then we're going to do a few cases. We're going to check if it is being added, then we need to store the new values with the property name on the current value. Alright? Although was if it is being deleted, we need to store the old values with the property name and the origin of us ECF current value and the origin of value. Current value is whatever is being putting know and the original values, whatever it was before. So that comes in handy know when we're checking if the NTT state is in a modified state at that point where we're seeing if it does modify it on a property has been modified, right. So we're seeing if you change this particular property that we're looking at, then we want to know what the original property of Ardi was and what the new property value is. All right. So that's what that switch statement is for. And right, No, that's all I'm doing on this for. So once again, we want to store if it is appropriate if it is a temporary property, if it is, that means it's a record that's a bolt to be added and there's no primary key because this is on before saving changes. So nothing is really happening just yet. Primary key ways. However, if it does up primary key which only would be present if it was at deleting or a modification, then we go ahead and make a note of what the key value is. Otherwise for the rest of the values where logging what the new values are, what the old values are, fair deleted, and both if it is being modified. So moving on, no, we need to actually save these audit entries or at least build up a list of audits to be sieved later on, right. So I'm going to do a for each eye again, I don't want to save for each audit entry in and I'm going to get that list audit entries docked. And I want off them where we have our Lambda expression, q-dot has temporary properties. We can see is equal to fall as meaning it doesn't have any or I can use. I'm not saying I've been doing this for readability purposes. So we're there are null temporary properties and I can't use audit entry here again, let me just see audit pending audit to ensure let me use that pending audit entry. All right. So for each pending audit entry in the list that we have compiled to this point where there are null temporary properties, then we want to add it to the audits DB sit for lead to injury. So pending audit entry dot and then here's why we had that to audit method. So remember that the two audit noise when to take whatever value was Boston and whatever was sit here and actually create on audits record for return. Right? So we're just seeing everything that we just compiled, all the key value pairs on. So on, converts it to an actual audit record and edit tool or DB sit. After all of that, I want to return the audit entries that do not have or sorry that dual have pending properties, temporary properties. So I want to return where they have pending properties. And of course that's us to be a list because I need cells returning a list. There we go. So that's what's happening on before save changes. I'm still getting that, Aaron. I think it's because I did it's inside of this for each and that's his card. So this should happen outside of that for each I apologize. So let me just call it quickly. And at the end of the method, we go through compiled audits and return the ones that are temporary. There we go. Everybody's happy with that. No, we've done that before. Saving changes. All right. And then we go ahead and save changes. Now what's going to happen after we save the changes often at this point is that we're going to have all the audit interests that's had the values that it wants being added where they didn't have an ID yet. They're all still stored inside of this null. I want to go back through and update the ID valid know that they have been stored. So instead of returning at these points, I'm going to put this in a variable. I'm just going to call it. Result or result. So we're just storing what this would be, right? And then we're going to have another method that I'm going to call after saving changes on where policy in the audit entries. Right. So we get the audit entries that were in a temporary ID state. We see that g and g is and all they've been updated. Now we need to do something after the save changes. So I'm going to collapse the on before and I'm just going to generate this method. And with that done, what we're going to do is go through the audit entries and check for whatever it needs to happen, right? As a matter of fact, this may not even need to happen. So I'm going to say if the audit entries is not equal to null, for whatever reason it might be null. Or audit injuries dot cones is actually greater than 0, meaning we actually have stuff to process afterwards. Then you can call this method. Alright? So inside of the method, what we need to do is go through because at this point we're assuming that something is inside of the audit entries, you would only get here. If something was, then what we are going to do is evaluate each audit entry and set off this list. And then we're getting the properties in each one of these, so two for each listed for each. So I'm going to say get me each prop in audit entry dot temporary properties. So remember that we compiled a list of all the temporary properties. Then I went to have an if statement to see if the property is our primary key, then we add the key value to be the current value. All right. So we're just doing a quick updates of that audit entries value to be the current value. Otherwise, the new value is our current value. So we're just seeing if it's a primary key, which is more than likely why it would end up being temporary. If something was being added, updated to the current volume, know that everything has been saved. Otherwise, if it wasn't our primary key, which as I sit there I can think of a scenario where you'd have a timber value for XOM. That's not the primary key. But in that event, we still update it. No problem. All right, so after that, we do the for each on, for each one that we are saving, that we're going to definitely add it to the audit. Just I call we added the audit interested in the before saving changes. So we just audits dot add under the two audit so it can be converted. Then we return a save changes. So we have to call a sea of changes at that point. All right. Now after we've saved those changes, we can return result here or save results rather. So that is really what I have implemented for auditing in multiple applications. And it works pretty well because like I said, it's really just storing string representations of what the entire record would be like. And we have the old values and the new values for a side-by-side comparison. So on a user interface you could easily print result because it's JSON, it may not be readily human-readable. So you may want to tweak it a bit so the details presented a B, it's more readable, but at least you can see everything that's happening. Keep a log of everything that is being deleted, modified, or added throat application, whatever they are valleys where at that time. So he can go ahead and do some tests, do some protists, and look in that audit stable, of course, you know, with all of those changes, we have to scuffled auto new table. So if the ad migration and we say added audit table. And then after getting that migration we have to see the data is. And with all of that done, you can go ahead and test it out and let me know how it works for you. 37. UPDATE: Implement Full Database Auditing - Fix: Hey guys, This is a quick fix for our previous video. You would have setup your auditing and the full database auditing. And we'll see how it serializes everything to JSON, stores it in the database. However, if you try to do multiple operations, but to buck, you might run into a difficult situation where it complains about a typecasting for an audit field or an audit record when that really should not be happening. And I'm just here to show you the fixed for that. So in our on before saving changes or save changes, what should happen is that this should filter out anything that is unchanged already touched upon to know we know that the entity state, I bet So anything be insert is going to get the added. And then we know that we have the entity state for deleted and modified or so. But then the reason we're excluding these two is that we don't need to audit anything that is detached, meaning it's not being trucked, our shouldn't be tracked or unchanged, meaning well nothing happened. So read essentially what happens is that once the Save Changes is really called, whatever was being tracked automatically goes to an onchange state. So if you do multiple operations, then you're going to end up with audit entries that are being saved. And then you go and try to say something else into the framework is still tracking the old objects that were most recently saved, including the audit record. So that is why it would need to be filtered out at this point because it is knowing an onchange state. However, for whatever reason this filter is actually not working as I would have liked it to the maybe working for you, Amy anatta of this program, that's good. But I have seen this problem happened more than once. So I'm just going to show you this fix for it. And it's less of a sophisticated fix. All I'm going to do is invert the condition and that works better for some. So what we're going to do is say, give me the entries where we know we want to try them, meaning the state is equivalent to dead, or it is equivalent tool modified. And then one more. Or it is equivalent to those BreakLine deleted. Right? Because we know that we want to track those. We don't want to track the other two. But so when we're trying to exclude them for whatever reason, the exclusion might not work as it should. So that's fine. We're just going to work our own that still maintaining the quality and the integrity of our code. We're nowhere seeing give us the ones that we know we will definitely want to audit. So we're getting those entries and then everything else would fall in line because there are they have or case statement based on which state it is that needs to be added to the audit entry. So that's a quick fix. If you had that difficulty, we can do that and I'm sure you get a better experience. And if not, of course, we will continue to investigate and look because we're all learning red. This is a very complex library and we're just doing our best to get the best out of it. 38. Data Validation with Data Annotations: Hey, guys, welcome back. In this lesson, we'll be looking at putting constraints and limitations, general rules around the properties and the values that they're allowed to have. Now, I would have used the word constraint more than one. And that would have been more specific to the migrations where we saw that constraints were getting added for foreign key relationships or for uniqueness and stuff like that. But then those are all migrations based on some of the rules that we're setting up in our model creating and based on the data types and what we set as a foreign key reference and such. Now, besides those specific situations, there are times when you want to be a bit more calculated and have a more hands on grasp of what is getting stored and how it is being stored. So a practical example. When we look at team, we see that we have a string for name, which gets translated into a RCR in the database, but then it's VRTR Max. Do we really want VRTRMx for a team name, right? So right there, we're opening up the database to the possibility that somebody could put an entire essay in that field and call that a team name. We don't necessarily want that. Other constraints might include default values. So we had added these base domain objects. And I'm just using this an example where we have the datetime. But then there might also be situations where you want default values in these properties. So if a value is not provided from the interface or from the user, you still want it to have a value. Let us take a look at limiting some of what our string columns can take, for instance. So from the DV context, and this is using fluent API. So we would have looked at fluent API earlier when we were specifying certain rules around what each entity can have. All of these lines of codes or blocks of code really are fluent based on fluent API. So what we're doing now is using fluent API for some validation. So I want to say model builder dot, and then it is going to be on team this time. So I'm going to say dot property, which then opens up for another Lambda expression. I'm going to use P as my tokens. I'm going to say P dot, and then I get to choose the property I'm interested in. So I did say name. And then after that, I can say what constraints it has. So I can say to the database that it is required, or it has a specific column type. What if I didn't necessarily want it to be VT? What if I wanted it to be VT. For instance. What if I want to set a max length, which is what we're about to do. So I'm going to say max length, and then all it needs is an integer value. I'm going to say no team should have a name that's more than 25 characters long. I think that's reasonable. There are some teams with long names like Barca, Munch and Gladbach over there in the Bundesliga. But I think 25 is sufficient or let's just bump it up to 50 to be on the extremely safe side. So no team should ever have a name that exceeds 50. Now, doing the rule light, this really sets up the constraint for one property, and it's pretty much one property at a time. But I can do this for multiple entities. So I want that same constraint on a league. I don't want any name of a league to be an essay, and I don't want any name of a coach to be too long. All right? Other things that I could do, I could set up this property as an index. When we talk about indexes, they're really high speed lookup points. I'm going to remove, I just duplicated that line, and instead of saying dot property, I'm going to say dot has index. And then this is now going to ask me for a Lambda expression. So I'm going to just use H like we're doing index and I'm going to say the index is on the name. That means if we search by the name, it should be a high speed. Point for the data, so the query should run relatively quickly. I can actually just do the same thing for our league and for our coach. Notice what I'm doing is just copying and pasting and replacing the names accordingly, because they all have similar structures. All the things that you probably want to do would be to specify that these columns should be unique or whatever value goes in them should be unique. So a case study for that, well, you wouldn't want two teams with the same name, though it is very probable. You wouldn't want two leagues with the same name, though that is, I guess, less probable. But in a more practical setting if you're doing a database for a school management system or a book, Storage. You have the ISBN, which is a unique number for every book, or you have a student's ID number, which is different from the default incrementing ID, but just that ID that they use in school, you have that ID number, then you'd want to specify that it is unique. So I could actually extend the has index to say that this index should also be unique. All right? And there are other things that you could apply. Some of these I've honestly never used. I've never had a situation where I needed to use them, but that's just me you can explore and find use for it. So here, I'm telling the database that this is a high speed lookup area on the table team and it is unique. If you wish to have an index on multiple columns, then you can actually just chain this along. So I can make an anonymous object type. We looked at that earlier, so I can just say in the Lambda, let's use coach. I'm going to say that the index in the coach table is a combined or both the name and the team ID should be indexes, right? So I can start my Lambda expression, say new open curly braces, so it knows that it's an object type. And then inside of this object, just specify multiple columns, H dot name, H dot. Team ID, comma, separate, et cetera, et cetera, and then by extension, I can make all of that unique. So that means that combination should always be unique. So that's more like making a composite key at that point. All right? So I'm just showing you a little tidbits. I don't know how practical they are necessarily in this particular situation, but you might just have these challenges in designing your database, and if you're coming from a strict database background, then you know how easy it is. Or relatively straightforward it is to do it in SQL Server Management Studio. However, in the situation that you're dealing with a code first database, you would like we said, want to manage all of the changes from our entity framework and let them trickle down to the database instead of mixing and matching. So when I've made all these changes, if I go over to the package manager console and add a new migration, and I'm going to say added validations and always remember to select the correct project. So I just change from console to the data, apologies. But now, when we look at our alter statements. You see here for the name in teams, it's now VRCR 50, and the old type is NVCR Max. All right? So there are a lot of performances performance inhibitions, that can come about by just leaving your data types as VRTA Max. So when you start putting in these constraints, you're actually making your database more compact, more efficient and well, just saving the stress of, you know, design considerations down the path in the future. So we have the alter column for the names. So each one is generated nicely, then we have the create index. So we see here, create index on team's name, and it should be unique. And then we have create index on the name for the leagues, which we didn't specify to be unique and create index on the coaches table, and the columns are name and team ID, and we looked at that filter code earlier. So I'm just going to go ahead and update the database, and I don't anticipate any issues with that command running. And there we go. We have a red line, right? Let's see what this red line is. So it is saying that create unique index statement terminated because a duplicate key was found. So we're seeing that we have data in the teams table already, and we're trying to say that create this unique index on the team name column, It should be unique. So you can see my team stable here, I have a lot of things repeating and repeating and repeating. But the point is also clear that I can't be saying that the name column should be unique when I'm having a CMLN so many times in the database. All right? So we see that the constraint works. I'm just going to delete everything that is not in the top three. Let's see if I get any other errors. Yes, I anticipated that I would get some foreign key. Let me clean this up. All right. So in order to clean this up, I had to go over to the coaches, make sure that nobody was coaching any of these records I was to delete. Also go to matches and make sure that none of the records I was to delete had matches. So that is a practical example of our constraints in operation, right? Because we did set the referential integrity to not be cascade, but to restrict. So those things just cannot happen once those constraints are in, and we're learning how to enforce them using EF Core. All right. So with all of that cleaned up, let's go back and attempt our update database again. And this time, I believe we're going to be successful, and there we go. Done. All right. So that is how you can go about setting up validation for your tables using FCR. Okay. 39. Fully Using Configuration Files: Hey guys, In this lesson we're just going to do a bit of refactoring and get to understand the full power of our configuration files a bit more. So when we did our configuration files, there were really designed with the intention of facilitating our seed configurations. So we named them league seed configuration. Well, the reality of the matter is that this entire class can be used for all the configurations relative to the target domain objects. So in other words, just that I call in the DB context, we actually have League related configurations. We already have this league configuration class. We can actually place all of this code inside of that class to further keep our whole DB context kind of clean. All right, it goes then we'll end up with a lot of these blocks of configurations and the loop. As many tables you may have many configurations. You want to kind of keep everything in trunks so that you can see them or find them very easily when you need to. So then just start by refactoring our team. So suddenly first one, Let's start with teams. So we see here that we have this configuration for team, this configuration, we have two others here. And then we have this whole configuration with the seeding. So first order of business, I'm going to rename this from being teams seed configuration to just team configuration. And I'll just let that refactor all the references, throw the code. Next up. What I'm going to do is bring over all of those configurations from the DB context. So I have ModelBuilder dot n TTT map, all of that. I wanted to cut that. And I'm going to come over to this seed configuration, what we're going to rename the file and nephew, don't worry about that, but right, no, I just wanted to move over the configuration. So this is the builder has data configuration. I'm going to go underneath that's still in the configure method. All right, and then I'm going to paste all of those configurations that I just put. Then you'll see an error appearing with ModelBuilder. And that's because we don't have anything called ModelBuilder in this file. However, if you look closely, ModelBuilder is like a generic version on our implementation That's allows me to say ModelBuilder dot entity and then imply the entity. Our builder object on this type is specific. It's entity type builder for the specific entity. So this whole implementation kind of embodies this entire line. All right, so all of these sets as ModelBuilder dot entity, that team, I can replace that with builder. And builder knows that it is relative to team. So everything that we do here is relative to team. All right, so I can just say builder dot Tasmania and then just list out all the rules. Replace that also with builder, just moving it up so we can see where it starts and stops. And then I'm going to continue with the other bits of configuration for the team, someone to cut that and then went to place it right there. And once again, I'll just use builder to replace that model.py in ModelBuilder dot entity, that team stuff. And there we go. All right, and then that's our configuration. So we already have that configuration being called right here. So once it hits this line is going to jump boards or the configuration file and see everything that needs to be done for a team. So I can't actually move these configurations above the has the eta. So let me just rename this file quickly before I forget. And then we're going to do the same thing for the other configuration files. So league seed configuration, it's no longer specific to seeding. So I'm just going to go ahead and rename that. Her friends go ahead and rename the file, and then I can bring over league related configurations directly into our builder. Go ahead and replace what I need to replace. And then I went ahead and did it also for the coach. So you can do that, know that you have the gist. And at the end of the day we see RDB contexts looking much neater. And then all of the messy configuration stuff there in specific places, either owned or project.