I'm a database professional and SQL Server specifically. I've been telling people these things for a VERY LONG TIME! When working as a consultant I pushed FTE's to get queries to run in tenths and think in CPU time and IO aspects to measure query performance. These things are only truly measurable in SSMS and against very large tables. Thanks so much for debunking so much of the bad training in EF. Personally, I use Dapper and Stored Procedures to insulate my tables from developer whimsy.
I work for a Fortune 100 company. Almost 99% of our SQL interfaces are written as stored procedures. It literally took me one afternoon to implement Dapper along with a few wrappers. We absolutely must have performance (we're talking milliseconds are a big deal) and Dapper is a no-brainer. I'm not disparaging EF. I'm just stating what works best for our shop. Also, Dapper is HIGHLY intuitive!!!! Then again, I've been doing .NET and C# for 20+ years.
I thought I was being super unprofessional when I just wrote SQL queries from C# code, so hearing about Dapper's existence and acceptance among pro's is very validating!
Same having grown up with ADO and even pre ADO methods I thought EF was gonna be better and has some nice features but you lose control. However I'm not really sure what Dapper does over the pure ADO approach. Tim mentioned mapping to models... So does that mean Dapper links with the MVC approach?
I know and use both Dapper and Ef for many years and love both of them. I use Ef as core tool for the database model, migrations and basic crud operations. For the more performance sensitive operations I use Dapper.
Agree, in my shop we use both, and just told my fellow devs that we need to keep both. We use EF for data model (even scaffolding) , simple retrieval and update, but if it comes to heavy performance stuffs using Dapper, and even write a high performance Store Procedure. It certainly like Tim said, it force me to write SQL and if I need to tweak, I know where to Tweak.
Worked both with EFCore and Dapper and compared. EFCore is great for getting started and it absolutley has some amazing use cases. Especially with prototyping and small projects. However, medium to larger companies have all kinds of Database stuff, from Sharding to CQRS implimentations and all kinds of custom migrations. If you don't know how any of these things work because you have only worked with EF, and you will (Almost certainly) find yourself in a situation where you don't know the tools. I have been there and it can be daunting. Get comfy with EF but that is just only a jump-start on a very long journey. God bless your hearts.
Awesome video! I'm currently working on yet another project that has the problem you described. A number of years of engineers with varying experience in using EF have made the performance and maintainability poor. The unfortunate solution is several months of development time. The benefit, though, is all of the good discussion and learning for the team as we work through past mistakes. In the distant past I design this particular app using EF because I thought it would stay small scale and I thought I was only creating a simple prototype. Over the years I've learned that a prototype will become production, and production will eventually have to scale.
I agree but not everything will scale - a lot of apps are used by a small number of users and some activities will be limited. We have an app that inserts millions of records in parallelism mode (1 minute) and does spatial processing in SQL Server on those records (another 1 minute). Then we summarize the data as summarizing 5 million records on the fly to show the web page can take 0.6 seconds (shocking for not a fast server) and if you have 10 concurrent users it'll slow down the system😁 The summarized data of the 5 millions records takes 0 seconds to load. If this happened all the time, we'd need more processing capacity than Amazon 😄 We didn't design it to scale other than to allow parallel modes instead of serializing the database processing doing 1 record after another for 5 million. We, in fact, prevent another concurrent situation and queue them that type of processing. But good design and practices always pay off in the long run and will make it easier to scale.
Yeah, those prototypes and MVPs end up becoming production apps way too often. I agree that over-optimization at the outset isn't a good idea, but making fundamentally sound design decisions even at that early stage is incredibly important.
Many excellent points. Schema first with stored procedures so you have to think about and understand what you are doing with the data. It is always about the data. The applications only exist to serve the data creation and consumption. What are the natural relationships? What will people likely search on? End users may speak in terms of the application look, feel, and flow, but as a developer you should be envisioning how the data flows in support of their needs.
I used to do direct ADO in classic ASP (back when my editor was notepad). So, EF always confused me as to the "why", even though I do like the data model concept. I always wanted complete control full stack... Honestly, MVC made me feel the same. It felt like a lot of extra things that really were there to force you to do something complicated to solve an issue that was more preference than purpose. I never knew that EF would make all the strings nvarchar(MAX), thats wild. I don't code for a living, so I skipped a lot of that and now basically just do Blazor Server with Dapper after learning from you. It works for everything I need and makes things like routing, etc, very simple. One thing I really appreciated is how you teach as things are done. By the book is rarely how things are done, that's experience that's valuable. Thank you for that!
Sir, can you give me any recommendations as to how implement solid authentication and authorization system using Dapper? Microsoft Identity system requires EF and I’ll have to use Pomelo EF with my MySQL database. I failed to do it with Dapper because it requires migrations and it MS Identity is trying to create it’s own tables in the database.
It looks like they are adding Raw SQL for unmapped types as part of EFCore 8. This could be really useful, statement from Microsoft "Currently SQL queries must return a type in the model or a scalar type. In EF8, we plan to allow SQL queries that directly return types that are not contained in the EF model."
A lot of things I agree up on, but also this video raises some more questions for me at least. 1) When already using EF core, why not let EF core execute the SP? 2) When using dapper, how do you manage the synchronization between the entities/models and the tables? Do you do this manually and I assume with a database first focus or happens this a different way? 3) If someone really, really wants to use dapper in a project that already has EF core, you can use them both in the same project I guess? I cannot think a reason why not. But again, why would you as EF core can also execute "hand-written" queries or SP's 4) Another reason I don't like is the migration management in code first situation. When more people work on the same project and do changes to entities at the same time, commit it at (almost) the same time, one or both of them will end up having issues when they try to update the database after a pull is done.
1) You can use stored procedures with EF if you are already using EF. This video focused more on the initial decision of which ORM to choose. 2) I personally recommend SSDT to build/update your database. You still need to manually change your table structures when changes are needed, but I'm ok with that. The frustrating thing I see is this idea that your models need to match your tables. That's not usually an efficient design. If you are just mapping tables to models, you need to really evaluate if you need a relational database. I more often see a mapping between models and queries, where only the data you need is returned rather than being sloppy and returning all of the data and ignoring some of it. 3) Yes, you can add Dapper to a project that already uses EF Core. That's what Stack Overflow does in their design. 4) If you use SSDT, your database gets updated with your code deployment.
"It depends" :) I've been doing custom software for businesses since the late 80s. Started with FoxBase (yes, Base, not Pro). Eventually did a LOT of Visual FoxPro. Worked with Oracle before I ever used SQL (Uniface front-end). Did some largish systems with SQL on backend and Visual FoxPro on front-end. Have worked with large commercial ERP software. I consider myself a data guy first that happens to know a thing or two about UX and front-ends that users actually enjoy using. So I really don't want to be a DBA because it just doesn't sound challenging enough (gd&r) However, I also have different requirements and time/budget constraints. I've used ADO datasets/table adapters, XPO, EF, Dapper, etc. They all have their place. For basic CRUD front-ends, XPO/EF are amazing tools. Using XAF framework I can have a fully functioning set of screens in minutes that look amazing and perform just fine. However, I've also used Dapper when the requirements are web based, public facing with stored proc requirements, etc. I've seen just about EVERY tool misused by poorly trained and unexperienced developers. Seen any good Access programs? Me neither, but I don't blame the tool. It's always the person. I'm sure Dapper can be abused/misused as well. My recommendation, regardless of your data access tool, learn SQL because if you don't, you'll be forced to under the gun which isn't pleasant. But ORMs, code first, etc. have their place but you still have to be able to optimize and troubleshoot - just expect it and prepare for it rather than trying to avoid it.
I'm curious what you are trying to test when you test the data layer. Are you looking to verify that the queries return the data you are expecting? Are you trying to test that Dapper does what it says it will do?
@@IAmTimCorey Thanks for the reply Tim, I think there's no point in testing dapper itself because the dapper team does that already. I believe the purpose of testing the DA layer is to ensure your queries and production database don't have a conflict and will return what we expect them to return. Like in EF we can either mock the DbContext or can use the In-memory provider.
Unit testing should not include interactions outside of the method, so a database should not be in the mix. That's what I'm trying to figure out. I'm not sure what value a unit test would give in this situation.
Before Entity Framework, I worked with Visual Studio's dataset designer to create table adapters and datasets to access SQL data and procedures. Table adapters used SQL queries to create the model for you along with the queries for CRUD operations. You could also make your own queries and use them. This was using Framework of course, I haven't seen them in Core. The trade-off in using EF was loss of control. Dapper gives me that back, which is why I prefer it.
Ah yes, the DataSet days. Interestingly, data-binding is kind of making a comeback in scenarios like Blazor / WASM. Funny how things come full circle. :)
Good video. But as I have always maintained, both tools are inadequate in the hands of a poor developer. The key here is knowing the tool you use and knowing it well. I have used EF & EFcore for serval years now (I tried dapper very briefly) and I can say Efcore has been great both for dev and production. With EFcore I can get the best speeds in almost any if not any scenario. Honestly I really don't know what dapper can do that efcore cannot unless you really don't know what you are doing. Also i don't have to manage two seperate projects, an sql project and then a c# project (in order to track Db changes). Again I have the advantage and power of intelliscense and if I need to write raw sql, I can. Migration is awswome! Don't have to change table names in sql or database manually. Very importantly performance and feature updates are assured in every update (Yearly update). Because the tool is backed by one of the biggest companies in the world, quality & longevity of the framework has a high probability. Even though you have made it clear in the video that both are great tools, the comment section so for does not paint the same picture and might be quite misleading, in my humble opinion, to someone who chances on this video. Still a good video!
The problem I'm having with this whole thing is he's basically saying using Dapper forces you to also learn SQL in depth, and EF doesn't necessarily. Ok but if your job (as mine is right now) is to set up an MVP for a product I simply don't have that kind of time. So I am that "poor developer" you refer to here, I'll fully admit that, but what can I do about it? Only so many hours in a day. I think a better solution I'd personally like to see someone (Microsoft?) do is make a video about best practices. Still after watching Tim's video here I'm not any closer to knowing what exactly it is that I'm doing wrong, other than not providing a char limit to string properties.
@@johnhershberg5915 I think it's fine to not know SQL in depth... at first. Understanding the data side is something you may not have time right now for, but you need to know it to know how things end up working. Everyone is a poor developer until they aren't. This isn't a criticism, it's just a fact of time and experience. It really is a good idea to offload the data logic to the server where the data is stored whenever possible if for nothing else for the network transmission and searching efficiencies. If you're not full stack yet, maybe find someone who is good with the data side and just sync with them. Something we don't consider often is the size of the payload and the effect on network transmission as we don't consider that a bottleneck much anymore, but it can be. If I can pull one part of data already searched and sorted, vs the entire dataset then searched and sorted programmatically in memory. It's going to be exponentially faster even if you make more small network calls with less overhead overall. Doing joins, and even things like stored procedures on the Database side just divides the processing components. Here's a thing I think he mentioned that is very important. ALL applications are data transfer applications. Thats it, its information in the form of data being moved from one place to another. In that case, the data model is probably the most important feature you can focus on at first. GUI, looks great but functionality is priority to me, even in MVP's. Although, we know that that's not what the customer wants, but what the customer needs. But yeah, correctly scoping DB fields is about as basic a "improvement" as you can get, and that starts with appropriate DB design.
I've followed a lot of your content the last couple years while learning C#, in the early days when I made a small application and felt overwhelmed by EF I found a video you made about Dapper and honestly I've never looked back. EF to me feels like such a huge bloated thing that I just don't need, I knew SQL before I started C# so for me Dapper has been perfect.
To use EF correctly, you have to understand what SQL the framework translates your Linq into. That's what it does. It translates into SQL. Then it does the mapping like all ORM. So if you're going to choose Dapper, so that you can learn to do SQL queries (and Store Procedures) correctly, you're sort of learning SQL in detail and Dapper and C#. I sort of can't find the strength to write SQL statements in strings. It seems like writing an SP, but not putting it into the database. It's all a little sloppy.
EF because it lets me do model first and a DDD approach. That way I can focus purely on the business domain and keep the database as an abstracted concern. Dapper if I'm in a project which is database first or the database is legacy.
I just love Dapper, Store Procedure in SQL is powerful as heck. Was able to resolve so many issues without having to recompile the C# project. But only set back is you will be stuck with SQL server and moving off to MySQL will require a lot of rewrites.
The question is, how often do you switch database servers in an actual project? Personally, I've changed the database layer once in 20+ years of consulting with hundreds of companies.
Great Video Tim! I've used EF since it's inception and know full well of the issues you described. I was fortunate enough to start a greenfield project a year ago and chose to go with dapper as the requirements were very simple ( read only data access and no complicated joins). With dapper, ive managed to write a dynamic query engine that handles filtering, paging and sorting. It uses reflection to figure out what queries to write. The good thing is, it's really just a glorified string builder. Solving complicated problems with simple solutions. I use generics to make sure the type mapping is also dynamic. At first i thought it may be overkill but having written it, it's very straightforward to work with and a lot less hassle than EF. Another good thing about thie project is I relearned a lot of sql along the way. Deferring data logic to sql is way more efficient than writing complicated business logic to retrieve and then manipulate data. I also do agree that EF has it's merits but it really does require a deep understanding of HOW it works for it to be used efficiently. If i was to use it in future on a fresh project, i would probably go down the cqrs route and use dapper for writes and ef for reads (depending on the complexity of the data schema/business logic). Thanks again for this video. It echos my sentiments on both technologies (i have a preference for dapper but in very specific scenarios). Keep up the good work :)
A really valuable discussion. I hope you make more videos like this cuz it is helpful and saves us a lot of time efforts with design decisions. Big love to you Tim ❤️❤️❤️
I remember a statisics course in college where my professor would use the word "parsimonious" at least once a week. The definition roughly implies "sparing, frugal, or restrained". I use that as a guiding philosophy in my development. I try to use the minimum i can to get the job done, keeping in mind maintainability. This video resonates with me!
I think the points on this video are more sentimental than factual. I did SQL before learning C#. I am a single developer. I would spend about a week, sometimes 2, designing the DB. With EF, I reduced the entire week to mere minutes or hours. Of course, i encountered those cases where EF wrote bad queries and i would fix it in the code or where necessary, write my own queries. I started using dapper and i love it. Both have their place. For a complex insert statement, EF rules. Why would you want to write an insert statement for every table? Moreso when you want to insert child values at the same time? You will execute the SQL, get the inserted ID before inserting it in child tables. Thats a loop, you can avoid 😉. You do realize you are only shifting the overhead to either C# or SQL. You only duty, sometimes it's to write code. Use dapper for complex select queries, for all other things, use EF.
You aren't "shifting overhead to either C# or SQL", you are making C# do what SQL is best at. You aren't relying on the power of your database. I see lots of people who are super-optimistic about EF, especially at the beginning. What I wonder is how many EF installations have you had to manage in production at any type of scale? That's where the problems come in. Not development, not early testing, not even early production. It isn't until it is too late. I'm working with a non-profit right now that has less than 300 employees and less than 20,000 active customers, yet they are being crushed by EF. And unfortunately, it is so woven into their architecture by now that it cannot just be replaced. Even replacing certain calls with stored procedures isn't an option. They have exceeded the capacity of their largest servers and have now moved to the cloud so that they can scale larger. That is an absolute waste of resources because of EF.
Was hoping more about Dapper vs Entity. Feel like I just visited the coders principles office. I still consider these as middleware and allowing intelisense easy buttons for field names. Agree Entity can create walls you can't get around. Being a Full Stack developer, you need to understand SQL. C# filtering or LINQ can really be abused like you said. Good points.
Very diplomatic. Using EF only lets you get expert in EF. Using dapper only forces you to become at least competent in the SQL backend. I think the latter is the far superior starting point, if not necessarily where you stay.
Working on a Blazor Server project, and so far from what I've been experimenting with, user authentication is a different beast since BS isn't a request cookie based system. I'm finding that the authention and authorization system almost has to be done using the authorize/role attributes setup, which seems to be pretty tight with EF. So, as much as I'd like to just use Dapper, would the best approach be EF for user auth and Dapper for everything else? That's what I'm considering, but trying to work out the logistics of splitting things while having them play well together
I use both of them evens at the same time in the same project! depend on situation. Dapper do a better job in some situation, but some other case EF is the better choice. For eg, when you need the power of IQueryable to compose your query programaticly without long "if" mixing with string concatenation, then there is no better choice than EF.
Dapper is literally just EF with less features. It benchmarks only a few milliseconds faster on raw sql execution. We use the term "micro-orm" because it solves only one of the many problems with database development. What about DevOps? EF has database migrations, but with Dapper you'll likely end up changing your database manually in production. What about code simplicity? Maintaining SQL is time and money. Entity Framework has navigation properties while Dapper has you writing a complicated repository class to manage relationships or creating many models to represent the same data. EF gives you table and entity splitting so you decide where you want the code and database to logically converge. EF has simple change tracking while Dapper has you constantly mapping objects and passing transactions around to make things work. EF also has OData, which can retrieve every permutation of your data with a single API endpoint. Even if a new C# developer doesn't know SQL the argument could be made they should learn LINQ first since it's applicable to other C# tasks whereas SQL is not. This person probably shouldn't be writing data access code in either case, though.
If you use SSDT to develop your database, you can deploy it just like your code (and put it in source control). As for EF doing mapping better, I would disagree. With Dapper, you make the best choice for the situation (one query, multiple queries, etc.) to get the data. With EF, it makes the call to return more data than you need. For instance, if you had a table with ten people in it, and a table with orders in it, and each person had ten orders associated with their account, and you wanted to get a list of people with their orders, EF would return 100 records. That sounds right, but it would return each person 10 times (one per associated record). That's a LOT of extra data transmitted across the wire. As an EF user, you never see that data. You see 10 models, each with 10 sub-models for orders each. Unless you actually look at the query, you don't see the wasteful transmission. With Dapper, you would do two queries and then map the data over. That's a simple (and small) example. The "normal" EF use case is joining multiple tables together (it is a relational database after all), and transmitting hundreds or thousands of times the amount of data across the wire compared to what you actually need.
@@IAmTimCorey EF has a feature called split queries for that exact scenario. You can also use projections, table/entity splitting, and raw sql to shape things differently
I personally prefer more Dapper than EF. In my case it's simply a matter of preference than anything else. I simply have the sense of more control over what is going on in my application with Dapper than with EF. Probably, I need to dive deeper in EF to know more about it's internal behaviour in order to have the same feeling of security I have with Dapper, however the latter is so simple and so powerful that I simply do not miss EF in any of the applications I build. To be frank I tend to use EF just for very simple applications or just for prototyping something quickly as a proof of concept or some sort of thing. But, as you said... both tools are great and have their place.
I kinda disagree with the last point: "You are too invested in EF that you can't go back". It all depends on how well engineered your project is. Not only EF, but if your architecture is highly coupled with a specific ORM or any framework for that matter, then yes, it will be really hard to replace that component in your system if not impossible or not viable at all. But if you followed the best practices (repository pattern, abstraction/interfaces, SOLID, etc) and you have a data access layer decoupled from any specific library or framework implementation, you can move things around with less or minimal effort. "It all depends on the person using the tool", I completely agree with that and moreover, I'd say "It all depends on the person designing the system".
I get the idea, but the reality isn't that clean. First, clean layers almost always break down in "real" applications over time. Second, even if the layers are entirely clean, you will have a LOT of queries to replicate into SQL statements. I'm working with a small-ish non-profit right now that would need to take probably 3-6 months to just replace their queries with raw SQL statements in order to move away from EF. That's just not viable.
@ghost mall - I'm not blaming the tool. I'm saying that it is being used improperly. When relationships get complex enough, you really need to write custom queries instead of using EF-generated queries. The problem is that most people don't do that. A great tool can be a real problem when it is used improperly.
Hi Tim, I agree with all of the issues you mentioned re EF. Unlike you, however, my dislike of EF (and ORM generally) does rise to the level of hate. My consulting experience is similar to yours in that I've been hired on a few occasions to fix some ORM disasters, and as you mentioned, this is not that easy to do because that code is typically deeply ingrained. Having said that, I think Dapper is a good tool. I also agree with your viewpoint that what really matters is the data. This point often seems lost in some development shops.
That's exactly been my experience with EF. Luckily, it seems that everything falls apart fairly quickly you don't know exactly what your model builder does, provided you integrate and test early.
Great video Tim. I've only been a developer for 6 months and have learnt everything I know from your courses. I've almost just finished converting an app from EF to Dapper. I also prefer Dapper and I like that I'm learning SQL at the same time. I still have to learn EF to understand how Identity works but for everything else it's Dapper. One thing I'm wondering - is it better to use stored procedures or write the queries in the classes?
I highly recommend stored procedures. That way, you can optimize them in SQL when you notice a problem in SQL. You don't need to find the T-SQL in your C# source code to optimize it when you find a problem in SQL.
I've defaulted to using Dapper for queries and EF for CUD due to easier maintainability. Stored procedures are technically faster, but they have versioning issues since you might need to deploy the stored procedure and your app at the same time vs just having the SQL directly in the app... but the security comment is also true. Everything is always a combination of pros and cons.
Hi Tim .. you already answered the question clearly when u said : c# should not do SQL job ...also a sentence u said in your sit about dapper : It is beginner-friendly... does this means dapper is only for beginners and not suitable for large apps ? .. thanks
Nope, Dapper is absolutely a tool for everyone. It is incredibly performant and has been proven in high-throughput situations. In fact, larger apps will more often benefit from Dapper over EF because of the performance gains.
EF does a great job for startups doing their MVP's. Yes, there you can accept a slower SQL code. If they prove their product profitable, then they will have the resources to refactor the code. You shouldn't strive for the best code in a MVP, but really to make it as quick as possible. There is where EF can help, especially considering that startups might have a limited skill team of programmers (sometimes a 'team' of a single person).
The problem I see with this is that MVPs tend to become the "real" application. So the very foundation of your application is knowingly less performant. I know you don't want to prematurely optimize, but there is also something to building the foundation properly. Otherwise, you work yourself into a corner. You won't have time to go back and fix it later, so you just live with the bad design decision.
@Feitan - That statement confuses me. I've not seen that to be the case. What do you mean EF gets faster? Complex queries are the biggest issue with EF, where it gets too much information. For instance, in a many-to-many join, it just returns lots of duplicate records and then deletes them on the C# side. Things like that are inefficient, but that inefficiency doesn't really show up until you have a "real" production database, where relationships are larger and the number of records is beyond what you normally see in a development database.
@@IAmTimCorey i've done some heavy lifting with both and sometimes EF was even or even on top of Dapper after some repetitive tasks, due to the caching maybe. And yes, it was in production. Not trying to say EF is faster tho', as many other experienced people have proven it to be false.
If you use SSDT, you can put your project in Visual Studio, track the changes in source control, deploy the changes with your application, and you can do things like renaming a column and having all other entities that use that column name (in the SSDT project) rename as well. Here is a video with more info: th-cam.com/video/ijDcHGxyqE4/w-d-xo.htmlsi=AlVFk0-p3PUR7ekK
Great video, I know you do a lot of dapper, but when people go for a job to work on existing software, and that software uses EF, then they will find it more difficult, It would be great if you did lessons on how to use EF better.
I get that. The problem is that since I've stayed away from it for so long, I can't do it justice to teach it at any level of depth. At least not at this point. I don't teach something unless I know it really well. Otherwise, I can lead people down the wrong path. That's why I have not taught EF in more depth. It may be something I take on in the future, but it would be a lot of work.
@@IAmTimCorey I completely understand where you're coming from and I personally prefer dapper myself, hoping never to use EF, but alas, I've just moved teams and found myself working with EF and the poor implementation the other developers have done with it. Thanks anyway. Much appreciate your thoughts on it.
@@IAmTimCorey how recently? A lot of bad things I hear about EF is from developers who barely have used it since core. EF Core 6/7 is very different from EF 6. Sure, if you dont know SQL you probably will make a mess with EF and how you create relations. But that's the same if you manually create tables and have no idea what you are doing. You should learn fundamentals of SQL regardless, but I don't think you need to be an expert in it, unless you want to work as a DBA. Also lets face it, majority of developers will work with small-medium sized apps and EF is fine for that. I've been at places where its the other way around. Legacy databases with several hundreds stored procedures calling into other stored procedures, no one dares touching the database because the DBAs working on it quit a decade ago. So you keep extending the app but building wierd work arounds to support the existing database. Or code using Dapper with SQL in code where a column name change would require manual updates in 30 different handwritten strings and you hope you didn't miss or misspelled any.
I'm very comfortable in SQL, but I've always preferred EF because it allows me to coordinate complex business logic across multiple entities using a single database transaction. I can break these complex operations into small, maintainable chunks of C# code, where each chunk is only aware of its small part of the domain. To do this in Dapper, it seems like you would either have a giant and complicated stored procedure, or several small procedures that requires several calls to the database. Is it actually more efficient to do it that way?! And I've always been uncomfortable putting business logic in stored procedures. I'd love to know how you manage this with Dapper
Well, saying that dapper forces you to learn SQL is for me saying that we shouldn't code in C# either and just learn C to make our own hash tables. I look at C# as an abstraction, like EFCore is for SQL - which leads me to the point; sometimes you just want to code fun stuff. But if the intention is to code real enterprise and performant applications, it should be done in C.
For me when using sql stored procedures and functions for heavy sql queries i get better performance than dapper, and when i do not need performance heavy queries i use EF, which makes it much faster than creating my own sql queries
As always, as "easier" a tool is, as more complex scenarios it tries to handle automaticly, as less you have control over it - or you need to dig in deep, to modify things. This is EF. Without EF, you have to do more yourself - write more code - but also: you are allowed and able to write more code. In Dapper you have to take care about upgrading database structure yourself, you also have to modify your code if you change database from T-SQL to mySQL, or SQLite or so. But in EF you have a 1:1 scenario from client to server. Adding a new column to one table, requires to update all EF database clients, cause it detects "structure changed - incompatible -stop working" otherwise. Although ignoring additional columns is a simpe thing in SQL. So it's really many pro's and cons.
I am a junior developer and I adopt your philosophy for dapper and I am really using it. But now I will get in a new position at my company and they are using EF. So I am kind of traped.
Not so long ago I used to think that ORM's were the best way to map between the database and domain model... now I think that the complexity that EF brings might not be justified. I rather just write the SQL and the object mapping code, which always feels like magic with EF, especially with Value Objects. Patterns like Unit of Work are also a bit easier to reason about since the code ends up being more explicit and straight-forward.
This is what I’m doing as well, but it feels unwieldy after a class grows in size. Wondering if I’m not doing it efficiently. Do you have any examples or articles that explain how to do this efficiently?
I used to write my own ADO code because it felt like the most efficient way to write data access code, but Dapper is so small and performant that it is practically the same as direct ADO code, plus it gives me the added performance benefit of having my data mapped correctly to strongly-typed properties in my model. That's a big performance gain.
Appreciate the reply Tim, sounds like dapper is exactly what I’m looking for then! Any videos you recommend for getting started? Should I start with your “Data Access with Dapper and SQL” video? Or any others you suggest? th-cam.com/video/dwMFg6uxQ0I/w-d-xo.html&feature=shares
This is the video where I introduce Dapper: th-cam.com/video/Et2khGnrIqc/w-d-xo.html Then here is an Advanced Dapper video: th-cam.com/video/eKkh5Xm0OlU/w-d-xo.html After that, the video you listed would be a good next step.
Weighing in as someone well-versed in SQL/T-SQL, and spent the last couple years using EF Core. While EF Core has some great features/improvements in .NET 5.0 - 7.0 era, it just hasn't been fruitful for me in the fulltime/Enterprise class environment. For hobby and small projects, I love EF Core. In the Enterprise world, it's like flying too close to the sun. It adds a lot of complication, especially in defining complex relationships. Doesn't mean I couldn't mix my strategies, but I find it best (and most maintainable) to stick with one consistent strategy. I think EF Core is wonderful, but for new projects in the Enterprise settings, I can't recommend it. You should still learn and practice it though.
It's sort of the long way of saying, I spent a couple years trying to disagree with Tim, only to come around to the same conclusion. This video is very honest and graceful, and I've come around to the same understanding. Damn you Tim.
I had seen one of your earlier videos on the topic. My lingering question is whether these limitations for EF are unavoidable by definition (and every ORM has the same problem), or whether EF could release a version in a year's time that optimises their way around it.
Mostly unavoidable. There is a reason why we don't have generic applications that just fit every scenario. Trying to figure out how to write a performant ORM that works for everyone is practically impossible. EF Core 8 has made some big strides, but there is still a lot that is on the developer to do. You need to customize it to be precise enough for your needs, and even then you will find areas where it just makes a mess.
Some SQL is nice know, because you inevitably run into a limitation that entity has. Its not a matter of if you will ever have to dive directly in the database and debug data, its matter of when. Be prepared.
What do you recommend for sql source control then , since EF core would handle the versioning etc with migrations ? I’m a fan of dapper as well , was wondering what tools are out there or patterns for managing db schema changes ?
Use SSDT to create your database (or manage your existing database) and put it into source control. This also allows you to update your database as part of your CI/CD process: th-cam.com/video/ijDcHGxyqE4/w-d-xo.htmlsi=iBQMtFUA_zT6Rkqm
I moved to tryinfg Dapper after watching one of your videos as a course I was doing used EF but coming from a point of not knowing much SQL it was hard to understand how it fully worked and when something didnt work it was a struggle at time to work it out. Tried dapper and as you say forcing me to use SQL maybe learnt a lot on how things work and I found myself finding better ways to do querys and structuring my models etc.
I totally agree with this video. I am starting on a new project and pondered using EF but as we may have junior developers working on the codebase, EF will add too much to their cognitive load. Besides in the past I got fed up with trying to figure out what SQL EF is generating when a query doesn't work. Btw - have you tried Repo DB?
Hey, Tim! Thanks for the video. I haven't noticed, if you mentioned about kind of built-in migrations stuff in EF Core in comparison with a need to use another library like FluentMigrator with Dapper. What are your thoughts on that?
@@RussianBananan SSDT contains SQL Server database project that allows you to manage your full schema in visual studio using publish and schema compare to produce scripts for schema updates
Hello Tim, It's a great video. I have a question. what about if you use EFcore with stored procedure(select, insert, update) insteat of direct queries?. I really love stored procedure and I'm testing all with EFCore.
When you get to that point, I wonder what value EF is actually adding to your process. It would seem like a lot of overhead for what Dapper does for you with much less complexity.
@@IAmTimCorey Thanks for you replay, I will investigate and test more in deep with dapper, I'll watch your two videos(Dapper and Best Practice with EF).
Hey Tim, Love your channel and this video. I agree with your perspective about Dapper. Question for you or anyone else though, is Dapper being worked on or is it dead? Seems like it hasn't been updated in many years. I'm asking because I'm considering using it for a new project.
Absolutely. The "issue" is that there isn't much to change. That's the beauty of Dapper. It is really simple and it "just works". There is talk of adding AOT to Dapper to make it even faster, but I'm not sure when that will happen. But yes, Dapper is a currently-maintained project.
9:00 - Apparently, I visibly winced at this point enough that my colleague asked if I was okay. I think anyone who is okay with creating an nvarchar(max) coming for a simple string value should be suspended from development until they go through remedial education. Holy hell....
Hi Tim, Awesome video, I first learnt about Dapper from your Course "Data Access", but I have a question, is it possible to use Dapper with MS Identity?
Yes, you can, although I don't simply because I don't like messing with authentication/authorization code to that level. It makes me responsible for security flaws in my code to a much greater extent.
I just started using EF, it already looks better than every ORM i used before. I don't think that "generates bad queries" is even an argument. 99% of the queries are great, you can see the sql in the console. If they're not great you can write raw sql if you want. It's not like you're force to use a tool. The main points for me for an ORM, i want to be able to refactor my database often and i don't want to write joins. I want strong typed stuff everywhere in my codebase. I want to reuse code easily.
There is a lot to unpack here. 1. Yes, EF is a great ORM. 2. "it generates bad queries" is absolutely an argument. A lot of times that is because of poor usage by the developer, but not always. When you do a join in EF between a Person table and an Order table, if each person has 10 orders, EF will return each person 10 times (one per order). So if you have 100 people, it would return 1,000 records including 900 duplicate Person records. Then if every order has 10 items in it and you want to return those as well, the result will be that you get back 10,000 records with 9,900 duplicate Person records and 9,000 duplicate Order records. EF will then eliminate the duplicates on the client side and display them as nice, neat objects. However, you just transferred all of that extra information across the network. That is not a good query (but it is the best solution EF can do unless you force it to call multiple queries). 3. "99% of the queries are great" - I just pointed out that no, they aren't. Looking at a query in the console doesn't actually tell you anything. You need to test it on SQL and see what it returns and how it performs. You can't see the execution plan in the console. You can't see if it is properly using indexes in the console. Anyone who writes a SQL statement and thinks it is good by looking at it hasn't learned enough about how SQL works. Testing it is the only way to properly evaluate it (and testing it against near-production data, since the data can change whether the query is performant or not). 4. "I want to be able to refactor my database often" - That is a terrible idea. That's not how databases work. You need to have a solid plan going into building your database and once it is built, you need to be very careful how you change it. Trying to do major refactoring on a database once it is in production is a BAD idea. Even doing it in development is a bad idea, since that should have a direct impact on your code as well. That's like saying that you want to change the structure and layout of your house's foundation as you build it. Your data is the foundation of your application. Redoing how it is structured will fundamentally change everything about your application. 5. "I don't want to write joins" - You are working with a *relational* database. The whole point is joins. If you don't want to do joins, you probably want to look at a NoSQL solution instead. Trying to avoid joins by using EF is not the way. 6. "I want strong typed stuff everywhere in my codebase" - That's what C# is - a strongly-typed language. I'm not sure what you think you are avoiding here. I would encourage you to dive a LOT deeper into EF and how it works before you use it in production. Get some more practice in how it works and in testing the results in a real-world scenario (not production, though). Here is a video I did on some best practices to consider when using EF: th-cam.com/video/qkJ9keBmQWo/w-d-xo.htmlsi=omflTeOH_5ed_afF
@@IAmTimCorey "bad queries", ok i just tested this and see what you mean. But how to you solve this? "AsSplitQuery()" seems to work. (5-10% faster in my tests) But thats an advanced feature from EF. (that you don't get with dapper) If i would write it manually i would write one big query "select * from customers left join orders" exactly how EF does it. With "great queries" i mean it doesnt do "stupid stuff" like other ORMs. It can handle child.FirstOrDefault() pretty well and also childCollection.ToList(). I don't care about 10% faster, i just don't want it to generate n+1 queries or something like that. I don't want to write joins manually, its tedious. I strongly have to disagree with "You need to have a solid plan going into building your database". Thats not how reality works. No project i've EVER worked on had a perfect database design. And when you take over mangled projects then everything is a mess. i definitely want to change my database all the time. But arguing that topic strongly depends on the environment. Just be careful if you don't allow your devs to change annoying (database) systems, they might eventually get annoyed working at the project. Strong typed LinQ, not raw sql. But i'm not familiar enough with dapper to compare them. I check out your video, thanks.
@@IAmTimCorey Another great point is that writing raw sql is very prone to errors. I never had a problem with ORMs. But when you write raw sql you constantly have to check if you did all joins correctly and not suddenly have 10x records or just 1/3 because you messed up a join. Raw SQL is even dangerous sometimes, ever forgot a where clause in an update statement? :D I think the options to manually pimp queries is great, but we also have that with an ORM. Do you really want to write joins between 10 tables manually? Do you really never want to refactor the database. Oh the column name was "name" good luck refactoring that in 10000 queries with raw sql where every table has a column called name.
Just a couple of things here that I'll touch on. First, you asked how you would do that join correctly. The answer is to make two separate calls to SQL. That is so much more efficient than trying to flatten objects into rows. If you have multiple SQL calls, you can then join the data in C# using LINQ very efficiently (let SQL do what it does best and let C# do what it does best). By the way, Dapper puts your data into C# objects too, so you can use LINQ, etc. just like you can with EF. When you are building your application in the first place, you might make significant database changes (although, that is still VERY expensive). However, once your application is running, that's a bad idea. You are approaching your database like it is only used by your one application. You don't do reporting? You don't have a data warehouse? You don't have any other application that needs your data? If you do, you break all of those so your application can be better. If you struggle writing good SQL, you are definitely going to struggle writing good EF queries.
Yes. It uses ADO under the covers and then it maps the return values to model classes. It does so incredibly efficiently, so that the performance including the mapping is not much more than just straight ADO calls.
If you have big datas "dapper" better or working with big datas. Example (millions) but you just working little a project or application can use ef core. So, you should determine it according to the needs of the project.
There's a balance here. I'm doing some consulting work for a company that uses an open-source data system that is built on EF (unfortunately the .NET Framework version). It is designed for small non-profit organizations, so they felt that it "didn't matter". The non-profit I'm consulting with has less than 200 employees, yet their database has millions of records. So small is relative. Also, even though other companies that use the software have smaller databases, they still have performance issues because of EF configuration that they cannot get around easily. It would take a major rewrite to change how the system was structured. It isn't as easy as replacing a bad query with a stored procedure. EF was abused, but it "worked" at smaller scales (basically, they didn't notice enough of a performance hit), so now they are dependent on how EF does things. The "solution" is to move the application to Azure so that they can scale up the application (they've exceeded what a reasonably-priced server can do with tons of RAM and SSDs).
I used Dapper before, but I'm currently switching to EFCore, because I miss database version control. If you're working code first in EFCore, that's all being taken care of. I don't think it's a perfect solution. Do you have any suggestions for database version control when using Dapper or when working database first?
@@DigitalNomadOnFIRE No, I'm looking for a solution to decrease downtime for a company with a very complex database and maintained by 20+ developers. There are more challenges than just database, but my focus is database management and version control to decrease downtime and making it easier to maintain. The project I am talking about is a prototype project, but it's helping me make choices
@DannyM - Check out SSDT. It will allow you to add your database to source control, you can build it in Visual Studio, and you can deploy it with your source code. Here is a video on how to use it: th-cam.com/video/ijDcHGxyqE4/w-d-xo.html
@@IAmTimCorey I still use SSDT even if I'm doing ORM with code first. I keep them in sync and because I have multiple projects in my solution that don't all use ORM so there are stored procs, views, etc. and then get the added benefit of your database schema being in source control. I've also found I like writing T-SQL in Visual Studio better than SSMS.
One thing I would contend is the 'debugging' is better eith Dapper/Stored Proc approach. It can be frustrating to find errors in SPs when called from your middleware code... At least doing it all in C# gives you access to sn dxellent debugger. I prefer SQL SP approach but its not great in thst area.
I agree that having two systems means debugging gets harder. However, there are some things to help. First, the SQL logs will be a LOT clearer and easier to search because you can search for the stored procedure's name rather than trying to figure out what the generated code will look like. That works the other way as well, where if you find something in the logs, it is easy to search your code for the offending bit. Second, your stored procedures should hopefully be rather simple. If you are putting full business logic in your procedures (beyond basic safety checks), you probably want to re-evaluate your planning. If they are simple, there is no debugging needed on the SQL side. If they are more complex, I would recommend unit testing your procedures so that they are rock-solid.
@@IAmTimCorey thanks Tim... not least for deciphering my awful typos!! I think that is perhaps where I have come unstuck. I used SQL long before C# or even VB so tended to put a lot of complex logic in the SPs. After being stuck in my ways, I'm taking a step back and almost going through a new learning curve on this... in fact just watching your other Dapper video now and I wasn't even aware of the SQL project type in Visual Studio!! That alone made the video worthwhile before I even get into Dapper.a
@IAmTimCorey I am doing it a lot, especially when you have some structures like tree nodes and you need to build a full tree or some other "unusual" tasks like that.
Yes, it can. The question is, what is EF Core giving you then? Is it just database creation? If so, why not use SSDT: th-cam.com/video/ijDcHGxyqE4/w-d-xo.html
Sure, but at some point, you lose enough of the benefits of EF that you start to wonder if the complexity it adds is still less than the value it provides. Most queries that EF does should probably be stored procedures. The way EF gets joined data is inefficient. But then if you try to use a stored procedure, the best option would actually be to have it return two result sets (or run two stored procedures). But EF isn't set up to do that. So now you have to think about dropping out to Dapper. Now you've added more complexity, bypassed more of EF, and in the end aren't getting enough benefits to justify the complexity.
It's easier to be a mess with EF, just has a table that has self reference (ex. Some product is related to another that is related to the first) and try to get all information just like you said 😂 But still love EF the same
If IQueryable didnt inherit IEnumerable, EF would be a lot better I think. Its just such a misleading abstraction to reuse an in memory collection API and apply it to database tables. I wish there was an ORM that did something like Dapper, but had an API that mapped more directly the SQL API.
Using tools wrong is not exclusive to EF. At my old workplace they used raw sql everywhere in their webshop. The product page takes 10 seconds to load, for one product.
Yes, every tool can be used incorrectly. That's obvious. But EF is known for being abused. As for your old workplace, the problem doesn't sound like the tools they chose. It definitely wasn't because they chose to write raw SQL. It sounds like they either wrote bad SQL or that they had other issues. A tool doesn't fix bad SQL or design issues.
Thanks for your words, could you make a video of dapper and webapi, with heavy artillery, such as layers, dependency injection, repositories, exception handling, for production
Im probably missing the point of dapper but is it just to call stored procedures? On all of my previous jobs and places i've interviewed, told me to stay away from store procedures bc of the inability to debug once youre stepping through the code. Why should you go to SQL if youre debugging? My current job relies heavily on Stored procedures which i find really annoying to debug. Specially if they have hundreds of them with multiple joins. I agree with having the proper structure and if you write the proper repo then things are much better in C#.
Dapper is doing a smaller version of what EF does. In fact, it uses the same underlying ADO.NET to make the calls to SQL. EF writes the queries and then executes them on SQL. Dapper asks you to provide the queries and then it executes them. You can use stored procedures (preferred) or you can write the SQL in your C# code and have Dapper execute it. The reason that stored procedures are preferred is because you get the benefits of having your SQL "stuff" in SQL. If you want to optimize a query, you can do it in SQL using your DBA. If your DBA wants to optimize SQL that is in your C# code, they have to give a developer the SQL and have them find out where it is and then make the changes. Even worse, if you are using EF, that optimization might not even be possible. I've heard the "don't do debugging in SQL" argument before, but it doesn't really hold water. Do SQL tasks in SQL and do C# tasks in C#. If your SQL call is incorrect, fix it in SQL. That's the way to properly optimize your system. Doing too much business logic is usually a bad idea in SQL, but so is "preventing" that by putting your SQL code fully inside of your C# code.
Agree with a lot of the points you mention, but stored procedures are a big no. They are a classic consultants solution. You come in, write the equivalent of method that was slow in code as a procedure, performance improves (obviously) you collect your check and goodbye. However you have now created a massive complexity for maintenance when something needs to be added or changed in that data fetching or data writing operation, as you essentially moved the business logic completely out of the codebase. CQRS pattern with well written Dapper/Entity code is the way to go for both the performance and the longterm maintainability. And longterm maintainability is something a lot of "consultants" never pay attention to, maybe even intentionally as they can get called back in 6 months to collect another check.
I think you are mixing two things together - stored procedures and business logic. Those are two separate topics. Yes, you can put business logic in stored procedures and that CAN be a bad thing (not always). However, you can also use stored procedures to house just your data access calls. For instance, if you have the query "select FirstName, LastName from Person" and you put that in a stored procedure, is that storing business logic? Nope. You could argue that the query should be next to the code so they can change together, but that's not a great argument. Your code is never together anyway. You separate it out. As for the benefits of stored procedures, there are many. First, it puts your database calls in your database. Second, it makes debugging failed calls much easier (you get a name to track down rather than a query signature). Third, they force you to use parameters, which increases your overall security. Fourth, and one that most people sleep on, is that you can lock down your data access permissions to only calling stored procedures. That basically eliminates the possibility of SQL injection.
@@IAmTimCorey in the video you have mentioned using stored procedures as a solution when a very important and often used query which has been poorly written. Unless there is a decent amount of business logic included in how the data is stored and queried for I don't see a way that a way for it to become a performance chokepoint. I can see your point of completely using stored procedures for data access as something that would be very high performance if done right, however I believe using CQRS pattern in combination with dapper is the best of both worlds. Big drawback of using stored procedures when you are working in a team on a long term project is that data access then becomes a piece of crucial code which lives outside of version control, it adds unnecessary complexity. In my 7 years of experience using stored procedures is great until it isn't and then it swiftly becomes horrible.
@@matez8355 No reason your stored procs (or any part of your database for that matter) are not source controlled. (If you're using Visual Studio that is, not sure about other IDEs), there is a nice project type for SQL database. You can suck in your schema and then do all your stored proc/database work in there and it will publish/generate scripts for deployment.
@@rjean99 parts of functionalities only working in visual studio is just laughable and even more laughable to be constrained to it with decisions such as this one. Make choices like these and then wonder why noone wants to work on dotnet legacy projects and why it is not used for new greenfield projects.
@@matez8355 Not had a failed project (yet) in my 30+ years of software development where I was the lead programmer. I'll let my record speak for itself.
EF has always been a dumpster fire. Dapper is near perfect, super fast, super transparent about what it's doing. It's the exact opposite of EF which is like black magic stuck in treacle.
Yes true, the marketing tricks is really disgusting. As a developer, i see the benefit of entity framework is put the stored proc logic to be able to compile by the c# compiler, where help the complexity of debugging.
![เจ็บที่ยังฮัก - ม่อน วรวิทย์ [ Official Mv ] จอนนี่มิวสิค](http://i.ytimg.com/vi/KALIGSRSjFs/mqdefault.jpg)
I'm a database professional and SQL Server specifically. I've been telling people these things for a VERY LONG TIME! When working as a consultant I pushed FTE's to get queries to run in tenths and think in CPU time and IO aspects to measure query performance. These things are only truly measurable in SSMS and against very large tables. Thanks so much for debunking so much of the bad training in EF. Personally, I use Dapper and Stored Procedures to insulate my tables from developer whimsy.
Thanks for sharing.
Thanks!
Awesome video, thank you!
1) Introduction ⭕00:00 - 3:02
2) Negative Discussions Regarding EF ⭕3:03 - 11:18
3) Why would you focus on Dapper ⭕ 11:19 - 17:36
4) Additional Perspectives on EF vs Dapper ⭕ 17:37 - 21:48
I work for a Fortune 100 company. Almost 99% of our SQL interfaces are written as stored procedures. It literally took me one afternoon to implement Dapper along with a few wrappers. We absolutely must have performance (we're talking milliseconds are a big deal) and Dapper is a no-brainer. I'm not disparaging EF. I'm just stating what works best for our shop. Also, Dapper is HIGHLY intuitive!!!! Then again, I've been doing .NET and C# for 20+ years.
Thanks for sharing.
I thought I was being super unprofessional when I just wrote SQL queries from C# code, so hearing about Dapper's existence and acceptance among pro's is very validating!
It is ideal to write your SQL in SQL.
Same having grown up with ADO and even pre ADO methods I thought EF was gonna be better and has some nice features but you lose control. However I'm not really sure what Dapper does over the pure ADO approach. Tim mentioned mapping to models... So does that mean Dapper links with the MVC approach?
Same here
I know and use both Dapper and Ef for many years and love both of them. I use Ef as core tool for the database model, migrations and basic crud operations. For the more performance sensitive operations I use Dapper.
Literally the same as me
Same as me but with XPO instead of EF. Making the switch to EF Core on new projects.
Agree, in my shop we use both, and just told my fellow devs that we need to keep both. We use EF for data model (even scaffolding) , simple retrieval and update, but if it comes to heavy performance stuffs using Dapper, and even write a high performance Store Procedure. It certainly like Tim said, it force me to write SQL and if I need to tweak, I know where to Tweak.
Completely agree. Surprisingly, this approach is new to most companies and tends to be a tough sell. IMO, you get the best of both worlds.
This is the way.
Worked both with EFCore and Dapper and compared.
EFCore is great for getting started and it absolutley has some amazing use cases. Especially with prototyping and small projects.
However, medium to larger companies have all kinds of Database stuff, from Sharding to CQRS implimentations and all kinds of custom migrations.
If you don't know how any of these things work because you have only worked with EF, and you will (Almost certainly) find yourself in a situation where you don't know the tools.
I have been there and it can be daunting.
Get comfy with EF but that is just only a jump-start on a very long journey. God bless your hearts.
Awesome video! I'm currently working on yet another project that has the problem you described. A number of years of engineers with varying experience in using EF have made the performance and maintainability poor. The unfortunate solution is several months of development time. The benefit, though, is all of the good discussion and learning for the team as we work through past mistakes. In the distant past I design this particular app using EF because I thought it would stay small scale and I thought I was only creating a simple prototype. Over the years I've learned that a prototype will become production, and production will eventually have to scale.
I agree but not everything will scale - a lot of apps are used by a small number of users and some activities will be limited. We have an app that inserts millions of records in parallelism mode (1 minute) and does spatial processing in SQL Server on those records (another 1 minute). Then we summarize the data as summarizing 5 million records on the fly to show the web page can take 0.6 seconds (shocking for not a fast server) and if you have 10 concurrent users it'll slow down the system😁 The summarized data of the 5 millions records takes 0 seconds to load.
If this happened all the time, we'd need more processing capacity than Amazon 😄
We didn't design it to scale other than to allow parallel modes instead of serializing the database processing doing 1 record after another for 5 million. We, in fact, prevent another concurrent situation and queue them that type of processing.
But good design and practices always pay off in the long run and will make it easier to scale.
Yeah, those prototypes and MVPs end up becoming production apps way too often. I agree that over-optimization at the outset isn't a good idea, but making fundamentally sound design decisions even at that early stage is incredibly important.
Many excellent points. Schema first with stored procedures so you have to think about and understand what you are doing with the data. It is always about the data. The applications only exist to serve the data creation and consumption. What are the natural relationships? What will people likely search on? End users may speak in terms of the application look, feel, and flow, but as a developer you should be envisioning how the data flows in support of their needs.
Thanks!
I used to do direct ADO in classic ASP (back when my editor was notepad). So, EF always confused me as to the "why", even though I do like the data model concept. I always wanted complete control full stack... Honestly, MVC made me feel the same. It felt like a lot of extra things that really were there to force you to do something complicated to solve an issue that was more preference than purpose.
I never knew that EF would make all the strings nvarchar(MAX), thats wild.
I don't code for a living, so I skipped a lot of that and now basically just do Blazor Server with Dapper after learning from you. It works for everything I need and makes things like routing, etc, very simple. One thing I really appreciated is how you teach as things are done. By the book is rarely how things are done, that's experience that's valuable. Thank you for that!
You are welcome. Thanks for sharing.
Sir, can you give me any recommendations as to how implement solid authentication and authorization system using Dapper? Microsoft Identity system requires EF and I’ll have to use Pomelo EF with my MySQL database. I failed to do it with Dapper because it requires migrations and it MS Identity is trying to create it’s own tables in the database.
Glad i've learned SQL first than C#, so it is more intuitive to use Dapper than EF magic. Great video, Mr Tim :)
Thank you!
Hello Tim! I just want you to know that you are saving my career as a developer. I always look for your videos before anyone else. Thank you
Happy to help!
It looks like they are adding Raw SQL for unmapped types as part of EFCore 8. This could be really useful, statement from Microsoft "Currently SQL queries must return a type in the model or a scalar type. In EF8, we plan to allow SQL queries that directly return types that are not contained in the EF model."
A lot of things I agree up on, but also this video raises some more questions for me at least.
1) When already using EF core, why not let EF core execute the SP?
2) When using dapper, how do you manage the synchronization between the entities/models and the tables? Do you do this manually and I assume with a database first focus or happens this a different way?
3) If someone really, really wants to use dapper in a project that already has EF core, you can use them both in the same project I guess? I cannot think a reason why not. But again, why would you as EF core can also execute "hand-written" queries or SP's
4) Another reason I don't like is the migration management in code first situation. When more people work on the same project and do changes to entities at the same time, commit it at (almost) the same time, one or both of them will end up having issues when they try to update the database after a pull is done.
1) You can use stored procedures with EF if you are already using EF. This video focused more on the initial decision of which ORM to choose.
2) I personally recommend SSDT to build/update your database. You still need to manually change your table structures when changes are needed, but I'm ok with that. The frustrating thing I see is this idea that your models need to match your tables. That's not usually an efficient design. If you are just mapping tables to models, you need to really evaluate if you need a relational database. I more often see a mapping between models and queries, where only the data you need is returned rather than being sloppy and returning all of the data and ignoring some of it.
3) Yes, you can add Dapper to a project that already uses EF Core. That's what Stack Overflow does in their design.
4) If you use SSDT, your database gets updated with your code deployment.
For the database part a simple repository with transaction (UoW) would do the job.
"It depends" :) I've been doing custom software for businesses since the late 80s. Started with FoxBase (yes, Base, not Pro). Eventually did a LOT of Visual FoxPro. Worked with Oracle before I ever used SQL (Uniface front-end). Did some largish systems with SQL on backend and Visual FoxPro on front-end. Have worked with large commercial ERP software. I consider myself a data guy first that happens to know a thing or two about UX and front-ends that users actually enjoy using. So I really don't want to be a DBA because it just doesn't sound challenging enough (gd&r) However, I also have different requirements and time/budget constraints. I've used ADO datasets/table adapters, XPO, EF, Dapper, etc. They all have their place. For basic CRUD front-ends, XPO/EF are amazing tools. Using XAF framework I can have a fully functioning set of screens in minutes that look amazing and perform just fine. However, I've also used Dapper when the requirements are web based, public facing with stored proc requirements, etc.
I've seen just about EVERY tool misused by poorly trained and unexperienced developers. Seen any good Access programs? Me neither, but I don't blame the tool. It's always the person. I'm sure Dapper can be abused/misused as well. My recommendation, regardless of your data access tool, learn SQL because if you don't, you'll be forced to under the gun which isn't pleasant. But ORMs, code first, etc. have their place but you still have to be able to optimize and troubleshoot - just expect it and prepare for it rather than trying to avoid it.
Hi Tim, A video about "how to write tests for your data layer when using Dapper" would be awesome! Thanks
I'm curious what you are trying to test when you test the data layer. Are you looking to verify that the queries return the data you are expecting? Are you trying to test that Dapper does what it says it will do?
@@IAmTimCorey Thanks for the reply Tim, I think there's no point in testing dapper itself because the dapper team does that already. I believe the purpose of testing the DA layer is to ensure your queries and production database don't have a conflict and will return what we expect them to return. Like in EF we can either mock the DbContext or can use the In-memory provider.
So an integration test that uses an actual database to validate that the queries work correctly. Is that correct?
@@IAmTimCorey Yes. But I've seen a lot of the teams and projects make it part of the unit testing...
Unit testing should not include interactions outside of the method, so a database should not be in the mix. That's what I'm trying to figure out. I'm not sure what value a unit test would give in this situation.
I'm glad to see an updated video on this. There have been a lot of changes since your last video on this topic
I'm glad you enjoyed it.
That's why I proudly say in interviews that I follow Tim Corey. 😊😊
Thank you!
Before Entity Framework, I worked with Visual Studio's dataset designer to create table adapters and datasets to access SQL data and procedures. Table adapters used SQL queries to create the model for you along with the queries for CRUD operations. You could also make your own queries and use them. This was using Framework of course, I haven't seen them in Core. The trade-off in using EF was loss of control. Dapper gives me that back, which is why I prefer it.
Thanks for sharing.
Ah yes, the DataSet days. Interestingly, data-binding is kind of making a comeback in scenarios like Blazor / WASM. Funny how things come full circle. :)
Good video. But as I have always maintained, both tools are inadequate in the hands of a poor developer.
The key here is knowing the tool you use and knowing it well.
I have used EF & EFcore for serval years now (I tried dapper very briefly) and
I can say Efcore has been great both for dev and production.
With EFcore I can get the best speeds in almost any if not any scenario.
Honestly I really don't know what dapper can do that efcore cannot unless you really don't know what you are doing.
Also i don't have to manage two seperate projects, an sql project and then a c# project (in order to track Db changes).
Again I have the advantage and power of intelliscense and if I need to write raw sql, I can.
Migration is awswome! Don't have to change table names in sql or database manually.
Very importantly performance and feature updates are assured in every update (Yearly update).
Because the tool is backed by one of the biggest companies in the world, quality & longevity of the framework has a high probability.
Even though you have made it clear in the video that both are great tools,
the comment section so for does not paint the same picture and
might be quite misleading, in my humble opinion, to someone who chances on this video.
Still a good video!
The problem I'm having with this whole thing is he's basically saying using Dapper forces you to also learn SQL in depth, and EF doesn't necessarily. Ok but if your job (as mine is right now) is to set up an MVP for a product I simply don't have that kind of time. So I am that "poor developer" you refer to here, I'll fully admit that, but what can I do about it? Only so many hours in a day.
I think a better solution I'd personally like to see someone (Microsoft?) do is make a video about best practices. Still after watching Tim's video here I'm not any closer to knowing what exactly it is that I'm doing wrong, other than not providing a char limit to string properties.
@@johnhershberg5915 I think it's fine to not know SQL in depth... at first. Understanding the data side is something you may not have time right now for, but you need to know it to know how things end up working. Everyone is a poor developer until they aren't. This isn't a criticism, it's just a fact of time and experience. It really is a good idea to offload the data logic to the server where the data is stored whenever possible if for nothing else for the network transmission and searching efficiencies. If you're not full stack yet, maybe find someone who is good with the data side and just sync with them. Something we don't consider often is the size of the payload and the effect on network transmission as we don't consider that a bottleneck much anymore, but it can be. If I can pull one part of data already searched and sorted, vs the entire dataset then searched and sorted programmatically in memory. It's going to be exponentially faster even if you make more small network calls with less overhead overall. Doing joins, and even things like stored procedures on the Database side just divides the processing components. Here's a thing I think he mentioned that is very important. ALL applications are data transfer applications. Thats it, its information in the form of data being moved from one place to another. In that case, the data model is probably the most important feature you can focus on at first. GUI, looks great but functionality is priority to me, even in MVP's. Although, we know that that's not what the customer wants, but what the customer needs. But yeah, correctly scoping DB fields is about as basic a "improvement" as you can get, and that starts with appropriate DB design.
I've followed a lot of your content the last couple years while learning C#, in the early days when I made a small application and felt overwhelmed by EF I found a video you made about Dapper and honestly I've never looked back. EF to me feels like such a huge bloated thing that I just don't need, I knew SQL before I started C# so for me Dapper has been perfect.
Thanks for sharing!
As someone who started they're career in a SQL heavy job, Dapper sounds like a pretty sweet tool. Being able to just write queries sounds awesome.
It really is.
To use EF correctly, you have to understand what SQL the framework translates your Linq into.
That's what it does. It translates into SQL.
Then it does the mapping like all ORM.
So if you're going to choose Dapper, so that you can learn to do SQL queries (and Store Procedures) correctly, you're sort of learning SQL in detail and Dapper and C#.
I sort of can't find the strength to write SQL statements in strings. It seems like writing an SP, but not putting it into the database. It's all a little sloppy.
I love how this answer is asked yearly. Always love the response. Keep up the great work.
Thank you!
EF because it lets me do model first and a DDD approach. That way I can focus purely on the business domain and keep the database as an abstracted concern.
Dapper if I'm in a project which is database first or the database is legacy.
Just be careful that you don't sacrifice the performance and simplicity of your application in order to follow a pattern.
I start using Dapper because of one of your previous videos. Thank you Tim for all your great work.
You are welcome.
I just love Dapper, Store Procedure in SQL is powerful as heck. Was able to resolve so many issues without having to recompile the C# project. But only set back is you will be stuck with SQL server and moving off to MySQL will require a lot of rewrites.
The question is, how often do you switch database servers in an actual project? Personally, I've changed the database layer once in 20+ years of consulting with hundreds of companies.
Great Video Tim!
I've used EF since it's inception and know full well of the issues you described. I was fortunate enough to start a greenfield project a year ago and chose to go with dapper as the requirements were very simple ( read only data access and no complicated joins). With dapper, ive managed to write a dynamic query engine that handles filtering, paging and sorting. It uses reflection to figure out what queries to write. The good thing is, it's really just a glorified string builder. Solving complicated problems with simple solutions. I use generics to make sure the type mapping is also dynamic.
At first i thought it may be overkill but having written it, it's very straightforward to work with and a lot less hassle than EF. Another good thing about thie project is I relearned a lot of sql along the way. Deferring data logic to sql is way more efficient than writing complicated business logic to retrieve and then manipulate data.
I also do agree that EF has it's merits but it really does require a deep understanding of HOW it works for it to be used efficiently. If i was to use it in future on a fresh project, i would probably go down the cqrs route and use dapper for writes and ef for reads (depending on the complexity of the data schema/business logic).
Thanks again for this video. It echos my sentiments on both technologies (i have a preference for dapper but in very specific scenarios).
Keep up the good work :)
Thanks for sharing!
mind sharing, if you have a public repo? i've been using dapper as well, and what you just said sounds interesting.
@@feitan8745 I'm planning to put it out before the end of February. I'll update this when it's available 😎👍
@@teriyakov cool man
@@teriyakovyea definitely be interesting to see
Tim Corey, thank you for taking the time to record this video, even when you seemed not to be 100% OK
You are welcome.
Right on!
This is pretty much exactly what I tell my developers. Good, solid wisdom here.
Thanks!
A really valuable discussion.
I hope you make more videos like this cuz it is helpful and saves us a lot of time efforts with design decisions.
Big love to you Tim ❤️❤️❤️
Thank you!
Nice video, I appreciate the hard work that goes into these videos. I'd love to see what you thing of some of the other tools out there.
Thanks!
I remember a statisics course in college where my professor would use the word "parsimonious" at least once a week. The definition roughly implies "sparing, frugal, or restrained". I use that as a guiding philosophy in my development. I try to use the minimum i can to get the job done, keeping in mind maintainability. This video resonates with me!
Thanks for sharing!
I think the points on this video are more sentimental than factual. I did SQL before learning C#. I am a single developer. I would spend about a week, sometimes 2, designing the DB. With EF, I reduced the entire week to mere minutes or hours.
Of course, i encountered those cases where EF wrote bad queries and i would fix it in the code or where necessary, write my own queries.
I started using dapper and i love it. Both have their place. For a complex insert statement, EF rules. Why would you want to write an insert statement for every table? Moreso when you want to insert child values at the same time? You will execute the SQL, get the inserted ID before inserting it in child tables. Thats a loop, you can avoid 😉.
You do realize you are only shifting the overhead to either C# or SQL. You only duty, sometimes it's to write code.
Use dapper for complex select queries, for all other things, use EF.
You aren't "shifting overhead to either C# or SQL", you are making C# do what SQL is best at. You aren't relying on the power of your database. I see lots of people who are super-optimistic about EF, especially at the beginning. What I wonder is how many EF installations have you had to manage in production at any type of scale? That's where the problems come in. Not development, not early testing, not even early production. It isn't until it is too late. I'm working with a non-profit right now that has less than 300 employees and less than 20,000 active customers, yet they are being crushed by EF. And unfortunately, it is so woven into their architecture by now that it cannot just be replaced. Even replacing certain calls with stored procedures isn't an option. They have exceeded the capacity of their largest servers and have now moved to the cloud so that they can scale larger. That is an absolute waste of resources because of EF.
dapper for select queries, ef core for everything else. the best of both world :)
Why? You also get the drawbacks of both solutions.
@@IAmTimCorey agree
@@IAmTimCorey select with dapper is faster while update,delete and insert is faster (code wise) in ef core
Was hoping more about Dapper vs Entity. Feel like I just visited the coders principles office. I still consider these as middleware and allowing intelisense easy buttons for field names. Agree Entity can create walls you can't get around. Being a Full Stack developer, you need to understand SQL. C# filtering or LINQ can really be abused like you said. Good points.
Thanks!
Very diplomatic. Using EF only lets you get expert in EF. Using dapper only forces you to become at least competent in the SQL backend. I think the latter is the far superior starting point, if not necessarily where you stay.
Thanks for sharing.
my man, thanks for sharing your toughts on those tools :D
You are welcome.
Working on a Blazor Server project, and so far from what I've been experimenting with, user authentication is a different beast since BS isn't a request cookie based system. I'm finding that the authention and authorization system almost has to be done using the authorize/role attributes setup, which seems to be pretty tight with EF. So, as much as I'd like to just use Dapper, would the best approach be EF for user auth and Dapper for everything else? That's what I'm considering, but trying to work out the logistics of splitting things while having them play well together
I have absolutely the same question sir! I found that examples of custom authentication and authorization systems using Dapper are very poor.
Dapper + SQL Stored Procedures for sure!
Thanks for sharing.
It's a preference thing. But using dapper and writing your own sql is just better since you know exactly what your code is suppose to do.
I use both of them evens at the same time in the same project! depend on situation. Dapper do a better job in some situation, but some other case EF is the better choice. For eg, when you need the power of IQueryable to compose your query programaticly without long "if" mixing with string concatenation, then there is no better choice than EF.
Dapper is literally just EF with less features. It benchmarks only a few milliseconds faster on raw sql execution. We use the term "micro-orm" because it solves only one of the many problems with database development. What about DevOps? EF has database migrations, but with Dapper you'll likely end up changing your database manually in production. What about code simplicity? Maintaining SQL is time and money. Entity Framework has navigation properties while Dapper has you writing a complicated repository class to manage relationships or creating many models to represent the same data. EF gives you table and entity splitting so you decide where you want the code and database to logically converge. EF has simple change tracking while Dapper has you constantly mapping objects and passing transactions around to make things work. EF also has OData, which can retrieve every permutation of your data with a single API endpoint. Even if a new C# developer doesn't know SQL the argument could be made they should learn LINQ first since it's applicable to other C# tasks whereas SQL is not. This person probably shouldn't be writing data access code in either case, though.
If you use SSDT to develop your database, you can deploy it just like your code (and put it in source control). As for EF doing mapping better, I would disagree. With Dapper, you make the best choice for the situation (one query, multiple queries, etc.) to get the data. With EF, it makes the call to return more data than you need. For instance, if you had a table with ten people in it, and a table with orders in it, and each person had ten orders associated with their account, and you wanted to get a list of people with their orders, EF would return 100 records. That sounds right, but it would return each person 10 times (one per associated record). That's a LOT of extra data transmitted across the wire. As an EF user, you never see that data. You see 10 models, each with 10 sub-models for orders each. Unless you actually look at the query, you don't see the wasteful transmission. With Dapper, you would do two queries and then map the data over. That's a simple (and small) example. The "normal" EF use case is joining multiple tables together (it is a relational database after all), and transmitting hundreds or thousands of times the amount of data across the wire compared to what you actually need.
@@IAmTimCorey EF has a feature called split queries for that exact scenario. You can also use projections, table/entity splitting, and raw sql to shape things differently
I personally prefer more Dapper than EF. In my case it's simply a matter of preference than anything else. I simply have the sense of more control over what is going on in my application with Dapper than with EF. Probably, I need to dive deeper in EF to know more about it's internal behaviour in order to have the same feeling of security I have with Dapper, however the latter is so simple and so powerful that I simply do not miss EF in any of the applications I build. To be frank I tend to use EF just for very simple applications or just for prototyping something quickly as a proof of concept or some sort of thing. But, as you said... both tools are great and have their place.
Thanks for sharing.
I kinda disagree with the last point: "You are too invested in EF that you can't go back". It all depends on how well engineered your project is. Not only EF, but if your architecture is highly coupled with a specific ORM or any framework for that matter, then yes, it will be really hard to replace that component in your system if not impossible or not viable at all. But if you followed the best practices (repository pattern, abstraction/interfaces, SOLID, etc) and you have a data access layer decoupled from any specific library or framework implementation, you can move things around with less or minimal effort. "It all depends on the person using the tool", I completely agree with that and moreover, I'd say "It all depends on the person designing the system".
I get the idea, but the reality isn't that clean. First, clean layers almost always break down in "real" applications over time. Second, even if the layers are entirely clean, you will have a LOT of queries to replicate into SQL statements. I'm working with a small-ish non-profit right now that would need to take probably 3-6 months to just replace their queries with raw SQL statements in order to move away from EF. That's just not viable.
@ghost mall - I'm not blaming the tool. I'm saying that it is being used improperly. When relationships get complex enough, you really need to write custom queries instead of using EF-generated queries. The problem is that most people don't do that. A great tool can be a real problem when it is used improperly.
Hi Tim, I agree with all of the issues you mentioned re EF. Unlike you, however, my dislike of EF (and ORM generally) does rise to the level of hate. My consulting experience is similar to yours in that I've been hired on a few occasions to fix some ORM disasters, and as you mentioned, this is not that easy to do because that code is typically deeply ingrained. Having said that, I think Dapper is a good tool. I also agree with your viewpoint that what really matters is the data. This point often seems lost in some development shops.
Thanks for sharing!
That's exactly been my experience with EF.
Luckily, it seems that everything falls apart fairly quickly you don't know exactly what your model builder does,
provided you integrate and test early.
Thanks for sharing.
Great video Tim. I've only been a developer for 6 months and have learnt everything I know from your courses. I've almost just finished converting an app from EF to Dapper. I also prefer Dapper and I like that I'm learning SQL at the same time. I still have to learn EF to understand how Identity works but for everything else it's Dapper. One thing I'm wondering - is it better to use stored procedures or write the queries in the classes?
I highly recommend stored procedures. That way, you can optimize them in SQL when you notice a problem in SQL. You don't need to find the T-SQL in your C# source code to optimize it when you find a problem in SQL.
I've defaulted to using Dapper for queries and EF for CUD due to easier maintainability. Stored procedures are technically faster, but they have versioning issues since you might need to deploy the stored procedure and your app at the same time vs just having the SQL directly in the app... but the security comment is also true. Everything is always a combination of pros and cons.
Thanks for sharing!
Hi Tim .. you already answered the question clearly when u said : c# should not do SQL job ...also a sentence u said in your sit about dapper : It is beginner-friendly... does this means dapper is only for beginners and not suitable for large apps ? .. thanks
Nope, Dapper is absolutely a tool for everyone. It is incredibly performant and has been proven in high-throughput situations. In fact, larger apps will more often benefit from Dapper over EF because of the performance gains.
The best thing about ef in my opinion is the ease of writing unit tests for your queries with the in memory database.
I'm glad you like it.
EF does a great job for startups doing their MVP's. Yes, there you can accept a slower SQL code. If they prove their product profitable, then they will have the resources to refactor the code. You shouldn't strive for the best code in a MVP, but really to make it as quick as possible. There is where EF can help, especially considering that startups might have a limited skill team of programmers (sometimes a 'team' of a single person).
The problem I see with this is that MVPs tend to become the "real" application. So the very foundation of your application is knowingly less performant. I know you don't want to prematurely optimize, but there is also something to building the foundation properly. Otherwise, you work yourself into a corner. You won't have time to go back and fix it later, so you just live with the bad design decision.
EF gets faster with time, when executing complex queries.
@Feitan - That statement confuses me. I've not seen that to be the case. What do you mean EF gets faster? Complex queries are the biggest issue with EF, where it gets too much information. For instance, in a many-to-many join, it just returns lots of duplicate records and then deletes them on the C# side. Things like that are inefficient, but that inefficiency doesn't really show up until you have a "real" production database, where relationships are larger and the number of records is beyond what you normally see in a development database.
@@IAmTimCorey i've done some heavy lifting with both and sometimes EF was even or even on top of Dapper after some repetitive tasks, due to the caching maybe. And yes, it was in production. Not trying to say EF is faster tho', as many other experienced people have proven it to be false.
@IAmTimCorey, one question, how do you handle migrations with Dapper? Do you use another tool/framework for that?
If you use SSDT, you can put your project in Visual Studio, track the changes in source control, deploy the changes with your application, and you can do things like renaming a column and having all other entities that use that column name (in the SSDT project) rename as well. Here is a video with more info: th-cam.com/video/ijDcHGxyqE4/w-d-xo.htmlsi=AlVFk0-p3PUR7ekK
Great video, I know you do a lot of dapper, but when people go for a job to work on existing software, and that software uses EF, then they will find it more difficult, It would be great if you did lessons on how to use EF better.
I get that. The problem is that since I've stayed away from it for so long, I can't do it justice to teach it at any level of depth. At least not at this point. I don't teach something unless I know it really well. Otherwise, I can lead people down the wrong path. That's why I have not taught EF in more depth. It may be something I take on in the future, but it would be a lot of work.
@@IAmTimCorey I completely understand where you're coming from and I personally prefer dapper myself, hoping never to use EF, but alas, I've just moved teams and found myself working with EF and the poor implementation the other developers have done with it. Thanks anyway. Much appreciate your thoughts on it.
@@IAmTimCorey how recently? A lot of bad things I hear about EF is from developers who barely have used it since core. EF Core 6/7 is very different from EF 6.
Sure, if you dont know SQL you probably will make a mess with EF and how you create relations. But that's the same if you manually create tables and have no idea what you are doing. You should learn fundamentals of SQL regardless, but I don't think you need to be an expert in it, unless you want to work as a DBA. Also lets face it, majority of developers will work with small-medium sized apps and EF is fine for that.
I've been at places where its the other way around. Legacy databases with several hundreds stored procedures calling into other stored procedures, no one dares touching the database because the DBAs working on it quit a decade ago. So you keep extending the app but building wierd work arounds to support the existing database.
Or code using Dapper with SQL in code where a column name change would require manual updates in 30 different handwritten strings and you hope you didn't miss or misspelled any.
I'm very comfortable in SQL, but I've always preferred EF because it allows me to coordinate complex business logic across multiple entities using a single database transaction. I can break these complex operations into small, maintainable chunks of C# code, where each chunk is only aware of its small part of the domain. To do this in Dapper, it seems like you would either have a giant and complicated stored procedure, or several small procedures that requires several calls to the database. Is it actually more efficient to do it that way?! And I've always been uncomfortable putting business logic in stored procedures. I'd love to know how you manage this with Dapper
Well, saying that dapper forces you to learn SQL is for me saying that we shouldn't code in C# either and just learn C to make our own hash tables. I look at C# as an abstraction, like EFCore is for SQL - which leads me to the point; sometimes you just want to code fun stuff.
But if the intention is to code real enterprise and performant applications, it should be done in C.
For me when using sql stored procedures and functions for heavy sql queries i get better performance than dapper, and when i do not need performance heavy queries i use EF, which makes it much faster than creating my own sql queries
As always, as "easier" a tool is, as more complex scenarios it tries to handle automaticly, as less you have control over it - or you need to dig in deep, to modify things. This is EF.
Without EF, you have to do more yourself - write more code - but also: you are allowed and able to write more code.
In Dapper you have to take care about upgrading database structure yourself, you also have to modify your code if you change database from T-SQL to mySQL, or SQLite or so.
But in EF you have a 1:1 scenario from client to server. Adding a new column to one table, requires to update all EF database clients, cause it detects "structure changed - incompatible -stop working" otherwise. Although ignoring additional columns is a simpe thing in SQL. So it's really many pro's and cons.
One solution for using Dapper with SQL is to use SSDT. That allows for the database structure to be updated using CI/CD rather than manually.
Is using EF core with the DB first approach difficult to maintain the context? I've always used code first and used migrations.
I am a junior developer and I adopt your philosophy for dapper and I am really using it. But now I will get in a new position at my company and they are using EF. So I am kind of traped.
Yep, sometimes you have to use what you are given.
Not so long ago I used to think that ORM's were the best way to map between the database and domain model... now I think that the complexity that EF brings might not be justified. I rather just write the SQL and the object mapping code, which always feels like magic with EF, especially with Value Objects. Patterns like Unit of Work are also a bit easier to reason about since the code ends up being more explicit and straight-forward.
This is what I’m doing as well, but it feels unwieldy after a class grows in size. Wondering if I’m not doing it efficiently. Do you have any examples or articles that explain how to do this efficiently?
I used to write my own ADO code because it felt like the most efficient way to write data access code, but Dapper is so small and performant that it is practically the same as direct ADO code, plus it gives me the added performance benefit of having my data mapped correctly to strongly-typed properties in my model. That's a big performance gain.
Appreciate the reply Tim, sounds like dapper is exactly what I’m looking for then! Any videos you recommend for getting started? Should I start with your “Data Access with Dapper and SQL” video? Or any others you suggest?
th-cam.com/video/dwMFg6uxQ0I/w-d-xo.html&feature=shares
This is the video where I introduce Dapper: th-cam.com/video/Et2khGnrIqc/w-d-xo.html
Then here is an Advanced Dapper video: th-cam.com/video/eKkh5Xm0OlU/w-d-xo.html
After that, the video you listed would be a good next step.
Awesome, sometimes just knowing where to start is half the battle! Thanks again!
Thanks!
Thank you!
Weighing in as someone well-versed in SQL/T-SQL, and spent the last couple years using EF Core. While EF Core has some great features/improvements in .NET 5.0 - 7.0 era, it just hasn't been fruitful for me in the fulltime/Enterprise class environment. For hobby and small projects, I love EF Core. In the Enterprise world, it's like flying too close to the sun. It adds a lot of complication, especially in defining complex relationships. Doesn't mean I couldn't mix my strategies, but I find it best (and most maintainable) to stick with one consistent strategy. I think EF Core is wonderful, but for new projects in the Enterprise settings, I can't recommend it. You should still learn and practice it though.
It's sort of the long way of saying, I spent a couple years trying to disagree with Tim, only to come around to the same conclusion. This video is very honest and graceful, and I've come around to the same understanding. Damn you Tim.
You made me laugh with your follow-up comment. Thanks for sharing.
I had seen one of your earlier videos on the topic. My lingering question is whether these limitations for EF are unavoidable by definition (and every ORM has the same problem), or whether EF could release a version in a year's time that optimises their way around it.
Mostly unavoidable. There is a reason why we don't have generic applications that just fit every scenario. Trying to figure out how to write a performant ORM that works for everyone is practically impossible. EF Core 8 has made some big strides, but there is still a lot that is on the developer to do. You need to customize it to be precise enough for your needs, and even then you will find areas where it just makes a mess.
Some SQL is nice know, because you inevitably run into a limitation that entity has. Its not a matter of if you will ever have to dive directly in the database and debug data, its matter of when. Be prepared.
Agreed. It is also important to be able to benchmark your queries and really understand how to improve their performance, even if EF Core wrote them.
What do you recommend for sql source control then , since EF core would handle the versioning etc with migrations ? I’m a fan of dapper as well , was wondering what tools are out there or patterns for managing db schema changes ?
Use SSDT to create your database (or manage your existing database) and put it into source control. This also allows you to update your database as part of your CI/CD process: th-cam.com/video/ijDcHGxyqE4/w-d-xo.htmlsi=iBQMtFUA_zT6Rkqm
I moved to tryinfg Dapper after watching one of your videos as a course I was doing used EF but coming from a point of not knowing much SQL it was hard to understand how it fully worked and when something didnt work it was a struggle at time to work it out.
Tried dapper and as you say forcing me to use SQL maybe learnt a lot on how things work and I found myself finding better ways to do querys and structuring my models etc.
Excellent!
I totally agree with this video. I am starting on a new project and pondered using EF but as we may have junior developers working on the codebase, EF will add too much to their cognitive load. Besides in the past I got fed up with trying to figure out what SQL EF is generating when a query doesn't work. Btw - have you tried Repo DB?
Thanks for sharing! As for Repo DB, I have seen it but not used it.
Every word he says is worth gold!
Thank you!
Hey, Tim! Thanks for the video. I haven't noticed, if you mentioned about kind of built-in migrations stuff in EF Core in comparison with a need to use another library like FluentMigrator with Dapper. What are your thoughts on that?
I recommend SSDT for SQL development and migrations.
Oh no, I was meaning schema changes and, thus, schemas/models migration, not the actual data
@@RussianBananan SSDT contains SQL Server database project that allows you to manage your full schema in visual studio using publish and schema compare to produce scripts for schema updates
Hello Tim, It's a great video. I have a question. what about if you use EFcore with stored procedure(select, insert, update) insteat of direct queries?. I really love stored procedure and I'm testing all with EFCore.
When you get to that point, I wonder what value EF is actually adding to your process. It would seem like a lot of overhead for what Dapper does for you with much less complexity.
@@IAmTimCorey Thanks for you replay, I will investigate and test more in deep with dapper, I'll watch your two videos(Dapper and Best Practice with EF).
Thank you Tim
You are welcome.
Hi, Tim! What do you think about use both tools. For instance, Dapper to reports and EF for CRUD?
Why? It seems like you get the drawbacks of each when you do that and I don't see the benefit.
Hey Tim, Love your channel and this video. I agree with your perspective about Dapper. Question for you or anyone else though, is Dapper being worked on or is it dead? Seems like it hasn't been updated in many years. I'm asking because I'm considering using it for a new project.
Absolutely. The "issue" is that there isn't much to change. That's the beauty of Dapper. It is really simple and it "just works". There is talk of adding AOT to Dapper to make it even faster, but I'm not sure when that will happen. But yes, Dapper is a currently-maintained project.
EF is great if you have perfectly formed DB but for everything else we use Dapper.
I'm not sure I've ever seen a perfectly formed database (outside of a demo).
@@IAmTimCorey Me neither, but I have heard the legends.
9:00 - Apparently, I visibly winced at this point enough that my colleague asked if I was okay. I think anyone who is okay with creating an nvarchar(max) coming for a simple string value should be suspended from development until they go through remedial education. Holy hell....
lol yeah, there are some big gotchas that people miss with the defaults of EF.
Co-signed. I would NEVER want to NOT know SQL intimately. EF is the scary kind of magic.
Thanks for sharing.
Hi Tim, Awesome video, I first learnt about Dapper from your Course "Data Access", but I have a question, is it possible to use Dapper with MS Identity?
Yes, you can, although I don't simply because I don't like messing with authentication/authorization code to that level. It makes me responsible for security flaws in my code to a much greater extent.
@ghost mall Thanks
@@IAmTimCorey Thanks Tim
I started with EF and when I saw Dapper I never considered using EF again. 100% of my access with the db is via sprocs.
Thanks for sharing!
I just started using EF, it already looks better than every ORM i used before.
I don't think that "generates bad queries" is even an argument.
99% of the queries are great, you can see the sql in the console.
If they're not great you can write raw sql if you want. It's not like you're force to use a tool.
The main points for me for an ORM, i want to be able to refactor my database often and i don't want to write joins.
I want strong typed stuff everywhere in my codebase. I want to reuse code easily.
There is a lot to unpack here.
1. Yes, EF is a great ORM.
2. "it generates bad queries" is absolutely an argument. A lot of times that is because of poor usage by the developer, but not always. When you do a join in EF between a Person table and an Order table, if each person has 10 orders, EF will return each person 10 times (one per order). So if you have 100 people, it would return 1,000 records including 900 duplicate Person records. Then if every order has 10 items in it and you want to return those as well, the result will be that you get back 10,000 records with 9,900 duplicate Person records and 9,000 duplicate Order records. EF will then eliminate the duplicates on the client side and display them as nice, neat objects. However, you just transferred all of that extra information across the network. That is not a good query (but it is the best solution EF can do unless you force it to call multiple queries).
3. "99% of the queries are great" - I just pointed out that no, they aren't. Looking at a query in the console doesn't actually tell you anything. You need to test it on SQL and see what it returns and how it performs. You can't see the execution plan in the console. You can't see if it is properly using indexes in the console. Anyone who writes a SQL statement and thinks it is good by looking at it hasn't learned enough about how SQL works. Testing it is the only way to properly evaluate it (and testing it against near-production data, since the data can change whether the query is performant or not).
4. "I want to be able to refactor my database often" - That is a terrible idea. That's not how databases work. You need to have a solid plan going into building your database and once it is built, you need to be very careful how you change it. Trying to do major refactoring on a database once it is in production is a BAD idea. Even doing it in development is a bad idea, since that should have a direct impact on your code as well. That's like saying that you want to change the structure and layout of your house's foundation as you build it. Your data is the foundation of your application. Redoing how it is structured will fundamentally change everything about your application.
5. "I don't want to write joins" - You are working with a *relational* database. The whole point is joins. If you don't want to do joins, you probably want to look at a NoSQL solution instead. Trying to avoid joins by using EF is not the way.
6. "I want strong typed stuff everywhere in my codebase" - That's what C# is - a strongly-typed language. I'm not sure what you think you are avoiding here.
I would encourage you to dive a LOT deeper into EF and how it works before you use it in production. Get some more practice in how it works and in testing the results in a real-world scenario (not production, though). Here is a video I did on some best practices to consider when using EF: th-cam.com/video/qkJ9keBmQWo/w-d-xo.htmlsi=omflTeOH_5ed_afF
@@IAmTimCorey "bad queries", ok i just tested this and see what you mean.
But how to you solve this? "AsSplitQuery()" seems to work. (5-10% faster in my tests)
But thats an advanced feature from EF. (that you don't get with dapper)
If i would write it manually i would write one big query
"select * from customers left join orders" exactly how EF does it.
With "great queries" i mean it doesnt do "stupid stuff" like other ORMs.
It can handle child.FirstOrDefault() pretty well and also childCollection.ToList().
I don't care about 10% faster, i just don't want it to generate n+1 queries or something like that.
I don't want to write joins manually, its tedious.
I strongly have to disagree with "You need to have a solid plan going into building your database". Thats not how reality works.
No project i've EVER worked on had a perfect database design.
And when you take over mangled projects then everything is a mess.
i definitely want to change my database all the time.
But arguing that topic strongly depends on the environment.
Just be careful if you don't allow your devs to change annoying (database) systems, they might eventually get annoyed working at the project.
Strong typed LinQ, not raw sql. But i'm not familiar enough with dapper to compare them.
I check out your video, thanks.
@@IAmTimCorey Another great point is that writing raw sql is very prone to errors.
I never had a problem with ORMs.
But when you write raw sql you constantly have to check if you did all joins correctly and not suddenly have 10x records or just 1/3 because you messed up a join.
Raw SQL is even dangerous sometimes, ever forgot a where clause in an update statement? :D
I think the options to manually pimp queries is great, but we also have that with an ORM.
Do you really want to write joins between 10 tables manually? Do you really never want to refactor the database. Oh the column name was "name" good luck refactoring that in 10000 queries with raw sql where every table has a column called name.
Just a couple of things here that I'll touch on. First, you asked how you would do that join correctly. The answer is to make two separate calls to SQL. That is so much more efficient than trying to flatten objects into rows. If you have multiple SQL calls, you can then join the data in C# using LINQ very efficiently (let SQL do what it does best and let C# do what it does best). By the way, Dapper puts your data into C# objects too, so you can use LINQ, etc. just like you can with EF.
When you are building your application in the first place, you might make significant database changes (although, that is still VERY expensive). However, once your application is running, that's a bad idea. You are approaching your database like it is only used by your one application. You don't do reporting? You don't have a data warehouse? You don't have any other application that needs your data? If you do, you break all of those so your application can be better.
If you struggle writing good SQL, you are definitely going to struggle writing good EF queries.
Why is Dapper different than pure/old ADO direct approach? Something to do with mapping to model classes?
Yes. It uses ADO under the covers and then it maps the return values to model classes. It does so incredibly efficiently, so that the performance including the mapping is not much more than just straight ADO calls.
@@IAmTimCorey thank you, for both replies. Much appreciated.
If you have big datas "dapper" better or working with big datas. Example (millions) but you just working little a project or application can use ef core. So, you should determine it according to the needs of the project.
There's a balance here. I'm doing some consulting work for a company that uses an open-source data system that is built on EF (unfortunately the .NET Framework version). It is designed for small non-profit organizations, so they felt that it "didn't matter". The non-profit I'm consulting with has less than 200 employees, yet their database has millions of records. So small is relative. Also, even though other companies that use the software have smaller databases, they still have performance issues because of EF configuration that they cannot get around easily. It would take a major rewrite to change how the system was structured. It isn't as easy as replacing a bad query with a stored procedure. EF was abused, but it "worked" at smaller scales (basically, they didn't notice enough of a performance hit), so now they are dependent on how EF does things. The "solution" is to move the application to Azure so that they can scale up the application (they've exceeded what a reasonably-priced server can do with tons of RAM and SSDs).
I used Dapper before, but I'm currently switching to EFCore, because I miss database version control. If you're working code first in EFCore, that's all being taken care of. I don't think it's a perfect solution. Do you have any suggestions for database version control when using Dapper or when working database first?
You're trading simplicity, control, having a clue what's going on and blazing speed for version control?
To each their own I guess.
@@DigitalNomadOnFIRE No, I'm looking for a solution to decrease downtime for a company with a very complex database and maintained by 20+ developers. There are more challenges than just database, but my focus is database management and version control to decrease downtime and making it easier to maintain. The project I am talking about is a prototype project, but it's helping me make choices
@DannyM - Check out SSDT. It will allow you to add your database to source control, you can build it in Visual Studio, and you can deploy it with your source code. Here is a video on how to use it: th-cam.com/video/ijDcHGxyqE4/w-d-xo.html
@@IAmTimCorey yeah and youse dacpac deployment with sqlpackage manager
@@IAmTimCorey I still use SSDT even if I'm doing ORM with code first. I keep them in sync and because I have multiple projects in my solution that don't all use ORM so there are stored procs, views, etc. and then get the added benefit of your database schema being in source control. I've also found I like writing T-SQL in Visual Studio better than SSMS.
One thing I would contend is the 'debugging' is better eith Dapper/Stored Proc approach. It can be frustrating to find errors in SPs when called from your middleware code... At least doing it all in C# gives you access to sn dxellent debugger. I prefer SQL SP approach but its not great in thst area.
I agree that having two systems means debugging gets harder. However, there are some things to help. First, the SQL logs will be a LOT clearer and easier to search because you can search for the stored procedure's name rather than trying to figure out what the generated code will look like. That works the other way as well, where if you find something in the logs, it is easy to search your code for the offending bit.
Second, your stored procedures should hopefully be rather simple. If you are putting full business logic in your procedures (beyond basic safety checks), you probably want to re-evaluate your planning. If they are simple, there is no debugging needed on the SQL side. If they are more complex, I would recommend unit testing your procedures so that they are rock-solid.
@@IAmTimCorey thanks Tim... not least for deciphering my awful typos!! I think that is perhaps where I have come unstuck. I used SQL long before C# or even VB so tended to put a lot of complex logic in the SPs.
After being stuck in my ways, I'm taking a step back and almost going through a new learning curve on this... in fact just watching your other Dapper video now and I wasn't even aware of the SQL project type in Visual Studio!! That alone made the video worthwhile before I even get into Dapper.a
I'm glad it was helpful. Here is a full video on SSDT: th-cam.com/video/ijDcHGxyqE4/w-d-xo.html
@@IAmTimCorey smashing, thank you!
You can use raw sql in EF. With some limitations, but nonetheless, it is good to know.
For sure. You can also call stored procedures. Those options are really helpful when you need to optimize EF.
@IAmTimCorey I am doing it a lot, especially when you have some structures like tree nodes and you need to build a full tree or some other "unusual" tasks like that.
So on point 👌
Thanks!
I'm using EF core for code first...but can Dapper be used in parallel for all my CRUD routines?
Yes, it can. The question is, what is EF Core giving you then? Is it just database creation? If so, why not use SSDT: th-cam.com/video/ijDcHGxyqE4/w-d-xo.html
@@IAmTimCorey Isn't SSDT SqlServer specific? How about other databases?
i mean map FromRawSql is an option in EF in case you need speed with a complex query or stored procedure
Sure, but at some point, you lose enough of the benefits of EF that you start to wonder if the complexity it adds is still less than the value it provides. Most queries that EF does should probably be stored procedures. The way EF gets joined data is inefficient. But then if you try to use a stored procedure, the best option would actually be to have it return two result sets (or run two stored procedures). But EF isn't set up to do that. So now you have to think about dropping out to Dapper. Now you've added more complexity, bypassed more of EF, and in the end aren't getting enough benefits to justify the complexity.
Dapper integration testing is missing the InMemory database option that EF core uses. That's the only downside that I've experienced
How about Dapper and support for spatial data (geometry for example Lines and Ploygons) in SQL Server and PostgreSQL with PostGIS
What about NHibrenate? Can it be evealuated too?
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
It's easier to be a mess with EF, just has a table that has self reference (ex. Some product is related to another that is related to the first) and try to get all information just like you said 😂
But still love EF the same
Yeah, making a mess with EF is definitely easy to do.
If IQueryable didnt inherit IEnumerable, EF would be a lot better I think. Its just such a misleading abstraction to reuse an in memory collection API and apply it to database tables. I wish there was an ORM that did something like Dapper, but had an API that mapped more directly the SQL API.
Using tools wrong is not exclusive to EF.
At my old workplace they used raw sql everywhere in their webshop.
The product page takes 10 seconds to load, for one product.
Yes, every tool can be used incorrectly. That's obvious. But EF is known for being abused. As for your old workplace, the problem doesn't sound like the tools they chose. It definitely wasn't because they chose to write raw SQL. It sounds like they either wrote bad SQL or that they had other issues. A tool doesn't fix bad SQL or design issues.
Thanks for your words, could you make a video of dapper and webapi, with heavy artillery, such as layers, dependency injection, repositories, exception handling, for production
I already have: th-cam.com/video/Xtt6mS0p2_c/w-d-xo.html
@@IAmTimCorey thanks for your contribution
Im probably missing the point of dapper but is it just to call stored procedures? On all of my previous jobs and places i've interviewed, told me to stay away from store procedures bc of the inability to debug once youre stepping through the code. Why should you go to SQL if youre debugging? My current job relies heavily on Stored procedures which i find really annoying to debug. Specially if they have hundreds of them with multiple joins. I agree with having the proper structure and if you write the proper repo then things are much better in C#.
Can Dapper also do things like migrations/scaffolding?
Goint to sql does not necessarily mean writing procedures. You can write sql queries in your c# project and run them with dapper
Dapper is doing a smaller version of what EF does. In fact, it uses the same underlying ADO.NET to make the calls to SQL. EF writes the queries and then executes them on SQL. Dapper asks you to provide the queries and then it executes them. You can use stored procedures (preferred) or you can write the SQL in your C# code and have Dapper execute it. The reason that stored procedures are preferred is because you get the benefits of having your SQL "stuff" in SQL. If you want to optimize a query, you can do it in SQL using your DBA. If your DBA wants to optimize SQL that is in your C# code, they have to give a developer the SQL and have them find out where it is and then make the changes. Even worse, if you are using EF, that optimization might not even be possible.
I've heard the "don't do debugging in SQL" argument before, but it doesn't really hold water. Do SQL tasks in SQL and do C# tasks in C#. If your SQL call is incorrect, fix it in SQL. That's the way to properly optimize your system. Doing too much business logic is usually a bad idea in SQL, but so is "preventing" that by putting your SQL code fully inside of your C# code.
Agree with a lot of the points you mention, but stored procedures are a big no.
They are a classic consultants solution. You come in, write the equivalent of method that was slow in code as a procedure, performance improves (obviously) you collect your check and goodbye.
However you have now created a massive complexity for maintenance when something needs to be added or changed in that data fetching or data writing operation, as you essentially moved the business logic completely out of the codebase.
CQRS pattern with well written Dapper/Entity code is the way to go for both the performance and the longterm maintainability.
And longterm maintainability is something a lot of "consultants" never pay attention to, maybe even intentionally as they can get called back in 6 months to collect another check.
I think you are mixing two things together - stored procedures and business logic. Those are two separate topics. Yes, you can put business logic in stored procedures and that CAN be a bad thing (not always). However, you can also use stored procedures to house just your data access calls. For instance, if you have the query "select FirstName, LastName from Person" and you put that in a stored procedure, is that storing business logic? Nope. You could argue that the query should be next to the code so they can change together, but that's not a great argument. Your code is never together anyway. You separate it out.
As for the benefits of stored procedures, there are many. First, it puts your database calls in your database. Second, it makes debugging failed calls much easier (you get a name to track down rather than a query signature). Third, they force you to use parameters, which increases your overall security. Fourth, and one that most people sleep on, is that you can lock down your data access permissions to only calling stored procedures. That basically eliminates the possibility of SQL injection.
@@IAmTimCorey in the video you have mentioned using stored procedures as a solution when a very important and often used query which has been poorly written.
Unless there is a decent amount of business logic included in how the data is stored and queried for I don't see a way that a way for it to become a performance chokepoint.
I can see your point of completely using stored procedures for data access as something that would be very high performance if done right, however I believe using CQRS pattern in combination with dapper is the best of both worlds.
Big drawback of using stored procedures when you are working in a team on a long term project is that data access then becomes a piece of crucial code which lives outside of version control, it adds unnecessary complexity.
In my 7 years of experience using stored procedures is great until it isn't and then it swiftly becomes horrible.
@@matez8355 No reason your stored procs (or any part of your database for that matter) are not source controlled. (If you're using Visual Studio that is, not sure about other IDEs), there is a nice project type for SQL database. You can suck in your schema and then do all your stored proc/database work in there and it will publish/generate scripts for deployment.
@@rjean99 parts of functionalities only working in visual studio is just laughable and even more laughable to be constrained to it with decisions such as this one. Make choices like these and then wonder why noone wants to work on dotnet legacy projects and why it is not used for new greenfield projects.
@@matez8355 Not had a failed project (yet) in my 30+ years of software development where I was the lead programmer. I'll let my record speak for itself.
awesome video
Thank you!
❤ Again!
Thanks!
EF has always been a dumpster fire.
Dapper is near perfect, super fast, super transparent about what it's doing. It's the exact opposite of EF which is like black magic stuck in treacle.
I really like the simplicity of Dapper.
Use EF until it requires Dapper as the creator's used case!
The problem with this is that when a situation requires Dapper, it is almost always too late to switch.
Yes true, the marketing tricks is really disgusting. As a developer, i see the benefit of entity framework is put the stored proc logic to be able to compile by the c# compiler, where help the complexity of debugging.