EF - Compiled Queries

Do We Really Need Compiled Queries Today?

EF Core already auto-caches most queries.

Compiled queries are needed only for ultra-high performance APIs like:

• Billing engines
• High-traffic read endpoints
• Microservices hitting DB thousands of times per minute

For normal applications—CRUD apps, admin portals—EF’s auto-caching is enough.

✅ When Should You Use Compiled Queries?

Use compiled queries when:

✔ A query is executed very frequently
✔ The query is parameterized (e.g., by ID, Name)
✔ You want to avoid repeated LINQ-to-SQL compilation cost
✔ High-traffic microservices / Web APIs

Not needed for:

✘ Ad-hoc queries
✘ Complex queries built dynamically
✘ Queries that run only occasionally

---

🟦 EF Core Compiled Query Example (Recommended)

EF Core provides EF.CompileQuery static API.

---

✅ Example 1: Simple compiled query

using Microsoft.EntityFrameworkCore;

public static class CompiledQueries
{
    public static readonly Func<AppDbContext, int, Task<Customer?>> GetCustomerById =
        EF.CompileAsyncQuery(
            (AppDbContext context, int id) =>
                context.Customers.FirstOrDefault(c => c.Id == id)
        );
}

✔ Use the compiled query

var customer = await CompiledQueries.GetCustomerById(dbContext, 10);


🟧 Compiled Query for Non-Async
EF also provides synchronous version:

csharp
Copy code
public static readonly Func<AppDbContext, int, Customer?> GetCustomerSync =
    EF.CompileQuery(
        (AppDbContext db, int id) =>
            db.Customers.FirstOrDefault(c => c.Id == id)
    );

🔥 Performance Benefit

EF Core normally does this per execution:

1. Parse LINQ
2. Build expression tree
3. Translate to SQL
4. Cache translation
5. Execute

Compiled queries skip steps 1–3 and reuse the plan.

Expect 5%–30% speed improvement for repeated queries.

✅ Summary

Topic	Explanation
What	Pre-compiled LINQ-to-SQL for speed
Why	Faster execution for repeated queries
How	EF.CompileAsyncQuery()
When	High-frequency, high-load operations
Limitations	No dynamic query shapes