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.

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.

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.

EF core 8 now supports query execution and directly maps the results to VM or Dto such as dapper

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 KZfaq "if your code doesn't work I don't care how fast it doesn't work."

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

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!

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.

I'd love to see some examples of more complex expressions with benchmarks!

Nick, continue with those videos. They are super interesting. Thanks for debugging it. You are a star!

@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

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?

My team keeps track of the top slowest queries. Another great tool to have under our belt to optimize these.

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 KZfaqr and trainer

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.

Please a video where you compare dapper and ef core with complext sql queries (joins and stuff). Nice video ! I love it.

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.

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 😅

Looks like an awesome addition to make to the EF repository wrapper

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

Could you also Test linq2db against both? It claims to be one of the fastest ORM Mapper.

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!

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.

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.

.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.

I would absolutely love to see examples with relations

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.

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?

please - more videos on this... brilliant

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.

Would using .AsNoTracking improve performance even further?

Didn't know about compiled queries, please do the video about the more complex scenario's!

I want see more videos about that! Love your content Nick!

Super interesting and useful info - thanks for this and all your other content!

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.

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?

Did you forget to set it default asnontracking? There is a good performance boost using that with EF

Awesome video. We definitely need the more complex scenarios. Also can you create a comparison calling a Stored Procedure?

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 :)

I always learn something from these videos

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!

Awesome! I didn't know that.

hi Nick, this is the comment you told me to leave down below

another reason why i wish methods has static fields or something similair

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.

Wow Nick, I loved this video! Thanks a lot...

we can complie it on demond and cache it, even to save memory cache could have an expiration criteria 🙂

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 ?

Would be interesting to see Linq2DB performance comparison/walkthrough also.

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 .

Hey, this is not an AsNoTracking query, so it's not a fair comparison since Dapper doesn't have a tracking feature. EF Core should be used with AsNoTracking option enabled, as it can be resource-intensive and use reflection, which can have a heavy impact on performance.

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.

why should i use Find() for querying with primary keys instead of First/Single?

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

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?

Hi Nick, Thanks for the video, can you please make a more complicated example video with joins...

Awesome tips, thanks!

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?

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.

nice, and we need more ef stuff

Why don't you use the EF Core AsNoTracking extension method? Is this implied since it is a ATO compiled query?

Why ef just not store cache of compiled functions at background then? it will be much more usefull than write compile for each fync

Would love to see a video on Expressions with EF Core

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 ?

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).

I would like to see speed test generic base repository with derived repository for more complex linq, vs pure dbcontext only

As for the memory you carry around with the application in the case of the compiled query, shouldn't be considered. In essence you make compiled quries for actions that you perform often which means it is a lot memory you don't have to now garbage collect multiple times. In an API scenario the query will be called multiple times per second meaning the memory footprint is way better. If you call it once per day then bohoo right?

Thanks a lot 🎉❤

Is it possible and necessary to use CancellationToken in such requests(Compile Async request)?

Would it be sensible to use Lazy or AsyncLazy to defer initialization of the compiled queries until they’re requested the first time?

Great video, thanks

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?

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!

It is valuable addon info to me

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?

Can I implement order by with EF.CompileAsyncQuery ?

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?

In dapper example, you use result.ToList() Why not cast? (List) result Will it differ performance or memory consumption ?

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

More Complex scenario please :)

Actually need to check absolute values. In most cases difference between 7us and 35us is nothing It would be good to use really big database to get it to more noticeable values close to seconds. In that case won't the difference still be ~20us?

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?

In web api, would this compiled queries on ejected to the http methods ?

The one thing that is very unclear - do you use AsNoTracking mode for context or just forgot to add this into benchmark?

Does these make more sense in simple or complicated queries or does it depend on the frequency they are executed?

We definitely want 😊

I cannot find any reason why to use EF or EF Core. I‘m using dapper since 6 years and it simply works, is easy to understand and really fast. And the best of all, there is no funky magic behind the curtain you have to deal with.

Please make a video about more complicated scenarios, Nick

Great video Nick! Please keep the EF Core vids coming, they are sooo good!

Hi Nick, great video but you still owe us the explanation why AsNoTracking() did poor in the previous video :P

Are there any drawbacks to using compiled queries besides the issues mentioned in the video?

Is it possible to use it with Add/Remove/Update ?

GREATE VIDEO , PLEASE MAKE A VIDEO DEEP DIVE INTO COMPILED QUERY

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.

Nick, could you please benchmark LINQ vs using for loops? doing operations like .Sum, .Where, .Average, .Select, etc...

Maybe you can show a video using Compiled Queries with Cancellation Token too