Improve EF Core Performance with Compiled Queries

Nov 20 2023


Many thanks to the sponsors who make it possible for this newsletter to be free for readers.


• If you have ever used Postman to debug and write tests for your REST APIs, guess what, those are the same concepts you need to know for writing tests for your gRPC requests in Postman. For more info about gRPC, they created a great beginner article here.


• If you’re an existing Java developer who wants to go full stack or pick up another frontend framework, this book is your concise introduction to React. Along the way, you’ll explore modern libraries and tools, such as Gradle, Vite, Vitest, and React Query. For the first time ever, it also covers React development with the in-demand TypeScript.



The background



Compiled queries in .NET emerged as a response to the performance challenges faced in data retrieval operations, particularly in applications using ORMs like Entity Framework.

Before the introduction of compiled queries, every data retrieval operation required the ORM to translate LINQ queries into SQL queries, a process that was both time-consuming and resource-intensive.

This translation had to be done every time a query was executed, significantly impacting performance, especially in applications with high database interaction.

Let's dive in...


How To Create Compiled Queries



Let's say we have a Domain class that represents a data model:

User Domain Class

How can we construct a basic query to retrieve a User using its Id?

Very simple...

Getting User By Id

Now, how do we convert this query into a Complied Query?

Compiled Query

What we have done here?

• GetUser field: This is a static field of type Func< AppDbContext, int, User>. It's a compiled query using Entity Framework's EF.CompileQuery method.

• GetUser Method: his is a public instance method that takes an int id as a parameter. It uses the static GetUser field (the compiled query) to retrieve a User object from the database. It does so by passing this (the current instance of AppDbContext) and the provided id to the compiled query.

And now we can just call the method created within the our context:

Using Compiled Query



Why to use Compiled Queries?



Improved Performance


The primary advantage is faster execution time, as the query translation from LINQ to SQL is done only once and reused, which is particularly beneficial for queries executed frequently.

Efficient Resource Utilization


Since the query is compiled once and cached, it reduces CPU usage and resource consumption associated with the query compilation process.

Automatic Caching


EF Core automatically compiles and caches the most common queries, reducing the need for manual intervention and simplifying development.


When Not to use Compiled Queries?



Highly Dynamic Queries


Avoid them if your queries change frequently or are dynamically constructed based on various conditions.

Infrequently Executed Queries


Not necessary for queries executed rarely, as the performance benefit may be negligible.

Limited Resource Environments


If memory usage is a concern, be cautious with the number of compiled queries.


Conclusion



Compiled queries in EF Core are a powerful feature for optimizing database access in specific scenarios. They are most beneficial for improving performance in scenarios with repetitive and frequent database queries. However, their use should be balanced against the potential drawbacks, such as increased memory usage and complexity, especially in applications with dynamic query patterns or limited resources.

That's all from me for today.

dream BIG!

Join 10,950+ subscribers to improve your .NET Knowledge.

There are 3 ways I can help you:

Design Patterns Simplified ebook

Go-to resource for understanding the core concepts of design patterns without the overwhelming complexity. In this concise and affordable ebook, I've distilled the essence of design patterns into an easy-to-digest format. It is a Beginner level. Check out it here.


Sponsorship

Promote yourself to 10,950+ subscribers by sponsoring this newsletter.


Join .NET Pro Weekly Newsletter

Every Monday morning, I share 1 actionable tip on C#, .NET & Arcitecture topic, that you can use right away.


Subscribe to
.NET Pro Weekly

Subscribe to the .NET Pro Weekly and be among the 10,950+ subscribers gaining practical tips and resources to enhance your .NET expertise.