Why Entity Framework performs faster than Dapper in direct select statement

Question

I'm new to using ORM in dealing with database, Currently I'm making a new project and I have to decide if i'll use Entity Framework or Dapper. I read many articles which says that Dapper is faster than Entity Framework.

So I made 2 simple prototype projects one using Dapper and the other uses Entity Framework with one function to get all the rows from one table. The table schema as the following picture

and the code for both projects as the following

for Dapper project

System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
IEnumerable<Emp> emplist = cn.Query<Emp>(@"Select * From Employees");
sw.Stop();
MessageBox.Show(sw.ElapsedMilliseconds.ToString());

for Entity Framework Project

System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
IEnumerable<Employee> emplist = hrctx.Employees.ToList();
sw.Stop();
MessageBox.Show(sw.ElapsedMilliseconds.ToString());

after trying the above code many times only the first time I run the project the dapper code will be faster and after this first time always I get better results from entity framework project I tried also the following statement on the entity framework project to stop the lazy loading

hrctx.Configuration.LazyLoadingEnabled = false;

but still the same EF performes faster except for the first time.

Can any one give me explanation or guidance on what makes EF faster in this sample although all the articles on the web says the opposite

Update

I've changed the line of code in the entity sample to be

IEnumerable<Employee> emplist = hrctx.Employees.AsNoTracking().ToList();

using the AsNoTracking as mentioned in some articles stops the entity framework caching and after stopping the caching the dapper sample is performing better, (but not a very big difference)

Albany

This isn't not much of a benchmark... You're only testing a very basic scenario, where EF happens to be faster, because it doesn't have much to do. In a real-world scenario, you'd probably find that Dapper is faster (because it only does one thing: map query results to objects), but you'd have to write more code to do the same thing. – Ciao

@Thomas Levesque, Thank you for the fast replay. Most of the articles I read or the developer opinions specially the developers whom decided to take the hybrid approach decides to use Dapper in getting data, so what are the other factors that I should take into consideration to make the previous example a real world scenario and feel the performance difference between EF and Dapper – Albany

You should try real-world scenarios, involving joins, filters, groupings, updates, inserts, etc – Ciao

you will start to notice a huge performance difference when you query with a few joins, once ef needs to compile your query for that you will get the performance impact – Rattlebox

I did some testing quite a while ago and found that EF was consistantly faster when returning a small number of rows. Im still not sure why this is the case but at a guess I think EF might do better connection management on default settings. In terms of mapping I dont think there is a substantial performance delta between EF and dapper, however if you are wanting to the edge performance tuning for queries there are things you just cant do with a LINQ ORM. My (old) article here: blog.staticvoid.co.nz/2012/3/24/… – Compeer

@LukeMcGregor, RE: the small number of rows I did the test with a table filled with 50K of rows. I'll read your article it seems interesting – Albany

This kind of test is useless. You should test a lot of operations (all CRUD) and with a real ORM, not dapper. Linq2db has some neat advanced stuff that even EF can't do, and works with Linq, so it would be a better choice to run your benchmark. – Calisaya

The article Entity Framework Core 2.0 vs. Dapper performance benchmark, querying SQL Azure tables confirms that Dapper is a bit quicker, but not enough to ignore "full ORM" benefits. – Steeple

Answer 1

