Making Entity Framework Core As Fast As Dapper
Vložit
- čas přidán 5. 04. 2023
- 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 show you how you can use Entity Framework Core's Compiled Queries to achieve very similar performance to Dapper. This technique optimizes both speed and memory; the results are remarkable.
Workshops: bit.ly/nickworkshops
Don't forget to comment, like and subscribe :)
Social Media:
Follow me on GitHub: bit.ly/ChapsasGitHub
Follow me on Twitter: bit.ly/ChapsasTwitter
Connect on LinkedIn: bit.ly/ChapsasLinkedIn
Keep coding merch: keepcoding.shop
#csharp #dotnet
![Eminem - Houdini [Official Music Video]](http://i.ytimg.com/vi/22tVWwmTie8/mqdefault.jpg)
It would be really valuable imho to see more complicated examples, not just for performance reasons but because I'm just very curious about how more complexity might change any of your approaches or design considerations in something that is closer to real world scenarios. Sometimes I come away from the videos and courses learning a lot, but not necessarily seeing how my design could really look like in a larger picture.
Agreed...him advocating for FirstOrDefault over SingleOrDefault because of the difference between looking up 2 records versus 1, is saying "don't worry about logical errors...we want another 1 microsecond of performance!"
I'm particularly interested if this approach is feasible in a filtered pagination scenario. If it's not, it would seem to have a limited use case for scenarios where performance would seem to matter more.
Yes, a follow-up video of more complex queries would be helpful, as it will paint a clearer picture for those looking to choose between Dapper & EF Core.
The fact that EF doesn't automatically do AOT compile for queries is really astonishing.
My exact thought. I just assumed this happened under the hood by default.
Dear Nick, i love your videos and just became a patreon because of this. I love these small videoes with ready to use stuff, 1 focus, which makes it easier to implement on an every day to day basis! Thanks for your content!
Hi Nick, thank you for the Benchmarks! Great information as always :) Please make a video on more complex queries. Take care and enjoy the Easter Holidays.
top drawer video... A big YES for follow-up video of more complex queries like joins etc... Once again you're putting out awesome content Nick!!!! My fav CZcamsr and trainer
Nick, continue with those videos.
They are super interesting. Thanks for debugging it.
You are a star!
As compiled queries were readily available in LINQ to SQL, it's interesting to see developers on EF Core disregarding the use of compiled queries (nor even questioning whether it exists at all, leaving valuable CPU cycles on the floor). Basically, all it takes is curiosity and peeking at the mechanism that translates C# based LINQ queries to SQL, and the subsequent materialization of data to typed objects. That path should lead to questions on whether the continuous translation could be omitted (i.e. cached, parameterized), and thus end with the discovery of compiled queries.
As a freelance consultant, absence of compiled queries on contracted projects are usually the norm, and it's usually a quick win to implement.
Disclaimer: compiled queries are more heavy during application start-up, obviously, as the expression tree needs to be evaluated and translated, but they're clear winners for the majority of applications that are online for hours or days on end. Nick mentions this as well, but given the amount of memory available to app domains these days, compiled queries should be the default approach imo.
They could make it a setting too. So you could have runtime queries while developing, compiled queries in production (assuming no weird discrepancies between the two)
Or use dapper and its even faster. And simpler. And uses less memory. And quicker to startup. And easier to understand the queries that are used to call the DB, no surprises in the generated sql.
@@gbjbaanb I am a Junior developer and I can't understand how you even can able to use Dapper in a real project... For example, you have to update an entity with some many-to-many relationship. How should I implement the Update() method in my repository? For now I just delete all records in link-table and then create them all again. How it can be faster then EF Core? EF Core see that you for example delete one item from the collection property and add one item. And it generates the appropriate sql query.
Or, even simpler example. Let's forget about many-to-many. Just a simple Update() for a small model. With Dapper you will write the SQL code that updates all the fields. EF Core will update only one field.
I am sure that I am wrong somewhere and maybe my question is even some kind of stupid, but can you explain it to me please? Grats!
@@gbjbaanb Yep. Dapper is much better and you have complete control over optimisation
@@user-qp8bt7gq4b EF Core translates to the same t-sql language you use in dapper when executing queries. So anything you do in ef core can be done in Dapper you just need to look at the output query EF core produces.
In 2013 I needed to used compiled queries for a Windows Phone application, something like you did to cache the queries to a local database. I'm glad to see them again in the wild.
Nick, this is excellent content, thank you. This feature gives me the best of both worlds - the convenience and feature set of EF core, and the performance where it's needed. Also interested in your thoughts on the new EF Core 8 raw SQL queries for unmapped types, in the same context of opt-in for squeezing performance, and what the limitations or gotchas are. Thanks!
Very good, thanks Nick! Now waiting for AsNoTracking() explanation)
Hi, Can you make scenario where stored procedure from database side is integrated into Entity Framework
Ef core power tools visual studio extension
Are you asking how to do it? Or if there's a similar performance overhead to be fixed?
Amazing video as always Nick.
In past we moved project from EF to sql procedures (this required lot of business logic rework). Performance hit was huge from EF for us, but good to know of things keep improving from EF Core.
Test with even more and bigger advanced queries please. Love you and your content! also very intrigued about ef vs dapper in medium to large applications.
Nice !!! Please keep continue with series of EF vs Dapper!
I use EF alot but still don't know all its perf aspects
thank you
please - more videos on this... brilliant
I want see more videos about that! Love your content Nick!
Looks like an awesome addition to make to the EF repository wrapper
Super interesting and useful info - thanks for this and all your other content!
I'd love to see some examples of more complex expressions with benchmarks!
@Nick Chapsas , would it make a difference to use FromSql(), FromSqlRaw() from EF ? While using native SQL for Dapper, it would be nice to see, how FromSql() performs from EF, excluding all LINQ
Next EF video will cover this
EF core 8 preview has sql unmapped types, pretty much how Dapper does it. With that you won't even need Dapper.
How about FromSql but also compiled?
Great content, I am looking forward to fromsql syntax benchmark
My team keeps track of the top slowest queries. Another great tool to have under our belt to optimize these.
Awesome video. We definitely need the more complex scenarios.
Also can you create a comparison calling a Stored Procedure?
Awesome tips, thanks!
Would be interested in your take on options such as lazy instantiating of compiled queries so that you defer the hit on performance from startup to first use, and on using in-memory caching and cache expiration of queries in cases where they might be used in bursts but then not be required again for some time, to help control memory footprint in large / long lived apps.
Lazy loading ... eish, I always fall for that trap, then I remove it :)
If you manage all the code, then Lazy is fine, but someone always iterates a list somewhere, and they love the fact that the nested references are loaded via lazy loading .... until they run it with real data. where there is more than two records :)
But yeah, I've never tried Compiled Queries with LazyLoading.
Might be wrong, but i think it will cause havoc if there are circular references in you poco classes
@@stoic7810 I don't think Mike Insch meant Lazy Loading of entities. I think he meant to use Lazy for compiled queries instead of Func
@@stoic7810 Yes, Lazy wont create the instance until it's called the first time, so it wont be as big impact on your startup time.
Please a video where you compare dapper and ef core with complext sql queries (joins and stuff). Nice video ! I love it.
EF core 8 now supports query execution and directly maps the results to VM or Dto such as dapper
Wow Nick, I loved this video! Thanks a lot...
How do I pass the CancellationToken to the compiled query? Since the lambdas inside use synchronous methods I cannot simply pass the CT there. How can I pass one, and ensure the query is still cancellable?
Why would u add cancellationToken if it's not async ?
I always learn something from these videos
Sounds like the expanded video about optimizing more complicated queries with lots of joints and counts and the like would be exactly what I have been looking for 😅
I would absolutely love to see examples with relations
I just subscribed to your channel, really enjoying your videos because you seem to emphasize application performance, something I highly value. I'm new to the field and have a lot to learn. I don't want to be offensive, as English is not my first language, but if possible, could you speak a bit slower? It's not your speech, it's just me being a bit slow, hahahah. But your content is great!
Didn't know about compiled queries, please do the video about the more complex scenario's!
Awesome! I didn't know that.
Great video, thanks
Personal opinion on this kind of subject is that if you have the experience to know when something like compiled queries is necessary then go for it. Otherwise I tend to go with the "it isn't a performance issue until it is one." If something becomes a performance bottleneck for one reason or another then use a profiler to find where it is, diagnose the root cause, and then optimize for your needs. I just ran into this one in a project I'm helping with where they were rending ~150 (at most) extremely simple polygons (7 or 8 points at most). I wrote a conversion function that would give you the polygon when you asked for it. In areas of the program that needed a polygon I would ask for one and do with it what I needed. Performance was perceptively instantaneous and memory usage was well within acceptable parameters.
The argument was around the idea of "we should cache it on the way in so it is calculated and created once at most. It'll perform better that way." I benchmarked it (with DotnetBenchmark of course) and doing the operation that produced a polygon 3x as complicated than needed an insane number of times (they would never get close to) the results were 7.4ms and 10MB of heap allocation. In some contexts that is insanely slow. In this particular context it is insanely fast. Unfortunately it was multiple days worth of discussion even after I shared my results. I'll never get that time back that everyone could've spent doing more productive things.
TL;DR
If your experience tells you this is the right time to do something more performant have at it. If you don't have the experience then focus more on how clear your intent is being expressed. Sometimes code that is easier to read and understand is worth more than code that executes faster. To steal a quote I read from someone somewhere on CZcams "if your code doesn't work I don't care how fast it doesn't work."
Yup, you are absolutely right. In fact, the very reason people use Dapper is not query compilation time - it's that EF sometimes does bad job when translating LINQ to SQL for complicated queries. And I hear people say: "well, you can guide EF to do better job" - and that's true in most cases, I can spend couple hours or even days trying to "guide EF" or I can write SQL query in 15 minutes, but who would want to do that, right?
Also you could show some samples of how to refactor classic existing scoped services from DAL (repositories etc) to support compiled queries. That would be great!
Thanks a lot 🎉❤
Could you also Test linq2db against both? It claims to be one of the fastest ORM Mapper.
IT would be great to see this. Also Linq2Db can be used together with EF Core as a synergy where it gives you possibilities to use all the features from both ORMs, which is just fantastic.
linq2db is great!
nice, and we need more ef stuff
Hi Nick, Thanks for the video, can you please make a more complicated example video with joins...
yes plz, explain it, and how hard to said compiled query is translate linq like script to sql as same as you directly write it , then cache and reuse it ?
I'm probably missing something, but as per benchmarks you may benefit from compilation even when doing it in the same method, right before executing.
Can compile be used in cases where the query is built at runtime based on filters chosen by the user li,e in search forms? Thanks in advance!
Would love to see a video on Expressions with EF Core
I wonder if EF cache expressions? So if you call _context.Movies.FirstOrDefault... it will eat some memory & time for the first execution, but does EF need to do it every single call afterwards?
Wow I didn't know you could do this. I'm thinking whether it's difficult to implement this for when you have a query that may apply certain filters or not depending on the request. Is there a way to chain this compiled queries like returning and IQueryable or it makes no sense?
More Complex scenario please :)
Would be interesting to see Linq2DB performance comparison/walkthrough also.
Great video Nick! Please keep the EF Core vids coming, they are sooo good!
If you dont control the db structure, lets say different team will be incharge of db and how the db model evolve and you still need to access the db writing different complex query from moultiple tables inculding update and inserts based on those queries would you still go with EF/Dapper way or choose lower level such Oracle.ManagedDataAccess incase of oracke db?
We definitely want 😊
Isn't precompiled queries only for speeding up the first time you do that query? When not using precompiled queries it is compiled and cached the first time and then subsequent queries will be fast. Or am I wrong?
great point, I asked myself the same question
Wondering about this too
The difference is that without compiled queries, even after EF caches the compilation result (i.e. the SQL), each time you send it the same query, it still has to traverse and examine the expression tree in order to determine whether or not a cache entry exists for that particular expression tree. With compiled queries, on the other hand, this step is eliminated.
@@amirhosseinahmadi3706 thanks for the answer! So that is why we see the difference in the benchmarks.
Hello @Nick Chapsas I have one question
public static class OneEntityCompiledQuery where TEntity : class
{
public static readonly Func FirstOrDefaultAsync =
EF.CompileAsyncQuery((DbContext context, Expression predicate) =>
context
.Set()
.AsNoTracking()
.FirstOrDefault(predicate)
);
}
Can I do something like this for more generic use of the CompiledQueries? To pass the where expression as a parameter ?
It is valuable addon info to me
Nice video Nick 😀
Question for you:
Based on my experience with EF Core, the main issue I had was to generate complex reports. When you have to join a lot of tables, do some aggregation, group by and related stuffs. Do you have any good practices to shared with use. Especially when you want to avoid as much as possible stored procedure or plain raw SQL
I found dapper when s complex join i was trying to implement in EF failed to generate correct sql. It was a bug and might have been fixed by now, but if EF fails like this, then you're SOL. The more complex your queries the greater the chance you'll have to write sql anyway. EF is great for the simple stuff.
Are there any drawbacks to using compiled queries besides the issues mentioned in the video?
Great video but does it worth the effort ? In my projects I just combine them - EF Core is great for Save operations and simple queries and Dapper for Stored procedures (mainly complicated read opperations). I checked to see that this is a practice in many other projects and fiths well in the CQRS pattern.
Even if you make EF Core faster - there are some restircitions how stored procedure can be executed, you don't really need to loose time with this.
Does these make more sense in simple or complicated queries or does it depend on the frequency they are executed?
Would it be sensible to use Lazy or AsyncLazy to defer initialization of the compiled queries until they’re requested the first time?
I'm curious what Benchmark is doing. It's my understanding that EF Core has a query cache, and if they see the same query they will reuse it hence saving the compile time.
I think they also have, or will have a way to compile the queries at build time and they are cached in a DLL.
I've never seen the need for these optimizations. But it's good to know they are there.
hi Nick, this is the comment you told me to leave down below
Does EF Team have a plausible solution, looks like yes. My impression is that they like to be always behind. Couldn't they make it behind the scenes?
Is it possible and necessary to use CancellationToken in such requests(Compile Async request)?
If I understand Dapper correctly, I would point out that in Dapper you have the SQL as a magic string. Rarely would this be limited to one line. If it was more complex, it would then likely move to a stored procedures. Which would then likely bleed in business logic with a stored procedure...not a good practice anymore (IMO). In addition, if an update gets involved, we now need a multi table transaction. What would be interesting is a comparison of Dapper and efcore in terms of a more complex db structure. I respect your speed argument, but a few milliseconds here and there are not as costly as a developer with bad coding practices.
Not every big string is a magic string. Especially with IDEs like Rider, I can use Raw String Literals and I have full syntax highliting of the query with checks on per-engine syntax. This is the best case scenario for EF. It only gets worse from here.
Imho sprocs are the best practice. Put all your sql into sprocs and then treat the db as an API. It requires more discipline from coders though, which is why it's considered bad practice 😅.
It's also easy now securable as you can prevent arbitrary access to tables so if a hacker ever got access, they can download your entire user table with a single query. Not so if you only use sprocs.
@@nickchapsas IMO - A string is within the code base that expresses a SQL statement is NOT a good place for a SQL statement. Its a string that contains a statement that a SQL processor has to interpret. Adding IDE assistance in the creation of the string (especially when its relational) means it has to understand the schema to be of assistance, which i feel adds additional coupling (it needs a connection string to do that). SQL statement as strings IMO is just not a good idea from a maintenance perspective. It leads to concatenation to create a SQL string that will select or filter the data as required. I feel that is not falling into the pit of success, but really poor code. Many developers simply want a tabular dataset, and throw the SQL at the engine. Millions and millions of dollars have been spent how to optimize a query. Using procedural logic to first limit the dataset , and deal with a subset, is an effective way to reduce the load on the data engine (regardless if its relational or otherwise). Using a stored procedure is also known to be an issue when it contains business logic. Data retrieval and storage need more thought, and IMO a SQL statement in code isn't something that cuts it for me.
@@snapching I think a lot of your gripes seem to come from your own bad practices to be honest.
@@gbjbaanb - If all you’re considering is performance then I agree with you. When you look at it from a maintenance, testing, CI/CD, and technical expertise standpoint then it quickly becomes untenable. I worked on a project that had over 500 stored procs and updating a stored proc was a nightmare. Changing the shape was nigh impossible because there was no real way to determine who was consuming it and whether they would be affected by your change. What you end up doing is making a new sproc with your slightly different requirements and so you end up maintaining multiple versions of essentially the same thing. Sooooo glad to be off that project 😅
I love your videos and you have taught me tons, but this one isn't working for me. In my app, I'm using the unit of work/repository pattern. I created a benchmark app, that pulls in my unit of work to test my linq vs compiled query. My linq query is 2 to 3 times faster and it uses less memory. The query I tested is a simple select with a IN clause in the where. Any idea why?
another reason why i wish methods has static fields or something similair
GREATE VIDEO , PLEASE MAKE A VIDEO DEEP DIVE INTO COMPILED QUERY
why should i use Find() for querying with primary keys instead of First/Single?
Would using .AsNoTracking improve performance even further?
In these tests it would actually make it worse. Check this video for more details: czcams.com/video/Q4LtKa_HTHU/video.html
@@nickchapsas Interesting, Microsoft says "They're quicker to execute". Common sense would think so too. I am curious now why there are no performance gains.
@@nickchapsas does that video combine no tracking with compiled queries?
we can complie it on demond and cache it, even to save memory cache could have an expiration criteria 🙂
Please make a video about more complicated scenarios, Nick
Why don't you use the EF Core AsNoTracking extension method?
Is this implied since it is a ATO compiled query?
I would like to see speed test generic base repository with derived repository for more complex linq, vs pure dbcontext only
Can I implement order by with EF.CompileAsyncQuery ?
.First is suspect, in the context used by Nick. If you know there will be one result, limit 2 will return the same number of records. If that is enforced by constraint, then the query will typically scan the same row count. If not enforced, then .Single is essential. The specifics are relevant, but don't go replacing all your method calls for performance.
This is very cool feature and in my opinion, that allocated memory is nothing for achieving that performance.
One of my question about this video is: How it fit in specification pattern and DDD?
I think we should put our specifications in infrastructure layer. any idea about that?
Didn't understand how to use asynchronous with this feature. I Try to write something like EF.CompileAsyncQuery((context, id, cancelationToken) => ...ToList()). But cancellation doesn't work (logic - i didn't use it in my lambda). On the other hand i can do EF.CompileQuery((args) => ...ToListAsync(cancellationToken)). But i do not understand how to right
I would personally not put async on methods that just return the Task. Eg the EF_Single_Compiled(). Just skips the whole state machine. Might change the performance slightly.
I have to wonder, but wouldn't a source generator be able to perform the compilation and inline the result at compile time, thus saving on the startup penalty?
I would be interested in seeing if the delegate can be hidden behind an extension method on the DbContext, and what the performance penalty of that would be, and if it can be optimized? I personally feel it's more natural to start off with context.SingleMovieAsync(id) instead of passing the context as a parameter.
My thoughts exactly
May you try to just add MemoryCache? If I remember well, if EF founds MemoryCache in DI, it will use it to cache the compiled query (so you shoudn't use pre-compiled functions).
In web api, would this compiled queries on ejected to the http methods ?
One item you didn't cover is adding "AsNoTracking()" on the EF call. [Pardon if covered in a prior video.] I've found this made a significant different in my EF routines when I know I don't need to do any updates on the returned data.
I covered it in a previous video. It would have made the operation slower here.
Informative. Dapper is not tracking the entity as per my knowledge and for EF core it is on by default. What would be the scenario like With Change Tracking , Without Change Tracking, Compiled query and Dapper comparison?
No tracking in EF in the current tests will make it slower. Check my previous vs dapper video for more details
Have you tried to benchmark FromSqlInterpolated vs Dapper?
Is it possible to use it with Add/Remove/Update ?
I didn't know Dapper, so in comparison. Does Dapper also use static queries like the pre-compile. For me it's make a big difference, if the queries stayed for ever. So for a web-application, not rly useful or better, use it with all respect .
Interesting. But still, its microseconds difference using raw sql spaghetti vs strongly typed LINQ. I would pick EF anyday and use raw sql only when it's absolute necessary.
In dapper example, you use
result.ToList()
Why not cast?
(List) result
Will it differ performance or memory consumption ?
You can’t cast a type down to something that it is not. It needs the ToList to actually enumerate and build the result
@@nickchapsas This time it's not exactly true. Since you left "buffered" parameter of .QueryAsync(...) as true (it is a default value), the result you get after 'await' is actualy a List.
Maybe you can show a video using Compiled Queries with Cancellation Token too
Why ef just not store cache of compiled functions at background then? it will be much more usefull than write compile for each fync
Did you forget to set it default asnontracking? There is a good performance boost using that with EF
Asnotracking would make it slower. Check the previous EF vs Dapper video for more details
Nice
does dappr not do compiled queries?
Nick, could you please benchmark LINQ vs using for loops? doing operations like .Sum, .Where, .Average, .Select, etc...
It just doesn't make sense to create a compiled query for every query you want as fast as dapper. It just makes the code so much more complicated for other users.
I've been a long time EF Core user, and a recent converter to dapper (3 or so years now)
When I was using EF I used compiled queries, and when I really wanted to make things faster in EF I used stored procs.
If I want to churn out an app fast I use EF Core for Insert, Update, Delete ... and dapper for Read. If I want a performant application with human readable code I use Dapper. ... and if I wanted to annoy everyone I used readers and writers :)
The one thing that is very unclear - do you use AsNoTracking mode for context or just forgot to add this into benchmark?
WOWOWOW 😮😮😮 It's realy impresive!
Nice :)