How to Update and Delete with Entity Framework Core

database update and delete with Entity framework core having traditionally a bit inefficient because we were forced to First find the relevant entity and only then you could request the update or delete from the database however Entity framework course 7 introduced a couple of new powerful apis that finally allow you to perform these operations in a very efficient way so today I'm going to show you the new way to perform database updates and deletes with Entity framework core let's start to demonstrate a new way to perform updates and deletes with Entity framework core you have prepared a very simple web API project where the idea is to manage a super simple list of games and before going through the code just keep in mind that the new apis that I'll show you require using at least version 7 of Entity framework core which of course comes with the.net 7 SDK so let's start by going through the code that I have already just to understand better what this API is about so I'm going to open up program.cs here which is known as the entry point multiplication and I'm going to collapses for a moment and we're going to notice is that what we have here is a three endpoints that are used to manage Aliso games the first endpoint over here is used to get the full list of games from the database which by the way the database is a SQL Server database and then we also have an endpoint for performing updates via the put a verb over there and then we have an endpoint for deletes now as you can see both delete and put are not implemented yet we're going to be implementing those in this video but we do have an implementation for get already and as you can see we're going to be listening in the slash games route and you're going to notice that we're receiving an instance of this game store context class so if you know Entity framework you know that Gangstar context is really an instance of a DB context and that is the the object that you can use to interact with your database without having to write any any T SQL right now this game store context is getting injected into our method over here this is using the dependence injection mechanism and it's actually registered a few lines above if you see here close to the top we invoke the article server method here to add the game store context right an instance of insert context into the service container so that later on it can be injected in into any other methods you can see we're injecting that in in get input and in delete now uh let's take a look at that game store contest very quickly so I'm going to open up my Explorer event here so that if you find it in my data directory over here gangster context and so as you can see it's super simple all it is is a class that in edits from DB context and it defines just a simple DB set of type game which is the entity we're going to be managing and the game is defined over here in my entities directory super simple it only has an ID a name a price and a release date and I have already performed the migrations and apply the migrations for this into my local SQL Server database so the mapping between this model and the corresponding gain stable has already been performed okay now going back to my program.cs I'm going to collapse this and this so we can do first is to actually exercise this API to see how it's working so what I'm going to do is just to open my terminal here so open that Ctrl J and then I'll just dotnet run okay so it is up and running and then in order to test this API we're going to be using is uh an extension to vs code that is known as the risk line which allows us to create a little file that we can use to involve the rest API directly from vs code now that extension let me show you very quickly if we open the extensions view that extension is known as rest client risk client is this one here by one child Mao so you can go ahead and install it it's free and it is a very very cool extension let me show you how to use it so I'll close this and I'll open up my explore review once again so I'm going to be opening this file name games.http over here that I prepared and let me collapse this for a moment what this allows us to do is to just Define in very simple terms how to invoke the different endpoints of address API so you can see this is going to be I get called the first line I get call and here is the location of our server if we open up the terminal we're going to see that that is in fact the location of our server right there and so it's just going to invoke the the games endpoint that we already defined right and I have already defined a other invocation for both put and delete over here that we're going to complete in a moment and so let me open Terminal again and if you go ahead and just click on send requests over here there you're going to see on the right side we get our list of current games right right there we have three games already defined in there and the whole point of our current exercise is to figure out a way to be able to both update or delete a either of these games using edit framework or right and so let me close this and let me also stop my server now so let's go back into our program.cs file over over here okay collapse this and this so let's focus now and let's start with the update operation so they put endpoint over here so and first thing we're going to do is just to look at the traditional way to perform an update operation and later we're going to be looking at the new way that you can do this right so usually what you do for an update is first you want to fetch the game to update right so let's say this is going to be VAR existing game okay equals a weight okay it's going to be context that games dot find async and then we're going to be passing the ID of the game to find right which we're receiving from the parameter over there now since we're going to be using the await keyboard over here we also have to specify that this is going to be an async an async a method there okay so then we found the game and then of course once we found it what we want to do is go ahead and update the properties right so in this case we will be doing existinggame Dot name equals update again name and then we have existing game that price equals updated game the price and finally we have existing game that release date is the updated game that released now of course we're getting a little bit of a warning over here over here as you can see because the compiler is warning us as saying hey uh this existing game could be new in which in fact could happen if for instance for any reason financing is not able to find the game right for instance if the the client provided an invalid ID right so assisting including all that's the one that we have here so how to solve that well one way to do that would be to actually just check for nullability there and return a specific result in that case so for instance we can say if existing game is null we can go ahead and return something that's kind of appropriate for that situation which would be results dot not found right so we could not find it and then just by doing that the compiler knows that existing game at this line 26 it has to exist right so uh the warning is gone now moving on uh after we do all of those updates uh since Entity framework is keeping track of all the changes that we make to the existing game object all we have to do now is just say wait context that say change is async right and double go ahead and send the query into a SQL Server to perform the related updates right and then finally likely we want to do something like return results dot the content right which is the typical response for an update operation so this is this is kind of how you could Implement a put operation there are other ways to define a uh put a restful endpoint but this this should work just fine for this example so let's see how this works right so let me let me go ahead and open up my terminal once again and I'm going to start the application let me actually clean this a bit before doing that there you go so let's start this and let's go back into that a GameStop HTTP file that we looked a moment ago over here and as you can see and let me collapse this for a moment we do have a defined here a body for a put request so let's figure out what game we're going to be updating here so let me send a request for the get first so send request here we have our get once again collapses for a moment and let's say we're going to be updating uh the first game Street Fighter 2 right but let's say that we want to change the price because it is on sale right so let's go ahead and try updating that one so I'm going to just copy the body of a Street Fighter 2 over there and I'm going to paste it over here this is going to be the body of our update and I don't think we need to specify a time here the day will be good enough but what we want to change is the price right instead of being 1999 let's say this is going to go down into 9.97 right which a very good deal so um yeah so that's that's going to do the update so let me open up my terminal once again so we can see what happens behind the scenes and so scroll down a little bit and now let's go ahead and of course we're missing just one more thing which is the actual identifier of the gained update so I'm going to put number one here because we want to do a put into the game number one with this brand new body right so that we replace the the element and so let's click on send request and as you can see we did get the expected no content result which means that this was successful right but more interestingly let's look at what happens in the console over here so notice that for making this happen we we had to execute not one but two queries into SQL Server right first one if you can see here this first query over here is the one that was used to First retrieve the item to update from SQL Server right and then after doing that we moved into the second query which is this one over here which is the one that actually goes ahead and updates in this case the price based on for the specific a item that we are or game that we are updating right so that is one of the the reasons why things are not as efficient as they could be because we need to do this this whole round trip of first retrieving the item and then sending out a subsequent request to SQL Server to perform the the update all right so and that's a case of update and then let's go ahead and now switch to a our delete operation right let's see what happens uh we delete and let's see let's do a very simple implementation of a delete which once again is going to be very similar to the one that we have for update so I'm going to just copy these few lines over here okay so let's go down into delete and let's paste that okay same idea and we have to specify that it's going to be async of course and so we go ahead we find we need to find it again to delete we need to check if it is new or not and then we can say context dot games dot remove existing game right because sadly here there's no way to say hey I want to just remove a whatever game has ID the ID that concept parameter you do need to find the game first and then you have to invoke a remove method and finally you can invoke a weight context that save changes async like that and of course at the end we will return results that no content right so this is one of those things that really I never really like too much I mean the fact that you need the entire object to perform a removal and this is because by doing this what you're doing is asking Entity framework to keep track of the fact that this entity is deleted and then we invoke this line here issues send that delete request into SQL Server okay so let's now look also how how this works so let me open my terminal once again I'm going to clean this and I'll do the net run and then let's go back to GameStop http and once again let's create the first the list of games once again I'll just click here so we can get the list of games on the right side okay and so let's choose one game to delete so let's say that we're going to be deleting the second one Final Fantasy 14 over there and so on the left side in the I have prepared this delete request where all we have to do in this case really is just put the number two right there okay and then we should be able to perform the the delete so I'm going to click on send request and as you can see this was a successful tool for no content okay but once again notice what happens on the on the SQL server side right so first we have to do this request a this query over here to find the game that was going to be deleted right and only after that we can go ahead and do the actual thing that we wanted to do which is to perform the delete operation as you can see here okay so once again an entire round trip just to perform a delete of this one game okay so this is the kind of stuff that makes things very inefficient and that I mean that's what the original case so far but of course there is a brand new way to do this these days and with starting with edit framework course seven so let's stop this and let's look at that new way to do things so let's see how can we improve this overall so let's go back into our update case or our put endpoint over there so instead of doing this what we can do is to take advantage of a new feature and a couple of new apis which are known in this in the case of update as bulk updates right and also both deletes for the case of delete so how to do that so let me actually remove all of these because we're not going to be using any of that anymore and what we want to do is the following so we're going to be saying wait context dot games dot where okay so this is we're going to be using this word here first to identify what is the item that we want to update into the database so we're going to just say so the game where game.id matches the idea that we receive as the parameter so that defines a filter and then what you want to do is to invoke dot execute update async this brand new method executed the async which is the one that you can use to define which properties are going to be updated or which columns are going to be updated in the in the table and with which values right so in this one you want to receive an object let's call it just updates okay and then this is going to be another error function so let's go into our next line and then via this updates object we're going to be able to set each of the properties that we want to update so we're going to say updates dot set property and in this one we're going to be receiving our game object over here and we're going to be able to say game.name it's going to be updated via updatedgame.name and then I'm going to close uh our column there and just like we do that we can do the same thing for every single subsequent property so let me just copy this and then in the next line I'm going to be doing this and then this again let's align this a little bit better and so for the second one we're going to be saying game dot price is going to be using updated game.price and then finally game dot release date is going to be updated game dot release date okay so once again the first line defines the filter of which game is going to be updated and then this execute update async a method executes a batch update with all of the updates that are going to be performed via the what was found by that filter is going to be set with the properties that are defined over here so each of the properties are going to be using the brand new value that is coming in this updated game popular there and the cool thing is that by executing this there's not going to be any change tracking or any need to First retrieve the item from the database to be to after that send it back via the video change tracking right this is just one call that goes straight into the database and then just to complete a recipe and provide a meaningful result what we can do is also capture the result of this operation here because it's going to return the number of rows that got updated right so if I just say let's say VAR rows affected equals then the result of that right so now we can go down over here and we can say the following we can say return rows affected if this was zero right if I said it means that it was actually not able to find the game that should have been updated so in that case what we can say is okay so results dot not found all right and then otherwise it means that yes we got some rows updated so we can return results dot now content okay and so that is how you can perform a database update uh using the new apis from it is framework core 7. so let's see how this actually works so let me go and open up my treadmill once again and I'm going to clean this and then let's do the net run and then let's head back into GameStop http and then once again we're going to go ahead and perform a put operation right so let's first just retrieve things so let's retrieve this let's see what's the current status of things and as you can see we still have our three games right Street Fighter well in this case two games because we deleted one of course now we still have three Fighter 2 over there so let's say that we want to bomb the price again because the sale is over and in fact this is going to be more expensive now so it's going to be 29.99 okay that's going to be the updated price and so let's go ahead and execute this I'll say send request and as you can see this time there was only one invocation into SQL Server so which is right here here's a command that in this case it looks different because what it's doing now it is using a filter the filter is is in the in the last row over here filters by ID and they will go ahead and update the table using the specific properties that we specified as you can see so there is no need anymore to go ahead and first retrieve the items and then send back the updates the the comment that you see before over here is not really related to that is just the one that we executed initially when we retrieved all the items am I want to go okay and so that is what makes this super efficient right there's no need for round trip anymore okay and so yeah and just like we did this we can go ahead and do something similar for our delete operation so let's go down here and things are going to be fairly similar so what I'm going to do is the following so let me copy this first line from the put down here and so I'll replace all of this with just this line because this is going to once again Define the filter of what is going to be the game that needs to be deleted and so after that line all you have to do now is just say dot execute delete async all right so that is going to go ahead and delete any game from the table SQL Server table that matches the filter that we have defined over here all right and just like we did before when we can return once again base it on the rows effect that I'm going to copy this base it on the rows affected we can go ahead and return either not found if we can find it cannot find the game to delete or a no content if we were able to find the game and delete it okay so let's test that out so I'm going to open my terminal once again dot net run and let's go back into games.http once again let's retrieve all of our games let's see what we have so far let's retrieve that right here and the right side uh right now we have two games so let's say that this time we're going to be deleting the FIFA 23 which has ID number three so let's scroll down into our delete request so let's replace two with three right there and let's execute the the request so I'll do send request and once again notice that yeah I mean this was a success so no content that's great but more than that down here you can see that we only have one query down here to be able to send the delete a the delete operation into a SQL Server by filtering by the ID wssify so there again there was no need to First query a SQL server for the item and then go ahead and perform the deletion right so yeah so let me collapse this close this and that let's go back here and so yeah so that's what I wanted to show you today I think it's a very nice a new feature Entity framework core and I think that this is definitely the right way to start performing our updates and deletes into SQL Server via Entity framework core I hope that was useful and if you'd like to know more about Entity framework core and minimal apis please check out my site for complete courses where I covered that and many other topics that are essential for professional.net backend developers consider liking this video if you found it useful and don't forget to subscribe to the channel so that you're the first to know whenever I publish new videos thanks for watching and see you next time

Channel: Julio Casal

Views: 7,254

Keywords:

Id: 9j-E7C29SM0

Length: 20min 30sec (1230 seconds)

Published: Tue Feb 14 2023