I Choose THIS Over EF Core - How To Use Dapper in C#

you've probably heard about these things called omms or object relational mappers and I think everyone's very familiar with Entity framework core hi my name is Nick centino and I'm not a fan of NY framework core I'm also a principal software engineering manager at Microsoft seriously when I say I'm not a fan it's just because the way that we leverage it doesn't really align with how I like to write software and it's not that I think that it's bad it's just a personal preference and more recently I discovered that I really like working with a package called dapper over Entity framework core so in this video I wanted to walk you through some of the very basics of working with Dapper why I like it and how it really fits in with how I'm already developing software a quick reminder that if you like this kind of content subscribe to the channel and check out that pin comment for my courses on dome train with that said let's head over to visual studio and look at some SQL all right on my screen I have a very simple application and what we're going to be doing is comparing inserting and reading records back to a sqlite database it doesn't really matter if it's sqlite MySQL Microsoft SQL Server whatever it happens to be we're going to be able to leverage a Dabber in a very similar way it's just that the sequel syntax will change and this is an important factor that I want to talk about a little bit later as we go through this video what I'd like to be able to do is compare sort of how I was classically writing some of my code and essentially avoiding ORS all together kind of rolling my own if you will and then how Dapper just kind of cleans that up a little bit for me and takes away some of the manual effort as I go through this it will hopefully help explain why something like Entity framework core doesn't really work for me again I just want to keep reiterating it's not that I'm suggesting Entity framework core is bad I think it's actually awesome for a lot of people that like to use it it is more performant than it's ever been before I'm not here to bash it it's just that how I write software Dapper really seems to align with me to start things off I just have a test database and I just wanted to basically delete it every time I go to run this to keep it nice and consistent but what I do is I start by opening up a SQL light connection like I said we're going to be contrasting how I used to do this and Dapper is also going to be leveraging the SQL light connection as we go through this I'm going to start off by creating a table so it's a very simple example here if I jump down a little bit lower in the code we can see I just have this query here it's a little bit funny how it's indented but I have a create table if not exist it's just this test table has three columns the ID itself a name that's text and value that's an integer personally for me you might be saying well hey like this is already kind of weird why do you have SQL rate in your code I actually like having SQL in my code the way that I write software generally is that I end up creating repositories specifically I like having the SQL represented in my code directly inside of my repository because that's the type of class I create manually for translation so because again for me me in my development I like going and writing my database I'll go into generally it's something like my SQL workbench I'll be creating my schema there and I kind of configure things how I'd like to see it working from there though I end up exporting that schema and then I have it available in code so literally I will have a big SQL file and that's something that I can run and again it's because I like having direct access to the SQL am I saying that this is better or worse in other ways absolutely on it's just how I'm very comfortable creating my applications so I do like having something like this in my code it's just that generally it will be a lot bigger because my schema is more complicated so for this example we're not going to see like a Dapper versus a classic way I just have it to get this example up and running I just wanted to use that as a reference point to say that I actually like having SQL in my code and I'm not creating a repository class specifically here but as we go through this you might imagine that my calls here to something that's in the data layer like this insert that we have or like a get method where we're trying to get things back out generally I would put these into a repository again if you're using something like a DB context from Entity framework core you might skip that Al together some people still wrap them in repositories go search online you'll see lots of people debating this but for me because I'm not using EF core I will go make my own repositories manually as I mentioned we will be doing a classic for me versus Dapper as I step through this so we're going to start by looking at an insert here and traditionally this is what I would end up writing in my code I might structure my query a little bit more uh differently just so it's a little bit more readable I don't like having all my SQL on one line here kind of like how this create looked generally I will write it something like this for better formatting this is just a quick example but you'll see that we have to do something like add with value for all the things that are on our specific record type and if I scroll back down here you can see that I have a record that maps to our create table call that we saw earlier for each thing that we want to be able to insert I do have to go write a line like this realistically it's not so bad for me right because a lot of the time again this stuff is really located around my repository generally I will translate these objects that you see so this type of record I will translate this if I'm going between what you might call like different layers so that I don't have something like I don't know in the user interface layer or right at the edge of my web API I'm not returning these entities directly there is generally a translation in between and that way I can have really primitive data types you know right at the entity level do what I need to in the middle if I do have to translate if I have to go update the schema it's generally not so bad because I have these translation layers in between that means if I go to change the schema maybe I add another column in there so I need another property that we would add onto this record down here I would go ahead and just do that and if I need to to sort of propagate that through the rest of my application I just have to go touch the spots where I'm translating it so that data can continue to propagate generally it's a sort of a breaking change that happens right around the repository so this hasn't been so bad but if you can imagine as my application becomes more and more complicated I have more entities more tables and stuff that are showing up in my application it means that yeah I do have to go maintain these queries where I'm inserting things or we're going to see pulling data back out but then I have to just make sure that I'm doing this for every single thing and it can be error prone that if I change the schema I have to remember that I have to go in basically add this mapping here so one of these lines and of course put it here because otherwise it's not actually on the entity itself I'm going to go through the classic get which is very similar and then we'll start to do the comparison with Dapper the get will look similar where I have SQL written directly in here so again in the repository let's assume right you're in this repository class that we've created and so having SQL there for me is a good fit again I want to come back to this idea of you might say well what happens if you want to change your database implementation aren't you going to be kind of screwed over doesn't something like Entity framework core kind of get rid of that like it abstracts for you so you can flip between things like yes and no for the most part but because I have a repository what I'm able to do is have an interface for that repository and I can swap out implementations if I need to generally the differences between the databases I use are very small so it might be that there's some minor syntax changes or something like that or maybe a couple of complex queries that I have to go alter But ultimately it's not so bad and realistically it's very rare that I'm just completely changing my database implementation so it's kind of one of those things that we think about abstracting awake for like a what if scenario but I've in my career probably only done this maybe three or four times tops like that's a bit of a stretch we generally aren't really switching implementations we look at this get we can see a similar thing showing up here we have to add the parameters but this one's not so bad right this one is what we want to look up the record by in this case we're getting it by the ID so adding in one more item here not so bad right we're not mapping all of those things where we are mapping them though is down here so again for every property that we have on our entity so every column that we would have in the table we'd have to go make sure that we're reading these things in that can be a bit of a pain in the butt if you're changing the schema like I said there's nothing that's going to force this ENT or this record to change and have more properties on it and therefore you might have a bit of weird Behavior but at the end of the day like I said because this is managed inside of my repository classes it sort of uh gives me a a bit of buffer between the rest of my application totally falling apart so manageable but it's a bit tedious sometimes this is what I have historically been used to when I switch to Entity framework core and I feel like I lose access to being able to you know have the sequel uh front and center for me to look at the queries and the create statements and things like that like I said it's not that it's wrong that it's doing it I'm just not comfortable with it myself when I go to switch to that it makes me a little bit uneasy there's a lot of things kind of feel a little bit too magical for me I often find that I am fighting against the apis that we have in na framework core so now we're going to look at the Dapper implementation for the insert call here so if I go expand this we can see that the codee's very lightweight and again if you're someone like me who likes having the sequel directly in the code this is a great fit it's not much of a stretch from what we've already seen now I am not going to go into the performance details of this yet we're just going to be talking about the ergonomics of work with this but if you stay to the end of this video I will link you to another one where I will dive into benchmark.pl across a couple lines here so it's just the same sequel that we already have this is the part personally that I like to have and see and work with but we don't have any more of this stuff up here we just have the record which to me is super cool because this is the part that seemed like a bit of nonsense sense and truly when I was trying to do this stuff over time I was always trying to come up with maybe ways that would make this feel a little bit more clean or lightweight but ultimately at the end of the day like having this just abstracted away where I can say hey go insert this thing and I just pass it the record and it happens behind the scenes I love it the other thing is that uh this might sound kind of funny like it's a problem but this can be quite brittle but it's good brittle what I mean by that is that if I were to go change some of the names or the types on the entry so on this record type it means that the squl that Dapper is going to be working with when it goes to do that mapping it will break automatically for me so it's like it's telling me hey look I can't make this thing or these things aren't mapping properly and it's like oh duh yeah I have to go add this thing or I renamed it let me go do that so to me it feels like sure it's not happening at compile time but I generally have a lot of test coverage I go run this stuff and I can make sure that the are covered and good to go so it's nice that it doesn't happen silently that's kind of the thing that really gets me is I I go change the schema and then as a result you know I might change it on the entry itself or I might change it in the database and sometimes these things feel like they're silently failing but Dapper will a bunch of the time call it out for me and say like I can't do this because it's not mapping properly this gets reduced down to something very simple which I really like to use if we go look at the get call it's going to be very very similar right it's not much code at all and again this is why I like it I'm just looking at the sequel here I can focus on the queries that I want to write because I like doing that again if you don't like doing that don't worry enity framework core is probably for you I'm not trying to sell you on Dapper just expose other people to it if you're like me and like having your SQL in the code if we look at this get though we can see that all that I'm doing is passing in the single parameter that we're trying to get by right this ID and we can see that's right in the wear clause and then Dapper is going to do that magical mapping for us again I think that's awesome I don't have to go do any of the stuff like we see back up here in this classic h i don't have to go do this I don't like doing that it's not for me it can be error prone to when you have the columns and stuff renamed but I would call out too that sometimes maybe I should say a lot of the time especially if I'm joining I'm not going to have a star here if I'm just doing a direct read from a table then I might leave this in like this but if I'm joining on stuff then I probably will do aliasing of the things that I'm trying to query back and again if the names and stuff don't match up Dapper's going to call it out for you when you go to execute this yeah I think it would be really cool if at compile time we had some type of static analysis to call that out for us maybe that's in the cards for the future or something like that but again with test coverage and stuff like that it works really well for me now I figured let's go run this thing right I just want to show you that it's all working because you got to see the code you got to see how similar it is or where the little differences are ultimately it came down to just like not having to map the properties directly and you'd get this in any framework core too where you don't have to do this stuff manually which is great now I can have something like that for myself and still have my SQL so if we go give this a little run it's not going to be overly exciting but I just want to show you that it does in fact work so if I get this up on the screen and then we go have a look at the code beside it you can see that we were doing insert and get and we would get Alice back out and then we would do the same thing for Bob so Bob is through the Dapper path and Alice is through this classic path nothing too exciting on the implementation or the execution of that but you got to see the comparison between the two calls one final thing that I'll mention for how Dapper works is that we get these extension methods on the data types or I guess I should say the data classes because I don't mean data types like the records I mean the types that we have with within the data name space it's a little bit confusing if we go have a look at the Dapper code right we can see this execute a sync if you see the tool tip when I'm hovering over here it does label it as an extension right so if I go into this we can see that it's an extension method on a DB connection and this was something important that I kind of mentioned at the beginning of the video where I said I'm going to be using SQL light here but if I'm doing my own projects it's generally like MySQL or something but Dapper doesn't care as long as you're implementing ing this thing here right so if you have an idb connection you're good to go I could go ahead and change most of the implementation of what I'm doing I don't have to be passing around a SQL light connection directly right so I can do this actually I guess I can't I have to use bar here and some of these might break that's on the classic path though right so if I go down to Dapper look at that it just works right same thing classic will probably break even if I put VAR here some of these things are very specific to SQL light calls but if I go down to Dapper boom doesn't care about the specific type it's operating off the interface in a nutshell that's why I like using Dapper it's because it's very similar to how I've been working with databases and a lot of my applications already not claiming it's Superior to Entity framework core just happens to fit better for my needs if you thought that was interesting and you want to see how the performance lines up compared to my classic way you can check out this video next when it's ready thanks and I'll see you next time