ORM (Object Relational Mapper) is a tool that creates layer between your application and data source and returns you the relational objects instead of (in terms of c# that you are using) ADO.NET objects. This is basic thing that every ORM does.

To do this, ORMs generally execute the query and map the returned DataReader to the POCO class. Dapper is limited up to here.

To extend this further, some ORMs (also called "full ORM") do much more things like generating query for you to make your application database independent, cache your data for future calls, manage unit of work for you and lot more. All these are good tools and adds value to ORM; but it comes with cost. Entity Framework falls in this class.

To generate the query, EF have to execute additional code. Cache improves the performance but managing the cache needs to execute additional code. Same is true for unit of work and any other add-on feature provided by EF. All this saves you writing additional code and EF pays the cost.

And the cost is performance. As Dapper does very basic job, it is faster; but you have to write more code. As EF does much more than that, it is (bit) slower; but you have to write less code.

So why your tests show opposite results?
Because the tests you are executing are not comparable.

Full ORMs have many good features as explained above; one of them is UnitOfWork. Tracking is one of the responsibilities of UoW. When the object is requested (SQL query) for first time, it causes round trip to database. This object is then saved in memory cache. Full ORM keeps track of changes done to this already loaded object(s). If same object is requested again (other SQL query in same UoW scope that include loaded object), they do not do database round trip. Instead, they return the object from memory cache instead. This way, considerable time is saved.
Dapper do not support this feature that causes it to perform slower in your tests.

But, this benefit is only applicable if same object(s) loaded multiple times. Also, if number of objects loaded in memory is too high, this will slow down the full ORM instead as then the time required to check the objects in memory will be higher. So again, this benefit depends on use-case.

Amur

When it comes to writing a complex query there are cases when EF doesn't generate an optimal SQL and you have to do it manually anyway. So I wouldn't say that EF is really "full", it just helpful for quite simple queries but you have to know it thoroughly to use effectively and you have to know SQL thoroughly too, as a result, you have to know 2 big subjects instead of 1 as it is expected when you just start reading a book about EF where it looks very simple and promising. But day after day you face new challenges again and again. – Thadeus

Some of them are solved with tricky LINQ and some only with pure SQL. You spend a lot of time to learn what you can do with EF and what not. The more workload of the project grows the more LINQ queries show their inefficiency and you have to rewrite them in pure SQL too. Then you realize that you have to turn off its default auto tracker and use some third-party libraries like EntityFramework-Plus to make it work faster. And at the end you think what's up? Why I've chosen this full ORM and spent a lot of time to learn it and now I use it only for simple queries?! And then you find Dapper. – Thadeus

@Sergey: Agreed; but there are few other aspects as well to be considered. Please read this blog. – Amur

@Sergey: "And then you find Dapper." great choice. IMHO, there are scenario where full-ORM, micro-ORM and hand written query play their role and have their importance. It is design decision what to use and what should we expect from it. – Amur

I've read the article. I wasn't saying about ORM with any scorn. On the contrary, I think that people did a great effort to make it work in the best way and it really works as well as it can at the moment. What I really meant that indeed you have to learn both SQL and ORM. And Martin F. says the same and even more - you have to learn SQL first. What I want to say that this should be warned in large print in every book and article for beginners who are just going to start working with DB via EF. But it's not and as a result, a novice gets into the misconceptions and false expectations as I did. – Thadeus

Even taking into an account all this I wouldn't recommend anyone use some more complex ORM than Dapper, except for the small sites or blogs. Maybe I'll change my point later when I get more experienced. – Thadeus

Answer 2

I read many articles which says that Dapper is faster than Entity Framework

The problem with the most of the benchmarks on internet is that they compare EF Linq to Dapper. And that's what you did too. Which is unfair. An auto generated query(EF) is often not equal to the one written by a good developer.

This,

IEnumerable<Employee> emplist = hrctx.Employees.ToList();

should be replaced by this.

IEnumerable<Employee> emplist = hrctx.Employees.FromSql(@"Select * From Employees").AsNoTracking();

Edit:

As pointed out by @mjwills, below is the results table for insert, update and select statements.

Dapper is outperforming EF Core 2. However, it can be seen that for EF plain queries, the difference is very minimum. I have posted complete details here.

Rainout

EF Linq to Dapper is the most accurate of comparisons. How many devs do you know that hand-craft their queries when using EF? Ef Linq to Dapper is usage-based apples to apples comparison. – Aprilaprile

Answer 3

There is no problem to mix them together. In my current project I'm using Dapper for selecting data and EF for creating and updating and database migrations.

Dapper becomes exteremely helpful when it comes to complex queries where more than two tables are involved or where there are some complex operations (joining by more than one column, joining with >= and <= clauses, recursive selections, cte's etc) where to use pure SQL is much easier than LINQ. As I know, Entity Framework (unlike Dapper) cannot use .FromSql() method on custom DTO's. It can map only one table that should be in your database context.

Merriment

The last sentence isn't true for EF-core's current version: 2.1.1. Also, FWIW, I don't agree with the start of the second paragraph either. Writing complex queries is a lot easier in LINQ than in SQL, esp. when using navigation properties. Finally, this doesn't really answer the question why EF is faster in this specific case. – Claustrophobia

@GertArnold I cannot find the way that I can map raw SQL query without DbSet #45698859. I you know how, can I ask you where to find how to do that? As for what is easier to use, it's a matter of taste. Just in my experience raw SQL is more flexible. For me. – Merriment

Not entirely FromSq, but: learn.microsoft.com/en-us/ef/core/modeling/query-types – Claustrophobia