EF Core LINQ for Beginners: Practical Query Patterns

Entity Framework LINQ: Mastering Data Queries for Beginners
This post is part 7 of 8 in the series Mastering EF Core: From Fundamentals to Advanced Techniques

Are you sure you’re getting the SQL you think you’re getting? 8 out of 10 EF Core bugs I’ve fixed came from LINQ that looked innocent – but translated into something wildly different.

If you’re starting with Entity Framework Core (EF Core), LINQ is your steering wheel. Master it, and you’ll sail through data access with readable, testable code. Misuse it, and you’ll ship N+1 queries, drag performance down, and make your DBA side‑eye your logs. In this guide, I’ll show you the LINQ patterns I use daily in production – explained simply, with copy‑pasteable snippets you can try today.

What you’ll learn (quick wins)

  • The mental model: IQueryable vs IEnumerable, deferred execution, SQL translation.
  • How to shape queries: filtering, projection, sorting, paging.
  • Loading related data: Include, ThenInclude, or projection – which and when.
  • Joins, subqueries, aggregations – pitfall‑free.
  • Performance switches that matter: AsNoTracking, split vs single query, compiled queries.
  • Debug like a pro: ToQueryString(), logging, and query tags.

I’ll use a tiny blog domain so the examples are realistic and not “Hello World”‑ish.

Minimal setup (copy & run)

We’ll model Authors, Posts, and Tags with many‑to‑many.

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ICollection<Post> Posts { get; set; } = new List<Post>();
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string? Content { get; set; }
    public DateTime PublishedOn { get; set; }
    public bool IsPublished { get; set; }

    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;

    public ICollection<Tag> Tags { get; set; } = new List<Tag>();
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ICollection<Post> Posts { get; set; } = new List<Post>();
}

public class BloggingContext : DbContext
{
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Post> Posts => Set<Post>();
    public DbSet<Tag> Tags => Set<Tag>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=blog.db"); // swap for your provider

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasIndex(p => new { p.IsPublished, p.PublishedOn });

        modelBuilder.Entity<Tag>()
            .HasIndex(t => t.Name);
    }
}

Tip: in real apps, move configuration to AddDbContext in DI and use connection strings.

The mental model: IQueryable vs IEnumerable

  • IQueryable<T> (server): a query plan. You’re building an expression tree that EF Core translates to SQL. Nothing hits the database until you materialize with ToListAsync(), FirstAsync(), etc.
  • IEnumerable<T> (client): in‑memory objects. When you call methods that EF can’t translate or when you enumerate, you’re on the client.
  • Golden rule: keep your operations translatable as long as possible; materialize late.

Common footgun:

// ❌ Anti‑pattern: materializing early then filtering in memory
var posts = await context.Posts.ToListAsync();
var recent = posts.Where(p => p.IsPublished && p.PublishedOn >= DateTime.UtcNow.AddDays(-7));

// ✅ Do it server-side
var recentBetter = await context.Posts
    .Where(p => p.IsPublished && p.PublishedOn >= DateTime.UtcNow.AddDays(-7))
    .ToListAsync();

Shaping data: projection beats Include

Filter (Where) and project (Select)

Projection keeps payload small and prevents accidental N+1s.

var list = await context.Posts
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.PublishedOn)
    .Select(p => new PostListItem
    {
        Id = p.Id,
        Title = p.Title,
        Author = p.Author.Name,
        PublishedOn = p.PublishedOn,
        TagCount = p.Tags.Count
    })
    .ToListAsync();

public record PostListItem(int Id, string Title, string Author, DateTime PublishedOn, int TagCount);

When to use Include

  • You need full entities + their navigations for updates.
  • You’re returning entity graphs from an API (rarely ideal).
// Loads posts with authors and tags as tracked entities
var posts = await context.Posts
    .Include(p => p.Author)
    .Include(p => p.Tags)
    .AsNoTracking() // if read-only
    .ToListAsync();

Rule of thumb: for read models, prefer Select into DTOs. Use Include when you truly need entities.

Sorting and paging (without surprises)

int page = 1, pageSize = 20;
var pageItems = await context.Posts
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.PublishedOn)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .Select(p => new { p.Id, p.Title, p.PublishedOn })
    .ToListAsync();

Key points

  • Always apply a stable OrderBy before paging.
  • For very large offsets, prefer keyset pagination (a.k.a. seek method):
DateTime? before = /* last seen PublishedOn */ null;
var items = await context.Posts
    .Where(p => p.IsPublished && (before == null || p.PublishedOn < before))
    .OrderByDescending(p => p.PublishedOn)
    .Take(20)
    .ToListAsync();

Joins vs navigations

Most of the time, navigate via properties; LINQ is happier and SQL is cleaner. But joins are handy for ad‑hoc relations or filters.

Inner join

var q = from p in context.Posts
        join a in context.Authors on p.AuthorId equals a.Id
        where p.IsPublished
        select new { p.Title, Author = a.Name };

Left join (GroupJoin + DefaultIfEmpty)

