Should I use Dapper instead of Entity Framework?
I was directed to this post Should I Use Entity Framework? @Tim Corey by a client who was having trouble trying to adapt some EF and OData code examples to Dapper. The problem is that Dapper and EF are designed to solve different problems. While dapper makes it easier and safer write and execute raw SQL queries, EF abstracts SQL away altogether, but still gives you very similar features to Dapper if you really need them.
I'm going to state my position straight up, NO, in almost all cases EF Core 6 or later will be a much better overall choice than Dapper. The performance benefits of Dapper have actually been surpassed by EF, removing the primary argument for considering Dapper at all.
My response to Tim Corey's post was marked as spam, but I want to share the opposing view that was neglected that might be important for you to consider if your team is faced with making a similar decision.
There are some scenarios where EF or a proper ORM can be problematic to a dev team, usually this is someone new to programming who has just learned SQL and then transitions to C#. Lack of knowledge in a toolset is not generally the best reason to discard it unless you are fluent in an alternative. We should be clear here, Dapper is NOT an alternative to EF, Dapper is an alternative to using string interpolation (or concatenation) to build raw SQL queries, and not really much more.
The referenced article lists a set of criteria to consider before starting with EF, Lets consider the same criteria from the position of a developer who does not have an extremely strong DBA background. For the average application programmer, raw SQL can be a lot more daunting, error prone and detrimental to application performance than the same developer using EF.
Criteria for using Dapper:
- Does your team understand how to use Dapper well?
- Are they fluent in SQL and understand the reasons to avoid
SELECT *and to make sure theWHEREclause is properly set?- Do they understand how the names and types of the properties in the specified columns map to the type that is specified as the response to the Dapper query?
- Does your team know how to diagnose issues with Dapper code?
- Do they have access to tools to test and validate raw SQL queries?
- Do they understand how to modify raw SQL to use interpolation safely?
- Does your team know how to diagnose under-performing Dapper queries and fix them?
- Do you understand SQL and the application of the business logic against the SQL schema.
- If not, maybe you should be using EF so that the architects can encapsulate common business expressions in C# according to DRY principals instead of having common SQL expressions stamped throughout the codebase.
- Does your team understand the code that Dapper wrote for you well enough to know what it is doing?
- Do you all know how to read SQL and how to debug it?
- The developers who are tasked with fixing a runtime issue (that is more likely to occur in Dapper due to lack of compiler type checking) may not have written the eroneous SQL or object mapping.
- Does your team know how to protect the Dapper credentials on client machines? > - This is the same in EF, so not a relevant criteria to use for comparison...
Here's the catch, with EF you can execute raw SQL too, and pretty much have always been able to. Dapper vs EF is often a circular argument, most of the points raised in support of Dapper are considered anti-patterns by the EF/ORM community. There are some similarities to the comparison of C++ to C#, C#/EF are high level implementations that come with some performance and configuration compromises to reduce effort and maintenance. C++ and Dapper offer lower level implementations that require you to assume a lot more responsibility to get the code and memory management right. But in modern versions of EF, ALL of the functionality and performance of Dapper has been absorbed or replicated in similar features in EF. So the argument is no longer EF vs Dapper, it is now LINQ to SQL query generation vs raw SQL string literals. EF gives you both options in the one package.
For execution of raw SQL, Dapper absolutely knocks the socks off the original legacy release of EF6 (.Net Framework) in terms of offering parameterization and sanitation of raw SQL queries. The alternative in EF was slightly different syntax and you had to manually implement parameters, so Dapper syntax beats the original EF6 SqlQuery(), but the speed difference for raw SQL execution is sometimes hard to measure, Dapper sometimes wins, but it's a photo finish. EF Core adopted most of the functionality that made Dapper stand out from EF via Formattable Strings and later improved that offering with interpolation of parameters directly. Explore EF Core - SQL Queries for more details. If single digit milliseconds difference is going to make a significant impact on your application, then perhaps Dapper is worth it, but it is no longer gauranteed to beat EF in literal sql query showdown, under the hood they are both utilising the same core ADO.Net runtimes and EF has additional optimisations that can achieve better performance than Dapper (I'll update with a link to some fresh research later..) The only difference that might be worth considering is that due to the significantly reduced functionality of dapper, it's runtime libraries are smaller and has a few less dependencies, but that is it. Runtime and development time the two are almost identical if you choose to use raw SQL queries coded into string literal variables.
What you choose for your data access in C# is entirely up to you. Just make sure that whatever you choose, you can support it long-term.
For me, from an architectural point of view, EF, as a higher level option, is a much better choice. Especially for medium and long term project or product management. Just as Tim has pointed out, we need to make sure we can support it long-term. An ORM is an easy winner here mainly because the "magic" strings are reduced or removed entirely. The magic I refer to is the raw SQL statements. If your schema will NEVER change, then Dapper can serve you well after you've manually written out all those SQL statements, but if your schema might change you will be forced to manually maintain field, table and relationship references in your SQL statements (that are strings) to match the evolving database and or data object schema.
A key feature of an ORM is that you can decouple the business object schema from the data schema and can manage the mapping or interface between the two if you need to without affecting the rest of your code base. If you don't want to manage the mapping, then in EF, changes to the model will raise compiler errors if the changed elements are referenced in any queries. If you are performing a simple rename of a column then tools in your IDE can automatically refactor all references in your code, which will include your strongly typed queries and you may not see any compiler errors at all. With Dapper, a similar change will not be detectable by the compiler, you will only pick it up through testing or at runtime.
Another feature of EF that cannot be overlooked is that there may be many very simliar, yet unique data queries and commands throughout your data access logic. If you need to specify a raw query for a table or a view, you can do that once and store that deinfinition as an IQueryable expression. This can be the base expression that the others that are very similar to can compose additional criteria or transformations. The direct equivalent of this in Dapper is to execute the base query, pulling all of the data into memory and then applying the additional steps. A common Dapper workaround to this performance issue is to duplicate the SQL and directly inject the subtle changes that might be required. This is a classic example of the type of code smells that the DRY principle guides us to avoid, especially if changes need to be made to some or all of the derivative queries. (there are some 3rd party query builder extensions available that make this easier, but it doesn't compare to expression trees.)
Performance in production should be more important than performance in development.
Dapper execution is faster than EF for individual queries because the SQL query is already composed. But this comes at a significant cost and risk to the SDLC. Performance of the team and how quickly they can respond to issues raised by clients should be more important than producing code that cannot be easily maintained. For many teams, the additional runtime overheads in EF are a worthy compromise compared to the time and effort it takes to maintain raw sql in a large project. As the scale and complexity of the project, team and data schema increases, so too does the viability of EF (or other ORMs) over Dapper, but usually at an exponential rate.
Considering that EF and EF Core contain almost all features that Dapper supports, (I haven't found one that isn't supported yet, but I'm open to suggestion) and that the EF object mapper in .Net 6 and & 7 is much more efficient than it was in the past, its not really appropriate to compare EF to Dapper, Dapper is a significantly cut down implementation of EF, or EF is Dapper with additional features. Even in 2019 it was the wrong message to send to the wider community, but especially so in 2022. Dapper is a great alternative to TableAdapters and an awesome extension to the traditional ADO.Net data interfaces, but the lack of IQueryable support for expression trees and to delay execution and composition of the underlying SQL query is a strong productivity limiting factor of Dapper.
I didn't really want to go into this here but the following statement is probably what triggered me to write this at all
Entity Framework on a desktop application is a major security concern.
This is a really baseless statement, at no point does EF need any permissions outside of what you would also need to achieve the same outcomes with Dapper. EF and Dapper both use the same underlying ADO.Net
DbConnectionobject that comes with the same constraints for management of credentials. Getting EF password security right is exactly the same process as for Dapper, so please do not use this as an argument for or against either solution.
Dapper is great for really small scale applications or data access libraries that contain a managable number of ad-hoc SQL query strings that you do not want to validate at compile time, because you are awesome and don't make mistakes. Dapper is also really good for extremely high frequency micro-execution environments like streaming analytics when milliseconds matter and have significant impact on the overall system. As general advice for development teams that are just writing line of business applications, you'll find EF to be a much more productive development and long term maintenance experience.
Keep in mind that Dapper was created at a time when EF was not very mature and Stack Overflow needed to reduce compute costs and improve the performance of a site that handles 10s of millions of requests each day. Their data schema isn't overly complex and they have a riggerous process for regression and integration testing before deployments. We all want our solutions to be as successful and in demand as Stack Overflow, and thanks to their pioneering efforts other toolsets and ORMs like EF have evolved to implement the interpolation and resolution of parameters from natural feeling SQL query strings and many of the performance aspects too. In 2022 we need fresh metrics and understanding of the alternatives before we use old arguments or ignorantly selective statistics as the basis for critical architectural decisions.
there have been whole books written on EF. That’s awesome, but it does indicate a level of complexity to the tool...
I'm pretty sure there are more books out there on SQL compared to EF... I have shelves of them and have completed multiple levels of certification on MS SQL Server but I am still learning every day. So by Tim's logic, I'd suggest SQL is a lot more complex than EF, perhaps its a good idea for teams to find way to be writing less SQL 
Hi Chris, I am using EF Core because of the SDLC and development speed for the easy work.
But the thing you don’t mention, are all the problems regarding using EF Core. Because of the EF Core abstraction around Sql statements, developers usually have no clue what the output of EF Core is, and it can be very, very unoptimized.
I think developers who do not know SQL statements, should not be allowed to interact with the database, in my opinion. That is not the reason why EF Core should be used. When creating a database centric application, knowing the primary tool should be top level priority. Always!
The amount of work that goes in to learning EF Core is huge.
The complexity of the LINQ statements grows out of control for complex queries. So there is a point where it makes sense to start using SQL, because development speed, performance and maintainability suffers.
But then you have to create some sort of validations your SQL Statements are still functioning whenever the schema changes. So back to the start where you need something for SDLC and statements / stored procedures anyway.
I will be using EF Core in the future. But not as a golden hammer for everything.
I agree that EF Core is not a golden hammer. But Dapper is only a tool that should be used by very experienced SQL developers when there is also an extensive library of regression tests to protect against the runtime failures that EF will pickup at compile time. The majority of application logic should be simple CRUD operations and should very very simple code in either runtime. If you follow the standard advice for optimising LINQ in EF Core then most applications will only end up with complex LINQ if the schema is not aligned with the business domain, in that case EF has some great mapping tools too, but there are other discussions to be had.
My argument still stands, almost all functionality in Dapper also exists in EF. You don’t have to use a pre-typed schema if you don’t want to, but it greatly simplifies the SDLC if you do. So if you are starting out, don’t choose Dapper from the start, don’t constrain yourself when EF gives you the best of both worlds. There are tools that can help you to rapidly develop your application and provide compile time validation to protect yourself from hours of debugging.
My personal view seems to be the complete opposite of yours. My view is that if your schema is such a mess that you start to use dapper as an argument… then that is actually the time when it is more important to your SDLC that you do use an ORM like EF. I still have a handful of manually constructed SQL statements in my applications, usually when I need to use native functionality specific to that RDBMS or for optimised analytics, in some cases it is because I do not understand an efficient LINQ query to achieve the same outcome… But I can still do that in EF, which is ultimately my point. When we do that though the only way to test the query effectively is at runtime, so we need extensive integration tests to validate the code which takes more effort to prepare and execute and indeed more experience to get right.
If you are having a lot of problems with LINQ, either performance tuning or how to construct complex queries, please reach out to either myself or the wider community. You can be a very effective developer using LINQ without fully understanding SQL, but if you do understand SQL you can be a 10X developer with LINQ and EF, that is 10X over developer who only knows Dapper ;)
Hi Chris, I am intrigued by your assertion that EFCore has eliminated the performance penalty. I have been using both dapper and ado for most projects last few years, but have recently retested EFCore and also came across this post. Just doing simple query for few thousand records I am still reading a 3 or 4x performance degrade using EFCore, I guess that might be faster than the past, i was seeing a 20x penalty. In our applications, dealing with 1000’s of hits a day, we really like to start with performance first, maintenance decisions 2nd, and cost 3rd (doesn’t always work out that way). But please do give us more information on how you are reaching parity using EFCore as compared to ado and or dapper type queries.
1000s of hits a day is not a lot ;) I’m running 2-7 millions of hits on and old .Net 4.8 API using EF and still getting between 20-200ms round trip times on the whole ASP.Net stack. So even at that scale the performance improvements that we might get from individual queries isn’t really the issue that we sometimes think it is. Would I convert it to Core, maybe. Would I go to dapper, no. The risk is simply just too high to the product and not worth any potential gains.
Maintenance directly relates to cost, it is usually a far higher cost than the actual runtime. But high maintenance affects the stability of your product, whether it is because you are evolving changes or fixing bugs, EFCore allows you to skip many lines of boilerplate code that you would be manually writing if you use Dapper instead. That really should concern you because it is a big change to make later.
Most performance issues that people experience are specifically due to the type of queries that they write, if you write the same query in both EFCore and Dapper and disable tracking, the execution time should come out with negligible difference (or better!). But if you do not understand EFCore many initial LINQ queries that you write will not use projections and are likely to be less efficient than what you may have written in plain SQL. The other scenarios where EFCore excels is in complex graph queries. In Dapper, you would have to flatten queries and reconstruct nested graphs, perhaps in some cases using automapper, but EFCore can now orchestrate multiple parallel queries and reconstruct object graphs simply by enabling the options.
My main argument is that in 98% of cases, EFCore (especially now in .Net 8!) will give you the same, better or close enough execution time, for much less overall effort. Choosing EFCore effectively means we get performance for free, not only that but we get performance and security improvements with each new release, in many cases without needing any further effort from us.
I would encourage everyone to start with EFCore and once you identify specific queries that need optimisation that you first explore the standard solutions for optimising EF. Then if you can justify the effort, use Dapper for those individual queries if you really want to and see if you can improve the execution time. This way you are still going to get the benefits from EFCore for the rest of your application and only have to manually manage those specific mission critical queries.
The main scenario where you might consider something like Dapper, would not be in a repo pattern, but when you only have a small number of queries in your whole solution, and probably a single developer. EF allows you to code faster individually, but also as a team, the schema and mapping is defined once and reused in all contexts, you never run into issues where the SQL syntax doesn’t match the object graph that you want to capture the results into. In general, initial writing and testing the queries is faster and more reliable in EFCore, there is less to write and test.
After you have written the queries, the single biggest risk in a Dapper code base is changes to your database schema. In EFCore, this risk is still a factor for any raw SQL, the same risk as Dapper, but at least all your other code is protected with design time checks and more descriptive runtime messaging. If your schema was never going to change, then this risk is obviously much less, but I haven’t worked in a project in a long time where the schema never changes.
I stumbled across this while I’ve been recently evaluating both EF and Dapper for my current project. I’ve read both Tim Corey’s pro-Dapper response to you on his site, as well as your case for EF here.
The background of my project: to re-write a corporate website on an existing/mature SQL database. The DB already has stored procedures for all its CRUD operations.
The most important stored procedure is to query about 10,000 records. The spoc by itself can do this in about 500-600 milliseconds.
I started my evaluations with EF Core (the most recent version as of January 2023, I forget off the top of my head) and created an integration tests to evaluate performance and ease of use.
Using EF the ‘ordinary way’ (without the stored procedure), took about 6 seconds to to populate its entity (this includes the .ToList() time, since this is what the customer is going to feel).
I created another test to evaluate how EF would perform when executing the stored procedure. This time it took about 5 seconds for EF to populate the entity (again, this is with the .ToList()), shaving off about a second.
I then created another test to execute the same stored procedure using Dapper. It took about 2.5 seconds, making it the clear winner in terms of speed/performance alone (also including the .ToList() and the additional time that takes).
I’ve usually used ADO.Net in the past, typically coding a repository and unit of work pattern around that. I’ve also used older EF from time to time, usually in a database-first approach, but Dapper is new to me. (And I most definitely don’t want to return to an EDMX file and all of its headaches).
So far I’m finding Dapper to be much more user-friendly and much easier to get started with, especially in this scenario. Perhaps if I had a blank database to start from, EF with code-first might make more sense. And most projects I’ve worked on have a DBA that isn’t going to let developers create tables and new fields without their intercession.
Because this database already has all the stored procedures for CRUD operations, the ease of use of Dapper, and the schema will not change much, we’re leaning towards using it over EF. Dapper may require a bit more coding, but it’s not something we’re afraid of. And so far, the speed difference is certainly noticeable. If this was on the scale of milliseconds difference, we might be more tempted to use EF just to keep with Microsoft tooling and its better knowledge base.
Having said all this, it’s quite possible that my EF query could be optimized in some way, or perhaps I’m doing something wrong. But somehow it doesn’t make sense to me to use the huge EF library just to perform raw SQL statements to squeeze some performance out of it. I don’t think that was its original intent.
But if I’m missing something, please let me know. I try to put the customer’s situation first, and not my particular preference for a technology.
Thanks for the feedback Mike, it’s a very valid question. My main argument for choosing EF over Dapper has the most power at the start of projects. The main benefits of EF is realised in the benefits to the SDLC.
The further into the project you get, it is hard to weigh up the value of converting to EF because you start to roll your own framework and project management processes to mitigate or workaround the runtime and development volatility that Dapper introduces. As your product without EF matures there comes a point where EF doesn’t offer you enough perceived benefits over your developed solution to bother implementing it.
Dapper requires a lot more code, you either have to generate it or hand write it. A lot more code is a lot more code that can fail and so a lot more code to test. Dapper is really a shiny wrapper around ADO.Net. If your choice is between
TableAdaptersor other first principal ADO.Net constructs and Dapper, 100% pick Dapper. EF is a Unit of Work wrapper around ADO.Net and includes an ORM. If you were going to add repository and UoW anyway, then save yourself a few hundred thousand lines of code and go straight to EF. Even if you don’t need all of the functionality of EF, I would still pick EF because of the longer term benefits and extensions that will become available to you.AutoMapperwhich if implemented poorly can easily add even more runtime overheads that can be avoided. Be sceptical if you are following a guide that recommends a UoW over the top of EF.This should mean that implementing EF or Dapper should be the same amount of effort. You can map the SPs to Entities which is more effort then Dapper or inline dynamic entities just the same as you are forced to with Dapper. If you are using EF7 then the performance should be better or very similar. If it is not then we are not comparing apples with apples. The Raw SQL and Interpolation aspect of EF is the one that has feature and speed parity with Dapper. The only reason to NOT use EF in this case is that you specifically want to prevent the possibility of using the
DbContextUoW and entity change tracking and schema management functionalities in the future.There is of course a generational learning gap between using EF and raw ADO.Net. There are not a lot of good guides on how to use EF the same way you use Dapper and even less people asking for it. So it is not uncommon to find EF implementations that become a lot more convoluted than they need to be in your specific situation. The argument that you should go with the framework and code that you and your team are most proficient in is a very powerful one.
Because EF has a lot of standardised tooling support for both development and deployment built into most IDEs, EF is often the framework that will provide a wider pool of talent and support to draw upon.
Think of Dapper as a first principals approach. If milliseconds matter, and you take the time to write your code in the exact same way you were going to do it in Dapper, then EF is now still a viable option. But choosing Dapper means that later developers can’t choose to use some of the other benefits that you get from an true ORM and that ALL SQL will have to be hand coded for every interaction.
Good, please never use EDMX with EF, we have evolved ;)
Just a side note, I suspect that you are not making good use of SQL if you need to return 10000 records to the client often enough that 1-5 second wait makes a difference. If you are upgrading an existing application this might be a great time to add structural or operational efficiencies in how you utilise the data.
This of course leads into my favourite term, “Commercial Pragmatism”. Use the tools in your toolbox that you know well and can produce great results with the raw materials that you are presented with.
Hi Chris, this post is certainly interesting to me, I am currently learning how to use EF, and I was using Dapper while implementing it with Repository Pattern.
The only problem that I am having is, how do I deal with lots of Migration files? I read in the documentation in Managing Migrations from Microsoft, that I should not try to delete the Migration files especially when it is in the production database. Do you have any solution in this kind of issue? or is it trivial?
I got feedback from my colleagues that, it is good to use EF in small to medium projects, but not good at large projects, what can you say about this?
Your feedback about my questions will be greatly appreciated.
From my experience, the larger the project, the more you need convention based managed ORMs, like what I get out of EF. If your choice was Dapper vs EF then I would say as the project grows, the value that you get from a strongly typed entities in EF and the schema management increases exponentially in favor of EF over Dapper. I would only consider Dapper in the smallest of projects but EF gives me all the features and benefits of Dapper, as well as everything else so although there were compelling reasons in the past, I would choose EF. It’s out of scope of a discussion involving Dapper, but there is a process for consolidation of migration files but it’s not well documented and is affected by your application version management and deployment, and if your solution is standalone or distributed. I’ll see if I can make a good example project to share with everyone.