Advanced Querying

Entity Framework Core - Advanced Querying 🚀

The Magic Kitchen Analogy 🍳

Imagine you have a magic kitchen where you can ask for any dish, and it appears! But sometimes you need special tricks to get exactly what you want. That’s what EF Core Advanced Querying is all about—special tricks to get your data exactly how you need it.

1. Value Conversions 🔄

What Is It?

Think of value conversions like a translator. When you store toys in a box, you might label them “RED” or “BLUE”. But in your code, you prefer numbers like 1 or 2. A value converter translates between these two languages automatically!

The Story

Little Emma has a toy box. She likes calling her toys by colors: “Red Bear”, “Blue Car”. But her toy database only understands numbers. So she creates a magic translator:

• Red → 1
• Blue → 2
• Green → 3

Now whenever she saves “Red Bear”, the database stores “1 Bear”. When she reads it back, she sees “Red Bear” again!

Simple Example

// Your friendly Color enum
public enum Color
{
    Red = 1,
    Blue = 2,
    Green = 3
}

// Your Toy class
public class Toy
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Color Color { get; set; }
}

Setting Up the Converter

protected override void OnModelCreating(
    ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Toy>()
        .Property(t => t.Color)
        .HasConversion(
            v => v.ToString(),  // To database
            v => (Color)Enum.Parse(
                typeof(Color), v) // From database
        );
}

Built-in Converters

EF Core has ready-made translators! No coding needed:

Pro Tip 💡

// Use built-in converter for enums
.HasConversion<string>();

2. Query Filters 🔍

What Is It?

Query filters are like invisible glasses that automatically hide things you don’t want to see. Put them on once, and they work forever!

The Story

Imagine a library where some books are “deleted” but not thrown away (soft delete). The librarian wears special glasses that automatically hide deleted books. Every time she looks at the shelf, she only sees the available books. That’s a query filter!

The Problem Without Filters

// Without filter - you see EVERYTHING
var books = context.Books.ToList();
// Includes deleted books! Oops! 😱

The Solution: Global Query Filter

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public bool IsDeleted { get; set; }
}

// In your DbContext
protected override void OnModelCreating(
    ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>()
        .HasQueryFilter(b => !b.IsDeleted);
}

Now Every Query Is Safe!

// Automatically excludes deleted books
var books = context.Books.ToList();
// Only active books! 🎉

Multi-Tenant Example

// Each company sees only their data
modelBuilder.Entity<Order>()
    .HasQueryFilter(o =>
        o.TenantId == _currentTenantId);

Bypass the Filter When Needed

// See EVERYTHING including deleted
var allBooks = context.Books
    .IgnoreQueryFilters()
    .ToList();

Diagram: How Query Filters Work

graph TD
    A["Your Query"] --> B{Query Filter Active?}
    B -->|Yes| C["Apply WHERE clause automatically"]
    B -->|No/Ignored| D["Return all data"]
    C --> E["Filtered Results"]
    D --> F["All Results"]

3. Raw SQL Queries 📝

What Is It?

Sometimes you need to speak the database’s native language directly. It’s like when you travel to a foreign country—sometimes pointing at pictures works, but speaking their language gets you exactly what you want!

When to Use Raw SQL?

• Complex queries LINQ can’t express easily
• Performance-critical operations
• Using database-specific features
• Existing SQL you want to reuse

FromSqlRaw - The Basic Way

// Get all products over $100
var products = context.Products
    .FromSqlRaw(
        "SELECT * FROM Products WHERE Price > 100")
    .ToList();

⚠️ DANGER: SQL Injection!

// NEVER do this! Bad! 🚫
var name = "'; DROP TABLE Products;--";
var bad = context.Products
    .FromSqlRaw(quot;SELECT * FROM Products
        WHERE Name = '{name}'")
    .ToList();
// Your table is gone! 😱

✅ Safe Way: Use Parameters

// ALWAYS use parameters! Good! ✅
var minPrice = 100;
var products = context.Products
    .FromSqlRaw(
        "SELECT * FROM Products WHERE Price > {0}",
        minPrice)
    .ToList();

Even Better: FromSqlInterpolated

// Automatically parameterized
var minPrice = 100;
var products = context.Products
    .FromSqlInterpolated(
        quot;SELECT * FROM Products
          WHERE Price > {minPrice}")
    .ToList();

Combine with LINQ!

var cheapProducts = context.Products
    .FromSqlRaw("SELECT * FROM Products")
    .Where(p => p.Price < 50)  // LINQ on top!
    .OrderBy(p => p.Name)
    .ToList();

ExecuteSqlRaw for Non-Query

// Update, Delete, Insert
var affected = context.Database
    .ExecuteSqlRaw(
        "UPDATE Products SET Price = Price * 1.1
         WHERE CategoryId = {0}", categoryId);

Safety Comparison

4. Stored Procedures 🗄️

What Is It?

A stored procedure is like a recipe saved in the database. Instead of explaining how to make a cake every time, you just say “Make Cake #7” and the database knows exactly what to do!

Why Use Stored Procedures?

• Performance: Database optimizes them
• Security: Hide complex logic
• Reusability: Call from anywhere
• Maintainability: Change once, applies everywhere

Create a Stored Procedure (SQL Server)

CREATE PROCEDURE GetProductsByCategory
    @CategoryId INT
AS
BEGIN
    SELECT * FROM Products
    WHERE CategoryId = @CategoryId
    ORDER BY Name;
END

Call It From EF Core

var categoryId = 5;
var products = context.Products
    .FromSqlRaw(
        "EXEC GetProductsByCategory {0}",
        categoryId)
    .ToList();

With Output Parameters

// SQL Procedure with output
// CREATE PROCEDURE GetProductCount
//     @CategoryId INT,
//     @Count INT OUTPUT
// AS
// BEGIN
//     SELECT @Count = COUNT(*)
//     FROM Products
//     WHERE CategoryId = @CategoryId;
// END

var categoryId = new SqlParameter("@CategoryId", 5);
var count = new SqlParameter
{
    ParameterName = "@Count",
    SqlDbType = SqlDbType.Int,
    Direction = ParameterDirection.Output
};

context.Database.ExecuteSqlRaw(
    "EXEC GetProductCount @CategoryId, @Count OUTPUT",
    categoryId, count);

var productCount = (int)count.Value;

Returning Non-Entity Results

// Create a class matching your result
public class ProductSummary
{
    public string CategoryName { get; set; }
    public int ProductCount { get; set; }
    public decimal TotalValue { get; set; }
}

// Register as keyless entity
modelBuilder.Entity<ProductSummary>()
    .HasNoKey()
    .ToView(null);  // Not mapped to table

// Call the procedure
var summary = context.Set<ProductSummary>()
    .FromSqlRaw("EXEC GetProductSummary")
    .ToList();

Diagram: Stored Procedure Flow

graph TD
    A["Your C# Code"] -->|Call| B["EF Core"]
    B -->|EXEC ProcName| C["Database"]
    C -->|Run Procedure| D["Stored Procedure"]
    D -->|Execute SQL| E["Get Results"]
    E -->|Return| C
    C -->|Send Data| B
    B -->|Map to Objects| A

Quick Comparison Table 📊

The Complete Picture 🎨

graph TD
    A["EF Core Query"] --> B{Need Transformation?}
    B -->|Yes| C["Value Conversion"]
    B -->|No| D{Need Auto-Filter?}
    D -->|Yes| E["Query Filter"]
    D -->|No| F{Complex Query?}
    F -->|Yes| G{Reusable?}
    G -->|Yes| H["Stored Procedure"]
    G -->|No| I["Raw SQL"]
    F -->|No| J["Regular LINQ"]

    C --> K["Final Query"]
    E --> K
    H --> K
    I --> K
    J --> K

Remember! 🧠

1. Value Conversions = Translator between C# and Database
2. Query Filters = Invisible, automatic WHERE clauses
3. Raw SQL = When LINQ isn’t enough (use parameters!)
4. Stored Procedures = Saved recipes in the database

You now have four superpowers to query your database like a pro! 🦸‍♀️

Each tool solves a specific problem. Pick the right one, and your code becomes cleaner, safer, and faster. Happy querying! 🎉