Learn Entity Framework Core From Scratch

[Music] foreign [Music] my name is Trevor Williams and I'm your instructor for this free course on Entity framework core now in this course we take a step back we're not going to be building out a whole application and looking at application development on a whole instead we're going to focus on Entity framework core as an orm and we're going to look at different configuration options that are available to you when designing your database through code First Development so in this course we're going to use a simple console app and we're going to look at different querying techniques explore best practices and overall develop the foundation that would be key to you building on top of Entity framework core for any other type of application development now that being said this is a free preview to the full course and the link for that is available either in the tag coming up above or in the description below this video now in the full course you will get even more info information about different techniques and options available to you when you use Entity framework core as your orm in your application so I encourage you to enroll in that course and I know that you're going to get invaluable information that you can use in your future projects until then however do enjoy this free course and I'll see you soon 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 Visual studio.microsoft.com and what we're going to be using is the visual studio IDE Community Edition now 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 will 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 and 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 will be doing a little web activity so it would be a good idea to just get the ASP net and web development workload also so at the bare minimum you need those too the more you click of course are the more that will be installed so you don't have to click as many as you see me having ticked here so asp.net and web development as well as dot 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 dotnet.microsoft.com and from that landing page you'll be able to navigate to the net five downloads page where you're going to be getting that SDK you can also go ahead and get the runtimes because we need them for hosting but at the very least you need that SDK 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 you're on and then you can just type.net Dash H alright so when you do that it will bring up if you're seeing a printout 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 sitting on 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 and you can move on to the next lesson all right guys so let's just start setting up our solution for this course so I have Visual Studio 2022 and what I'm going to do is create a new project for this course I'm going to be using a console app and some class libraries and the reason we're using a console app is that if you can understand Entity framework core from a console apps perspective just understanding the concepts then the possibilities are going to be Limitless when you actually have to use it in an application so we'll start off with a tiny console app and what I'm going to do is name this uh Entity framework core dot console app and then I'm going to remove that dot console up from the solution name since they're going to be other projects so the solution is Entity framework core and our project name is Entity framework core dot console app then we can hit next now we choose the version of The NET Framework that we wish to use now the majority of the course was actually recorded using.net 5. however you can go to 6 or 7 because I do show Entity framework core five six and seven all of which the functionality is compatible with each increment as a matter of a fact Entity framework course 7 has features that can be leveraged from net 6. so if it is that you need to use.net6 for this course then go right ahead because everything will still work however for this creation I'm just going to use.net 7 which at the time of recording is the latest and greatest and then I'm going to tick do not use top level statements and I actually find this kind of confusing because by ticking it out it would imply to me that I don't get the program class and the main function however it's the opposite so if you leave it unchecked then you will get the simple program class without the overhead of the namespace and the class and the function if you do tickets then you'll get all of that so for consistency I am going to ticket and then we can go ahead and hit create and then we end up with our project so now we have the program.cs file in that namespace and then the cool thing is that we can save on some of the horizontal spacing if we or just put a semicolon at the end of that namespace line it gets rid of those braces so that's optional for you you can do that if you wish I won't necessarily do it once again in the name of consistency now we can add the other projects so let's just go ahead and add a class Library I'm just going to right click the solution go to add new project and this time we're adding a C sharp class library now if you have it to the side you can always click class Library here or if not you can always search for the template up top and remember that we're dealing with C sharp so I can just select class library and I'm going to call this one Entity framework or dot data so I actually just copied the previous name and I'm just changing that just to be a bit efficient and then we are using a.net 7 library to match the previous app so if you use.net6 make sure you choose the appropriate option all right and then we hit create and then the final project is another class Library so we just repeat those steps go ahead and hit new project choose the class library and this time we're going to call it entity frameworkcore dot domain and once again we're using the same version all right so just so we can understand why we have the three projects project number one which is a console app acts as our point of entry so here you see the configuration it knows it's an executable file if you have any experience with console apps you know that they'll just run in the command prompt looking window so that is going to be where we put in our application logic and we're doing all of the fancy stuff with Entity framework now there is a challenge here with the nullable right so um this nullable configuration was introduced to.net6 to help us to or help the compiler to point out where we might run into null references and it gets very strict so for this course I'm just going to say disable just so we can get through the course with fewer disruptions but in my other courses I do walk you through how to manage that so right now you can just say notable disable and you can proceed now if you look at the other two we have the data which is just library and notice it also says Net 7 and it also asks if it should practice that null ability so we can disable that as well and for the domain so the data is going to have like our data configurations and the domain project is going to have the actual domain models and once again we disable so with those three projects created what we're going to do next is go ahead and start having some fun so stay tuned hey 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 so 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 has a name but then it has a league ID so this league ID is what we call a foreign key to the league so a team belongs to a league all right so that's the kind of 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 uh I I hope you understand at least the concept behind this diagram all right so what we want to do though is set up tables like this inside of EF core so we want to use EF core to model out a database that will generate these tables so you have different different conventions when it comes to developing on top of a database so you can do a 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 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 so 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 the database and EF core supports either one that is the beauty of it so for 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 EF core set of files all right so enough talk let's get into that so my data models are domain models are going to end up going in the domain project all right so I'm going to get rid of this default class that we've got with it and I'm going to go ahead and add a new class and I'm going to call this class League no 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 three foreign Keys relating to other tables and those tables don't exist yet so since in this situation league is a standalone table meaning League is there a league is going to be either at least right now without any teams but the team depends on the league right so we want League to exist first so 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 so you see here especially if you're familiar with the data types in SQL you'll see that we're not seeing varchar here right we're seeing string so EF core will be able to translate the native C sharp uh what should I say data types into the equivalent SQL data type so we'll see that later on so that is one of the benefits we can use our native C sharp inside of our EF core application and translation to the database will happen Anonymous to us all right and another important thing to point out is the naming conventions and the support that you get from if core when you follow certain conventions so generally speaking an ID column is going to be called ID sometimes you might qualify it and say League ID or the table Name ID now if core will identify either one of these conventions and it will automatically infer that this is intended to be your primary key and auto incrementing SQL identity column right so it will automatically see that naming convention and tell SQL that this is the primary key column so if you vary if you 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 if chord than to force EF chord on another pass it's not the hardest thing to do but why give yourself more work when it can be that easy so just by saying ID you will know that this is the primary key and fit up everything else for me all right now I'm going to create the next table or the next class represent the next table which is team so 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 so 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 the second would be a navigation property and most times you'll see them making this virtual let me just add that so public virtual league league being data type of the table the class that we just created right with the same name that's fine but the combination of these will leave EF quarter infer that this name matching the table name and the word ID means that this is a foreign key to that table right so if you've had to set up a foreign key in 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 if core will just say okay I know that this is a foreign key and I know that I can automatically uh include or automatically present the related details to this record through this property later on we'll see that I want to shoot too much information but I'm just trying to show you that from the get go once you follow these fundamental naming conventions you of course is going to do a lot of the work for you so that's actually it for now we have created the classes that are supposed to represent the tables in the database so of course the column names match the data types are you know 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 all right guys we're back and the objective of this lesson is to set up our data class so that it knows that it needs to connect to a 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 NET Framework sorry with Entity framework because right now nobody knows anything we're just talking about Entity framework it's named into the 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 get packages and let us go to browse and we're going to be searching for Entity framework core SQL Server so before I 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 other variation has a dependency on this one right so if you look at it it's a modern object based mapper4.net supports link queries change tracking and it works with a number of databases SQL Server Azure sqlite postgres and others so what you realize is that from nuget you can actually get different libraries to support the different databases so in our case we're using Microsoft SQL Server right but if I was to search for Entity framework core and just look through the different variations we see our SQL Server we see in memory we see SQL Lite you see postgres you're going to see MySQL 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 nuget all right as I said today we're so we're only working with SEO servers so let's just 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 OK accept any license agreements and let new get installed in our project so know that it is installed in our project if I look back in the Cs project file just by clicking the project file for the data Pro 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 align similar to this so you could say node package reference include the name of the library the version that you're interested in and then just do a build and it will automatically go and get the dependencies for you so you could have done that also but let us thus proceed using nuget 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'm going to create another class and then I'm going to call this class Football League DB well Football League context or Football League DB context we tend to put that in right so DB context just means that it is the context or the the connection let's just say context equals connection so DB context means DB connection file so to speak and then this is just letting you know what it 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 yeah of course so you have different DB contexts you have DB context you have identity DB context if you wanted like 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 that is satisfied I can now tell it about the tables all right so this remember this is going to represent the connection to the database so whatever is in the database needs to be represented here so 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 off 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 now I need a I 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 so right click dependencies add project reference and then add the domain project click OK and then we can just go ahead and use our using statements right and then I'm just going to duplicate that line and do the same thing for League so at the DB set for League our calling it leagues in the database however DB set for team objects we're calling that teams all right so this once again will generate or interact with a table called teams and anything that is in the teams table will be kind of serialized or converted to or 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 EF core class comes with a number of functions that we can actually override and do our own thing with but the one I'm interested in is on configuring so this means whenever you're configuring the DB context what do you want me to do right so I'm going to override it um 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 dot use SQL Server so you see that that Co that is courtesy of our DOT SQL Server uh EF core library right so use SQL Server and we're going to put in our connection string right here so 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 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 so local DB backslash backslash and then we're going to say Ms SQL localdb all right get that spelling right just take it 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 all right next up we have the initial catalog and this is basically the name of the database what are we calling the database so I'm going to call it football league underscore EF core all right so 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 SQL Server Explorer real quickly just to show you that it really does not exist in there I have a few tests databases and as you can see it is not there so when we come back what we're going to be doing is using EF core to generate this database so that we can start interacting with it hey guys welcome back so in the last lesson we had set up our DB 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 so our first migrate version will be to create the database because a migration will always say what existed what exists know what is different Let me give instructions to make those changes at this point there is nothing so what so the what is there no 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 nuget packages and that library is literally tools right we need EF core 2. so going over to nuget 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 app 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 the 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 our console app for both of these because we need to be accessed 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 so to run migrations using visual studio we want to go to the package manager console so you can go to tools nuget package manager and you see the package manager console all right I have mine available to me already down here in this panel and of course you can move the panels all about let me just reduct 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 so in this package manager console I can say get hyphen help and then type in Entity Framework and then that give that a few seconds and then it will generate like a nice document showing us all of what is possible through the entity Frameworks you see it starts off with that unicorn and it tells us about the library it gives us all the commandlets and what they're used for so the same list that I just pointed out in nuget 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 will I mentioned earlier that you have code code first versus database first so if you're doing code first uh well that that's the ad migration of the database if you're doing database first then you would want to scuff full 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 to you can script the DB context crypto the migrations if you're still old-fashioned you 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 other migration so the default project once again needs to be the executing project in the solution and I'm going to say add Dash 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 might 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 how there's nothing here I have no migrations no database yet I'm going to call this one initial migration I also like to camel case my names uh 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 uh camera 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 was successful but I have an error so let's read this error and in this course I'm not going to shy away from Earth because I know that a lot of people hate the errors that they get in EF core 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 newget but I'm going to try and do it through the project file so I'm going to create a new item group and give it a package reference to include Microsoft dot entity frameworkcore.design and I'm just setting the version that I know I'm using through the rest of the projects so this version may vary based on when you're doing the course so if you if you're taking this path then that's fine if not you can always go to newget and add the Entity framework core.design package like we've done for all the other packages now our next very important step is to make sure that we have our data project selected so I just did CLS 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 all right so for us that's data so I'm going to go ahead select data and then I'm going to just press up the op Arrow key which brings me back the ad 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 it has 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 so let us take some time now 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're writing a script if you're 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 call 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 okay that's an object being passed in here create table name of the table leaks right and then it's automatically going to make sure that it creates so even though in the DB context I had suggested sorry I'm trying to say a lot here I had suggested initially that when we are creating the data 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 and this would have been a little error right 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 no 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 EF core just did so it's creating the leagues 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 knowable and it is a SQL Server identity column Auto incrementing all of that was generated because we used the word or the name ID for this column so EF core inferred that this is what we wanted as our primary key all right and then you'll see here where it's seeing the column it type is string that's the c-sharp type but then in the database we're making it in varchar all right just let that soak in a little then we go ahead and add the constraints to actually meet this the primary key which is ID the ID column all right then it repeats those actions my great sorry 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 a column League ID so 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 EF chord and then you end up doing twice as much work when if core 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 right 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 right this is what I want to be done done means that if I am to undo this migration from the database this is what should be undone so don't just like a up is opposite of down the code that is in up is going to be doing something entirely contrary to the code that is in done 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 when you want to make that chain sometimes it's easier said than done so at least he of course saying well I am telling you I am telling the database what to do when the migration is being done and I'm also going to tell it what not to do or what to do when this migration is being undone so all of that is automated within this file 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 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 it looks at the migrations it doesn't know about and then just picks up from that point and executes the migrations going forward so here you see it's a applying migration and then it's telling me which migration it applied if there were five you 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 table tables I'm going to see EF migrations 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'll see keys okay so that's as a primary key and then you see here that League ID is a foreign key so 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 do it like that and let ifcor have complete dominion over everything in the database but there is a way that we could have generated an SQL script based on these instructions and then you would be able to execute it on your own so when we come back we look at that hey guys welcome back so in this lesson we're going to talk about scripting our migration so the case study for why you would 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 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 their particular behaviors that get added accordingly and the script grows with the different migrations all 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 then 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 and there we go so 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 all 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 EF core allows us hey guys welcome back in this lesson we're going to take a look at the EF Core 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 EF Core Power Tools through Visual Studio you are able to generate a similar diagram and see how EF core visualizes your database or whatever database it is connecting too 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 dialog comes up you just search for AF Core Power Tools and when you see it in the search results you just go ahead and hit download 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 dgml 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 this 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 reopen 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 EF Core 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 EF Core 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 the project that has the DB context we're going to right click go to EF Core Power Tools and then we'll say add DB context diagram and then it's going to generate a new file with an extension.pgml 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 metadata 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 know it 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 or what the different symbols or the different colors are so you see here red represents a navigation property the what's that purple or bluish I'm sorry I'm slightly colorblind but this purplish color represents the foreign keys and then the primary key is highlighted and then the different properties are so you can take some time to just take a 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 EF core 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 hey guys welcome back so in this lesson we're going to be setting up EF core with some additional options to spit out more details about what it's doing so it of course really quiet 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 SQL that's being upper 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 allow to console.right line so because we're using a console app it's on the right so we lock to control that red line and I'm going to add to the configuration or the 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 visual representation of what exactly is being done and I'm going to extend that to let it know that we want a log level so I'm just going to say log level dot information and I think I'll have to include there we go include a library for microsoft.extensions.logging uh I think I got ahead of myself just now so it's new DB logger category and then we close the curly brace here right and then we have the log level information then we close the parenthesis there so just take a look at that line apologies for that so we'll log to console.writeline new array and then we're just passing in the command name line right there DB logger category database.com 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 out to the console another thing I'm going to include is enable sensitive data logging so that enables us to see or it tells EF core that everything that is happening in the background that we 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 app you don't necessarily have to do this step I just kind of wrote some code so that we can start interacting with ef 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 login 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 timestamp we did this command and then it's showing you that we execute the 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 the 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 if core 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 EF core commands we can see actively the SQL that is being generated in the background we also see the runtime right this is 61 milliseconds but there might be times when maybe I queries running too long and you're wondering why we probably need to tweak it all of those things you can do when you have this kind of logging at your disposal hey guys welcome back so in this lesson we're going to take a look at how we can perform simple insert and the select queries using EF core now coming off of our previous lesson where we looked at the verbose logging and adding additional information to the console you have seen that I had this bit of code that we are about 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.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 just in case you're wondering where that name is coming from that is the name of our DB context here so 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 or 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.net core well let me not say regular.net core in like a bigger.net 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 AF core so I'm trying not to focus on those other aspects of development in this particular course all right so we're instantiating our Football League DB context I'm calling the object context um 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 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 leagues 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 leagues table in this situation I want to add now if we take a look at the add function you'll see here that the overload is expecting they well not the overload the parameter that is expected by the add function rather is off 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 that's the beauty of EF chords allows us to stay in C sharp because in sql's 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 would syntax will be it is purposeful but having to put it into the C sharp and then convert it and then do all of that he of course 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 then we're passing in the properties and League really only had idea name so because ID is already an auto incrementing primary key 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 so let us go ahead and run this command and see what happens so logs here are 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 SQL 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 are adding or you are updating data or deleting data you have to call Save changes because all this method does and the like methods for the 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 okay I need to update this I need to delete that but then until you save changes save changes actually says generate the SQL sent 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 async but then there is also the save changes without the async so that's fine I'll use it async since this is not an asynchronous function I will just keep it simple but once you're using asynchronous programming you know you can always use the async uh 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 async versions and I changed the main function into an async task and then because of that now I can I have to say await add a sink I wait save changes async all right 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 part of each property with each value again so you can always just initialize so I'm just going to say VAR league is equal to and then I can initialize a new instance of League let me let me just do this to cut down any long typing there we go and then I can give this one La Liga you can tell I'm a football fan right So La Liga so this would be the Spanish league know that I have my object that represents the league that I wish to add to the database I can now say add async 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 at a break point and run and what I'm going to do here is initial 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'll see now league has an ID of zero and the value La 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 add it to the database then you bring me to the page to see the data I just submitted but 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 so I'm just going to press F5 to continue with the execution and once again we see our very friendly message here seeing 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 anything other than zero when it goes to the add operation is going to give an error so I just want us to see the kind of feedback that we're going to be getting from EF core all 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 SQL 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 try to do this in SQL directly if 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 if core 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 don't know and I just want to do one more example of how Entity framework core can make life easier so in this situation I want to add a new league and we're calling this one Siri Ah that's Italian league and we add the league like we know we have to do then we save changes but then I need this 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 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 async task and I've called it add teams with League ID well I called it with League idea 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 alright so you can just go ahead and replicate that and then in this method I am trying something different instead of one objects 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 EF core's ability to do bulk operations now a little tidbitable bulk operations it is a feature that allows if chords a kind of batch multiple like add operations and so on into one SEO 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 so 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 right and each one I'm giving its name so this is Juventus and this Lee guide e is the ID coming over from our league object right and then the next team would be AC Milan following the same format but in the third one which is as Roma I'm doing something different instead of using the foreign key I am now using the actual navigation object and using 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 us see what this would do so I'm going to put a break point uh 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 all right so let me just step through the league hasn't been created as yet so step I use F10 so I don't have to go over to the context I know we have our ID of it if we look in our lead League object we see it updated accordingly then I'm going to use f11 to go into this method where teams is now defined so 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 as Roma is here with the actual navigation object instead of the league ID all right so League ID for as Roma zero but then League ID for everybody else is eight as expected however the navigation property for everyone else is null and for as Roma it has its navigation property now let us see what happens after we save changes I'm just going to press F5 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 yeah 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 is Roma and it does this 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 right so he of course is doing that for you in the background so 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 the name burn Munich and then I'm passing in this league object no note I'm not doing any AD I'm not saving changes in between these two lines right so fresh league first team and it's getting that first 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 async lines you have the option of course to highlight the table that you wish to interact with so if I said context.leaguesad async and then pass 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 core 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.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 so Ace of course 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 would 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 hey 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 this simply 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 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 crud so I don't know if I if I use that acronym prior to speaking on this topic but crud 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 look at the U and the D which stands 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 so link which is short for language integrated query right so it's basically like a dialect I call it a dialect of of C sharp right it's a way that allows you to write a query using C sharp and then of course e of course is going to translate that into SQL so let's get started first and simplest thing that you can do the simplest link query to retrieve data from the database would be to and I'm just going to Define a variable so I want all the leagues to come back and I'm going to say VAR leagues is equal to context which is connection to the database dot table I'm interested in which is leagues and just this is telling you that okay look in the BB set of leagues but then I want it in a list so I'm going to say that to list and then it's going to ask me to include a certain Library so I'm just going to control that and you see it's telling me that I need the library system.link in order to do this all right so 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 leaks that's pretty much it right so then what's going to happen is that context creates connection to the database it goes to the leaks table and then this to list just says extract the data bring it back in the tabular form but then materialize them into a list of League objects so just the same way that we had created the list of teams where is it here's my list of teams right the same way we created the list of teams it's the same way it's going to materialize this into the list of leagues for us so what I'm going to do is do a four each and I'm going to say for each league in leagues I want to console.writeline 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 outputs 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's 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 and decrypt why this SQL 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 word Stripe 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 see context dot table name dot give me them as a list now some important things to note when it comes to this syntax this is what we call the executing statement so without this that query wouldn't be run this would just say well lease is not just the hash set or the DB set of the table called leagues right so when we put on the two 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 for each Loop so it would just be like in a state of stasis here and then when we start to for 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 for 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 for each VAR league in context dot leagues the point is that this would actually kind of create a lock and and a very inefficient one at that as a matter of fact the more operation questions you have doing in that for each the 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 all day yes you might try this and it works but it is not the most efficient way to do it the most efficient way on 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 now stored in memory that connection is now close to the 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 operation 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 all right 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 go about your operations hey guys welcome back in this lesson we'll start looking at how we can filter our queries I know 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 asynchronous was happening in this method well thanks to Entity framework core we do have to list async and when I control dot I just have to add that using statement for Entity framework core and then thanks to that I can now make this asynchronous as well as add the awaits before the async call all right so let us move on now to our query filters so when we want to add a query filter and I'm going to say VAR leagues all right is equal to our 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 this where function takes a parameter that looks like a predicate or it is a predicate or a Lambda expression so the 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 just 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 a Lambda expression so if I used X right it'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 so maybe this one would you know read better because once again it's saying database give me the table called leagues and give me the records where any one League has a name that is equivalent to some value so if we wanted to find say Siri ah then that's what that would kind of look like right once again this token could have been Q it could have been X it could have been y could have been Z right so it doesn't really matter what you use as that token now if I do this uh it will not execute until once again we end it with our to list and since we have the async version of the to list we can say to list async and then I'm going to await that and of course make the method and async task all right and 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 and I wait this function call and of course and now it 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 made the condition it will be added to the list and eventually returned here so let us run this query and see what we get all right and then we see it is coming back we have our one record coming back if we take a look at the SQL we see it's just a simple select square but it adds on that where 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 a good protection against SQL injection so the reason EF core 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 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.writeline enter League 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.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 if statement but you notice that that looks just like an if 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 get to 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 Siri ah I press enter and then it took 83 milliseconds to go out and look at it now it's parameterized League name and then it is executing with the parameter so you see once again this is automatically kind of safeguarding against SQL injection through the parameterization right and we're getting back our Syria now we only have one one league with that name but when 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 so we're trying again with red stripe premiere league and the filtering will now bring back all of the records you see it is working right 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 it said dot equals League name so it allows us to kind of 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 enter lead 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 that equals but I'm going to say dot contains all right because in C sharp if we were to try to figure out if a string has another string in it will say that contains so that is what I'm going to pass over in this where expression so let us take a look at this so I'm just going to use one I'm going to enter one uh a bit of string to search for so I'm going to enter premiere right I know I don't have any League that is an exact match for Premiere 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 is looking for Premiere so this one has the where clause for the league name premiere of course there are none but then this one has the where League name like n or Char index l dot name is greater than zero so it just it just charred 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 Premiere coming back all right so that's another way that you can go about filtering your queries now when it comes to the like you can use the include the content sorry or you can use EF function so I'm just going to duplicate this and I'll comment that part out so that's option one another option would be to use the EF function so I still need the Lambda token it's the need to initialize this but I'm going to say e f dots functions Dot and then you'll see here I can do contains I can do like and a bunch of other functions that would be available to me if I was doing straight SQL there are some of them are right here for Access for us all right so I'm going to do the like because that is more more like what we're looking for so I'm going to say like and then I'm going to say Q dot name so the like method takes what is the expression or what is this string what is the database column that I am comparing on and then the second parameter would be the string pattern so if I wanted to put in a specific pattern if you're familiar with SQL already then you know that when you're dealing with like you have that wild card so I could say premiere a modulus which means it starts with the word Premiere if we put the modulus in front it ends with the word Premiere 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 like 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 a hard code what we're looking for so 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 La Liga I don't know which other league in the database might have the letters l a 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 or queries when we come back we'll look at how we can go about aggregating data Maybe one the first one in the list the last one in the list we want to sum of everything in the list those kinds of things so when we come back we'll take a look at that 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 link and EF core 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.leagues where and I'm just saying contains a now the case study for an aggregate function are one of these additional execution methods would be that you probably don't want the whole list probably you want something done against list or you want to cherry pick the list so when we talk about aggregate functions in Isco we talk about things like min max count sum those kinds of operations all of those are available to us through these additional aggregate functions now another thing that you probably want to do 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 so you'll see here that we have of course the async methods and for every async there's a non-async version right but it's not necessarily the other way around so not every method has an async counterpart but you'll see that later on so here I can say I want the first or default or I can say the first so the difference between first and first or default is that first will 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 we'll 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 our 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 where 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.leagues DOT first or default this condition so that is an executing method so remember going all the way back to our simple select say in context.league doesn't really do anything but then once we put on that method it executes we saw the same thing with the tool list and the where but in this situation I can just say first or 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 where the DB set we can access all of the execution methods so let's just go through them kind of in uh in order so we already looked at the to list we know that the two list is going to execute the select star statement we also have first and first our default and we just discussed the difference between them two the two of them so we can say leagues DOT first async or first or default async and once again they do have non-asynchronous versions to these methods right so based on your situation you may be using the async you may not be using the async but I'll just keep it kind of consistent and stay with the async we also have the single or single or default so single uh it kind of does what the first would do except first is seeing a list and it's just going to pick the first literally the first from the list single is going to operate similar to the first where if it is it is looking for one all right so 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 query you write 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 all right and you can always read up more when you just hover over the the method it will kind of 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'll see have count so you can say leagues.com to sync you will execute the cone query uh you have Long Count you have minimum you have maximum you have some you have a number of other ones like I said you can always just say leagues dot or context.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 meets your 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 async so 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 async you're expected to put in whatever unique identifier is there for 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 async work so let us try executing and see exactly how this would work out for us so I've set a breakpoint at the start of this method and I'm just going to go through step by step so we can see the SQL being generated I believe when it gets to the single and singular default or at least the first well 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 it will 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 then first or default if I'm not mistaken will generate pretty much the same SQL as the first but like I said it 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 all right so I'm just going to kill that execution and I'm going to comment out that line just so that we can continue with the rest of the execution so I continue 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 am finding the mean of something finding the max or 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 count when we ask for the where we ask for Content we ask for Long Count the difference between the two was that we had select count star for the count and then select count big star for 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 Contour big con 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 this one just to see the SQL 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 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 for a reference and I'm sure you'll find good use for them in the future 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 uh 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 the 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 seeing from some token so this could be you know we used Q in our Lambda Expressions this I represents something just like that queue we could say team we could say Q I'm saying I alright so from I in and then we say the table that we're interested in looking in so context.teams then select I so like I said it looks a bit more like SQL 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 what we look at that later on so when I do this query it is going to give me aquarable of teams right so that means I need to convert that into a list but let us let us execute and see what we get and then we can refine as we go along and if you look at that it actually executed this query quite perfectly right to give 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 acquiriable 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 iquerable and there are certain functions that are unique to lists that you probably would want to use later on in your coding that you Pro you can't really get when you're dealing with an iquerable so from time to time I actually just wrap this all and in 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 async task right so then we are getting our list and as you can see the the list the collection types work fairly similarly but like I said there are certain things that the list affords us that an iquerable 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 query so right now this is just doing a standard select Star right but what if we wanted a where class 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 our expression or any one recording the database so I can say where I name is equivalent to and then put in my record so the same way that we're able to inject in our 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 dot name and then we have the league name which I'm just going to do a quick prompt in this method for also what this would be team name so enter team name let me just change the verbal and there we go so we are prompting for the team name and our part off and then we're seeing from the teams tableware if that functions like I don't name right or expression and then we want to select I and they want to put all of that in a list to return to our variable called 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 the part of the team name we see our select statement with the like statement being generated for us and we're getting about Baron Munich since it's the only team that we have that has ba Y 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 abandoned writing my queries like this I'm 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 needs be 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 edit a record it goes and finds that record presents it to you make your 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 record 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 button in retrospect we realize oh that really should have been the Scottish premiership right so we know that we want to modify record with ID3 so what I'm going to do to retrieve the record is say VAR league is equal to context dots leagues dot find and I'll just use async of course if I'm using async I have to transform my method into an async task and then I waited here right so we're going to say find async and then remember that all we have to pass in is the primary key so we know we want record with the ID3 now when we get that League the change I want to make is League dot name I want that name to now be Scottish Premiership then we save our changes so we already looked at the Save changes from when we're inserting is 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 results of this or I want to print back to screen this record so I'm just going to create a method called get record which is specifically just to go and find this record so I have it defined up top here or I'm just going to say VAR league go and find the same record so after the save changes then it would have closed the connection to the database so 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 go so when I execute we see here that it is doing the select that's where it is doing the find so 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 I'll 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 reopening that select query that's a select query 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 happens 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 so you see of course 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 he of course 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 he of course actually tracking in the memory what objects it has if there any changes me 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 Etc Etc so this 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 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 updates 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 at an object of type team all 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 a of course going to look through the database tables for the record with that ID so I'm going to go over to the teams table and what I'm going to do is manually insert just so we can get through this activity uh team that is local it is in the red Stripe 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 because it is not in League eight and based on the teams in League 8 we can summarize that Serie ah 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 right 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 the correct spelling and then the league ID would now be the Red Stripe Premier League right so let's use League id2 we see that we have another duplicate but that's fine let's just use league with the ID too 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 leagues sorry 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 and note there is no asynchronous there's no asynchronous version of this we have the single and we have the batch version now we already looked at the batch the 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 async for that one all right and of course I have to make this async and then all our problems are solved so let us take a look at what gets done when we pass in this kind of update so you'll see that it is a similar kind of operation now one it doesn't update once again because it sees the ID right so it knows that there's a team with id7 and then it will go ahead find it automatically 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 use siba 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 queue to do an insert take note of that so the update command or the update function was saying that okay 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 toting this as an alternative to the insert I like to keep them very separate if I'm inserting something I use the add function if I'm updating I use 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 so I have 10 and we know that that we only have well no eight teams right or teams with up to id8 so id10 is completely wrong it will try to do 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 robot 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 us 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 your changes and then save the changes and then tracking will allow EF core to know that okay this record was modified so I have have to create the update statement for it because even if we 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 no 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 AF core 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 and we're back so up until now we have looked at all of the letters in crude except the D which is delete so we've looked at how to create we spend some time looking at how we can retrieve and the different ways we just looked at how we update and know 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 is related data all right so the simple delete let us take a look at the options that are available to us and I'm going to spend some time with the leagues table because we have to do some cleanup one we have duplicates uh 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 bud we'll see that we have two options we have remove and we have remove range so remove just 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 see that it needs the whole entity to be removed all right so typically when you're writing an SQL statement you would say delete from tableware maybe ID is equal to one all right and then you will just now 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 run time 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 they remove puts the entity in a state called deleted so it marks it to be deleted but as we 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 id4 4 because that is one of my duplicates all right and this one doesn't have any related records so this is a simple delete just finding the one with the id4 when we get it then we can say that is the one I want to remove and then as you know we say save changes of course using the async at async to the method declaration all right and there we go so when I call that one and I'm just going to comment out that second call for now when I run this operation we see where it carried out the select query to retrieve the record and then it went ahead and said delete from leagues the same query that we know and love all right of course with that where class make sure we don't wipe the tables in the database all right so that is how a simple delete really works now the reason kind of differentiating between a simple delete and uh Delete with relationships is that when we have related records a delete operation kind of becomes a bit more sensitive meaning well there is 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 they they 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 the ID 9 so I'm just repeating all the same code from the symbol delete what makes it different is that I know that I have at least one team related to the Bundesliga right so that means if I remove Bundesliga then that team would also get removed just the same way if I removed Siri ah 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 for teams was created and the foreign key constraint was put in by default it gave referential action dot 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 their foreign key value will now become null right so it will delete the parent and just set all the foreign key values to null and then you have no action which is well that just it says just ignore the constraint and then another one that says default which will 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 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 at that and you get a circular reference kind of situation or or different tables are lying on the same data so efco 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 SQL 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's the second delete for the related record let's verify that that happened so if I refresh teams Bundesliga is gone if sorry leagues and then if I refresh teams that means Bayern 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 SQL 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 ef core at that point so that is one of the dangerous situations that you 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 a 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're going to retrieve it and then just tell it that's the one we want to remove save our changes and then that's it 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 so I've already written some code you can pause right here replicate it but I'm going to walk you through what each line is doing so we start off with the method called tracking versus no tracking and in this method I have two statements one where we're getting uh value from the database and I'm just calling it with tracking and then another one where I'm getting it without tracking so if you take a keen look at the two lines the difference that you'll note is that I have dot as no tracking in the statement so the first one is what we're used to context.teams I want the first record that has the ID too all right that could almost have easily been a find except the as no tracking does not work when we're using a find all right so if if we were to try and find like we would have for delete an update and so on we can't put as no tracking in this kind of statement hence my writing on the first or default and then for consistency I did it both times all 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 the id8 now this kind of seems not intuitive the way it's written because you'll probably say so why don't I say give me the first team with as no tracking the reality is that after the first or default statements and all of this pretty much becomes the object that we're looking for so in other words the only thing I can do after first or default and well we're using the async so I'm just going to wrap that inside of our parentheses so after this statement executes right and I only have to do that because it's uh async if it wasn't async then I'll I wouldn't have had to do that but at the end of a first or default operation we're going to start interacting with the actual fields that are in the object so that is why that has no 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 means this condition and that is a statement now the the real advantage to not Tracking 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 he of course is going to have to be monitoring a hundred records and that's just on one request what happens to the other hundreds of records that you might be juggling in the system right so then the the EF core engine has to work overtime to be tracking all of these all the time so in a simple operation like maybe you're just doing like a read-only list like you're listing um 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 the wrong method so that we could here we go made the change and then it is being tracked in that moment to be saved all right but then for a large read operations you can always use the as no tracking to kind of reduce the attention that EF core has to pay to each record Being retrieved now what I've done after retrieving the width and with no tracking I've made changes to their respective names all right 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 entities 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 um but I'm sure that this will get updated afterwards anyway but we will see all right so let us go ahead and execute I've set a breakpoint at the Save changes line and I have the variables in the watch so I have the end 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 so we actually have some enums given to us by AF core where we can say entitystate dot and you have modified added deleted anything that pretty much will be able to do in a crude context we can tell what what change is about to be saved or what state it is in right before it is about to be saved so we see here that record with id2 is in a modified state record with id2 was the one that we retrieved with tracking 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 id8 so no matter what change we made to it it just doesn't cure 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 EF core 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 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 move 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 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 E of course so that is one of these situations so sometimes you need to release it from being tracked but 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 hey guys welcome back in this lesson we want to quickly review our one-to-many relationship and how if core 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 EF core 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's 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 int and int by default cannot be null so you realize that that migration that was generated would have uh not knowledgeable here it is League ID was the column and not and nullable is false meaning it cannot be null in the database well c-sharp supports nullable data types if I said int 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 I 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 outer column is now seeing that nullable is equal to true all because we put that question mark So in C sharp if you make the data type nullable then that is how EF core will know that it's a nullable or null not null or nullable in SQL should be true pretty much right so it's saying that all type was in the new type is in TS but it is now nullable so that is one of the ways that we can make a foreign key notable so why would you want to make a foreign key Noble 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 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 will 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 replaced it with only the navigation property that is also going to try and generate a nullable 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 is 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 knowledgeable 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 undo this action it says it right here remove migration so remove migration will always try to remove the 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 you see it's 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 then I remove 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 I collection so this can be an Eye collection it can be innumerable it could be a list it really is up to you but it will 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 SQL 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 query the teams table to say get me all the teams with the league ID right or you just get all the teams with League ID but then you have to inter join on the league table to get the details of the lead 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 id1 and include all the teams so automatically I'm getting the league I'm getting the name and I'm getting all 20 30 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 and 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 the system that is designed to help you do things better so in this particular situation I am referring to say the foreign key creation because you have you might have other ideas as to what you want to name this foreign key column and I 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 if core 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 EF core can do for you hey guys welcome back in this lesson we want to start looking at many-to-many relationships now the case study for a minute to many relationship would be when you have many of 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 draw.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 arrow to connect them all right so we have league and we have team now I need a new entity in the mix and I'm going to call this one match no a match is going to comprise and I'm just going to write out the fields and entities you already know what's in league and team but this one is going to have an idea of course it's also going to have home team and away team which says hey 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 there's many too 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-to-man is really between many teams and many teams so that means we have two we have Team being related to this table twice all right so forgive me if you're you're kind of 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 foreign key for the away 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 naming 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 all multiple fee deals that probably repeat across all tables like maybe when you're doing auditing or you know the update created those kinds of fields that maybe everybody needs to have because clearly all of our Fields follow in the same convention or all of our tables sorry will 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 common and then I'm going to add a class inside of common that I call this domain objects and this is not a naming convention either I just call it base domain object people call it base 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 so it's really like I feel safe 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 alright so I'm just going to do that for much so no match by default has an ID field team and I 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 day you can now 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 a property that represents my home team so 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 called home team and away team ID if core doesn't know or 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 all right so we have foreign key we have foreign key and then I have the date time so I'll change this to date instead of time because date can capture date and time right so we'll know the date and the time of the match as opposed to my initial design which only said time so 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 so I'm just going to duplicate that and add the new model to the DB set and I'm calling it matches all right 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 League we have we have team referencing the league table so we know that our 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 at a time for any one record or row but then a team can have many away matches and whole 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 right up until now a of course still oblivious to all of that so what I'm going to start by doing is overriding our on model create method so we have on 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 not to say every migration will repeat the code for these but it just means whenever you're building out the database and do it dealing with the database this is what I want you 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 the fluent API so our entity called team and then we just I'm just breaking lines so we don't go too far out and then we're going to say dot and then you're going to see a bunch of options here now I have 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 right and then we just Define a Lambda expression m dot and I'm going to say like home matches a team will have many home matches that is true then we go to the next line with one so what is going to infer at this point know is that you are going to have many home matches with one m dot and they know you are seeing properties from the whole 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 match is or a whole match a match only has one and I'm going to say if I'm dealing with whole 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 am telling it about so 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 he can't help EF chord 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 so 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'll 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 are 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 and truly you just need to put the list navigation property on either table and then EF core would just infer that there is a customer product table to be generated sorry I stated 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 would be many being the list to many so there are different scenarios and each scenario may come with its own quirks this is The Quirk of our scenario and I think it's good to do the harder scenarios at least you get to see what might have to be in place should you run into difficulty and it's 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 the is on the column away team and it's between teams and that ID and on the latest 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 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 show an errors because these are the kinds of things that people afraid of this kind of technology so 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 in 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 I have never been warned by SQL Server directly about something like this but if core 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 now and following EF course suggestion that you know this delete behavior is 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 actually see where that is reasonable because when I when I said it earlier that you delete a team and it automatically wipes out the matches that could be problematic all right and especially where a team could be on the home team ID or the away team ID and then you know that 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 it of course 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 nullable so that is one way of setting up a minute to many relationship like I said it may differ based on your situation given the structure of the r database this is what we had to do to accomplish all of this these are General guidelines of course so 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 hey guys welcome 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 it 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 objects like we said because the main 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 a name suggests will be the foreign key to the team table now our team or teams 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 meet 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 nullable 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 no 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 am 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 all right so let us take a look at the migration that we can generate from this alright 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 told it as a vital step towards creating a table and that is to include it in rdb 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 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 e of course 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 coaching 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 EF core will kind of do behind the scenes even if you miss a step so you want to be kind of deliberate but if core is going to make certain assumptions for you based on how it knows it needs to operate let's just go ahead and update 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 is 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 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 hey guys welcome back this is a quick video I just want to show you how you can update your database diagram so we'll be 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 you did it 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 EF Core 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 EF core this is a one-to-many and this is also a one to many you will 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 see it says 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 and see exactly what's happening this nice overview diagram is a great way to get up to speed with what references what and how these relationships are formed 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 a more indicative scenario of what would happen in a software situation so 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's typing in a team name and then they would probably indicate which League this team belongs to through a drop down list so you would Pro you would have this scenario if you require 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 us say they wanted to add a new series uh 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 its 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 id50 when we saw that it was the max e of core well the database itself will reject that if core will attempt it the database will give an error and then it bounces 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's scenario one when R well 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 or 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 the 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 it 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 I have our teams 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 leak so you can tell that I copied and paste it right so this new league is sifa short for Cayman Islands Football Association let's work with that and these are some of the teams that would be in sifa 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 mini to mini tables so remember that matches represents our mid to mini 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 mini to mini and remember that our match or our minute to mini 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 they know 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 two four one in this scenario the next part is the fact that I'm using this ad range so up until now we've always just been doing ad Right add represents one we're only passing in one object when we say add or add async however when we say add range or add range async 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 then for each one in the list you 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 Jose Mourinho and he is going to be coaching team ID3 now remember that this is actually a nullable 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 all right there's the same way that I can add Jose Mourinho with the team ID I can add Conte without the team the only thing is that he will kind of be orphaned heel exists 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 Jose Mourinho with his own so I had commented the other function so 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 error so we see we're adding Bundesliga at the top we're adding Bayern Munich we're adding Fiorentina we're adding FIFA so you can see that even when we clumped the objects the EF core once again knew intelligently which one is the dependency insert that gets 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 C4 so C4 has an ID of 11 and that's why Rivoli and Walter house both have 11. that's in 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 now you're probably wondering why didn't you just create one insert statement and do it line by line well that goes back to the bulk operations the EF core team 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 a 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 our web application scenario just remember that you would have given you use a 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 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 EF core and let afcor do the rest 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 at scenario four 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 SQL you would also know that you have to do either yeah some form of join it could be right it could be left it could be inner but you have to do some form of join in that SQL 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 at the simple select query here for reference and we know that we execute with that to list and that is how we get it our data now 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 their 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 not all in one call for whatever reason your scenario May determine why you would need to write this kind of query 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 VAR 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 now 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 alarm the expression is going to be used based on the data type which is expression Funk and then you'll see the league object or the object of whichever table you're on all right so we're basically seeing 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 query being run to give us back select basically select star from the teams with the inner joins on the team IDs matching the league ID or the league ID rather in the teams table match in the league ID from the leagues 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 so because of that referential Integrity that we have enforced EF core clearly knows how to formulate that 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 acquiring the leagues table it's going to bring back that League however the team's object will be no so let me just put a break point there at the end of that execution just so you can see what that will look like so this is the data coming back in that leagues object and if I expand then you'll see id2 red star premier league and the teams that are in that red square premier league and all of the details so this team has no coach it has the id7 you see that it is related to league with id2 and you can get the name so right there you can say League object dot teams Dot and it just access anything you want or from the League's 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 mini 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 VAR team is equal to and we awaitor context as it calls our methods here so if you look at its context.teams dot include but then as we established even when we say context.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 too or let's say three I think I'd put in the coach with ID3 we can just go back and check so team with ID3 there we go so I'm going to say dot include then put on my executing command which is in this case first or default because that is the executing I only want one so first or default takes the Lambda expression where I'm going to specify that I want the team with the 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 our SQL 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 or default we're selecting the top one and then we have that where class to filter down to where the team ID must be three so I 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 the where you place the first or default has a lot to do with if the statement will work or not well it will just give you an error so if I put context.teams then first or default I cannot do an include after first or defaults 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 async that is not being shown clearly so let me take off the async and show you that when I say dot I'm really just getting the properties how much is ID right so if that's what coming that 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 Nifty 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 our default and those red lines are because I need the async and there we go all right guys so for our next scenario we're going to be looking at Grand children 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 we 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 much 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 we 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 the 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 hour away matches all right so I am team number one want to see all the away matches and then we can see that but then when we include their way 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 the object that represents the table it 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 that way it matches and from that way matches which is of type match I want to include details on the home team 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'm I can say get me the teams and for every team you get me get me all their their way matches and all their whole matches once again though when it's a whole match when it's an away match I need the details of the home team when it's a whole match I need the details of the away team so then I can say after you include the whole matches I want you to include the details of the home team and just 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 although we called 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 up on foreign keys so 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 then 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 that 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 has doesn't have the navigation property I'm looking for and expect to find it right so I include that way matches then I say when you get that away matches then include the home team four that way matches objects then I go ahead and say also include pretty much the whole matches and when you include that I want you to then include the away team details after you 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 t0 T4 T2 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 I will call that t0 then we left join that on and then we're selecting from matches and then we're either joining it back onto teams right so the 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 bring 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 the situation that we have to work with when 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 EF core 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 away matches which is only one so this team with id1 which is Juventus only has one away match and one whole match okay fine there are way much we know there are a team that's implied because we we included the away matches so we know we are the way away team Juventus is their 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 Inter Milan and I think Juventus stands a good chance because they don't even have a coach all right so then Juventus may just win that away match if we look at the whole matches it's the same situation we are the home team this is the whole matches so obviously Juventus is the home team but if we look at the away team we see that the away team is as Roma and the coach's null but then if we had included the code 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 coaches want the coach details so I can say once again dot 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 code 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 our way team is as Roma and the coach for as Roma is Jose Mourinho 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 ef core he of course trying to make your life as easy as possible so this just cut down or a whole bunch of joints and inner joins for you because if we look at that SQL it just got a bit bigger because now we have to interjoin or left join the coach on the table and notice that it's saying inner foursome and left for some 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 knowable it will just do an inner because it knows that they have to be an absolute match for me to bring back something now 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 enough query we await context.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 whole match and I want to include their coach and of course it's a list so that is the SQL statement that gets generated for us you can sit down and 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 would might be tempted when you look at the data you might be tempted to say well why didn't we just check if whole matches is null and I mean I would understand because we're saying how much is that count is greater than zero what if I had said how much is is equivalent to null since that is the data that we see but how exactly would EF core 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 this generated is not bringing back data that we expected now if we look at the SQL query that was generated we see it's a regular select core with the left join but then this this filter is just throwing it all the way up so that's e of course we are seeing I can't make uh I can 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 overzealous with how we're doing it and that's fine it's good to experiment but just be careful so that's it 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 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 like a custom object with only bits of data from all of the data set being returned and that is all you really want to tax it across in your system so let us go through some examples of what you know when you would probably need to do something like this all right so 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 it's a VAR teams and whether it's one team or many teams the principal 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 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 all right so in that scenario what I would have to do is add a select so I would say context.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 to 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 any one that you do so if I wanted all the league IDs from the team table then I would be getting a list of integers uh this keeps 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 will at least 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 comma 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 name column one comma column two etc etc all before the from that's not really an option here so that's why we call up 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 a 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 I knew what all 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 you see 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 now comma separate because now 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 go which field names it has and what values it gets so team name getsq.name and then coach name and don't be intimidated by the fact that what you're typing doesn't come up in intellisense because once again we're doing this on the Fly and it's just going to work with us a q Dot and I can say dot coach dot name so right there is going to have two properties team name and coach name if I hover over teams then you see it's 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 now say four each and I'll just do a four each year to print them out for each item in the list of teams I can console dot right line and print team and put item.team name and I'll just do a pipe as a delimiter coach and item.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 fact that team name was a property all right so that 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's probably it shouldn't go inside of domain but let's just work with it I'll just call it models uh by right you would probably want to create a new project but this is a very small demo project so I'm not going to go to while I was a number of projects but a bigger project you'd want to have this in a dedicated space not mixing with your domain objects but I'm calling them 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 us say our model would be a cloud called team detail all right so team detail is going to have a few properties let us 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 that we would ever be able to get just by running a regular query would have to include everything about the coach and would have to include everything about the league name and then it can get kind of annoying on the receiving end when you have to say team dot coach dot name team.ly you understand 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 this initial query let me just say the entire thing and I'm going to add an include for the team so remember it's your query whatever data you need you go get it so I need team detail sorry the league it's a team so we're looking at the teams 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 right 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 and I'm going to say Q dot League dot name so off 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 hmm uh 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 fees in the anonymous type at the time that developer did it because when we have a strongly typed class we can all just look at this class definition and once we know that we're getting this data type and 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 SQL 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 or First Command which was selecting one property we see that it seeing 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 or 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 if core generated exactly the statement it needed to to get exactly that data so it's selected T name as team name which is the Alias right so we called the field name differently inside of our our Anonymous type well it gave the Alias to the column inside of the SQL and then it is 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 Juventus has Conte Roma has Jose 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 l dot 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 joins and then when we print it or we see team name coach and Siri ah Syria uh 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 am 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 you'll simply extend your model and extend your query accordingly all right 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 leagues 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 a 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 base short for Baron Munich maybe and then we're seeing 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 so we say await context.leagues dot 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 any because I'm already inside of a Lambda expression I can't reuse the same Lambda token so Q here is already tied up representing a league record so I can't use Q in the any 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 now 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 querying against related records all right so once again teams is not the main table this is the main table however our 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 but 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 Bundesliga hey 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 scripting 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 acts like a source control for your database so in this particular list we'll be looking at how we can add a function how we can add a view 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 time 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 a 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 SQL 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 now 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 there is these are going to be empty because I made a migration it 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 down 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 it 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 migrationbuilder dot and then I can say SQL which is going to take a parameter of type string so it's expecting the SQL command as type string right there so what I'm going to do is use my ADD sign to turn this string into a literal string and then I just paste that SQL statement right in there so that is what that is going to look like so migrationbuilder.sql and then you pass in any SQL so although it's SQL to create a function it's a SEO to create a view like we're about to do or a store 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 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 who are the things that I undo 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 migrationbuilder.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 dirtier 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 this is overzolos and repeated the word matches so let's try that again you probably detected it and didn't get that errand 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 scalar functions there we have our function appearing so this migration was a success now when we come back uh we're going to review how we can interact with a view later on we 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 where querying and it's going to be slightly different from how we create query our tables and there's certain rules that we have to know about so when we come back we look at the modifications needed for that 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 SQL 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 SQL 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 post tweaks that's up to you I'm not being prescriptive I'm just saying that this is my convention to know the table is different from the views all right you could also create a whole new folder and put in a views called review 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 coming 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.cs we're going to follow suit with what we've been doing up until now and I just created a method query view which is just going to have the sole purpose of calling context.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 our spin now as soon as it hits the code I'm greeted with this exception and it's in the entity type requires a primary key to be defined if you intended to use a keyless entity type you have to explicitly know you have to explicitly let it know sorry that it has no key all 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 EF core like we've seen thrives of relational Integrity primary key is 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 a request so in by that standard our new table or our new entity rather it doesn't know if it's a table it doesn't know it's if it's a view book 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 V1 yes it has no primary key so the exception so that in the model builder we have to let it know that it is 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 okay all right so I'm aware that I shouldn't try to trap 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 to view and two of you basically allows us to specify the name of the view in the database that it should look for so this is like an an extra precaution to make sure that it doesn't see any of this as any new new elements or SQL subjects or new work it has to do the next time we do a migration it will 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 bit of code again and I'm going to leave that breakpoint 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 will see we are getting back details because the view is executing correctly now we're getting button 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 is 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 will put it in the model builder as soon as I can find the 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 name you can always say to table and then give it that name much like what we did to view 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 SQL and especially know 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 SQL commands and that's from SQL raw and from SQL interpolated now note the syntax await context dot teams so we still have to specify our table and then we say from SQL raw then we can put in our raw SQL statement and then to list now I must point out some of the limitations here and the dangers associated with this raw SQL 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 what they give you from SQL 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 SQL Royal just comment out 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.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 con or the entity behind the DB set so in other words if I just want it but the names of the teams and I said select name from teams right or let me let me keep it simple ID comma well not simple let me use ID comma name right I only want two columns from the teams table if I attempt that then we end up with this exception saying that you know there are certain there are certain columns not represented in the query set because it tried to query only two columns what it's expecting us 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 you know join say can't directly have related data however I can at this point say include so after writing on 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 Jose Mourinho all right so that is one of the things that you can do if you ever need to run this from SQL raw and need related data just know that you can do the 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 that 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 user 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 or curly braces and then place name in that area now if we take a look at the SQL that gets generated for that we will see which we didn't necessarily look at the 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 select star from teams where name is equal to and then you 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 quote the value and close single quotes when we're dealing with a string as a result it is complaining that it cannot find the keyword as because as Roma is being seen as as a command in SQL so to remedy that in our interpolated string we'd 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 AKA bad practice SQL injection all right so if you ever have to end up taking some parameter and using a raw SQL 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 SQL injection so in a situation like we employ the services of its close cousin from SQL interpolated so if you look at this one it's it's asking for a formattable string right from SQL is only asking for a string but then from SQL interpolated is asking for formattable string which means that it will only accept one interpolated string and two we don't have to treat this SQL 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 quotes around the interpolated or the injected value into the string so let's take a look at that SQL 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 0 and it has defined the parameter up top and that is the difference between the from raw from SQL raw and from SQL interpolated so I would always recommend that if you have to mix and match with variables to pass in a raw SQL statement used from SQL interpolated for your own protection and peace of mind hey guys welcome 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 our 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 so I have on screen a new migration add delete team by idsp SP short for stored procedure and I have the code to create the stored procedure called SP 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 method so you 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.cs and you'll see I already created the method execute non-query command now when we're going to execute the non-query command there's few things that are going to be different between this and when we know that we're expecting our results 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 rows 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 bad 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.teams we know that you just get an error when it tries to execute that method all together however in this situation since we don't know what the stored procedure is going to be doing we don't know which table is directly interacting with we say context.database instead of context.db set name so context.database and then the methods that we get are going to be slightly different in the case of the DB set we get from SQL raw and from SQL 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 Raw async hence their weight we're using the async one and then similarly we have it execute sorry SQL interpolated async so we have the interpolated version and we have the raw version no 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 this third procedure will 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 SQL raw async 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 passing 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 SQL that has been generated and I see here it has been parameterized with team id2 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 id2 so I can't use that one that's 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 breakpoint this time which means that everything got Excel executed properly now if I look at affected rows I'll see 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 SQL command statements using raw SQL hey guys welcome 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 rules 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 and if core allows us to hard code that so that whenever our database is being generated whether it's being 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-go 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 on model creating method right so under all of this we can say model builder Dot and then we specify an entity so 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 so I'm going to say entity coach and that entity code has data and then this has data allows me to now specify as many coaches as I need to so that's not curly braces that's parentheses right and then in these parentheses I would now start giving it new coach objects so I can just say no new coach I can specify the ID from the get-go so I'm going to use some IDs that I know won't clash with the existing ones so ID 20 name is equal to I'll use my name in this situation and the team ID well I can leave those as no because I'm seeding the coach now that brings another important point if you have hierarchical data then you need to make sure that you are seeding 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 see the data with those dependencies because I can't be expecting to put in Trevor Williams the coach of team id5 but then I'm defining the team with the id5 after all of that all right so let me let me show what exactly what I mean so if I say team I want a new team so 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 SQL statement is going to be generated so I can be creating coach and inserting coach with Team ID 21 team ID 20 doesn't yet exist so I have to make sure that I maintain my ordering according to the levels of dependency now like I said you can put in as many as you want so I have new coach here and then I can just come and separate as many objects of new coach as I need so if I wanted three coaches in the system initially uh of course the IDS can Clash and 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 so 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 in 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 seating for multiple tables you can see that it can get quite cumbersome and honestly I don't like seeing all of that in the on 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 like 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 so 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 seed configuration now in this class which I'm going to make public I am going to inherit from I 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 all right so I can now just say has data I'm just going to take this part though that's for team sorry let me just take the has data part of it I'm going to cut I'm going to erase all of this from that from the DB context and then over here I'm going to say Builder Dot and then put on the 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 seed 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 seeding let's do the same thing for the teams all right so I'm 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 seed configuration and then I'm going to update the references in the file so this is for type team this is for team and then our has data section is going to come directly from this so I'm just going to cut and paste over in its dedicated configuration file then back in the DB 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 other default teams and coaches I am now seeing that the migration Builder is doing something different we have 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 because 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 all 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 dawn the delete data is just reversing all of those inserts with that delete statement so if I do an update database I'm going to get this error all right so 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 be seeding 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 Identity type configuration of type league and they were just building one league with the id20 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 step at the update database and after the levels of logging and being very verbose we see done and we know now that we have this data so once again this is no uh 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 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 am doing my build I'm when I'm creating the model I need to be aware of these configurations for seeding 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 so 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 like auditing purposes you know when you have people entering data into your 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 named 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 I if you just look at the column names there they're not really very cohesive right they 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 if you have um 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'd notice that it just added those columns to every single table that exists server 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's maybe a mistake with it we can always remove the migration but look at what happens when I try to remove the migration 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 okay you see all of that so 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 I feel 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 I don't know maybe this error message could have given us a bigger hint or a better hint at 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 these audit Fields so I'm going to simply just double click the file very slowly or you can right click and say rename and I'm just going to copy that file name I don't need the dot CS I just need the name because this is the name of the migration that is actually stored at timestamp underscore and whatever verbiage you put behind it so in package manager console I'll say update Dash 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 down all right so that's all we'll talk about when you have the up and you have the down so the the commands that it executed just now were to remove from everything see there it's altering and it's dropping the column modified if you look in the down portion well the migration file sorry if you look in the down 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 when 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 our previous version I can actually go ahead now and safely say remove migration and it will do it willingly happily and without any queries or qualms and there we go it has removed that previous migration so I'm just going to make the adjustments to my base domain object there we go so I've updated these column names to be a bit more descriptive of what is going to be in them right create a date modified it created by Modified by now after doing all of that we're just going to add our migration again to add the date audit fields 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 non-nullable date time field in the database you have to 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 not knowable date field so it's putting in that default date which in the database you would probably see something like zero one slash zero one slash zero zero zero one right 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 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 put in a modified it at least if we 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 20 and 30 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 so 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 safe changes right so we see quite a few options of save changes to be overridden we have the default one which is what we call context.savechanges we can override it and then we can carry out some operations here before we say actually save the changes so you notice here that save changes really returns a 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 manipulates of these values before they get saved to the database so there are quite a few things to be aware of when you're in this area all right firstly there is an object given to us called change tracker and I think we looked at this earlier but let's just look at what it allows us to do so we have change tracker and then we can say dot entries so this actually gives us the list of entries going in to the save changes are being tracked by the context in memory so I can say VAR entries is equal to change tracker dot entries 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 and 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 change 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.update and passed it in and then there is 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 okay I should be tracking this as a modified object deleted well self-explanatory anytime we say context dot 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 uh concept that you can probably adopt for different reasons right so you might have some complex audit logging or a second database so you need to write logs to you may need to track when was this added when was this modified is this about 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 right and then we can go ahead and save the changes afterwards so I can extend this innumerable because this is this function is just returning an innumerable 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 enum to filter on added or Q dot state is equivalent 2 entity states that I just did added so this would not be modified right so right here we're filtering out and getting all the entries that are either about to be added or modified next I'm going to have a for each Loop going through these entries and I'm going to be converting them into the base domain objects so just 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't 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 in the object you can see the original values so even for audit logging purposes you can see what were the properties before or the values on the properties rather before and what are the values no 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 so I'm just casting it into the base domain object so that I can start interacting with the fields all right so for each one I'm going to say auditable objects dot create a date nice and simple is equal to date time dot no because I'm about to save the changes so it must be know that this this the uh modification sorry was done so modify date it's the same time but then think about this now 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 to create a 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 so 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 it if the entry dot state is equivalent to entitystate dot created or added rather right so when it's going through it will say okay for this entry I'll set the modified date so whether it's being created or 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 you 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 so this is like a final hoorah 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 objects in common and once that is in place we can go down to the audit Fields regardless of the the higher level type I'm going to say and then made the adjustments and then save the changes now before I moved on I just noticed that there are zero references to this method so in my program.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 that save changes async however we have overridden just save changes so that's that's my bad slip off command 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 over right because I think that there are several so I just want to make sure we get the right one so we have saved changes save changes and save changes async 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'll have to provide a Boolean 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 so I'm going to use that one and just re-organize my code here all right there we go so we're public override task int save changes async and that is the parameter so you can go ahead and make that adjustment and then in addition to that we need to make this async because well it's async so we need to make it an asynchronous method and then if it's calling asynchronous it's a asynchronous method which means that we want to use the asynchronous method down here also so I can just change that one to see if changes async and it requires an await so I can just pass that away and that should be it um just just so I have com consistency I'm going to use the same cancellation token in the parameter right there all right so that with that adjustment now 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 apologies 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 where 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 P3 and I'm getting that date time value right there all right and when it's being inserted create date modified date P2 and P3 are going in accordingly here's oh here's P1 right there's P1 which is our created date and then we have P2 being null because that will be the 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 call Save changes now we've overridden it to do some additional things before that SQL statement so that is a nice little way to inject your own little logic and a little consistency or clean up 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 username 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 username as or as any form of value here because save 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 would be a fun activity 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 on 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 varchar in the database but then it's varchar Max do we really want 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 in constraints might include default values so we had added these base domain objects and I'm just using these as an example where we have the date time 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 DB 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 are blocks of code really are fluent based on fluent API so what we're doing know is using fluent API for some validation so I'm going 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 it then opens up uh for another Lambda expression so 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 now see 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 in VAR chart what if I wanted it to be varchar 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 so 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 uh like Borussia Munchen gladbach in 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 so when we talk about indexes they're really high speed lookup points so I'm going to remove I just duplicated that line and instead of saying that property I'm going to say dot has index and then this is not 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 so that means if we search by the name it should be a high speed lookup point for the data so the query should run relatively quickly and I can actually just do the same thing for our league and for our coach and notice all I'm doing is just copying and pasting and replacing the names accordingly right because they all have similar structures other 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 like a database for a school management system or a book uh storage you know 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 do you have that ID number then you'd want to specify that it is unique so you so I could actually extend the has index to say that this index should also be unique right and there are other things that you could apply some of these have honestly they never use I've never had a situation where I needed to use them but that's just me you can explore and find uh 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 course 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 say h dot name H dot team ID comma separate etc etc and then by extension I can make all of that unique so that means that come combination should always be unique so that's more like making a composite key at that point all right so I'm just showing a little tidbits I 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 dot data apologies but now when we look at our alter statements in you see here for the name in teams it's now varchar 50 and the old type is envirachar Max all right so there are a lot of performance constraints performance inhibition sorry that can come about by just leaving your data types as varchar 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 in 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 all right let's see what this red line is so it is saying that create unique index statement terminated because a duplicate key was phoned 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 teams table 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 AC Milan so many times in the database all right so we see that the constraint works so 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 about to delete also go to matches and make sure that none of the records I was about to delete had matches so there that is a practical example of our constraints in in operation right because we did set the referential Integrity to not be Cascade but to restrict so those 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 EF core hey guys welcome back in this lesson we're going to be looking at resilient Entity framework core database connections so when we talk about resiliency it talks about retry logic so as it stands if our application starts and we cannot connect to the database immediately it's just going to give up The Ghost and say I can't find the database please you know look somewhere else sorry I just can't run uh what we would want to do though is build in some retry logic and this would especially come in handy when we are building distributed systems and the database maybe over a huge Network there are times when the packets drop on the network so something as simple as a blip could affect your application so what we don't want is to let that quote-unquote blip affect the user experience yes we are working with a console up but the concept would be the same if we're working with a web app or a desktop application the fact is that we want and Entity framework to be a bit more resilient when it is trying to connect to the database so what we're going to do in our DB context and that is the football league DB context not the auditable one what we're going to do here is extend our options Builder a bit so we're already using SQL Server I'm going to press the comma and then I'm going to say SQL Server options action and that's colon because we're just naming the parameter and then here we're going to say SQL options which is our object for our SQL options now what would happen is that yes I am doing this on configuring inside of the DB context but if we're in like a.net core application web application that is this would be more done in the startup or program.cs based on which version you are using at the time so the the factor means I would have dot use SQL server with the connection string which would probably be coming from the app settings and then you would say comma and then we are putting in our SQL options they actually don't even need that that verbiage right there it's just the name of the parameter but we are putting in SQL options here so I'm just going to try and space it out so it looks like it makes a bit of sense right so we have the SQL options let me all right and then in the SQL options we can say SQL options Dot and if we look we'll see retry on failure enable retry on failure and then that is a method that's going to take a few parameters so we can say Max retry count okay I'll say try up to five times right I can also say Max retry delay how much time in between I can say time span Dot and say I want from seconds maybe try every 30 seconds to connect right and then I can say err numbers to add and you could leave that as null we don't even have to set that but the error numbers the ad would pretty much or here it is this strategy specifically tailored to a SQL Server including SQL Azure it's uh pre-configured with error numbers that are transient errors that can be retried right so it's just saying um these are these are numbers that would help you with probably a debugging probably not absolutely necessary right so the fact is that this kind of ties in with our transactions because when we're carrying out a transaction we might try to commit and the commit failed basically because it couldn't connect to the database now that's not a good reason to give up on the operation so we can wait 30 seconds and try again of course the time between waiting and then Max retry account you and your team and your business you decide what is the best or what are the best parameters for those operations but as it stands this is possible right you can enable the retry on failure and then it will do all of that and potentially that transaction can be committed without the loss of the user experience because what we may perceive as five seconds user might just say oh well the it's a lot of data going to the database so I'll sit here and wait right so as long as this system isn't timing out or giving them that 500 error they might just sit and wait patiently because we're just making sure that their data is going to the database on the first try so that's really it for how you would make a database connection a bit more resilient with Entity framework core 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 they were really designed with the intention of facilitating our seed configuration so we named them League seed configuration but the reality of the matter is that this entire class can be used for all the configurations relative to the Target domain object so in other words just like how 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 GB context kind of clean all right because then we'll end up with a lot of these blocks of configurations and there will you know 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 we're going to start by refactoring our team so I said league first but let's start with team 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 seating so first order of business I'm going to rename this from being team seed configuration to just team configuration and I'll just let that refactor all the references throughout the code next stop what I'm going to do is bring over all of those configurations from the DB context so I have model builder dot entity team I have all of that I'm going to cut that and I'm going to come over to this seed configuration well we're going to rename the file in a few don't worry about that but right now I just want to move over the configuration so this is the Builder has data configuration I'm going to go underneath that still in the configure method right and then I'm going to paste all those configurations that I just cut now you'll see an error appearing with model builder and that's because we don't have anything called model builder in this file however if you look closely model builder is like a generic version or implementation that allows me to say model builder dot entity and then imply The Entity our Builder object on this type is specific it's entity type builder for this specific entity so this whole implementation kind of embodies this entire line right so all of the success model builder dot entity.team I can now replace that with Builder and Builder knows that it is relative to team so everything that we do here is relative to team alright so I can just say Builder dot has many 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 so I'm going to cut that and I'm going to place it right there and once again I'll just use Builder to replace that model Dot and modelbuilder.entity.team stuff 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 it's going to jump over to the configuration file and see everything that needs to be done for a team so I can actually move these configurations above the has data 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 seating so I'm just going to go ahead and rename that reference go ahead 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 now that you have the gist and at the end of the day we see our DB context looking much neater and then all of the messy configuration stuff they're in specific places around our project all right guys so far we've been seeing some of the new features of EF core 6 and I'm sure you've noticed that it's just a steady progression from F core 3.1 EF Core 5 and now six each one just improves on the previous version but most of the code that you're probably accustomed to in terms of Link how the DB context refers to the domains all of those things are pretty much intact it's just that they keep on optimizing certain things as we go along now there are many other features some of them within the context and confines of this course cannot be covered uh because we really want to get just whole EF core functions but other features that you will definitely make or take advantage of in bigger projects would be like temporal tables where if you're using an Enterprise SQL server and not just the local EB context like we are here then you are able to take advantage of temporal tables which will be keeping a history of everything that happens which would actually negate the need for our whole auditable DB context that we have to do if you're using temporal tables that's what they're there for EF core no has an API that can allow you to directly interact and query and write to and interact with temporary temporal tables you also have the option of migration bundles which is more like for devops situation so instead of running update database every single time in the package manager console you can actually bundle your migrations and have a version of that bundle that gets applied during a deployment so that means the DB side of a software deployment much easier to happen like I said these are more for enterprisable operations and for the confines for the context of this course we will not be covering those so I encourage you to check out my other courses on complete asp.net core and The Entity framework development where we go end to end with an Enterprise application development and I will be highlighting all of those key features as we go along and of course I hope that you are able to apply everything that you've learned so far to your own project and that you're making the best decisions possible foreign [Music]

Channel: Trevoir Williams

Views: 4,029

Keywords: Trevoir Williams, Code With Trev, .NET Development, Entity Framework Core Beginners, Entity Framework Core, entity framework core tutorial, entity framework code first tutorial, ef core best practices, entity framework beginner tutorial, .net core, dotnet core tutorial, ef core 7 tutorial, entity framework code first tutorial c#, entity framework crash course, trevoir williams udemy, entity framework core 7, entity framework core c#, entity framework core relationships

Id: GDkA28nu9SM

Length: 305min 6sec (18306 seconds)

Published: Mon Sep 25 2023