Should I Use Entity Framework?
My answer is typically “No”. Now, “real” programmers often think I’m an idiot and database administrators typically cheer but there is much more nuance than either camp wants to admit so let’s talk briefly about what should go into this choice.
First, if you say there is only one “right” way, you don’t understand software development. Sorry, but it’s true. Every situation, codebase, and team is unique. Trying to address every situation proactively with a solution indicates you really haven’t seen many situations. If code were so prescriptive, we wouldn’t need developers. We could just let AI write the code for us. So, if you came here to evangelize your “one right way”, please at least consider the fact that you might not be always correct.
OK, so what are the criteria for using Entity Framework? Here is my list I typically go through with a customer:
- Does your team understand how to use Entity Framework well?
- Does your team know how to diagnose issues with Entity Framework code?
- Does your team know how to diagnose under-performing EF queries and fix them?
- Does your team understand the code that EF wrote for you well enough to know what it is doing?
- Does your team know how to protect the EF credentials on client machines?
Let’s briefly go over these. First, there have been whole books written on EF. That’s awesome, but it does indicate a level of complexity to the tool. If it takes you 500+ pages to teach you how to use EF effectively, that’s a lot of knowledge to maintain in your team. Sure, you can get by with probably 20% of that but only until you run into an issue.
That brings up the next question. Things in Entity Framework break. EF writes code for you. It tries to get it right but the real world is messy and not always predictable. When things go wrong, you are going to need to dive deep into the code EF wrote for you. So how fast can you read and comprehend the generated code so that you can identify what the issue is and how to fix it? Remember, bugs typically occur in production so fixing the issue will probably be associated with a time crunch.
Slow-performing queries are a given with EF. You can’t assume that software can write perfect queries against your database. For one, the type and amount of data you have in your database will determine how a query performs. For another, the software cannot replace the human element. It takes a human to make certain choices that are logical but not easily quantifiable. A general rule of thumb is to assume that about 10% of your EF queries need to be replaced by more efficient, hand-written queries or stored procedures. SQL is amazing at optimizing ad-hoc style queries but it can only go so far. You as the developer need to be able to go the rest of the way.
That also means you need to understand how to monitor and manage your database performance. Just adding indexes isn’t the answer. You need to know which under-performing queries to address and how to write better SQL statement to optimize them.
Knowing what the EF code is doing relates to the debugging issue but it is more than that. It also relates to the general performance of your application. You need to understand exactly how your code (the EF code, that is) is getting the data from the database, how the work is being done on it, and how you are getting. it. Knowing that process will help you reduce some of the performance issues that can easily slip into an EF-driven application.
The final question is one that a lot of people seem to miss. When you use EF, you have to grant the EF credentials full access to your database. EF needs to read, insert, update, and delete data as needed from every table in the database. Do you want to give those same permissions to your end user? Probably not. So how do you protect yourself? App.config encryption isn’t the answer. The user controls one end of the encryption, therefore they have access to the unencrypted password. Getting EF password security right on a desktop application is quite difficult. Web applications are easier, since you control the web server and you assume no one has access to the hard drive. However, EF is used in more than just web applications.
Note that I did not once say “Don’t Use EF”. I just said that there are a whole lot of questions that need to be answered first, and typically people cannot answer them satisfactorily. That is why my recommendation is typically not to use EF. If you can’t support the code you write, you are in for a disaster at some point. You can’t just stick your head in the sand and say “it won’t happen to me”.
So what is the alternative that I suggest if EF is not the right choice for you? Well, I recommend Dapper. It is beginner-friendly, super-fast, and there is no hidden layer to understand. Let’s look at those same questions but with Dapper as the ORM:
- Does your team understand how to use Dapper well? It is about 4 lines of code, so yeah, typically.
- Does your team know how to diagnose issues with Dapper code? There is no Dapper-generated code. It is all code you wrote.
- Does your team know how to diagnose under-performing Dapper queries and fix them? Dapper uses SQL queries you wrote so you should. If not, maybe you shouldn’t be using SQL (using EF isn’t a way to not understand SQL).
- Does your team understand the code that Dapper wrote for you well enough to know what it is doing? Again, no generated code.
- Does your team know how to protect the Dapper credentials on client machines? At worst, this is no different than EF. At best, this is a non-issue.
Let’s address the security issue right away, because that is a huge win. If you use Dapper, you can choose which database objects your application has access to. If, for instance, you only use stored procedures for data access, you can limit your Dapper credentials to only being able to execute certain stored procedures. Then, even if the user had direct access to the credentials, they couldn’t do anything beyond what they could do in the application.
As for how to use Dapper, there isn’t much to it. You establish a connection, call your query (ad-hoc or stored procedure), and get the results back in a strongly typed IEnumerable<T>.
At the end of the day, I have found that Dapper is quick to set up and use and while I do end up doing more manual work up front to create my data access layer, I also understand it better and have better control over how it operates. Over the long-term, I am less concerned about bugs and issues in my data access layer. There isn’t any “magic” code that I don’t understand or that I’m afraid to touch.
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.