For those asking about Compiled Queries, I originally had them in my notes but I totally forgot to record them. Sorry for that. That's what getting a single item by the PK using compiled queries would look like: | EF_Single | 34.855 us | 6.8 KB | | EF_Single_Compiled | 10.325 us | 2.75 KB | | Dapper_GetById | 7.302 us | 2.23 KB | That's what filtering would look like with compiled queries: | EF_Filter | 32.77 us | 7.84 KB | | EF_Filter_Compiled | 12.98 us | 4.24 KB | | Dapper_Filter | 12.60 us | 3.84 KB | As you can tell, it's way closer to Dapper in speed and memory. Do keep in mind that they are NOT free or a silver bullet. Again, sorry for not adding that part in the video. I will be making a dedicated video on them to address this.
@@xelesarc1680 EF is great for not worrying about SQL for basic operations you might want to do. Dapper is awesome if you need performance or if you need highly specific GET queries where writing the SQL is actually easier than dealing with EF. It's not at all strange to have one technology for querying data and a different technology for command endpoints.
Great video as always. So am I correct in assuming that most of the overhead in EF comes from translating the Linq expression tree to SQL and that using FromSql or FromSqlInterpolated would perform about as well as Dapper?
yes i would like to see that too, iused all read query with as no tracking i think its more faster then i see this video i want change all my read to dapper
@@MRender32 Oh, wait, you do want that? So copy and paste your code three times, then you will have a project with three sizes of what it is now. If you want to to it with build process you can do a lot of things, Grpc was doing this a lot before source generators was even a thing, even microsoft itself, and fody, just pick a tool and go for it. But at the end, I think your commentary should not be exactly there.
EFCore won't be able to go reflection free without the work of 3rd party authors. while EF core is generating some Expression-trees, which could be removed by generating the exact code neccessery beforehand in many cases, the expensive part is still the translation from said expression tree to 3rd-party SQL language. thus any Db-Adapter like MySql/Maria, Postgres, Mssql will need to be source-generating aswell, what we learned so far is, source generators are non-recursive, they can't modify existing code or see other source generated code. In the end, we would have to pre-generate the expression trees, and use typed queries to be able to go full source generating. which would make queries look more like `db.Users.QueryByFirstNameAsyn(firstName)" where QueryByFirstNameAsync is an extension method that uses a partial UserQueryByFirstName-Class which is source generated to include the exact neccessery Ado.Net code. But this still only is the db access part and quite easy. EF Core does a fair bit more than that.
I'll continue to use Dapper, thanks. The last time I used EF I was writing esoteric extensions to make sure EF produced correct queries and not something monstrous with unnecessary joins and other clauses. It was a waste of my time. Nowadays I just write clean SQL and map the results with Dapper.
To be fair EF has improved massively through core6 and core7. We were seeing some odd queries with nested selects etc, but it’s all pretty clean these days. Also, I’ve been surprised to see some of the query optimisation it does. It’s come a long way.
Very much interested in seeing more about ef core. Particularly the AsNoTracking() and AsSplitQuery() options espesially on objects with complex relations
Thanks for the video. Just one remark, before everybody runs away to refactor the data access layer and switch from EF to Dapper: Can we let it sink in for a moment, that these tests provide measurements using nanoseconds or microseconds? I'd suspect, that the actual overhead introduced by the ORM is mostly insignificant, as soon as your ORM of choice has to speak with something that's not living on localhost?
Haha good point.. our company gave us a database server so slow I suspect it's a 386 with 6mb of RAM. Developing on my local PC, a process may take a second whereas running it on the remote DB takes a minute or more.
I will be happy to see more comparisons for complex queries like joins and grouping or try new EF Core features like ExecuteUpdate and ExecuteDelete, I think in those areas the gap between them would extends. Maybe I'll perform those benchmarks myself.
Amazing what having some (partial) competition in the form of dapper has done for encouraging EF to get better. I'm sticking with dapper and hand-crafted SQL though.
I think that Dapper's problem is that it is dependent on the syntax of the database being used, whereas using EF (assuming you have the drivers), you can change the database under the hood without having to change all the SQL statements (since they're not used), but at the cost of some inefficiency. Please correct me if I have made any inaccuracies
@@dwhxyz I agree with you on this, generally it remains the same, but it's not guaranteed that it won't change in the future. Additionally, you may have different types of databases (e.g. in unit tests) and want to use the same methods but with a different database that may lack some functionality. The convenience of not having SQL logic in the backend and leaving everything to the EF libraries to manage this part is certainly there, allowing one to focus only on functionality. I use both Dapper and EF, and to be honest, Dapper is convenient in some ways and not bad, but in terms of clean code, refactoring, and eventual (Although, as you said, it is quite remote as a choice or as a phenomenon that can occur.) replacement of the database, EF remains, in my opinion, an excellent framework.
@@dwhxyz Well, every couple of years? I've worked on 3 different projects that had to support exactly those backends that the customer provided and that had been certified by the authorities.
@@lupf5689 On the whole it is the exception. It's very rare and unusual for a large organisation running a large enterprise solution to change their backend database during the life of that solution. Regardless - given the choice I personally would not waste my time with EF.
@@dwhxyz I agree you!!! I am using EF at the moment... but just because it was already there. It is definitely not my favourite. When someone wants to use EF or dapper... come up with reasons "why" one over the other but NOT because of a DB vendor lock -in. Chances are that if your company switches from one DB to another say from Oracle to MSSQL or even from RDBMS to a NoSQL.... you are doing it for some significant reason. And it's "nice" that you could "just replace" the underlying DB but it then does not guarantee that you stick with the exact same performance. And that even implies you CAN actually translate your DB schema from one DB to another.
Interesting results… we use EF as a SelectOnly provider to dynamic request. As we dont want to “build” a query dynamically, we use EF to built if for us. The rule is : if it’s a static request : Dapper, EF otherwise. I would be very interested to see how you operate with dapper and dynamic query that can change with querystring filter for instance.
Interesting comparison, but in real world examples, a proper ORM always beats having to mix business logic in both your service tier and database tier. Additionally using NHibernate (and to a lesser extent EF Core), you have the best of both worlds as you can use your mapped classes for general CRUD and when you want a more efficient query, you can use Criteria or HQL queries or with EF as well you can use SQL queries, which is then effectively the same as using Dapper.
To be honest that's pretty impressive, EF core is really good as of now, but i don't think it will ever match Dapper in performance/efficiency. Simply because EF Core has way more stuff to offer that makes your life easier, i don't mind if it's 20us slower than Dapper
In my opinion, EF enables quicker coding and simpler maintenance, although it may not be well-suited for extensive projects, it is perfectly suitable for most business applications.
EF is wrapping a lot more. With dapper you have to do a lot of the general query generation, the template of it. If performance is your #1 goal, sure, it's better. That loss of performance I've easily become OK with it because of the very short onboarding of team members due to less complexity of EF, and the lesser amount of mistakes coming out in pull requests.
we are the odd ones out at work and use nhibernate :D but interesting to see them compared, I thought EF Core was a bit closer to dapper, but I guess it's only to be expected.
Год назад
There's something that is totally left away in this tests, which is initialization... while dapper has none, creating the DbContext will take its time, and that, specially when you're working with AWS Lambda or Azure functions is a very important performance hit in the cold start cases...
I was waiting for you to compare the new EF methods such as ExecuteDelete and ExecuteUpdate with dapper methods, these two new methods are insanely fast compared to the old way of EF core.
I'd love for you to compare linq2db, When I tested it years ago it seemed to me to have a lot of the benefits of EF but the speed of dapper, or much closer to that speed.
Would be interesting to see what you'd see with ExecuteSQL in EF Core which would be a simple way of having only one toolset that got you most of the way to dapper.
Hey Nick! Thanks for the comparison of these. In a previous video (sorry can't remember offhand which one) you had mentioned that you avoid using Stored Procedures, and instead use an ORM. You said this was because using SPs encourages poor programming practices. Is it possible you could visit this topic sometime and explain what poor practices this encourages? Thanks for all the valuable insight you provide!
Many moons ago stored procedures was the norm. The main reason was because you could secure the application account to be able to execute stored procedures only. This gave a nice layer of security should the application account be compromised - an attacker would not be able to dump or manipulate the database directly. It also meant that an application could not feed intentionally (like EF!) or unintentionally (due to a bug) the database with horrible SQL statements or harmful SQL statements. The DB admin could either write the stored procedures themselves or review any that were written. It also gave the added benefit that in a live environment a DB admin could update stored procedures on the fly without deploying application code should the immediate need arise. I'm not convinced moving away from stored procedures was a good thing but most people have been conditioned to believe it was based on stuff they are told or read/watch on the internet!
@@dwhxyz I used to write applications with Access front ends that were entirely run by stored procedures and triggers. There are certain things to be careful about when doing it, but it worked when following those rules. That just an extreme example from long ago, but these days I prefer to keep my SQL in my database code project as either views or stored procedures and have my C# code without any SQL in it. Just a personal preference and it works fine for me.
@@haegor I also did a fair amount of MS Access front end with Sybase/SQL Server back end work back in the 90's. I remember when I first installed and MS Access V1.0 in ~1992, connected it to Sybase followed by generating a few forms with the wizard. My mind was well and truly blown that I could all that without a single line of code!
@@dwhxyz It definitely was the norm. If you are around for a while, like myself, then you know this! The points you are making were indeed many times the drivers. And people who have never experienced stored procedures that way don't understand it. This is/was also especially powerful when 'consumers' is not only a programmer/code but you also were using things like MSAccess or Excel to query into databases. And another MAJOR point was that networks were REALLY slow and workstations were also underpowered and you were better off running an expensive/complicated SQL statement or set of statements together on the server and only return the smallest possible set of data. And yeah I can see that people today don't "like" them from the perspective that these stored procedures and views are encapsulating business logic, and that reading just the code is not enough. Other than that... even still today! any database access and slow query performance can be addressed with the use of stored procs and/or views.
As someone who hasn't done much with SQL in C# (believe it or not) and it's arguably not the point of this video, I'd be interested in hearing more about this hybrid approach of using both Dapper and EFCore at the same time for their strengths. Is it just a simple matter of using EFCore to generate the databases using its migration systems and then using the model objects created by the migrations for the output objects in Dapper?
No, it's pretty much a CQRS-like approach where you use EF for anything that involves modifying data and Dapper for read-only processes. A very simple example would be using EF for POSTs and PUTs and Dapper for GETs in an API. I say process because most of the time you will need some kind of read before or after a change in the database so for that scenario you would use EF to select the entity and track the changes. I've been doing this for years and IMO is the best approach by far, it makes DDD and CQRS very easy. Regarding the second part of your comment, you shouldn't be exposing EF's models to the outside since they are domain models. The best approach would be to map Dapper queries to DTOs specifically created to display data. This also makes it much easier to map the results since you don't really need the whole entity with nested classes to display simple things like, say, a user and its country. I.E: User.Name + User.Address.Country.Name (Domain Model) vs UserName + CountryName (DTO). You can now simply map the result of the select query to those 2 fields instead of building the whole entity.
For measuring the performance of add and delete separately, you could use Postgres with a table that does not have a PK nor indexes. the performance won't be affected (at least for several millions of rows). But seeing that the performance of both is pretty similar, i'm not sure that this is worth it
EF for writes is amazing. The data modeling and migrations is superb. For reads, it depends. Writing non-trivial efficient queries is hard enough. Adding the LINQ abstraction over that query generation and hoping the server's query optimizer will like what EF produced is too much of a headache. I appreciate that EF added raw SQL as a first class feature, but if I am going that route I prefer Dapper. The hybrid approach give you the best of both worlds with the fewest headaches.
There is no logical justification for using dapper, because the biggest drawback of dapper is writing queries as a string, as we saw in the tests, the speed of update, add, delete is almost equal to dapper, and for queries you can use the EF CompileQuery feature, which It has a speed very close to dapper.
There is something interresting about the result of the first part. Could be cool to see how dapper performs if it used the same sql query as the entity framework find method generated.
Usually like most videos Nick does but this one, i might question some of his results. Testing for perf and other related stuff, and would assert tends to be very hard to do right and might assert for DB stuff this may not be that good. Too many factors come into play, usage patterns, load, concurrency, startup, long haul to think of off the top of my head. To see one operation for doing and add and tearing everything down, only to rise and repeat, could easily see any DB will allocate a lot of memory. Why they are optimistically getting enough memory for its internal cache, real world usage at start up will possibly incur a one time perf hit to help speed up later operations... Honestly the last EF i used was 6.2 or so, i remember on it perf was really bad and spent time looking at various settings, in the end got one of our data imports from 20 minutes down to around 3 minutes. Just seems from what i saw with the benchmarks demonstrated, could easily see where EF would be slower and doing a simple cmd.Execute() that Dapper i think is doing is really light weight, so ya Dapper will probably always win in these. But long haul heavy stress loads with multi-users, which might be more typical in the real world, wonder if Dapper would still be as performant? I like Dapper for the things i've done with it but wonder if the usage patterns changed..., still as performant? Just saying for any benchmark to be valid needs to mirror typical/average use cases.
Its as fast as dapper if you compare with ef6 and scale the graph so that ef core and dapper are close to each other. But if you zoom in, you will see the slowness.
The fact that databases itself are slow is the reason why EFCore is not optimized much. There is just no point as your db will fail first on high load scenario. Usually db over the network responds in 50ms, while EF takes time to compile query and so on for extra 0.04ms.
This. At least once a year one of our junior developers suggests, that we should use ORM X or switch to NoSQL Y after they spent a few days proving that it performs 10x better ... against local host. -.-
I'm actually curious to see this too. This video is a great look into how efficient both EF Core and Dapper are, but that's relative to each other. It would be interesting to see the total picture, e.g. when querying a database that's on another different server.
You could have used an in-memory SQLite database with "Data Source=:memory:". SQL conversion should still be happening, but there is much less speed variance for I/O.
Hi👋 , I apologize in advance for my English. In my practice, I have encountered a sufficient problem. Ef is good at everything except reading data. If you have complex queries, writing from via linq + expression becomes very painful. The power of naked sql is very lacking, and dapper comes to the rescue here. As a result, 2 libraries can easily get along in the project. Everything about reading => dapper, everything else => ef (migrations, unit of work, db configuration, and so on)
Remember to consider whether you really need an ORM and what benefits is it really giving you or if it is more abstraction and complexity than you really need.
@nickchapsas maybe now video about RAW queries in the new EF 8? They return unmapped types with basic SQL queries. It's exactly the same as what dapper do. I'm curious if it'll be a dapper killer feature
@@nickchapsas Fair - but it would still be interesting to see if those differences grow exponentially with the complexity of the query (ORMs are all fun and games until you have to do anything more complex than "select * from pubs where id = 1")
@@b33j4y But where would you stop? I think the answer there is even more opinionated and based on a lot of factors in your application and domain. If the code you are talking about is in some sort of hot path the answer is maybe to use one over the other, or even, both are not sufficient and you need to remodel my bad DB design, in order to get decent speed. Whereas if this is a piece of code that runs at 12:00 am generating some daily report in app down time; well you may not worry so much about slower mapping. You can always continue running a similar benchmark where you will compare EF against Dapper and let them both map two related objects, then three, until you are satisfied. But if, and this is an assumption, Dapper maps faster and more memory efficient for one, I am guessing it will also be faster for two. Based on Nick also isolating the sql operations and that came out pretty much the same; my gut feel tells me EF is mostly loosing time during the mapping and tracking operations, which is why he also put that NoTracking debate to rest.
4:15 SingleOrDefault means single or the default value of what you are trying to get, usually that is null because its a class when getting entities out of a db, but if it is something else like a non-nullable list of ints, lets say IDs, it would return the default value 0 if the int you are getting isnt there.
Well with Dapper you are fiddling yourself with SQL statements. So why do you use Dapper instead of a native (to the used database) library? IMO you use EF in order to get some abstraction in order to not have to fiddle with SQK statements so Dapper and EF isn't the same type of library.
But those EF queries are so intuitive to write. I also question EF core startup time vs benchmark; conventionally I always found EF & EF core heavy at startup
Awesome video Nick! I’m curious about something with EF. How are you managing reporting with a lot of nested of join. That the hardest part to generate something effective and performant
EF did do better than I expected in your benchmarks. The problem is that in real world applications, developers use EF as an excuse not to learn SQL or anything about databases. So it's those who don't understand how a database works that are the ones using EF, and they end up writing bad code that generates really bad queries. e.g. pulling too much from the DB (such as the entire table) then to make it worse they use linq to filter it in C# code for the rows they actually needed. Speaking from my experience on people I work with who use EF that think they are so-called seniors.
I think there are more reason than "not knowing better" for the use of EF or any other ORM. We currently have to support 4 different DBs, since that is what our customers have deployed. Sure as hell, I wouldn't want to manually maintain migrations and a bunch of slightly different queries for each and every statement. That said, we actually do handwrite a limited number of SQL queries, since these are critical for the performance of the whole system and I have yet to see an ORM that can produce something with similar performance. And for quite many EF translated queries, we at least made it a habit to check the actual SQL output to ensure they are at least ok.
I disagree. EF makes for way cleaner code so in a majority of applications the performance hit is worth it, it's not about laziness. Dapper gets messy very quickly if you're doing anything more than simple operations
I agree that everyone who use an ORM should understand database basics, however, SQL is not entirely unlike C/C++ in this setting, great SQL is great, but most developers don't write great SQL, in fact, most SQL production queries I've seen are a horrible and inefficient mess. C# with LINQ greatly increases the base level of quality in SQL queries and database interactions.
@@lupf5689 I can definitely see the appeal if you are supporting 4 different databases. So far, I've worked on applications that use different databases but never more than one type in a single application.
@@CabbageYe That's ok - I guess we'll have to disagree. In my experience, I've found applications that use it hard to optimize. When the application's performance is poor, I'll monitor what queries are running on the database and identify slow queries (to use my example again, where clause is not specific enough and/or whole table loaded into memory) and then suggest improvements to the developers who are working on that application. However, it then takes them a long time to figure out where in the application that query is being executed as there may be several places that table is accessed. If EF is meant to be easier and cleaner maybe they just don't know what they're doing?
Your SSD may be able to do 5 GB per second in long, sustained transfers of large files, but database transactions are typically very small. The usage pattern and performance characteristics are very different. For a better idea of database performance, look at its rated IOPS.
Entity Framework is good at translating the query into SQL, but that obviously takes some resources. I don't know if Dapper can also do such translations or if you must always provide the actual SQL as a string. What will the results look like if you pass the RAW SQL via Entity Framework as you do with Dapper?
This is exactly, in my experience, always the problem with EF. I write a EF linq expression, looks good and then at the generated SQL to then do the face palm and think to myself "like why!?". Only to find myself re-ordering linq methods, which sometimes generates way different SQL, and all kind of other mods to the expression to see if I can get it to generate something that makes sense. Just last week I fiddled with a query for probably a good 45 min, until I decided F that... here is the raw sql which made me go from a query that was timing out to a query that ran under 100ms.
@@paulkoopmans4620 Seems what I thought EF is good at is infact the exact opposite. I wonder are there any libraries that does a good job at writing c# queries which is better than EF. Combining such library with Dapper seems a potential better solution for those not wanting to write raw SQL...
Dapper is undoubtedly faster but for most projects, this performance is not worth it compared to the effort to write this queries. Especially when you have projections by automapper, it's just very convenient and clean
2 sets of comparison be more fair. CASE 1: COMMANDS Compare how EF and Dapper handle commands (i.e non queries). I expect EF core to win overall. CASE 2.1 QUERIES Using EF core in "abstract mode" i.e using the BEST optimized Linq with AsNoTracking and with Projections and With proper use of Iqueryable CASE 2.2 QEURIES Using EF core with "Raw Sql" In short proper use of EF Core vs dapper
Thanks for the video. I am trying to convince my company to use an ORM instead of rolling their own (buggy) data layer. I am a fan of EF, and looking at your benchmarks, in reality these performance differences (in nanoseconds) don't translate into anything non performant, unless of course we scale up massively.
Just prepare some example application with "usual" queries used in your company's application and you can show the difference of writing and supporting of those two approaches. I was in such a situation a few years ago and only real example helped me.
I'd love to see how it compares during the lifetime of an application. I suspect that EF wins overall but the problem is that EF has become quite bloated again and that automatic mapping and SQL generation could be expensive.
Amm it looks not representetive if we choosed to use EF we benefits from complex object parsing, could you compare complex objects with joins, also would like compare bulk and batch updates
@@nickchapsas Yup but it will be not really differenece i mean you should care abount network latency for that cases more then executing time, most critical is complex quering
I often in different projects facing with the issue when we have some foreach and for instance getting some data by id, make some changes for fist entity, and then next one is have same FK for another instance and entity is already tracking issue caught, how do this in right way do you have some video regarding this topic?
Dapper hasn’t been updated in two years, yet EF Core, even with .NET 6 and .NET 7 performance improvements, still can’t match Dapper. According to their repo page, RepoDB is even faster than Dapper .
I don't know why but my result is absolutely different. Changes with your example is I already have data in DB, my class has 6 properties, and I use MSSQL. | EF_Find | 612.6 ns | 11.58 ns | 18.36 ns | 608.9 ns | 264 B | | Dapper_GetById | 224,532.6 ns | 2,060.73 ns | 1,927.60 ns | 225,137.5 ns | 7401 B | | EF_First | 294,439.0 ns | 4,397.88 ns | 3,672.43 ns | 292,793.7 ns | 13865 B | | EF_Single | 383,249.3 ns | 24,299.37 ns | 71,647.25 ns | 417,302.3 ns | 13866 B | | Dapper_Add_and_Delete | 860.7 us | 5.18 us | 4.59 us | 10.73 KB | | EF_Add_and_Delete | 1,070.8 us | 21.04 us | 25.05 us | 35.03 KB | PS: potato laptop and SATA3 SSD
