LINQ and EF Core — From In-Memory to SQL

LINQ on in-memory collections (IEnumerable<T>) evaluates in C#. LINQ on EF Core (IQueryable<T>) translates your method chain into SQL and executes it in the database. The SQL translation is the engine of the entire EF Core data access layer in Part 4. Understanding when work happens in SQL (efficient) versus in C# (potentially loading thousands of rows into memory) is the single most important performance skill for ASP.NET Core developers.

IQueryable vs IEnumerable #

// IQueryable — query is built but NOT executed until materialised
IQueryable<Post> query = _db.Posts          // DbSet<Post> is IQueryable
    .Where(p => p.IsPublished)              // adds WHERE clause to SQL
    .OrderByDescending(p => p.CreatedAt);   // adds ORDER BY to SQL

// Still no SQL executed — query is just an expression tree

// Add more operators before executing
if (filterTag is not null)
    query = query.Where(p => p.Tags.Any(t => t.Name == filterTag));  // adds JOIN

// Materialise — THIS is when the SQL executes
var result = await query
    .Skip(skip)
    .Take(pageSize)
    .Select(p => new PostDto { Id = p.Id, Title = p.Title })
    .ToListAsync();

// Resulting SQL (approximate):
// SELECT p.Id, p.Title
// FROM Posts p
// WHERE p.IsPublished = 1
// AND EXISTS (SELECT 1 FROM Tags WHERE PostId = p.Id AND Name = @tag)
// ORDER BY p.CreatedAt DESC
// OFFSET @skip ROWS FETCH NEXT @pageSize ROWS ONLY

Include — Eager Loading Navigation Properties #

// Without Include — accessing p.Author triggers a separate query per post (N+1!)
var posts = await _db.Posts.Where(p => p.IsPublished).ToListAsync();
foreach (var post in posts)
    Console.WriteLine(post.Author.Name);  // N separate SELECT queries!

// With Include — loads Author in the same SQL query (JOIN)
var posts2 = await _db.Posts
    .Include(p => p.Author)              // LEFT JOIN to Authors
    .Include(p => p.Tags)               // LEFT JOIN to Tags
    .Where(p => p.IsPublished)
    .ToListAsync();
// One SQL with JOINs — no N+1

// ThenInclude — load nested navigation properties
var posts3 = await _db.Posts
    .Include(p => p.Comments)
        .ThenInclude(c => c.Author)     // load each comment's author too
    .Where(p => p.IsPublished)
    .ToListAsync();

AsNoTracking and Query Optimisation #

// Read-only query — no change tracking needed
var posts = await _db.Posts
    .AsNoTracking()                           // ✓ saves memory and CPU
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.CreatedAt)
    .Select(p => new PostDto                  // project to DTO — only needed columns
    {
        Id    = p.Id,
        Title = p.Title,
        Views = p.ViewCount,
    })
    .ToListAsync();

// Use AsNoTrackingWithIdentityResolution when using Include but not tracking
var posts2 = await _db.Posts
    .AsNoTrackingWithIdentityResolution()
    .Include(p => p.Tags)
    .ToListAsync();

Common EF Core LINQ Mistakes #

Mistake 1 — N+1 queries (accessing navigation properties without Include) #

❌ Wrong — 1 query for posts + N queries for each author:

var posts = await _db.Posts.ToListAsync();
var names = posts.Select(p => p.Author.Name);  // N lazy-loads!

✅ Correct — eager load with Include:

var posts = await _db.Posts.Include(p => p.Author).ToListAsync();

Mistake 2 — Calling AsEnumerable() / ToList() before filtering (loads entire table) #

❌ Wrong — loads all rows into memory, then filters in C#:

var posts = await _db.Posts.ToListAsync();         // entire table in memory!
var filtered = posts.Where(p => p.IsPublished);   // C# filter — too late

✅ Correct — filter before materialising:

var posts = await _db.Posts.Where(p => p.IsPublished).ToListAsync();