var q = from a in context.Authors
        join p in context.Posts on a.Id equals p.AuthorId into gj
        from p in gj.DefaultIfEmpty()
        select new { Author = a.Name, PostTitle = p != null ? p.Title : "(none)" };

Using navigations (preferred)

var q = context.Posts
    .Where(p => p.IsPublished)
    .Select(p => new { p.Title, Author = p.Author.Name });

Aggregations and grouping (without client fallbacks)

EF Core translates most aggregates well.

var perAuthor = await context.Posts
    .Where(p => p.IsPublished)
    .GroupBy(p => p.Author.Name)
    .Select(g => new { Author = g.Key, Count = g.Count(), Last = g.Max(p => p.PublishedOn) })
    .OrderByDescending(x => x.Count)
    .ToListAsync();

Avoid mixing non‑translatable methods (e.g., custom C# functions) inside group queries. If you need them, project first to a translatable shape.

Subqueries: Any, All, Contains

// Posts that have the "ef-core" tag
var postsWithTag = await context.Posts
    .Where(p => p.Tags.Any(t => t.Name == "ef-core"))
    .ToListAsync();

// Authors who have ONLY published posts
var strictAuthors = await context.Authors
    .Where(a => a.Posts.All(p => p.IsPublished))
    .ToListAsync();

// IN (...) with Contains
var ids = new[] { 1, 5, 9 };
var selected = await context.Posts
    .Where(p => ids.Contains(p.Id))
    .ToListAsync();

Large Contains lists? Consider temporary tables / table‑valued parameters (provider‑specific) or chunking.

Dates, strings and nulls: common gotchas

  • Time zones: store DateTime in UTC (or DateTimeOffset), compare with DateTime.UtcNow.
  • String methods: use translatable ones like StartsWith, Contains, ToLower() sparingly – prefer EF.Functions.ILike/Like when available.
var q = context.Posts.Where(p => EF.Functions.Like(p.Title, "%linq%"));
  • Null‑safe navigation in queries:
var q = context.Posts
    .Where(p => (p.Content ?? "").Length > 1000);

Loading patterns: Include vs explicit vs projection

Include (eager): loads navigations in one logical query.

var post = await context.Posts
    .Include(p => p.Author)
    .Include(p => p.Tags)
    .FirstAsync(p => p.Id == id);

Explicit loading: good when you already have the entity and want on‑demand navigation(s).

var post = await context.Posts.FindAsync(id);
await context.Entry(post).Collection(p => p.Tags).LoadAsync();

Projection: best for read APIs / pages.

var dto = await context.Posts
    .Where(p => p.Id == id)
    .Select(p => new PostDetails
    {
        Id = p.Id,
        Title = p.Title,
        Author = p.Author.Name,
        Tags = p.Tags.Select(t => t.Name).ToList()
    })
    .FirstAsync();

Performance levers that actually matter

  • AsNoTracking() for read‑only scenarios (skips change tracking, reduces memory/CPU).
  • Split vs single query: .AsSplitQuery() prevents huge Cartesian products when including multiple collections.
  • Projection to limit columns.
  • Avoid chatty queries: pull what you need in one roundtrip.
  • Compiled queries for very hot paths.
static readonly Func<BloggingContext, string, IAsyncEnumerable<Post>> CompiledByAuthor =
    EF.CompileAsyncQuery((BloggingContext ctx, string author) =>
        ctx.Posts.AsNoTracking().Where(p => p.Author.Name == author));

await foreach (var p in CompiledByAuthor(context, "Alice"))
{
    // use p
}
  • Tag your queries to find them in logs/DB traces:
var q = context.Posts.TagWith("Homepage: latest posts").Where(p => p.IsPublished);

Debugging the translation (be curious!)

When something feels slow, look at the SQL.

var sql = context.Posts
    .Where(p => p.IsPublished && p.PublishedOn > DateTime.UtcNow.AddDays(-30))
    .ToQueryString();
Console.WriteLine(sql);

Also enable EF Core logging in your host (e.g., appsettings.json) and watch for warnings like “Possible unintended client evaluation”.

Real‑world scenarios (copy & adapt)

Latest published posts with author and first 3 tags

var items = await context.Posts
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.PublishedOn)
    .Select(p => new
    {
        p.Id,
        p.Title,
        Author = p.Author.Name,
        Tags = p.Tags.OrderBy(t => t.Name).Select(t => t.Name).Take(3)
    })
    .Take(20)
    .ToListAsync();

Full‑text‑ish search by title/content

string term = "linq";
var search = await context.Posts
    .Where(p => p.IsPublished && (
        EF.Functions.Like(p.Title, $"%{term}%") ||
        EF.Functions.Like(p.Content!, $"%{term}%")))
    .Select(p => new { p.Id, p.Title })
    .ToListAsync();

Top tags in the last 90 days

var cutoff = DateTime.UtcNow.AddDays(-90);
var topTags = await context.Posts
    .Where(p => p.IsPublished && p.PublishedOn >= cutoff)
    .SelectMany(p => p.Tags)
    .GroupBy(t => t.Name)
    .Select(g => new { Tag = g.Key, Count = g.Count() })
    .OrderByDescending(x => x.Count)
    .Take(10)
    .ToListAsync();

