EF Core SQL Generation — What EF Core Sends to SQL Server

Understanding what SQL EF Core generates is essential for writing performant data access code. EF Core translates LINQ to SQL — but the translation is not always optimal, and sometimes it falls back to client evaluation (executing the query in memory) without a clear warning. Enabling SQL logging during development and validating generated queries with SET STATISTICS IO catches performance problems before they reach production.

SQL Logging and LINQ Translation #

// ── Enable SQL logging in development ─────────────────────────────────────
builder.Services.AddDbContext<AppDbContext>(opts => {
    opts.UseSqlServer(connStr);
    if (builder.Environment.IsDevelopment()) {
        opts.LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging()   // include parameter values
            .EnableDetailedErrors();
    }
});

// ── AsNoTracking() — read-only queries (no change tracker overhead) ────────
// For list/detail pages where you don't need to SaveChanges:
var posts = await _db.Posts
    .AsNoTracking()                  // skip change tracking — faster reads
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.PublishedAt)
    .Take(10)
    .ToListAsync(ct);
// Generated SQL:
// SELECT TOP(10) p.Id, p.Title, ... FROM Posts p
// WHERE p.IsPublished = 1
// ORDER BY p.PublishedAt DESC

// ── Include() — single collection (generates JOIN) ────────────────────────
var post = await _db.Posts
    .AsNoTracking()
    .Include(p => p.Author)
    .Include(p => p.Category)
    .FirstOrDefaultAsync(p => p.Slug == slug, ct);
// Generated SQL: single query with JOINs for Author and Category

// ── AsSplitQuery() — multiple collection includes ─────────────────────────
// Without split query: cartesian explosion (Posts × Tags × Comments rows)
// With split query: separate SQL statements for each collection
var postWithTags = await _db.Posts
    .AsNoTracking()
    .AsSplitQuery()                  // separate queries per collection
    .Include(p => p.Tags)
    .Include(p => p.Comments.Where(c => c.IsApproved))
    .FirstOrDefaultAsync(p => p.Id == id, ct);
// Generated SQL:
// SELECT ... FROM Posts WHERE Id = @id          (1 query)
// SELECT ... FROM Tags JOIN PostTags ...         (2nd query)
// SELECT ... FROM Comments WHERE PostId = @id    (3rd query)

// ── Projection — Select only needed columns ────────────────────────────────
var summaries = await _db.Posts
    .AsNoTracking()
    .Where(p => p.IsPublished)
    .Select(p => new PostSummaryDto {   // only fetch these columns
        Id         = p.Id,
        Title      = p.Title,
        Slug       = p.Slug,
        AuthorName = p.Author!.DisplayName,   // navigation inside Select — joins
        ViewCount  = p.ViewCount,
    })
    .OrderByDescending(p => p.ViewCount)
    .Take(10)
    .ToListAsync(ct);
// Generated SQL: SELECT p.Id, p.Title, p.Slug, u.DisplayName, p.ViewCount
// FROM Posts p JOIN Users u ON u.Id = p.AuthorId
// WHERE p.IsPublished = 1 ORDER BY p.ViewCount DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Common Mistakes #

Mistake 1 — Include() with multiple collections without AsSplitQuery (cartesian explosion) #

❌ Wrong — Include(Tags).Include(Comments); 10 tags × 50 comments = 500 rows returned for 60 pieces of data.

✅ Correct — AsSplitQuery() generates separate queries per collection; no row multiplication.

Mistake 2 — Not using AsNoTracking() on read-only queries (change tracker overhead) #

❌ Wrong — list endpoint loads 100 posts with change tracking enabled; 100 snapshots maintained in memory unnecessarily.

✅ Correct — AsNoTracking() on all read-only queries; only use tracking when SaveChanges will be called.