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

EF - N+1 Problem

 ✅ What is the N+1 Query Problem?

The N+1 problem occurs when your code runs:

• 1 query to load a list of parent records

• + N additional queries to load related data for each item individually

So total queries = 1 + N
This is hugely inefficient.

---

🟦 Simple Example (Best way to understand)

Suppose you want to load a list of Employees and their Projects.

You write:

var employees = context.Employees.ToList();

foreach (var emp in employees)
{
    Console.WriteLine(emp.Projects.Count);
}

❌ What actually happens with Lazy Loading:

Query 1:

SELECT * FROM Employees;

Now assume Employees table has 10 records.

When we loop through them, EF loads Projects for each employee:

Query 2:

SELECT * FROM Projects WHERE EmployeeId = 1;

Query 3:

SELECT * FROM Projects WHERE EmployeeId = 2;

Query 4:

SELECT * FROM Projects WHERE EmployeeId = 3;

… and so on 10 times.

---

✔ Total queries = 1 (main query) + 10 (project queries) = 11 queries

This is the N+1 problem.

---

🟥 Why is this bad?

• Causes too many database round-trips

• Slows applications massively

• Wastes network + DB CPU

• Very harmful in Web APIs, where response time matters

---

🟩 How to Fix the N+1 Problem? (Eager Load)

Use .Include():

var employees = context.Employees
                       .Include(e => e.Projects)
                       .ToList();

✔ SQL Generated (Single JOIN Query):

SELECT * FROM Employees
LEFT JOIN Projects ON Projects.EmployeeId = Employees.Id;

✔ Total queries = 1 query only

No matter how many employees exist.

---

🟧 Interview Answer (Best Version)

The N+1 problem happens when the application fires one query to fetch the main data and then fires N additional queries to load related data for each item. This usually happens with lazy loading.

For example, loading 100 employees and then loading each employee’s projects separately results in 101 queries.

The solution is using eager loading with .Include() to fetch all related data in a single query.

---

🟦 Analogy (Very Easy to Remember)

Imagine you want 10 grocery items.

❌ Lazy Loading Way (N+1)

• You go to the store (1 trip)

• Then you go back 10 separate times to buy each item (10 trips)

Total trips = 11

✔ Eager Loading Way

• You go once and buy all 10 items together

Total trips = 1

EF - Entity Framework Core

 ✅ 1. What is Entity Framework Core? (Interview-ready definition)

EF Core is Microsoft’s Object-Relational Mapper (ORM) that lets you interact with a database using .NET classes instead of SQL queries.
It handles:

• Database Connection & Commands

• Mapping classes ↔ tables

• CRUD operations

• Migrations

• Change Tracking

• LINQ queries

Interview one-liner:

EF Core is an ORM that allows developers to work with databases using C# objects, eliminating most SQL and improving productivity while maintaining performance.

---

✅ 2. EF Core Workflow (Very Important for Interviews)

Model (C# classes)
        ↓
DbContext (Mapping + Configuration)
        ↓
Database (Migrations)
        ↓
LINQ Queries (CRUD)

---

✅ 3. Hands-On Practical Example (Minimal, Perfect for Learning)

Let’s create a simple Employee Management app with EF Core.

---

🟦 Step 1: Install EF Core Packages

Run in terminal:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

---

🟦 Step 2: Create Model Class

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Department { get; set; }
    public decimal Salary { get; set; }
}

---

🟦 Step 3: Create DbContext

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer("Server=.;Database=EFCoreDemo;Trusted_Connection=True;");
    }
}

---

🟦 Step 4: Create Database Using Migrations

dotnet ef migrations add InitialCreate
dotnet ef database update

This creates your database + Employees table.

In interviews, they often ask:
What are migrations?
Answer:
Migrations help you evolve your database schema over time while keeping data safe.

---

🟦 Step 5: Perform CRUD Operations

---

▶ Insert Data

using var context = new AppDbContext();

var emp = new Employee
{
    Name = "John",
    Department = "IT",
    Salary = 50000
};

context.Employees.Add(emp);
context.SaveChanges();

---

▶ Read Data (LINQ)

var employees = context.Employees.ToList();

---

▶ Update

var emp = context.Employees.First();
emp.Salary = 60000;
context.SaveChanges();

---

▶ Delete

var emp = context.Employees.First();
context.Employees.Remove(emp);
context.SaveChanges();

---

✅ 4. Frequently Asked EF Core Interview Questions (with answers)

1. What is DbContext?

DbContext is the main class that manages database connections, CRUD operations, and mapping between classes and tables.

---

2. What is DbSet?

DbSet represents a table. It lets you query and save instances of a model.

---

3. What is Change Tracking?

EF Core automatically tracks object changes and updates only modified fields.

---

4. What are Migrations?

Migrations manage schema changes without dropping the database.

---

5. What is Lazy Loading, Eager Loading, Explicit Loading?

• Eager Loading: Includes related data immediately using .Include()

• Lazy Loading: Related data is loaded automatically when accessed

• Explicit Loading: Manually load related data using Entry(entity).Collection().Load()

---

6. How to use LINQ with EF Core?

Example:

var itEmployees = context.Employees
                         .Where(e => e.Department == "IT")
                         .ToList();

---

✅ 5. Relationship Example (Highly asked in interviews)

Example: One-to-Many

Employee → Projects
One employee can have many projects.

---

Models:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }

    public List<Project> Projects { get; set; }
}

public class Project
{
    public int Id { get; set; }
    public string Title { get; set; }

    public int EmployeeId { get; set; }
    public Employee Employee { get; set; }
}

DbContext Mapping:

public DbSet<Employee> Employees { get; set; }
public DbSet<Project> Projects { get; set; }

Query with include:

var employees = context.Employees
                       .Include(e => e.Projects)
                       .ToList();

EF - Lazy Vs Eager loading

What is Lazy Loading?

Lazy loading loads related data automatically when it's accessed, not when the main entity is loaded. It can cause multiple SQL queries.

What is Eager Loading?

Eager loading loads all required related data in a single query using .Include(), improving performance in most web applications.

When should you avoid lazy loading?

Avoid lazy loading in Web APIs because it can produce many unnecessary queries and severely impact performance.