Coding Short: Dapper and EntityFramework

hi this is shaun wildermuth with coding shorts again today we're going to get in a little bit of trouble there's always been a disconnect between people who don't want to use orm or object relational mappers and people that do and i've tried to stand on the thin line of whatever works for you works for you one library that gets a lot of attention is dapper today i'm going to show you how to use dapper to extend entity framework what i tend to do because of the speed and way the entity framework works is use it throughout my projects but when i find these bottlenecks of performance or of how to create a query using link that is translated in the correct way i tend to drop down into dapper and so that's what we're going to cover today i know there's going to be a lot of people complaining that entity framework is awful and that dapper is too hard to use but let's try to be sane and let's keep the comments a little light let's take a look [Music] i get the question a lot why would i use entity framework where can i just use dapper dapper has proved to be faster why does it matter the serious answer is you should use whatever you're most comfortable with they're all a compromise in one way or another whether that's through complexity or that through some speed difference in the way it works and dapper's no different than that dapper isn't magical in that way one of the things i like to point out to people that i think is important that's important to me is that i use dapper and entity framework i know sacrilegious you have to pick one or whether you know you're still using in hibernate or you're using llb gen pro or whatever it is it doesn't matter orms typically are all compromises tuned to one thing or another there is no perfect orm uh stop trying to find it go ahead and build one of your own and see how those compromises are important that's actually how i discovered i decided i was going to be smarter than everybody else and try to build one and it's really hard it's really hard to make use cases that work in every situation correctly so let's look at an example just sort of talk about where i would use it right now i'm just using entity framework we can see the same old code you may have seen a bunch of times i might have an api like i do here and i'm just doing some queries against entity framework directly against the objects and i'm using some entity framework tricks i can include and then include in order to build up a hierarchy that i want to get back so let's go ahead and run this here we can see that i'm getting back a hierarchy each of these objects has some data about in my case invoices you were getting that the client's invoices and that includes the individual bills that make up that invoice would have the client uh normally if i had included them but they're not included so i'm not worried about that because we're already getting it from the specific client so why duplicate that data right i have made these decisions about how this api will work but i've got a problem right back in here i want to implement a new api called get large invoices and i'm just calling it invoices large so we come back here and i do slash large of course it's going to fail because i said i haven't implemented it yet and the problem i have is this is getting some very interesting data we can actually see if we look at the data class that i've created for it it's really just a summary it could be you know something like a report really that you're going to be using it's something special and frankly creating it using the api in entity framework might be hard might be more difficult might be easier to go to people that know sql better knowing i'm using sql server and say can you cruft up that query for me and it's been a little while since i hand coded sql so forgive me if this is a little little messy but i crafted up a quick example in my in just a quick editor window so looking for invoices that i'm doing a select so i can get the total of each invoice from the time bills that i'm also looking at so i can get the employee on those and when i execute this i'm getting a bunch of data and it's returning all the data i need and i could look at my result and actually get less i could go ahead and fix these selects but the important part of the story and i've decided that it because i want them to be of a certain size that i actually want to make sure that i'm only getting the items that are of a certain size are larger so i'm just using a parameterized query here this is a case where this might be much more complicated or more importantly involve client-side code to get it to work in the way i want to with entity framework so i'll just use sql to do it and use dapper for that use case for that niche reason and so let's go ahead and create a query i'll just paste that in as a query i'm going to put in the full name there and then i want to be able to use dapper and so i want to get a connection and i'm going to get it from our context right i can just go to the database for our context and say get db connection right and i'm putting in a using statement because it has a scope it's the connection itself supports i disposable so i want to make sure that i'm cleaning it up after i use it and then i can just save you know i want to get the let's say summary equals await connection dot query async right so i'm going to query for invoice summary and i'm going to pass in the query and then i'm going to pass in the parameters right it's a parameterized query so i need to have these matched client id and this is going to take in the id that was passed in and then min invoice and i'm going to just use this as oversize right and so when it does this i can then just say okay summary right because even though we asked for invoice summary here we're actually of course only specifying the name of whatever that row represents and dapper's going to go ahead and convert the result into invoice summary and even though we're returning more data than necessary which of course we could fix by cleaning up this query it's going to only map the items in that summary for us and allow us to return it right so let's save that let's run it again i'll go back to our large and here we're just getting the few that actually match that notice that the totals here are above that size and if we were to do something like oversize equals ten thousand right we're specifying this as part of the api and we should get more because there's a number of them that are over ten thousand and so we've created this api that does something more special that might be harder an entity framework and it being hard i'm not concerned about being in any framework it being efficient is and so being able to go to dapper and go this is exactly what the query is going to be the query is going to be cached effectively and maybe even i need to put index hints here for something really complicated right or even calling a stored procedure that's going to allow us to handle these more single case isolated statements but if i could do this in dapper why would i ever use something like entity framework right the reason is that get isn't that hard right handling get in dapper isn't terribly difficult even in something as complicated as this it isn't that much the power for me really comes into when you're doing dappers for insert updates and deletes because i like the fact that the energy framework can track those changes for me that becomes sort of a big deal so i rarely use dapper in those situations it's almost always for a complicated get sometimes i've used it in the past for doing batch queries when i want to do updates of a bunch of things but that's even pretty pretty rare the only times i've really done it is when i brought in dapper to do things like this where i had this very interesting one off so let's see what that looks like what if i just did dapper for everything so let's implement this as a dapper call instead and again remember dapper's going to be a little bit more efficient because it doesn't have some of the overhead but are we really going to get the solution we want i'm going to go ahead and comment out this query and let's uncomment out this one right this is essentially doing the same thing i'm saying get the invoices and the time bills and the employees and the cases right get all of those same objects time bills cases time bills employees right and i'm specifying that it has to be an id that they pass it same thing i'm doing here but i'm using client id instead right we execute this and we're going to get a single row that represents each one of those right so this isn't necessarily super efficient because it's returning a lot more data that's necessary and maybe this would be better as a batch query or something like that but let's take the simplest sort of case here let me copy this and let's make our other piece here the same way we did before right go ahead and make a query let's get a connection just like we did before i'm going to comment this stuff out and then what do i want to do this is where it gets a little complicated it gets complicated because we need to have a way to sort of map all the individual objects right because each row has its own object and so if i did something like invoices equals await connection dot query async same thing we did before and said invoice and query right and let's go ahead and put that i got to figure out how the visual studio 2022 is putting this really awful object i need to make it stop and go away this is worse than it was before and do my new client id equals the id right parameterized query should be all good problem is this is just going to return to us here in the return okay invoices right let's run this actually that was the right one just look at those invoices and we're getting it but guess what time bills and client and every other related piece is just being ignored the zapper doesn't think about it even though we're getting all the data here it just knows that we want an invoice we want to return those so the trick here is to actually pass in the different types and these different types have to be in the order that they're selected so invoice time bill employee and case and this is really how it does this sort of magic and it does the magic by past you pass in a lambda that takes the invoice the time bill the employee and the case and then this is effectively just going to allow you to map it so what does that look like we're going to take the time bills right which ordinarily would be empty and we're going to say if it's no remember question mark question mark equals if you saw my earlier video about that i'm going to say is a new list of time bill maybe that's not necessary if your underlying object already did a new when you created a new one but a lot of times that's going to be necessary and let's come down here and say return invoice because what we want to do is do the mapping and return that individual invoice this is going to be called for each row and so what do we do here this is going to be called for not only each row but each object in each row and it's going to separate them by default by a key and that key needs to be id or the type id luckily our all minor ids becomes easy and so what do i do i say invoice dot time bills dot add i'm gonna add that time bill to it and so i'm gonna get called for each time bill but it's more complicated than that because i need to set up the time bill because it has its own related entities so the employee equals an employee so if the employee is already been set we're going to ignore it if not we're going to take this employee here and map it here and we're also going to do the same thing for case so what is this all doing i always do this it's complaining because i actually have to tell it the resulting type as well which is invoice so this not only takes invoice but the return it's also telling it should be an invoice as well so while this code isn't complex it is something you need to think about and as you create these queries instead of letting things like the include work it's going to help you build this up and this building up of one object at a time can be expensive but let's go ahead and run this just to show you that we really are getting the same object back right we're getting the same object and notice that the clients are null because i never set them because we weren't getting clients in our query but everything else in the array the case information case status employee the time bill the amount number of hours etc all just simply work right so if you're more comfortable here than you are with something like link use dapper i don't care like it's fine i find this a lot more complicated and more work than being able to do this now that i'm comfortable with link it's only really that i suggest dropping down into something that's more pure and i find dapper to be an abstraction over straightado.net code and so this is better for me than writing the raw ado.net code to do things that i might need to worry about the performance or the complexity of the query and trying to get entity framework to craft the kind of query that i could maybe do by hand i hope this has been helpful thanks for watching another coding short my name is sean wildermuth of wilder minds see you next time [Music] [Music] you

Channel: swildermuth

Views: 1,345

Keywords: Programming, Software Development, C#, .NET, DOTNET, .NET 6, ASP.NET, ASP.NET Core, Minimal APIs, Global Usings, Top-Level Statements, dapper

Id: W5BxH9CdcsY

Length: 14min 31sec (871 seconds)

Published: Sun Sep 05 2021