Advanced EF Core

Raw SQL Queries

Sometimes you need to execute raw SQL for complex queries or performance optimization.

FromSqlRaw

// Simple query
var products = await _context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > 100")
    .ToListAsync();

// With parameters (prevents SQL injection)
var minPrice = 100;
var products = await _context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", minPrice)
    .ToListAsync();

// Can combine with LINQ
var products = await _context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", minPrice)
    .Where(p => p.Quantity > 0)
    .OrderBy(p => p.Name)
    .ToListAsync();

FromSqlInterpolated (Safer)

var minPrice = 100;
var products = await _context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {minPrice}")
    .ToListAsync();

ExecuteSqlRaw (Non-Query)

// Update/Delete/Insert
var rowsAffected = await _context.Database
    .ExecuteSqlRawAsync("UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = {0}", 1);

// Interpolated version
var categoryId = 1;
var rowsAffected = await _context.Database
    .ExecuteSqlInterpolatedAsync($"UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = {categoryId}");

Transactions

Transactions ensure multiple operations succeed or fail together.

Automatic Transactions

// All changes saved in a single transaction
_context.Products.Add(new Product { Name = "Laptop", Price = 999 });
_context.Products.Add(new Product { Name = "Mouse", Price = 29 });

// Both saved or both rolled back
await _context.SaveChangesAsync();

Manual Transactions

using var transaction = await _context.Database.BeginTransactionAsync();

try
{
    // Operation 1
    var product = await _context.Products.FindAsync(1);
    product.Quantity -= 1;
    await _context.SaveChangesAsync();

    // Operation 2
    var order = new Order { CustomerName = "John", OrderDate = DateTime.UtcNow };
    _context.Orders.Add(order);
    await _context.SaveChangesAsync();

    // Commit if all succeeded
    await transaction.CommitAsync();
}
catch
{
    // Rollback on error
    await transaction.RollbackAsync();
    throw;
}

Transaction with Raw SQL

using var transaction = await _context.Database.BeginTransactionAsync();

try
{
    // EF Core operation
    _context.Products.Add(new Product { Name = "Laptop", Price = 999 });
    await _context.SaveChangesAsync();

    // Raw SQL operation
    await _context.Database
        .ExecuteSqlRawAsync("UPDATE Inventory SET LastUpdated = GETDATE()");

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

AsNoTracking for Performance

AsNoTracking() disables change tracking for read-only queries, improving performance.

// Tracked (default) - slower, can update
var products = await _context.Products.ToListAsync();

// No tracking - faster, read-only
var products = await _context.Products
    .AsNoTracking()
    .ToListAsync();

// Use for API GET endpoints
[HttpGet]
public async Task<ActionResult> GetProducts()
{
    var products = await _context.Products
        .AsNoTracking()
        .ToListAsync();
    
    return Ok(products);
}

Global No Tracking

builder.Services.AddDbContext<InvenTrackDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
});

Global Query Filters

Global query filters automatically apply filters to all queries.

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public bool IsDeleted { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Automatically filter out deleted products
    modelBuilder.Entity<Product>()
        .HasQueryFilter(p => !p.IsDeleted);
}

// All queries automatically exclude deleted products
var products = await _context.Products.ToListAsync(); // Only non-deleted

// Ignore filter when needed
var allProducts = await _context.Products
    .IgnoreQueryFilters()
    .ToListAsync(); // Includes deleted

Compiled Queries

Compiled queries improve performance by caching query translation.

private static readonly Func<InvenTrackDbContext, int, Task<Product?>> _getProductById =
    EF.CompileAsyncQuery((InvenTrackDbContext context, int id) =>
        context.Products.FirstOrDefault(p => p.Id == id));

// Use compiled query
var product = await _getProductById(_context, 1);

Batch Operations

// Add multiple entities
var products = new List<Product>
{
    new Product { Name = "Laptop", Price = 999 },
    new Product { Name = "Mouse", Price = 29 },
    new Product { Name = "Keyboard", Price = 79 }
};

_context.Products.AddRange(products);
await _context.SaveChangesAsync();

// Remove multiple
_context.Products.RemoveRange(products);
await _context.SaveChangesAsync();

Performance Best Practices

1. Use AsNoTracking for Read-Only

var products = await _context.Products.AsNoTracking().ToListAsync();

2. Project Only Needed Data

var products = await _context.Products
    .Select(p => new { p.Id, p.Name, p.Price })
    .ToListAsync();

3. Avoid N+1 with Include

var products = await _context.Products
    .Include(p => p.Category)
    .ToListAsync();

4. Use Pagination

var products = await _context.Products
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

5. Use Indexes

modelBuilder.Entity<Product>()
    .HasIndex(p => p.Name);

// Composite index
modelBuilder.Entity<Product>()
    .HasIndex(p => new { p.CategoryId, p.Price });

6. Enable Query Logging

builder.Services.AddDbContext<InvenTrackDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.LogTo(Console.WriteLine, LogLevel.Information);
    options.EnableSensitiveDataLogging(); // Dev only!
});

Complete InvenTrack Example

public class ProductService
{
    private readonly InvenTrackDbContext _context;

    public ProductService(InvenTrackDbContext context)
    {
        _context = context;
    }

    // Optimized read
    public async Task<List<ProductDto>> GetProductsAsync(int page, int pageSize)
    {
        return await _context.Products
            .AsNoTracking()
            .Include(p => p.Category)
            .OrderBy(p => p.Name)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(p => new ProductDto
            {
                Id = p.Id,
                Name = p.Name,
                Price = p.Price,
                CategoryName = p.Category.Name
            })
            .ToListAsync();
    }

    // Transactional operation
    public async Task ProcessOrderAsync(int productId, int quantity)
    {
        using var transaction = await _context.Database.BeginTransactionAsync();

        try
        {
            // Reduce inventory
            var product = await _context.Products.FindAsync(productId);
            if (product == null || product.Quantity < quantity)
                throw new InvalidOperationException("Insufficient quantity");

            product.Quantity -= quantity;
            await _context.SaveChangesAsync();

            // Create order
            var order = new Order
            {
                CustomerName = "Customer",
                OrderDate = DateTime.UtcNow
            };
            _context.Orders.Add(order);
            await _context.SaveChangesAsync();

            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

Key Takeaways

• Raw SQL: FromSqlRaw/FromSqlInterpolated for complex queries
• Transactions: BeginTransaction for multi-step operations
• AsNoTracking: Use for read-only queries (better performance)
• Global filters: HasQueryFilter for automatic filtering
• Compiled queries: Cache query translation
• Batch operations: AddRange/RemoveRange for bulk
• Indexes: HasIndex for frequently queried columns
• Logging: Enable to see generated SQL