Prevent N+1 in a listing endpoint

// ❌ Anti-pattern
var posts = await context.Posts.Where(p => p.IsPublished).ToListAsync();
var dtos = posts.Select(p => new PostListItem(
    p.Id,
    p.Title,
    // Each access may trigger lazy load or separate query if proxies enabled
    p.Author.Name,
    p.PublishedOn,
    p.Tags.Count));

// ✅ One roundtrip, projected
var dtosBetter = await context.Posts
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.PublishedOn)
    .Select(p => new PostListItem(
        p.Id,
        p.Title,
        p.Author.Name,
        p.PublishedOn,
        p.Tags.Count))
    .ToListAsync();

Dynamic filters without spaghetti

When filters depend on user input, compose expression predicates.

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() => _ => true;
    public static Expression<Func<T, bool>> And<T>(
        this Expression<Func<T, bool>> left,
        Expression<Func<T, bool>> right)
        => left.Compose(right, Expression.AndAlso);

    static Expression<Func<T, bool>> Compose<T>(
        this Expression<Func<T, bool>> left,
        Expression<Func<T, bool>> right,
        Func<Expression, Expression, BinaryExpression> merge)
    {
        var param = Expression.Parameter(typeof(T), "p");
        var body = merge(
            Expression.Invoke(left, param),
            Expression.Invoke(right, param));
        return Expression.Lambda<Func<T, bool>>(body, param);
    }
}

// Usage
var filter = PredicateBuilder.True<Post>();
if (!string.IsNullOrWhiteSpace(author))
    filter = filter.And(p => p.Author.Name == author);
if (!string.IsNullOrWhiteSpace(tag))
    filter = filter.And(p => p.Tags.Any(t => t.Name == tag));
if (publishedOnly)
    filter = filter.And(p => p.IsPublished);

var results = await context.Posts.Where(filter).ToListAsync();

Keep logic on the server by composing expressions – not by if + early ToList().

Tracking vs no‑tracking: choosing deliberately

  • Default is tracking: EF keeps snapshots to detect changes on SaveChanges(). Use it for updates.
  • For queries that only read, add .AsNoTracking() or configure NoTracking by default for read paths.
services.AddDbContextPool<BloggingContext>(o =>
    o.UseSqlServer(conn)
     .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

Then opt‑in to tracking when you need it: .AsTracking().

Testing queries safely

  • SQLite in-memory mimics relational behavior better than EF’s InMemory provider for LINQ translation.
  • Assert against result shape and SQL when feasible (ToQueryString()), especially for tricky queries.
// xUnit example
[Fact]
public async Task LatestPosts_HasStableOrder()
{
    using var ctx = TestDbFactory.Create();
    var q = ctx.Posts.Where(p => p.IsPublished).OrderByDescending(p => p.PublishedOn);
    var sql = q.ToQueryString();
    Assert.Contains("ORDER BY", sql);
}

Common mistakes I still see (and how to fix them)

  1. Calling ToList() too early → keep it as IQueryable until the last moment.
  2. Filtering after Include → filter first, then include (or better, project).
  3. Using Select * via entities in APIs → project to DTOs; hide internal fields.
  4. String ToLower() without collation awareness → use Like/ILike or case‑insensitive columns.
  5. Huge Contains lists → bulk insert to a temp table or chunk queries.
  6. Multiple collection Includes → prefer .AsSplitQuery() or projections to avoid Cartesian blow‑up.
  7. Accidental client evaluation → watch logs and eliminate non‑translatable methods inside queries.

FAQ: Your first 10k LINQ queries

Should I use Include or projection for read endpoints?

Projection. Smaller payloads, better control, fewer surprises. Use Include when you’ll modify the entity graph.

Why is my query slow after adding a second Include?

You likely created a wide join. Try .AsSplitQuery() or project the collections separately.

Is GroupBy safe in EF Core?

Yes for typical aggregates (Count, Sum, Max, etc.). Avoid custom client logic inside the group key or selector.

Where do I debug generated SQL?

ToQueryString() for ad‑hoc, provider logs for production. Also tag queries to find them quickly.

When do I use compiled queries?

Super hot paths with identical shapes executed thousands of times. Don’t prematurely optimize.

How do I avoid N+1?

Project what you need in one query, or use Include for navigations when needed. Never loop and query per item.

Can I mix repository and unit‑of‑work with EF Core?

EF Core already implements a UoW. Keep abstractions thin; don’t hide IQueryable unless you must.

Should I return IQueryable from services?

No – return results or specifications. Let only the data layer assemble queries.

Conclusion: Query confidently with LINQ

You don’t need magic – just the right habits. Keep queries server‑side, project aggressively, pick the right loading pattern, and peek at the SQL when in doubt. Start applying these patterns today and your EF Core queries will be both clean and fast. Got a tricky LINQ translation you’re unsure about? Drop it in the comments and let’s dissect it together.

Post Views: 668

Mastering EF Core: From Fundamentals to Advanced Techniques

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *