Everything You Need To Know About EF Core 8 Raw SQL Queries

EF core8 is getting raw SQL queries that can return unmapped types and in this video I wanted to do a deep dive on this feature and show you everything that's possible like composing SQL and Link queries and returning data from views is this feature going to replace Dapper I don't think so at least not until the performance is similar but this is something that can change quickly since this is still in preview I already did a video introducing this feature a few weeks ago and Nick chapstas also made a video benchmarking the performance of EF course raw SQL queries feature versus Dapper the results were pretty clear and Dapper is still faster but note that EF cord 8 is still in preview and we could see some performance improvements by the time the release comes out I'm going to replace this query here that is fetching an order summary by the ID to use raw SQL queries I'm going to access the database facade that I Exposed on my application DB context instance and I have access to the SQL query method this is a generic method and I'm going to return an order summary object and then I need to write my SQL query to fetch this data now if you take a look at the argument you'll see that this is a formattable string object I'm going to explain why this is important when I write my query so I'm going to make it a Verbatim and interpolated string and then I'm going to write my query so I'm going to say select star from order summary stable and I'm going to add a where statement that the ID in the order summaries table has to be equal to the order ID coming from my request you might raise an eyebrow here and think that we introduced the potential for a SQL injection attack as I mentioned the SQL query method has a formattable string argument and what's going to happen here is this will be converted into a formattable string which is going to take this interpolated value in this case our order ID and it's going to convert it into a SQL parameter you don't have to be afraid of a SQL injection attack in this situation what's also interesting is that the SQL query method returns an iqueryable which can further be composed on I'm going to show you this in just a moment but let's say we just want to return from our method we can call first or default async and let's try this out I'm going to start my API and we're going to head over to postman I'm going to send the get request to fetch the order summary by ID to our API and we're going to hit a breakpoint inside of the get order summary query Handler now I'm going to run this query and I'm going to show you the query that gets executed in the database if you take a look here you can see we have a select statement fetching the customer ID the order summary ID and the total price which are the fields in our order summary class or record you'll notice that the query here is the one that we wrote which is the select star from order summaries where the ID is equal to the order ID now notice that the order ID that we passed in from our API is properly converted into a SQL parameter which you can see as P0 here so as I said this is actually a formattable string Under the Hood which is going to convert your interpolated values into SQL parameters I'm going to return from our API and we get the response back in Postman I'm going to show you another example now of what's possible and I mentioned that the SQL query method returns an i queryable which means that you can continue chaining link operators and expand the query that will be sent to the database so we can send another where statement for example we can say that the total price has to be greater than zero for us to return an order summary and then we're going to combine this condition and this condition and send them to the database together so let's see how this looks I'm going to send another request from Postman I already added a breakpoint and the end of this method so we're just going to quickly examine the SQL that was sent to the database you can see our raw SQL query here which is parameterized and then there's the added condition here that the total price has to be greater than zero which we added by calling the where method I'm going to stop this and I want to show you another example I showed you how the SQL query method Works which accepts a formattable string and converts interpolated values into SQL parameters there's also the SQL query raw method which actually accepts a SQL string so you have to be very careful with this one the point with this version is that you can create your SQL dynamically let's say you want to expose a field query filter on your API so that your API consumer can specify which columns to return from the response you could do something like this where instead of saying select star you could fetch the specific columns so let's say we only want to fetch the ID and the total price I could inject an interpolated value like this that would be coming from my query parameters this is not going to be converted into parameters with the SQL query raw method but it's actually going to be a real interpolated string value and if I actually wanted to use a parameter I would have to do something like this which is pretty similar with Dapper and then pass the parameter in the parameters list so if I were to run this query it's only going to fetch the ID and the total price from the database and then it's going to apply my order ID filter this is obviously very dangerous because here you are definitely exposing a SQL injection attack and you have to sanitize your inputs to make sure that nothing malicious comes through what I'm going to do now is I'm going to create a database View for the order summaries table so I'm going to say create View and I'm going to name it V order summaries and then I'm going to say as and we need to write a SQL query that is going to populate our view so I'm going to say select the ID the customer ID and the total price from the order summaries table I can additionally apply a filter for example I can say that the total price has to be greater than zero and this is enough to Define our view I'm going to run this and we're going to create our view and now I'm going to show you how easily we can query this View using raw SQL queries with ef core I'm going to rewrite this to fetch the data from The View and I'm going to use the SQL query method to achieve this so I'm going to say select star from the order summaries where the ID is equal to the one that is coming from the order ID so I'm going to get rid of the where statement here and this should be enough to fetch the order summary data from the order summaries view before this feature was introduced you had to add keyless entities into your database model to represent the views but with ef core 8 this just becomes a simple SQL query which I'm going to show you right now I'm going to send a get request to our API and we hit the breakpoint inside of our get order summary query Handler which is this time querying the data from the order summaries View and if I execute this you're going to see that the query is indeed querying from the order summaries view the order ID is properly parameterized and we are returning this from our API and as you can see we get the response back in Postman I showed you how you can query views using raw SQL queries it also has support for querying functions and store procedures if you are using those in your database then this feature will be very easy to work with I think this is an excellent feature to be added to EF core and it already replaces most of the use cases for Dapper although the performance isn't as great we'll wait and see if this changes by the time that EF core 8 is released and we can only hope that the performance will be as good as dapper if you enjoyed this video then make sure to smash that like button and subscribe to my channel also take a look at the video that you can see right here and until next time stay awesome

Channel: Milan Jovanović

Views: 12,694

Keywords: ef core, ef core 7, ef core tutorial, ef core 6, ef7, ef8, ef core vs dapper, ef core repository pattern, ef core sql, ef core raw sql query, ef core sql query, ef core sql query raw, ef core sqlquery, ef core sqlqueryraw, ef core sql first, ef core unmapped types, ef core performance, ef core optimization, ef core views, ef core functions, ef core stored procedures, ef core sql views, ef core sql stored procedures, ef core sql queries, ef core raw sql queries

Id: tBSWhHaFB-c

Length: 8min 43sec (523 seconds)

Published: Tue May 02 2023