How Entity Framework Tries to Copy Dapper in .NET 8

hello everybody I'm Nick and in this video I'm going to show you why even though promising it is highly unlikely that EF core 8 will be good enough for you to replace a Dapper for your SQL queries there's some great work being done by the EF core team however I'm going to drill into exactly what's going on what is being added and why the performance just isn't there at all at least for now if you like our content and you want to see more make you subscribers in this notification Bell and for more training check out Nick chapsters.com alright so let me show you what I have here and I want to make something very clear we are still in preview the numbers are likely to change however I'm not too hopeful that they're gonna come too close to what we're gonna see from Dapper at least in EF core 8 I believe that we're gonna see an optimization in nine and eventually ten all right so let's look at the code so what I have here is the same project I've actually been using for the past two EF core videos and I'm gonna put links in the description down below because I do go in detail in those videos as well for other aspects of EF core but in here we're going to be testing in queries the same thing that Dapper does ef core 8 is now able to do as well so it is an even playing field we're gonna test the exact same functionality for both libraries now I'm only briefly going to be explaining the setup and the methodology for this type of testing here the benchmarks because I already have two in-depth videos on those so please check them in the description down below if you want to find out more or if something does not make sense I do explain it there but before we take a look at the setup I want to show you what we are going to be testing so consider the following Dapper allows me to have a query like this where I can say query single or default async and then pass an object and then what I'm gonna get out of this back from that query will actually be mapped into my object so if I go to sqlite which is a database of choice for this Benchmark I have an ID title and a year of release as names of these columns and then in the movie Object I have an ID time and year of release so we're gonna have an object mapping happening from this SQL query all the way to movie This is the whole wild proposition of an orm now traditionally if you want to do this with ef core you would be using sort of the DB set so you have a movies DB set and then use that DB set to say singular default and pass down a delegate and anything that matches that delegate written as the value however what you couldn't do is run queries for unmapped types so if I had something like this and I said at Movies context and I go to the database yes we did get those two methods in.net 7 the SQL query and the SQL query row that accept a generic type but if I try to pass down the movie and then I do something like this where I say SQL query return a movie and I say select everything from movies where ID matches that movie ID with a limit one and then singular default async this will actually fail the this method in dotnet 7 would only work for things like integers or strings so if you have a scalar thing you want to return it will work but if I go and I try to run this then as you're gonna see over here we're going to get an exception and that's because the element type of movie is used in SQL query method is not natively supported by your database provider blah blah blah so this was not possible in.net 7 which is what I'm using right here however now in dot net 8 what I can do and I'm going to go ahead and just update to dotnet 8 preview 4 for this project I'm going to say properties over here in.net 8 and also update all the EF corre related nuget packages so all of them moved to the latest preview then what you're going to see and again this is not part of the movie context the context does not really know of these movies DB set existence so what I can do here is just run this and just debug it to show you what's happening and if I try to run this query now where I say here's a year of release assume that this is a parameter and use this as query for my movie Object over here pass it down as a parameter and say to list async then this now will in fact work and I do get the seven movies that do match that query so if this gives you the exact same experience as Dapper now the very interesting thing about this approach which I both like and a bit skeptical about is how they chose to deal with parameters because in Dapper you have parameters like this you have an ad symbol and then you have the name of the parameter and you create this Anonymous object or you passed on an object that has this property name and then it's being matched and you return the result back but in this approach over here what you have if I go to the API is actually something called a formattable string so if we take a look at what's Happening Here by just quickly extracting this into a variable is actually very very interesting I'm gonna just debug this again and go here and show you what's going on now on first glance you might say something like oh this is so prone to SQL injection which because you have a string table light parameter here I can't believe you're doing this anyone can inject their thing now here's the thing for multiple string Works in a very interesting way even though it looks here that the string is actually just the query with that injected parameter through the interpolation behind the scenes what you're getting is actually a format so it is taking that interpolated parameter and it moves it into an argument in effectively the same way that Dapper would do it conceptually and then you have an argument array which has this parameter in here which will then be passed as a parameter and replaced so you don't have to worry about SQL injection because it is very smart about how it's building that from a double string behind the scenes the actual screen is not what you're saying here the actual string is that templated format and then that is passed down it goes into the SQL query and that's what Returns the values in the end in any case I'm going to merge that for multiple string all the way in here again and now with the project in.net Aid what I'm going to do is actually run some benchmarks now a bit of a quick introduction before I run the Benchmark what I'm doing here is I'm deterministically creating a hundred items in this sqlite database I'm using sqlite because the thing we're really testing here is the ability of the libraries itself to generate those queries and run them the execution part does not matter so if it was postgres sqlite SQL Server it doesn't really matter at all what we care about is the ability of the libraries themselves to take this and run it so we have an even playing field in terms of what database is backing it up and we can isolate the actual overhead of using the library which is what you're going to be seeing in those results now we're using async for everything and we're using a seed for the generation so it's the same items being generated over and over again I do go way more in depth into the details in those previous videos I highly recommend you check those out after this video but effectively what we're testing is two queries the first one is select a single movie by its ID over here with with a limit of 1 and singular default and the same thing for Dapper and then select all the movies that match a year of release I'm passing down 1993 there's seven movies that match that so these two methods will return seven and these two methods will return one I'm going to change this to release mode the program.cs is ready I'm gonna run my benchmarks and see what I get back all right so results are back and let's see what we have here so as you can see EF core query for one item 45 microseconds and 10 kilobytes of memory double at seven microseconds and 2 kilobytes of memory huge difference and then EF core query it's actually even less than the single item and 11 kilobytes and the same thing in the upper 12 microseconds and 3.8 kilobytes which is very weird because given the fact that more items will be returned by the query you will expect this to actually be slower than the get single item now the memory deviation makes sense but the speed certainly does not so I'm assuming they have to optimize things here even further now what I'm curious to know is whether strings them silence in my theory about strings here actually plays any role in the degradation so what I'm going to do is I'm going to duplicate my EF core versions and I'm going to add a raw version because we have a SQL query row here now ultimately what we're going to see here is the following remove string interpolation change this to zero and pass down a new object array which is what actually is going on behind the scenes even for the non-roll version and then pass the parameter as an object in that array that's what's going on behind the scenes and I'm going to do the same for the other one but how does I compare with a potential string issue so I'm going to copy this roll the changes back because we actually want the original version to have something to compare to then paste this say raw here and run this and see where we stand all right so results are back and let's already have here so as you can see there is actually not much of a change at all there's a bit of less memory being allocated slightly faster but that is about it and ultimately that's because this operation informatable string is actually pretty optimized for what it is and because eventually what you're going to call is that SQL query raw method anyway so you're not really saving much so what is the conclusion based on those results well keep an eye on this but I would say that for now I don't have enough details to confidently say to replace a third-part library like Dapper if you're running queries like this whether you're using it just for your read side of things or for unmapped types but now I want to know from you what do you think about all this and are you using a mixture of both orms because you have unmob types leave a comment down below and let me know well that's all I have for you for this video thank you very much for watching especially next to my patreons making videos possible if you want to support me so we're going to find the link description down below leave a like if you like this video subscribe like this in the Bell as well and I'll see you the next video keep coding

Channel: Nick Chapsas

Views: 39,402

Keywords: Elfocrash, elfo, coding, .netcore, dot net, core, C#, how to code, tutorial, development, software engineering, microsoft, microsoft mvp, .net core, nick chapsas, chapsas, dotnet, .net, ef core vs dapper, ef core, dapper, entity framework vs dapper, entity framework core vs dapper, entity framework vs dapper performance, unmapper queries, unmapped type queries, ef core 8, ef core 8 preview, .net 8, ef core unmapped queries, dapper vs ef core 8

Id: CTHuvYulOpo

Length: 9min 46sec (586 seconds)

Published: Thu Apr 13 2023