Nathan Reid     About     Archive     Feed

Life, Software, and the Pursuit of Everything

Why I ditched Entity Framework for NPoco

I used to be a big proponent of the Entity Framework. It made working with databases so easy! I was tired of using DataReaders and DataTables and doing the extra legwork to convert these into strongly-typed objects. Entity Framework solved this so simply by allowing me to create a model from the database that I could then keep in sync. Much of my database work these days is with MySQL, so perhaps the Entity Framework is more reliable with MSSQL, but I found it very frustrating and more difficult to work with than NPoco.

Database-first, while a great idea in theory, didn't work well with my case-insensitive table names. All of the autogenerated classes came out entirely lowercase, so instead of a class named "UserRole", I'd have a class named "userrole". As a result, I'd have to go through and rename all of the entries in the model. I also experienced many other issues with corrupted models when updating that would force me to instead remove and regenerate the entire model.

Another issue I had was with generating the database from the model. In one project, we created the model from an existing database, then used the model to generate the database on the developers machines. However, this didn't work very well; most notably, EF converted all of the VARCHAR fields to TEXT fields.

One other huge pain-point was merging differences in source control. I'd add one column, another developer would change a different column, and all hell would break loose during the merge, which often resulted in regneration of the whole model because dealing with all the XML changes just took too long. In the end, managing the model often took more work than managing the database and the code, so it wasn't worth it.

I also experimented with Code First; crafting the code by hand was nice, but about this time I ran into 3 other issues:

  1. Dependency Injection
  2. MySQL drivers kept breaking various features of EF
  3. EF couldn't handle complex queries

Dependency Injection

At the time (this may be different now) dependency injection with EF was vary difficult. I was just beginning to introduce dependency injection and IoC at our office, and EF made it very difficult to swap out with mock or in-memory implementations. We could do it by wrapping a repository around the Entity Framework repositories, but this felt a little backwards. This wasn't a showstopper, but it did hamper our unit testing, which I was also trying to standardize into our workflow at the time as a way to streamline testing without hampering our productivity. For the units that interfaced with EF, We made do with manual and integration testing instead, but it certainly wasn't ideal.

MySQL Driver Issues

We experienced so many strange issues due to this. You had to run X driver to support Y version of EF, but that driver also caused some other issues, or the driver didn't register itself properly, or couldn't be bundled with the app unless also installed in the GAC, etc.

Complex Queries

This is what finally drove me to NPoco - the EF query parser would sometimes create very strange, convoluted, broken or highly ineffeicient queries. One query in particular had 8 "where" clauses that were dynamically added based on which options a user selected; if I removed any one of those clauses, the query came out right, but if all 8 were in use, the query ended up joining the same table 8 times, once for each where clause.

Some queries wouldn't work due to certain EF functions not being implemented in the MySQL driver (datetime arithmetic, for example); other queries would work, but took upwards of 10 seconds to run; when hand-written in SQL, they would take less than half of a second to complete. I learned a lot about increasing performance in the Entity Framework by disabling change-tracking or always using projection for my read-only quries, but even then I encountered slowdows.

I discovered that, on a particular server hosting a business critical application, running any query in MySQL workbench instead of EF resulted in saving approximately 350ms on every query. The application needed to deal with hundreds of these queries each second and they needed to return extremely quickly. This was a major bottleneck in the application, so I began looking for a solution that would enable me to have the simplicity of strong-typed objects while retaining control over the actual queries. In my search, I stumbled across micro-ORMS and soon fell for PetaPoco, then NPoco for the following reasons:

  1. First up was performance: when I tested the queries with NPoco, I was still saving that 350ms! Entity Framework full-blown ORM code, while enabling it handle so much for the developer, also introduced a signifacnt slow-down.
  2. Next, I easily incorporated IoC and Dependency Injection with some simple Database Factories. Now I could easily
  3. Finally, I could easily write a query in MySQL Workbench, test it, optimize it if necessary, and plop it into my code, with total control over the final query and almost no overhead! If I later needed to debug that query, I could do the reverse (and I made a simple tool to help with going back-and-forth: http://nathantreid.com/PetaPocoSqlConverter/.

Long live developer freedom!

I love NPoco for the same reason that this blog runs Jekyll and that I happily switched from ASP.NET WebForms to ASP.NET MVC to ASP.NET WebAPI with HTML front-ends - greater flexibility and exposure of the inner workings while enhancing ease-of-use.

These days, I don't see any point to running Entity Framework or any other large ORM that abstracts away from the actual SQL. Most SQL queries are very simple to write; if they are complex, using a tool to generate said query is likely to be even more complex and error-prone. With NPoco, I can have a simple project up and running in minutes, and still be able to scale up to a large project with no difficulties.

I'm not at all opposed to using tools to make work easier (after all, I'm not writing in IL or assembler, and I love Resharper), but each tool should be carefully evaluated and in my opinion, EF takes more than it gives.

comments powered by Disqus