Bits of .NET

Are your Entity Framework queries making too many database trips? Discover how IQueryable.ToString() reveals the actual SQL and helps you batch queries efficiently.

The Hidden Debugging Gem:

// Instead of this naive approach (multiple queries)
var users = db.Users.Where(u => u.IsActive).ToList();
var orders = db.Orders.Where(o => o.UserId == userId).ToList();
var products = db.Products.Where(p => p.CategoryId == categoryId).ToList();

// Each ToList() executes separate SQL query = 3 database trips!

// Use ToQueryString() to see what's happening
var query = db.Users.Where(u => u.IsActive);
Console.WriteLine(query.ToQueryString());

// Output shows the generated SQL BEFORE execution
// This helps you identify if filters are applied in database or memory

Batch Multiple Queries into One:

// Problem: N+1 query issue
var products = db.Products.ToList();
foreach (var product in products)
{
    var category = db.Categories.FirstOrDefault(c => c.Id == product.CategoryId);
    // Each iteration makes database call!
}

// Solution: Use Include() and projection
var optimizedQuery = db.Products
    .Include(p => p.Category)
    .Select(p => new 
    {
        p.Name,
        CategoryName = p.Category.Name,
        p.Price
    })
    .ToQueryString(); // Check the SQL first!

// Execute optimized version
var result = db.Products
    .Include(p => p.Category)
    .Select(p => new 
    {
        p.Name,
        CategoryName = p.Category.Name,
        p.Price
    })
    .ToList();

// Single SQL query with JOIN instead of N+1 queries

Debug Complex Queries:

// Complex query with multiple conditions
var complexQuery = db.Orders
    .Where(o => o.Status == OrderStatus.Completed)
    .Where(o => o.CreatedDate >= DateTime.UtcNow.AddMonths(-1))
    .GroupBy(o => o.UserId)
    .Select(g => new 
    {
        UserId = g.Key,
        TotalAmount = g.Sum(o => o.Amount),
        OrderCount = g.Count()
    })
    .Where(x => x.TotalAmount > 1000);

// See the generated SQL without executing
var sql = complexQuery.ToQueryString();
Console.WriteLine(sql);

// You'll discover if grouping happens in SQL (good) or memory (bad)
// This helps optimize database indexes

Combine Multiple Conditions Efficiently:

// Instead of multiple OR conditions (slow)
var searchTerms = new[] { "laptop", "computer", "notebook" };
var query = db.Products.AsQueryable();

foreach (var term in searchTerms)
{
    query = query.Where(p => p.Name.Contains(term));
}
// Generates: WHERE Name LIKE '%laptop%' OR Name LIKE '%computer%' ...

// Better: Build dynamic expression tree
var parameter = Expression.Parameter(typeof(Product), "p");
var property = Expression.Property(parameter, "Name");
var constant = Expression.Constant("laptop");
var containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });

var expressions = searchTerms.Select(term =>
    Expression.Call(property, containsMethod, Expression.Constant(term)));

var body = expressions.Aggregate(Expression.OrElse);
var lambda = Expression.Lambda>(body, parameter);

var optimizedResult = db.Products.Where(lambda);
Console.WriteLine(optimizedResult.ToQueryString());

Real-World Example – E-commerce Report:

// Before optimization (5 separate queries)
public ReportDto GetMonthlyReport(int year, int month)
{
    var totalOrders = db.Orders
        .Where(o => o.Date.Year == year && o.Date.Month == month)
        .Count();
    
    var totalRevenue = db.Orders
        .Where(o => o.Date.Year == year && o.Date.Month == month)
        .Sum(o => o.Amount);
    
    var topProducts = db.OrderItems
        .Where(oi => oi.Order.Date.Year == year && oi.Order.Date.Month == month)
        .GroupBy(oi => oi.ProductId)
        .Select(g => new { ProductId = g.Key, Count = g.Count() })
        .OrderByDescending(x => x.Count)
        .Take(10)
        .ToList();
    
    // 3 database calls!
}

// After optimization (1 query)
public ReportDto GetMonthlyReportOptimized(int year, int month)
{
    var startDate = new DateTime(year, month, 1);
    var endDate = startDate.AddMonths(1);
    
    var query = db.Orders
        .Where(o => o.Date >= startDate && o.Date < endDate)
        .Select(o => new 
        {
            Order = o,
            Items = o.Items
        })
        .ToQueryString(); // Check SQL first!
    
    // Execute single optimized query
    var data = db.Orders
        .Where(o => o.Date >= startDate && o.Date < endDate)
        .Select(o => new 
        {
            Order = o,
            Items = o.Items
        })
        .ToList();
    
    // Process in memory - faster than multiple round trips
    var totalOrders = data.Count;
    var totalRevenue = data.Sum(d => d.Order.Amount);
    var topProducts = data.SelectMany(d => d.Items)
        .GroupBy(i => i.ProductId)
        .Select(g => new { ProductId = g.Key, Count = g.Count() })
        .OrderByDescending(x => x.Count)
        .Take(10)
        .ToList();
    
    return new ReportDto
    {
        TotalOrders = totalOrders,
        TotalRevenue = totalRevenue,
        TopProducts = topProducts
    };
}