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 the WHERE clause 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 DbConnection object 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