Entity Framework Core vs Dapper Performance in 2023
Вставка
- Опубліковано 28 вер 2024
- Check out my courses: dometrain.com
Become a Patreon and get source code access: / nickchapsas
Hello everybody I'm Nick and in this video I will compare Entity Framework Core's and Dapper's performance to see which one is faster and more memory efficient and by how much. The results were actually pretty interesting.
Workshops: bit.ly/nickwor...
Don't forget to comment, like and subscribe :)
Social Media:
Follow me on GitHub: bit.ly/ChapsasG...
Follow me on Twitter: bit.ly/ChapsasT...
Connect on LinkedIn: bit.ly/ChapsasL...
Keep coding merch: keepcoding.shop
#csharp #dotnet
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.
Nice👍
so nick i want ask something i hope u read this, do you recommend dapper over ef?
It totally depends, that was his point
@@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?
I would love to see a video about why AsNoTracking is slower and also less memory efficient.
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
I was shocked to see this. I always used this as blogs everywhere show improvement with it.
same here, thanks for the videos nick
I have not seen this in practice. That would blow my mind. Ive shaved 40% off queries by just switching it off for some calls
Are you sure this is correct? AsNoTracking has always been more performant and memory efficient for me
I switched from EF to Linq2DB and never looked back. I would love to see a comparison in speed and ease of use between these 2.
Right choice 👍
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.
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.
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.
I wonder how using Compiled queries would influence the EF Core results. Maybe an idea for another video Nick?
I updated the pinned comment to include the results of compiled queries. I will make a dedicated video on them too!
@@nickchapsas looking forward to that video on compiled queries, sounds interesting
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.
You should've used EF Core's new `ExecuteUpdate` and `ExecuteDelete` methods for the delete and update benckmarks.
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.
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.
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
wonderful analysis thank you!
I would love to see AsNoTracking memory usage explanation as well
Thanks!
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.
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.
Before a year or two we have rewritten our API to CQRS where queries are using dapper and commands are using EF Core. Works flawlessly tbh.
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...
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.
Man you read my mind. Yesterday I was thinking about this. And was planning to check by myself this weekend. Thanks!
It'd be interesting a video about AsNoTracking
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
Very interesting video i prefer Dapper over EF Core. It would be nice to have compare joined queries which can be a litle slow.
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.
What about complex objects with 2 or more joins? I would like to see benchmarks about this too.
What is shown in the video is the best case scenario given that the more complex the expression, the more expensive the operation.
@@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.
@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
I wonder, what if we use directly sql query in ef core(FromSqlRaw etc.) and dapper?
I do not see the executeUpdate in EF, It would be interesting to compare it with the Update in Dapper
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.
But the main point of single or default is to throw an exception if there is more than one.
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)
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.
That language injection thing caught me off guard - never seen that before, where you get SQL keyword coloring inside the string.
It's a feature of my IDE JetBrains Rider. It also checks the database as I type to see that the table names and the columns actually exist
Understanding why No tracking is slower would be good. Also, where do you stand on using Store Procedures with EF or Dapper?
Yes, why is AsNoTracking slower? I would love to see that analysis.
They did some speed improvements in EF Core 7. I might have missed this detail, were the tests performed on EF Core 6 or 7?
Wow I'm kinda stuck on 40-50 for months. How much time do you dedicate to it?
Nick can you specify what versions of EF core and dapper you are using for future reference?
I want to see how LINQ syntax compares on this
Hi @Nick Chapsas Where can I find the video's code? I just want to test it locally and see the outcome.
The code is available to my patreons
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?
How do you get SQL recognition and coloring for your SQL queries that are strings? Is it just Ryder?
Still faster! But for how long? Nice video @nick
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?
Any consideration kept for the LINQ to SQL conversion using EF-Core?
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.
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
What is shown in the video is the best case scenario given that the more complex the expression, the more expensive the operation.
@@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
@@nickchapsas would like to hear about difference between EF and EF.FromSql vs dapper for plain queries
Go for that AsNoTracking analysis please!
thank you!
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
I would love to see a video about why AsNoTracking is slower and also less memory efficient.
It is okay not to used using in dapper statement?
why would you use using?
Can you do an web api performance compare between linux and windows? That would be really interesting
If you use EF could you create a nice course for EF?
Part 2 with compiled and rawsql in EF core?
Compiled is coming in part 2. Part 3 will be something else actually
+1 for the AsNoTracking video
how about updating 1K records in one shot?
How about compiled queries for EF?
Why no tracking was slower? Any context on that?
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.
You can use stored procedures and I think you should for the safety reasons. It works perfectly.
Lots of videos lately, nice
ef for write and dapper for read, that's if for now :)
Shouldn't you be using compiled queries and a proper database? EF was optimized to run on postgresql sqlite is not real the target I think was EnableThreadSafetyChecks turned off? Did you use dotnet ef dbcontext optimize?
Check the pinned comment about compiled queries. A proper database is irrelevant because is the same for both dapper and ef core. We only care about the overhead of the expression to query conversion and execution, which is the same no matter the db engine
@@nickchapsas Not necessarily due to the single threaded nature of Sqlite thats why I asked about EnableThreadSafetyChecks and ef dbcontext optimize.
Nick, with my high respect for your work, IMHO you should maintain yourself far away from the dark side of the force: ORM benchmarks. Dapper != EF != NH and so on. Different use cases mean different tools. If you are focused just on runtime performances you shouldn't use an ORM.
I completely disagree. I think that identifying and recognising the performance difference between the common grounds of two things, is part of the picture. In the same way that would "benchmark" how long it took me to write the SQL queries for dapper, compared to how simple it was to not write them in EF to show how much more productive I am with EF. It absolutely is relevant
@@nickchapsas well... the usage of an ORM in an app is not just about movies. You should try a lot of use cases, with many kinds of relations types (one-to-many, many-to-many, one-to-one). You should try batch massive updates, querying/loading a bunch of objects graphs. If you are using Dapper you have to use some of dapper-extensions to manage persistent-session-state, unit-of-work, denormalization, query-system-flexibility, RDMS-query generation efficiency. To really test performances, you have to know each system in deep and each use case in deep. ORM's benchmarks are just "the dark side of the force"; don't waste your time. Perhaps would be more interesting to talk about widespread persistence (RDMS+DODB+KeyValuePairs+Files).
BTW ORMs benchmarks, could be a good strategy to have a lot of comments in your videos.
Fait vous jeux and keep coding. 🤓
Just typed a long comment and its gone. Looks like this is a another channel where my comments just go straight in the bin. Nick - can you check if my comment went into your review folder ?
It's not there. UA-cam's spam filter is really annoying. It looks like coding related comments get shadow deleted.
So basically, EF is NOT faster than Dapper, just that compiled queries are comparable to it and even then, there is an important tradeoff there.
Nick The greek is Great!
EF loses again.
Not criticizing you vid, just my 2 cents: using dapper because it's faster is definitely a form of premature optimization. Most of the time speed of development, error-proneness, etc. will be more important than 10ms higher performance. Especially in regards to databases, so many things can go wrong and the consequences are often quite bad. So unless you absolutly need the higher performance you should stick to EF Core, imo.
I’ve been working for systems that my read SLAs are below 5ms. For me performance actually mattered especially before EF got optimised with the recent updates.
@Nick Chapsas please show us an example of an implementation of this
Hi Nick
It would be very exciting to know why AsNoTracking is slower and also less memory efficient....
Great videos, thanks for that!
+
+
+
+
Me too
You should have also compared EFcore raw sql APIs for queries as well. I'm also wondering how far could you push it using compiled queries, compiled models, using context pooling, customizing transactions isolvl to read uncommitted, using assplitquery, MARS, or AsyncEnumerable APIs.
I think this video deserves a part 2. Thanks anyway. Keep up your great work. Always love and appreciate your content.
P.s. please explain the asnotracking thing.
AsNoTracking is a way to tell the EF Core that if the entries that were retrieved from the DB would be changed, I don't want it to save the changes.
Presumably, let's say that you make a GET request and after that, for some reason the model that was retrieved from the DB will be changed by some other method that you didn't know.
The next time when you make another request that calls DbContext.SaveChanges() you will also save the changes to the model from the GET request that you've done.
Now, AsNoTracking it's faster because, when doing a query from the Database, EF Core has something called if I remember correctly "Tracking context". That context also does caching for already tracked entities, but also adds an overhead to your query. If you do AsNoTracking, then you won't have that overhead of adding the entities that you want to the Tracking context or however it's called
There is no point in comparing raw SQL queries in EF Core because it defeats the purpose of using EF Core in the first place. Context pooling would also make it worse because here we are reusing a single DB connection on serial requests. Read uncommitted is a DB-specific setting so it's irrelevant.
I have the same thoughts on comparison with raw ef calls. If those results are comparable I do not see any reason why someone would use dapper.
Also what about places where more complicated readings needs to be done. As with dealing with complex writting down to database, having easy way to write complex reading is also benefitial.
@@nickchapsas Well, there is db.Database.SqlQuery(someSQLQuery) which maps the result of a pure SQL query back to entities. That is pretty handy for some use cases and surely has it's place.
@@nickchapsas I don't think it defeats the purpose of using ef. It still does the model mapping which is the sole purpose of using dapper in the first place. So if ef provides that means out of the box, it should be given the fair shot.
You are correct about the pooling on the context of the current benchmarking scenario though.
Do a video about AsNoTracking pls
I think when EF Core stops using reflection entirely and move into source generators we would see a huge drop in the memory consumption
i won’t be satisfied until the build process triples the assembly size of my projects
@@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.
We decided, when implementing CQRS, to use EFCore for commands and Dapper for queries, it seems we made the right choice.
I worked on a project like this and the dapper queries worked perfectly. However, as usual the EF side of things ended up being a mess and a detriment to the project - just the same as every project that uses EF I've had the misfortune to work on! Just use Dapper everywhere and put EF in the bin!
I think this is reasonable. EF works well for tracking changes for saves. But it is a nightmare to work with for queries that are anything more than get by id
I personally like the detachment, if my application requires rebuilding and deploying because I need to change a query, it'll drive me insane. So I stick with Dapper and Procs, just makes more sense to me at scale. Fine use EF to prototype but in production, and at scale, its more of a pain.
@@dwhxyz i think you use EF in the wrong way. In what case is better the raw query instead of the EF Select(x => new {.. method, with autocomplete, reference, ecc? Yes if you add a field you need to map that field and rebuild the project but probably that field have some logic and you need to rebuild your project in any case, for example adding that field on a form view. With EF your code is more OOP instead the classic row querys that are "just strings"
Yep, this is the way
Very much interested in seeing more about ef core. Particularly the AsNoTracking() and AsSplitQuery() options espesially on objects with complex relations
How big of an impact on overall web app speed this has in real world setup (the database is on remote server)? We are still talking about micro seconds here. Gaining extra 30 micro seconds of speed when added to something like 20-50ms on top of the time needed to execute a query over network and receive the response is almost not noticeable. 20ms + 10/1000ms vs 20ms + 40/1000ms. It ends up 20.01 vs 20.04 which is not that drastic at all. To me memory allocation is bigger factor here. If I can save 8-10 kb of memory per request that is huge, especially if we have high traffic.
here we go again :). This argument always comes up when Nick does one of these measurements. Your suggested real world setup, with your API running on server X and your database on server Y on a (local) network or even remote somewhere on the internet. While that is definitely "A" scenario, it is not the only one!! If you run your webAPI inside IIS on the same server with MSSQL then that network latency immediately goes away. Every developer knows that these types of setup are notorious for adding latency. Your network administrator might not like it but from the application and system design you don't want any of these types of latency. And then the other thing to think about is that others are doing this in cloud environments where such latency may or may not be as bad either. That is why Nick explains the system in the beginning, and that the read and write speed to the database is eliminated. If that becomes part of your system design where in the cloud you can be guaranteed to have something back from your database in 1ms or even less, then the additional time is way closer and therefore more significant. Also in the cloud, depending on your setup, when you work with virtual processors. if something gets achieved in 40 microseconds or 80 microseconds it translates to vCore usage and basically will be twice as expensive. You can run twice as much of this query now without having to scale, which effectively saves you money
Really nice unoppinionated comparison!👍
What I'd like to add is that the real downside of EF Core are Bulk/Batch updates, e.g. Upserts for 10k rows (you can make it efficient with it as well but the API for this is very clunky imo).
I suggest you make an overall ef vs dapper video, suggesting why and when to use one over another.
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.
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.
And it's not like EF could have evolved from then.
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.
Have you also tried using linq2db instead of dapper? It's goal always was to be one of the fastest ORM Mapper with linq support. In some scenarios it also was faster than dapper.
Sincerely if you care about speed you will most likely not use an ORM. However its really good to see these benchmarks.
Hi Nick, querying tables and including FK relationships in the results via Dapper always feels very bloated vs querying FK relationships with EF. Since you prefer Dapper for Read-queries, I would love to see a video on your hybrid setup with a more complex database structure
What is it like when you execute raw SQL queries in EF Core? Because if its negligibly different or even better then surly there is no point using Dapper at all.
Okay, i love Nick videos and i could learn a ton of them, but sometimes i feel that instead of actually being neutral and teaching us new tools or good ways of doing things he does try to "sell" us something, like Mediator on the Events video.
Even't were doing exactly what he told them to do but he complained that it was a wrong behaviour and shoved mediator to "fix it".
Here i feel a bit the same with Dspper i see it as an interesting tool and a performance target for ef core but the test made were not fair.
On ef core you can execute raw sql too if you need and it wasn't tested.
More opinions here, even then, is the code in the backend good enough to actually have the bottleneck im the orm? Usually on the apps i have worked on the bottleneck was either on the database itself or in the logic at the backend.
Having 2 ORMs instead of one means managing 2 hells instead of one.
Dapper maybe it is still relevant because it "secures" jobs for some developers that are focused on dbs, those that relies on stored procedures for everything because otherwise i see no real point om why would i want to use dapper myself especially when i can avoid jumping between languages.
Feelings apart, good code on any of them will be most likely good enough for most of us
I would like to see a part 2 where you compare with Linq-to-sql and even raw sql (no Orm)
Comparing ef core to dapper is like comparing a simple knife to a Swiss army knife. Especially if we think about writes you should add some explanation why ef core is less memory efficient, what is it doing behind the scene (tracking changes of entities, generates query based on changed fields and so on). Without understanding trade off in functionality vs performance (speed and memory efficiency) this is purley academic discussion, being far from real life scenarios.
what about linq2db?
it light weight, but without any raw sql, i think it's better approach
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.
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.
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
How often is the backend database changed ? I've never seen this happen in my nearly 30 year career!
@@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.
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.
A video about AsNoTracking would be great!
My last job we used EF6 and I thought it was slow. My new job we use Dapper and I think I love it. It forced me to learn more SQL.
Fantastic comparison. Looking forward to seeing the video about AsNoTracking
Atleast EF CORE is catching up to DAPPER. 🎉 unlike before EF is so slow.
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.
You compare apples to oranges. Compare Dapper to EF Sql Interpolated. I also miss Upsert benchmark. And Bulk operations.
Not at all. I am comparing two ORMs in their critical paths of how people use them. This was always the argument.
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.
Is there really value in micro-benchmarks and differences measured in tens of microseconds? Is the time spent hand-rolling all SQL queries really worth the tiny improvement that you won’t even notice if you add all the other logic and overhead?
Depends on your performance requirements. For me it was, yes.
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 .
RepoDB will get a video of its own at some point
@@nickchapsas Thanks, looking forward to it, love your channel!