Using Stored Procedures with Entity Framework 6 CORRECTLY In .Net 6

so you guys want to execute stored procedures while using Entity framework 6. maybe you just don't want to use Link queries for any number of reasons well then you're at the right place I want you guys to leave this video with a better understanding of what our stored procedures and how to properly call them within the.net environment using Entity framework but importantly you do need to know the current limitations of Entity framework 6 when doing this so remember to stick through the whole video so you don't miss any of these key points but let's not waste any more time let's get into the topic of stored procedures with Entity framework using a.net 6 API and if you end up finding this video useful please drop a like on the video so we can spread to more developers And subscribe if you would like thank you okay so quickly I just want to talk about what are stored procedures how they compare with link and how they work with Entity framework 6 and their limitations so let's dive into that very quickly what are certain procedures they're basically stored SQL code that you can reuse over and over and just call and it'll execute the same thing it's kind of how you reuse code and make its own method and then you just call it from other methods basically the same concept except with SQL code so why would you even use stored procedures over using link link is really easy right easy to use within.net well yes but stored procedures are slightly faster than link queries since they can take full advantage of SQL and they can be cached since they live in the database since they are SQL you can also be way more complex with the queries because you don't have to worry about performance like you do with link and then you offload having to parse and then create the query like you have to do with link because again stored procedures are SQL and they already live in the database so you don't have to then process anything it just works executes and sends back your result but now I want to touch on the limitations of Entity framework 6 with stored procedures all this information comes from the Microsoft documentation which I will be linking below if you want to go check that out but basically there's some limitations and some things that you just can't really do are not easy to do with with this and you're better off using a do.net or some other third-party library to call stored procedures in.net instead of using Entity framework 6. so SQL queries must return data for all properties as an entity type what does this mean basically have a class with certain Fields you have to return data for all of them when you query the database or when your stored procedure comes back for a certain table it has to return data for every single one you can't miss any so column names must must match column names so that means they have to match you can't name them other things and expect them to like line up because you can't do any of that pre-processing because the stored procedure just sends all this data to Entity framework and then Entity framework has to map them all and so that can be confusing and it'll throw an error also it cannot contain related data so basically what is my recommendation what does this all mean it basically means that join queries inside of your stored procedure or you know queries that grab from a lot of different tables are impossible to do you cannot do them why because you are only allowed to basically use stored procedures on one table at a time if you use more than one table in your SQL query it'll work in SQL but when that data comes back Entity framework 6 will not be able to map it so basically my recommendation is for calling stored procedures unless it's a very simple one table stored procedure select query um basically only do this method with creating updating or deleting unless it is a very simple selecting from one table then you can kind of do this I don't see a massive benefit of doing this but I still want to show you guys how to do it if you guys do want to use Entity framework 6 in your.net project to call a stored procedure but that is my recommendation for you but keep an eye out for Entity framework seven because I think they might fix this issue of being so closely tied to the entity types but with that being said let's dive into the code so I can show you guys how to actually do this in a.net application okay so this is the project that I'm going to be using that I'm going to be adding the stored procedures to it's a project that I already used in a previous video there'll be a card it's one where I created an angular 14 application with a.net6 API so that's where this you know API came from if you guys want to go check out that video I have interest in that there'll be a card at the top but let me just show you guys kind of what's here um and I'll show you guys the database as well so basically there are four API calls where it's just crud so it's a get a post a put and a delete so that's all this is uh those methods are going to work but we're not going to be working in this uh let me guys show you guys quickly the database so the only thing different if you guys were maybe one of the people that actually saw that previous video is now I have two tables so basically I have a table for players and a table for teams so if you look in the players uh we have is Neymar and Buffett Messi and vinicius Jr and then we now have a foreign key of Team ID that points to this primary key which just has three team names that is it uh you guys don't know why it why exists in a bit whenever I show you kind of one of the uh limitations let's say um but that's gonna exist so first thing I want to do is let's create kind of the controller the interface and the repository that we are gonna so first things first I'm gonna create a SP controller basically just stored procedures controller that's just where I kind of want to have them all I have this SP controller now this is where I want to have all my sword procedures so I kind of just have them in one place and this is like where I'm gonna go to call them so now I just need to add a few things to get going I'm gonna need to use the interfaces that will be coming later when to actually create the interface for this let's put its decorators to make sure that it is a controller I just want to use some controller base even though they're basically the same thing doesn't really matter we need a Constructor this Constructor is going to be SP controller and we are going to need to call the repository once we actually uh create it so it'll be empty now because it doesn't exist yet but we will create it in a second um and with that I might as well go ahead and create it so I need to create the interface and then the repository so those are the two uh next things to create so I'm going to go into the interfaces and I'm going to create the ISP repository because that's going to be the interface for the repository now that I have this created I want to add in the five methods or the five stored procedures that I will be showing you guys in this tutorial so with that being said I'm going to put them in here we'll create them in a second so we're gonna have a get player this is basically going to be a stored procedure that just goes and gets one specific player based on his name and jersey number next we're gonna have a stored procedure that just selects all from a given player list next we have a very special one this is one where I want to teach a lesson per se it is going to be the get joined player list this is basically going to get all the players from the player table but then also go and get the team information from that secondary table with that foreign key that I just showed you guys we're gonna have an update player which this one is not going to be a read at all like it says it's going to update a player So based on the team ID and the player ID it's going to go and update the team ID let's say a soccer player changes teams we want to go change the team name and then we're going to create a player normal thing we just want to add another player to our database so we need the team name name and jersey number because the ID will be created once it's inserted so now let's go to the repository and actually create our repository so I've created the repository now let's actually start um doing all the setup for this so we can connect it to the controller to the interface and do all that stuff correctly so first thing we're going to need is we are going to need our interfaces in here let me scoot this down a little bit put this up here we're going to need the ISP repository that's going to go right there because inside of that are all the definitions of the methods that we have to now Implement I also want to add in our DB context which is our sample database context that is what the database was called just sample database with those two tables uh and then we're going to need a Constructor as well boom and the last thing is we need to go to our program.cs and we need to actually add in the connection so that we know that our repository is going to use that given interface so we have to put that in our program so that it knows to add that dependency let's add that in here boom we're all good to go so now we have the repository the interface and the controller so let's start with the first one we're going to start with get player so first of all we need to create the stored procedure once we create the stored procedure then we can come back to the code and now connect everything to actually call it so let's go and create the stored procedure well now we're back in ssms and let's actually go down here to programmability in the stored procedure this is where you actually are going to create your stored procedures within a database so right click it new stored procedure and then you're looking here A bunch of template code but really you go down here this is where you're going to name it so replace this with get player here is where you add parameters ours is actually going to have two parameters since we are getting a player how do you get a player well you need their jersey number and their name uh and then here you have sample code but this is basically where you write your SQL query for those you are familiar with SQL we're just gonna have a very simple query uh using both those parameters so we're going to do a select everything from player so if you look at player we're basically selecting this whole row we're not doing a join just selecting the row where the name equals the name and the jersey number equals a jersey number so now we've run this whole thing command succeeded go down here and we refresh and we'll see that we now have this get player created if we go to modify it we'll see everything we're using the correct database the get players named correctly your parameters and then your queries all there if you want to test it in here you can just execute it pass in some quick value so we can see that it works and this is what you're returning so now the next thing you actually want to do is you want to call it from your API so let's go ahead and go do that okay so now that we're back at our sword procedures controller the next thing we want to do is just quickly create this method this method is just going to be the get player method very simple we are defining you know the route to it we are saying that we are returning a player object and we are just calling the repository method that is get player uh which we have defined in here so now going back now we have to go create the actual method in the repository so we can actually access the stored procedure so now let's go there okay so now that we're at the repository the first thing I want to do is I just want to do the kind of base implementations of all these just so this interface can just be happy and now we're going to go back to the get player which is the one we're actually doing let me move over the code and let's set this up so right here it's going to throw an error because this needs to be async but that is okay we just do this and then I'm not going to handle this no error so I want to explain this code and why exactly it is this way what happens here is we have our context obviously and then we are acting on the specific players table you there is a version where you can say database but database does not work it does not work for calling stored procedures you have to act on a given entity a DB set a table um which is why it becomes an issue um in a little bit I'll explain the issue in in the other example in this one the get joined player list I'll explain but when you use it you're gonna have two options you're gonna have from SQL raw right here and from SQL interpolated interpolated interpolated um from SQL raw basically just takes the legit string that's here and just smacks that into the database and then that's what's run but SQL interpolated actually parameterizes the values that you pass in which is the safer option if any of you have ever done any type of security any type of just any type of course like that you know that you want a parameter parameterize your SQL queries and you do not want to put just straight up raw strings into your database because it's an easy way for your database to get hacked so what you want to use is you want to use this or if you use SQL raw you want to create SQL parameters and if you guys want to know about that you guys can just go Google it it's very easy and very simple but if you just want to make it easier for yourself just do it this way using this and then you always have to iterate you have to enumerate over whatever values come back here so now let's actually test it and see if this works okay so my API is running now let's actually test our one get player method right here we're gonna go down here we are also like we did in our SQL example we're going to get mbappe jersey number seven we're gonna go back and we're gonna get this object so we're gonna see that it hit this right here and it returned it if you guys want to see it again we can do it again and execute it again we're going to see that we get a player object of one a model player because it was acting on the player's table that's how it knows how to bind the model and the entity to the table because it like I said whenever I was showing you guys a slide there has to be a perfect perfect match which is why it wants to go and get this team ID and then the team and more on that in a second but with that being said we got the object back it all came back and if I show you guys this object right here look at that the response body we got the item that we wanted we got this exact row back in our application and now we can pass it to the front end so now the next one I want to show you guys is how to get the player list so let's get the full list of soccer players that are in this table so what did I say the next thing we're going to do is we're going to create the get player list method so I already created it here but before we go to the repository we need to actually create it as a storage procedure in our database so let us go do that right now so this one's going to be very simple because we don't really need anything we're just grabbing everything essentially so go here I'm going to remove all of this and then I'm just going to write a very simple query select from player and that's it we just want everything essentially we there's nothing specific we run everything we execute create it let us refresh this we'll see get player list we can execute it we see that we get all our values back so now let's actually create it in the repository if we go to our repository we see that it is right here it is again going to be extremely similar to this except there's not even going to be parameters in this one so we're going to go in here get the player list we're going to see I use the exact same structure same method this needs to be async and again we you have to enumerate over this if not this will throw you an error it has to do it that way so this is going to come back we can test it again now we have this running let me actually try it out boom we're going to go through we're going to see that we're getting four objects back we're getting all our players let's fast forward and in our response you see that it got all four of our soccer players so that's all great okay so now that you get the player list it's logical that you want to do another crud operation so let's actually create a player so the next thing that we need to do is we need to go to our stored procedures section down here and actually create that create player stored procedure so we're first just gonna name it create player then the next thing that's slightly different from the last one is that we're gonna actually have three parameters for this one because to create a player we need a jersey number a name and then that team ID to fulfill that foreign key to whatever team the player plays on and then our query is going to be a little different because it is not just reading this one's going to be actually inserting into the player's table and how do we do that you insert into player you pass in the column names here and then the values which are these variables and then you run refresh it's all here everything's good let's go look at it our query looks fine so now let's actually go back to the code and actually create a player through the stored procedure kind of workflow now that we're back at the controller let's actually create the controller method and then let's actually do what we need in the repository to actually call the create player stored procedure that we just created so what does our controller method gonna look like very easily and very simply it's going to look like this the create player route and then we are just receiving these three values and then we are calling the repository and passing those three values very simple nothing's changed from that except we are returning int because now here we are now actually receiving things the only thing that we're going to receive is how many rows were affected or what changes to how many rows happened so that's why it's an INT so now let's go to the repository and do that whole thing now that we're in the repository this is what our code is going to look like for creating a player and I'll walk you guys through to what is going on so again I have to make this async basically again like I just mentioned we're going to be returning the affected rows so it's going to be an integer of how many rows were changed so imagine you had a very big update to a lot of tables um maybe a payment was made and now a date has to be updated to a lot of rows well you would get how many affected rows and then you could maybe store that number or check is this correct did everything work properly but the difference here is if you guys notice I'm doing I'm calling the database now I'm not calling a specific specific table when you are doing an operation that is not a read you call it a database and you execute SQL interrelated async it is not a from it is executing so the query stays the same as an execute the create player stored procedure and then again you pass in correctly parameterized values but this part changes where now you're calling the database object with that being said this works though for creating so now let's actually create a soccer player now that I have this running let us create I don't know uh let's do Ronaldinho so let's do 10. foreign let's do PSG because he also played on PSG and he didn't play on any of the other two so let's just see my D1 so you can see that we got one as a response because one thing was changed one row was changed and if we go back to our players and we select we see that he was created successfully down here so that works also perfectly as well so now you might say oh I can create but how do I update what if a player changes teams well we can we can do that as well what do you guys think so let's go to the stored procedure and create that one and then we can do the code just like we've been doing okay so the next one that we have to create is the update so we are going to do update player this one will also need two parameters to come in those are going to be the team ID and the player ID because we're basically updating hey did this certain player go to a different team if so we have to change it and with that being said our query is not going to be super difficult or super long and it's basically just going to be update in the player table go to the player table and update and then set the team ID where the player ID equals that makes sense right we're not going to create this everything's good let's refresh make sure that it is created we can go here we can check everything looks good so now let's actually go to the code and actually interact with the stored procedure from our API now that we're in our controller our method to hit our repository it's going to look like this our route of update player team ID player ID and again just like the create player we are going to get how many rows were affected by these changes but now let's go to the repository in our repository we're going to scroll down to our method and we are going to add in our code here let's make this async as well so our code is again very similar to the create again acting on the database very key not on a table but it is the same thing here and then we have our update player in the string and everything else is exactly the same so now let's test our code let me restart this okay so what player do I want to update well let's go to our database so mbappe is currently on team one that is PSG but we all know the rumors that he might go to Real Madrid so let's update him let's see in the future he actually goes to real so let's change this so player ID of four and he's gonna go to Team ID of two we execute that we get a response of one that means one thing was changed we can go back in here re-execute this query and we see that it's team ID is now different so now he technically is a member of Real Madrid not PSG like he was just a second ago so right there we have our create we've pulled data and we've also updated a value you guys probably noticed that I was kind of avoiding this get joined playlist well I added in all the code for it and I just want to explain why I was avoiding it and why was a point of pain when using Entity framework 6 to try and grab a bunch of data and when I mean grab a bunch of data I tried to join the players table with the team table just strictly on this this is the stored procedure I created for this it is you select player as P join team T So this does just not work it just doesn't work it doesn't work in any framework six and maybe one of you guys can figure it out big brain guys on YouTube can figure it out but I can't if you can't in the comment section let me know but you cannot pull all of this I even tried to change the column name of this but since the Entity framework 6 needs like every single value to work correctly it actually will force you to have to change a column name and even then it still won't work um because it only wants to grab from the players table it doesn't want to allow you to grab from players table and then dot include anything that won't also work so I just wanted to show you guys this as I tried to get it working as much as I could and through everything I found and through the information that I showed you guys at the beginning of its limitations of it being so tightly bound to the entities and how it pulls using this like table and this literal DB set of player it only like knows player and it doesn't know everything else I believe that's something that they're working on with Entity framework seven um but we'll just have to see but I just want to kind of let you guys know that doing join means is basically just not really doable or is a lot of trouble and at that point I'd rather use um link queries that would be way easier or using some other third-party package to actually do the query if you really wanted to use a stored procedure for this um but with that that's all I have for this I showed you guys kind of how to do all the other crud operations how to pull data how to create how to update and it is very simple as you see you just have to make sure that you're using this safe way of using your parameters because you do not want to get SQL injected but with that being said that is how you guys do this but let's say you don't even like this let's say you're like oh this is way too much trouble I'd rather just use Link queries well then go check out this video so you can learn all about all the link Clauses how to use them what they are and all that good stuff

Channel: Israel Quiroz

Views: 13,637

Keywords: Stored Procedures with Entity Framework 6, Stored Procedures .net 6, how to use stored procedures in .net 6, entity framework, c#, entity framework core, EF stored procedures, stored procedures ef core 6, stored procedures entity framework 6, stored procedures .net 6, dotnet 6 stored procedures, stored procedures dotnet 6

Id: gUNeGj4zqHg

Length: 22min 29sec (1349 seconds)

Published: Sat Sep 24 2022