was successfully added to your cart.

Why Don’t You Use Entity Framework?

Why Don’t You Use Entity Framework?

By March 30, 2018 Ask Tim 21 Comments

The Case for Speed, Simplicity and Ease of Maintenance

Microsoft’s data access method of choice is Entity Framework. I disagree with that choice. Obviously there are a ton of really smart people who design and build Entity Framework. There are also fellow Microsoft MVPs who I realy respect like Julie Lerman who spend a ton of time evangelizing Entity Framework. So it does seem odd that I would disagree with all of them. Let me lay out for you the reasons why and I will let you decide which way to go.

The goal of Entity Framework (EF) was to create a simple way to abstract away the database and allow developers to just work with data objects. Basically, as a developer, EF should simplify your life by allowing you to stay in C# and not think about how the data is getting into the database or back out. That’s the goal. Now, let’s talk about the reality of it. If you look at this simple getting started with EF example, you will see that you need to learn about DBSet, Data Context, Initializers, Seeds, Pluralization, and more. That’s before you take on the more complex example. That doesn’t seem simple to me. It seems like in order to avoid learning SQL, you ended up learning a whole new set of language tools. This isn’t reusing your skills, it is adding a new skill in C# for express purpose of avoiding learning a skill in SQL. Since you still need to know SQL, it doesn’t seem like a good trade-off.

In contrast, my tool of choice is Dapper from the folks who make StackOverflow. I created a whole video on it (found here: https://youtu.be/Et2khGnrIqc). I won’t go into all of the points I covered in that video, but basically Dapper is simple to use and it allows me to use stored procedures, which in turn can help me make my database much more secure.

Now let’s look at the three areas that we should consider when choosing the best data access tool for our needs: speed, simplicity, and ease of maintenance.

When it comes to speed, the speed I think is most important is application speed. How fast is it for our customers. In this category, Dapper wins hands down. You can go to their page on GitHub to see that Dapper is an order of magnitude faster than EF. Now some might argue that speed of development is important as well. I agree, but not at the expense of my customers. When it comes to development speed, EF has a better story once everything is set up. Once you have all of your configuration set, your settings tweaked, and your models built, it is pretty fast to make small changes. Dapper isn’t slow, it is just that you need to make the change in two places (your model and your stored procedure) if you want to add or drop a field.

Simplicity is another area where Dapper crushes EF. In four lines of code, I can get data out of a database and into a List<T>. Try doing that in EF. You will have adapters, context, and more and won’t even have data yet.

Finally, ease of maintenance is another Dapper winner. If I have a database developer on my team, they can work just in SQL. If I use EF, I would need to get a database developer who also knows C#. Either that or I don’t have a database developer on my team but then who optimizes the database? The C# developers?

To summarize, Dapper is quicker, simpler, and easier to maintain. It fits better into an existing organization with existing databases. It is simpler to learn. It is powerful and yet not complex. So, for all of these reasons, I choose Dapper for my data access needs. How about you?

Join the discussion 21 Comments

  • Mauricio Caterino says:

    I totally agree. I have EF as a must learn skill. But, Dapper is my choice for access and crud operations. It’s faster and simple.

  • ThomasW. says:

    Hi Tim,

    I entirely agree with you. Even better, I followed your tutorial about Dapper, and within an hour I was able to build a simple WinForm app which perfectly executed all my simple CRUD operations via stored procedures. I tried EF, and I must say that I also like it due to the graphical interface etc. but as you mentioned it requires far more effort to complete the job. Thanks for reminding me the Dapper, I must learn more about it.

    Cheers,

    Thomas

  • Christian I. says:

    Hello Tim,

    At some points I agree with you. At work I use Dapper or EF depending the project. But the argument “Allow me to use “Stored Procedure” it’s a no argument. If I can avoid (that’s means almost all the time) “Stored Procedure”, I avoid. My current customer has an application with a lot of “Stored Procedure”, “Trigger” and so on …. a real sh### to maintain, business is (partially in the database), real sh### to test. You can use “Stored Procedure” with Dapper and EF.

    • Tim Corey says:

      OK, so I have a video on YouTube that covers Stored Procedures. You might want to check it out (https://youtu.be/Sggdhot-MoM). There is a lot more to stored procedures than just making SQL calls. First, they are optimized in a way ad hoc calls (which is all EF does) cannot be. Second, you can lock down your database so that users can only called stored procedures (no table access, etc.) to reduce your vulnerability and practically eliminate SQL injection. Third, they describe the proper way to interact with your database.

      A database usually does not support just one application. Nor is the application the only way people access the data in that database. Since the application is not always in the picture, putting the security and business logic in the application leaves your database vulnerable. If a user listens for your connection information (and because they are on the machine running the application, assuming it is a desktop machine, they can) and gets the database password, what can they do? In my case, nothing beyond what the application can do. In your case, anything (since an EF connection needs to be an administrator, or practically so, so that it can run full CRUD operations).

      I know stored procedure maintenance is tougher because it isn’t in the same place as C# but I would maintain it shouldn’t be. You are doing two different things (database development and C# development). Trying to combine the two is a shortcut and there are numerous penalties for doing so. These include reduced security and reduced performance. EF tries to tell you that you can eliminate the SQL Developer’s job by using it but that isn’t true. You still need to do database work, especially as you get bigger. Unfortunately, EF looks great in small scale and in development. However, have you ever tried to optimize SQL when it supports a large-scale EF system that gets hit by hundreds or thousands of users? It is a mess. With all the ad-hoc queries being called, it is hard to make anything more efficient. It is also hard to work with the database outside of that one application as well (what if a different application needs a column added?)

  • Paul says:

    I have to disagree.

    1) Speed – yes, Dapper is generally faster (but in 90% of apps, this is irrelevant and the other two factors are far more important)
    2) Simplity – writing a LINQ query, particularly when aggregating or joining several tables in hugely simpler than hand-crafting SQL statements.
    3) Maintenance – again LINQ wins because it is written in c# and therefore type safe and able to be included in refactoring. If you change an EF property name, it will be updated in all queries when you use refactor tools. It someone changes it manually, you get a compile type error which is quick to see and quick to fix. With Dapper, you have bits of SQL that you manually need to edit.

    • Tim Corey says:

      Here is my responses:

      Point 1 – Dapper being an order of magnitude faster than EF isn’t irrelevant unless you are building hobby apps. Taking 10 times as long to make a call is a BIG deal. A single call almost never happens in a vacuum. One process usually takes multiple steps. Those speed issues just keep piling up until your application is sluggish and the only way to fix it is to completely replace your data access layer and rewrite your application. That, to me, is a big deal.

      Point 2 – Here is the problem with this argument: it implies that a C# developer that doesn’t feel comfortable with SQL should fix that problem with more C#. Your SQL database is not just a dumping ground for data. You need to maintain and optimize it differently than you do C#. Otherwise, your “simple” LINQ statements make SQL bog down and you can’t optimize it because everything is an ad-hoc query, which makes it harder to do anything on the SQL side to make things better.

      Point 3 – I get it and maintenance when you change something is a pain if you are using stored procedures and Dapper. However, using EF implies that the application is the only client of the database. So what happens when you want to do reporting out of your SQL database (and not through your application)? If your application changes one field, you just broke every report, script, and other application that uses that database. Not only that, when you go to create those reports, how are you going to do it? You will need to create stored procedures to populate their data. In my case, the stored procedures already exist. It is much more DRY than EF. If I decide to change how a stored procedure works, it changes for the report and for the application. Sure, if I add or remove a field I’ll need to change it in the report and the application, which is a pain. That pain is unavoidable though, unless you want to argue that the only purpose for a database is to serve an application (which, in the days of big data, analytics, and more, that idea is outdated at best).

      Just to round out our discussion, you also failed to address speed (EF is an order of magnitude slower than Dapper – essentially every call EF makes is 10 times slower than the equivalent in Dapper), security (With stored procedures, I can lock down a database to only stored procedure access. With EF, you have to have full CRUD access to all tables. If Dapper’s connection string gets hacked/discovered, the user has no more permissions than the application. With EF, the user now has full admin rights.), and reusability (With stored procedures, I can use them in other applications or other “things” like reports or even direct calls. With EF, your data access logic, safety checks, etc. exists outside the database and cannot be easily reused.)

  • rledyard says:

    I see a lot of parallels to ASP.Net WebForms. Replace “You don’t need to learn SQL” with “You don’t need to learn HTTP, HTML and CSS”.

    • Tim Corey says:

      Yup. One of the many reasons why WebForms are not really recommended anymore.

    • Jonesjj says:

      I agree. I think many of these comments are missing the point. This post didn’t seem like an argument *for* Dapper. I think it was intended as a post *against* EF. The fact that you have a really good C# hammer doesn’t make everything a nail.

  • Travis Laborde says:

    obligatory “Vietnam of Computer Science” reference 🙂

    http://blogs.tedneward.com/post/the-vietnam-of-computer-science/

  • I totally agree with you. EF is a whole new tech to learn for the same tasks which you were doing in the past with your SQL skills. It’s ok to use EF for a small project, but when there are lot more data to deal with, EF is a nightmare (personal experience). I have never been a fan of EF and also try my best to convince my customers to stay away from it. I also posted about on my website to use dapper with Asp.net core and it was really quick to get the app up and running with database interaction.

    http://www.talkingdotnet.com/use-dapper-orm-with-asp-net-core/

  • Jan says:

    Why not use both?

    EF Migrations can help speeding up application development, simplifying schema setup and so on, EF Linq is also a great helper for maintainable queries. When performance is needed, use Dapper.

    Both tools serve a purpose.

    • Tim Corey says:

      I think you answered your own question. If you say “when performance is needed” in relationship to an application, the answer should be “always”. There shouldn’t be a time when you say “we really don’t need performance here”. There are a couple reasons for that. First, why plan to be slow? I don’t know of anyone who says “man, this application is too fast”. Second, you cannot predict how an application will be used in the future. I’ve created a TON of applications where I thought they would be used temporarily for a small purpose and they ended up being production applications that were widely used for a long period of time. I once created a simple Access-based database application that was supposed to be used just for the summer. It was used company-wide for over a decade (maybe longer – I left the company and lost contact with those still there).

      Now there are times when you choose the less efficient route in order to gain something else. For instance, sometimes reflection is the best choice even though it is slower than other options. I get that. However, this isn’t a small piece of your application that you will barely use. This is your data. Probably the entire reason your application exists. I can’t fathom the idea of saying “it is ok to slow down the core of our application”.

      Another part of this argument is that it speeds up development. Great. So you get a one-time speed bonus (let’s forget for a minute that maintenance and debugging are harder). How many times is your application going to be used? 10,000 times? 100,000? The more your application is used, the more expensive that one-time speed bonus becomes.

      The final piece is the idea that you can always switch if you need the “extra” performance. In my experience, once you go down the EF route, you are stuck with EF. Backing it out and replacing it with Dapper or some other data access method will be incredibly difficult if not impossible. So it isn’t like you can make one choice today and change your mind tomorrow. You have to choose up front – development speed or production speed. I’ve made my choice.

      • Jan says:

        I get your point.

        But I don’t plan to be slow. Sometimes “EF-slow” is simply fast enough. Not everyone writes Facebook scale applications, sometimes a typical LOB app for 200 people must be made. And if an EF query in a UI view needs 100 ms vs. 10 ms, that simply doesn’t matter. And some people (although obviously not all people) would agree, that EF is simple, approachable, productive and fulfills the job. Not everyone wants SQL strings all over their Repositories.

        If in the same LOB for 200 people a high performing batch job is needed, one can always use Dapper for that (and even reuse the POCO Entity classes).

        EF Core performance by the way isn’t that bad anymore, see https://koukia.ca/entity-framework-core-2-0-vs-dapper-net-performance-benchmark-querying-sql-azure-tables-7696e8e3ed28.

  • Branislav Petrovic says:

    Hi Tim,

    do you have any experience working with LLBLGen Pro Runtime Framework (ORM) as alternative to EF and Dapper? There are some relevant infos that this ORM is best choice to use in LOB applications with a large number of users and data.

    Cheers,

    Branislav

  • vincent says:

    You didn’t even touch on the cloud stuff and server-less computing like azure functions, where you now you have a pay for execution time pricing model, so it stands to reason that if EF is 10X slower then it stands to reason that it’s 10X more expensive to run an EF application then stored procs. Now add into the mix memory optimized tables and compiled procedures which can be hundreds of times faster and to the modern (frugal) developer this should be a no brainer. If you’re really clever it’s easy enough to query system views and run procedure meta data through the razor engine and presto, you got all the wrapper code just the way you like it. Too many rookie developers in the mix yapping about how great this tool is or that tool is. Tools don’t solve problems, people do, so invest some time into really learning and understanding SQL, you’ll be rewarded many times over.

  • Neil Hewitt says:

    You know I use dapper with SP’s for every single call. barring auth which is already built in to most boilerplate.. my dev journey saw me initially writing Sql in c# then as the core app grew and i progressed moving them into SPs for optimisation and maintenance.. then i struggled with entity because i didnt understand why i was bein pushed to learn a new way of writing code that ultimately just generated SQL something I was now pretty fluent with.. enter dapper.. I now only ever use Dapper.. it’s all I need.. my SQL queries are maintainable compiled.. easily loggable and can be modified on the fly without pushing a new release.. yes I have most of my business logic in my sps but for me that’s a huge advantage.. I’ve always felt like I must be doing this all wrong though because everyone seems to be on the entity train.. but this article and your replies to the comments literally could have come directly from my mouth. I could literally write off any challenge as to the benefits of dapper with SPs over entity within a single sentence… but I’m just baffled as to why everyone that writes c# or accesses SQL doesn’t use dapper with sps by default.. the only thing I can think is they don’t understand or care about db performance and are just following the person in front…

  • Alex Buyny says:

    Interesting article and conversation! We are currently considering EF vs a more standard approach in my company (Dapper + stored procs). Still have not decided…

    Re: (Tim) reusability of EF when you need to have report / second app on the database. How about this solution – abstract your models and repositories into a separate DLL (nuget package) and reuse it in both projects.

    • Tim Corey says:

      Regardless of which solution you use, you can put your data access code into a separate dll and then reuse it. I would definitely recommend it. If you use Dapper, you can also create a couple generic methods that allow you to extend your data access dll later as well, so that you can have additional data access code in a project that is project-specific. That allows you a lot of flexibility.

Leave a Reply