EF Core LINQ Querying: Filtering, Projections, and Performance

If you're building .NET applications with a relational database, ef core linq querying is one of the most powerful -- and most misunderstood -- tools in your toolbox.Get it right and you get clean, readable code that generates efficient SQL. Get it wrong and you end up with N+1 disasters, over-fetched result sets, and mysterious performance cliffs that only show up in production.

This ef core linq guide covers everything you need to query confidently with EF Core 10. We'll walk through how LINQ-to-SQL translation actually works, filtering, projections, eager loading, AsNoTracking, compiled queries, and more. By the end, you'll have a clear mental model for writing queries that are both readable and fast.

How EF Core LINQ Querying Translates to SQL

The first thing to understand is the difference between IQueryable<T> and IEnumerable<T>.

When you write a LINQ query against a DbSet, EF Core builds an expression tree. Nothing hits the database yet. The query is only translated to SQL and executed when you materialize it -- with ToListAsync(), FirstOrDefaultAsync(), CountAsync(), or similar terminal methods. This is called deferred execution.

LINQ deferred execution applies in plain LINQ too, but EF Core takes it further. While IEnumerable<T> runs in-process, IQueryable<T> keeps the expression tree alive so EF Core can translate the whole chain into a single SQL statement. The moment you accidentally call a method that isn't translatable -- say, a custom C# method in the middle of a Where() -- EF Core either throws or silently pulls the entire result set into memory and filters in-process. Neither outcome is what you want.

Key rule: keep your query logic inside IQueryable<T> until you're ready to materialize. Anything after AsEnumerable() or ToList() runs in C#, not SQL.

Basic EF Core LINQ Filtering with Where()

Filtering is the bread and butter of any query. EF Core translates Where() predicates directly to SQL WHERE clauses.

// Single condition: get all published blog posts
var publishedPosts = await dbContext.BlogPosts
    .Where(p => p.IsPublished)
    .ToListAsync();

// Multiple conditions chained (generates a single SQL WHERE with AND)
var recentTechPosts = await dbContext.BlogPosts
    .Where(p => p.IsPublished)
    .Where(p => p.Category == "Technology")
    .Where(p => p.PublishedAt >= DateTimeOffset.UtcNow.AddDays(-30))
    .ToListAsync();

// String operations: EF Core translates Contains() to SQL LIKE
var searchResults = await dbContext.BlogPosts
    .Where(p => p.Title.Contains("EF Core"))
    .OrderByDescending(p => p.PublishedAt)
    .ToListAsync();

Notice that each Where() call chains onto the existing IQueryable<T>. EF Core merges them into one SQL statement. You're not running three separate database round-trips. You're building one expression tree and firing one query.

String operations like Contains(), StartsWith(), and EndsWith() all translate to LIKE patterns in SQL. EF.Functions.Like() gives you more control if you need wildcard patterns. For full-text search scenarios, providers like SQL Server expose EF.Functions.FreeText() and EF.Functions.Contains().

For a deeper look at filtering patterns in LINQ itself, check out LINQ Filtering in C#.

Projections with Select()

Fetching entire entity graphs when you only need two columns is wasteful. Projections let you tell EF Core exactly what data to return -- and it generates a SELECT with only those columns.

// Define a DTO using a positional record (C# 10+ / .NET 10 style)
public sealed record BlogPostSummary(
    int Id,
    string Title,
    string AuthorName,
    DateTimeOffset PublishedAt);

// Project to the DTO inside the query -- EF Core generates a narrow SELECT
var summaries = await dbContext.BlogPosts
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.PublishedAt)
    .Select(p => new BlogPostSummary(
        p.Id,
        p.Title,
        p.Author.DisplayName,    // navigating a related entity in Select() is fine
        p.PublishedAt!.Value))
    .Take(20)
    .ToListAsync();

The SQL generated here selects only Id, Title, PublishedAt, and the author's DisplayName from the joined table. It does not fetch the rest of the Author entity. This is a significant saving when your entities have 20+ columns or large text fields.

You can also project to anonymous types for quick ad-hoc queries. But for anything crossing a method boundary or being serialized, use a named DTO record. Anonymous types get awkward fast.

Projections also let you do computed values in the projection itself. EF Core will try to translate expressions to SQL. If a computation can't be translated, it will be evaluated client-side after the database returns data -- which is fine for cheap calculations, not fine for filtering logic.

For more on projections as a LINQ concept, see LINQ Projection in C#.

Loading Related Data with Include() and ThenInclude()

EF Core supports three strategies for loading related entities: eager loading, explicit loading, and lazy loading. Eager loading -- using Include() -- is the most predictable and the one you should reach for first.

// Eager load: Blog -> Posts -> Comments (two levels deep)
var blogsWithContent = await dbContext.Blogs
    .Where(b => b.IsActive)
    .Include(b => b.Posts.Where(p => p.IsPublished))   // filtered Include (EF Core 5+)
        .ThenInclude(p => p.Comments)
    .Include(b => b.Owner)                              // separate branch
    .AsNoTracking()
    .ToListAsync();

Include() generates a SQL JOIN. ThenInclude() goes one level deeper on the navigation property you just included. You can chain multiple Include() calls off the root entity to load different branches of the entity graph.

Filtered includes (the .Where() inside Include()) were added in EF Core 5 and they're a great way to avoid pulling in soft-deleted records or records you'd filter out immediately anyway.

Watch out for cartesian explosion. When you have multiple collection Include() calls (e.g., Posts and Tags), EF Core generates a JOIN that can produce massive result sets. Each combination of Post and Tag becomes a row, so 100 posts with 10 tags each returns 1,000 rows just for those two tables. That brings us to split queries.

Split Queries: Taming Cartesian Explosion

When a single query with multiple collection Includes would explode the row count, AsSplitQuery() tells EF Core to break it into separate SQL statements -- one per Include.

var blogsWithTagsAndPosts = await dbContext.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery()   // generates separate SELECT statements, no cartesian join
    .ToListAsync();

The tradeoff: split queries use multiple round-trips instead of one. For most web request scenarios this is negligible. The benefit is SQL that doesn't explode quadratically as your data grows.

You can also set split query as the global default in your DbContextOptionsBuilder configuration:

options.UseSqlServer(connectionString, sql =>
    sql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

Use single query (the default) when you have one collection Include or simple reference navigation Includes. Use split queries when multiple collections are included and you've confirmed cartesian explosion is causing a problem.

AsNoTracking() for Read-Only EF Core LINQ Queries

Every entity you load through EF Coreis tracked by default. The change tracker watches the entity for modifications so it can generate UPDATE statements on SaveChangesAsync(). That tracking has a cost -- memory and CPU for diffing.

If you're loading data purely to read it -- for an API response, a report, a view model -- you don't need tracking.

// Read-only query: tracking disabled, faster and lighter on memory
var activeUsers = await dbContext.Users
    .AsNoTracking()
    .Where(u => u.IsActive)
    .OrderBy(u => u.LastName)
    .Select(u => new UserListItem(u.Id, u.FullName, u.Email))
    .ToListAsync();

// You can also set no-tracking as the default for a DbContext
// configured for read-heavy scenarios:
// services.AddDbContextFactory<ReadDbContext>(options =>
//     options.UseSqlServer(conn).UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

AsNoTracking() is consistently one of the easiest performance wins in EF Core. Benchmark it on your read-heavy queries -- the difference is real, especially when loading many entities.

AsNoTrackingWithIdentityResolution() is the middle ground: no change tracking, but EF Core still ensures that if the same entity appears in two places in the result graph, it's the same object reference. Use this when you've got related data and need referential consistency without the tracking overhead.

Avoiding the N+1 Problem

The N+1 problem is one of the most common performance mistakes with ORMs. Here's the pattern:

1. You load a list of N entities.
2. For each entity, you access a navigation property that wasn't loaded.
3. EF Core fires a separate SQL query for each access.
4. You've made N+1 database round-trips instead of 1 or 2.

With lazy loading enabled (it's opt-in in EF Core), this happens silently. A loop over 100 posts that accesses post.Author triggers 100 extra queries. The code looks innocent. The database log tells a different story.

The fix is eager loading with Include(). By loading the navigation property upfront, you get everything in one or two queries.

To detect N+1 in your own code, enable EF Core's query logging. Log output to the console during development and look for repeated identical queries. If you're using Serilog in .NET, you can route EF Core's Microsoft.EntityFrameworkCore.Database.Command log category to a separate sink so database queries stand out. The Logging in .NET Complete Guide covers how to configure category-level filtering.

// ❌ N+1 trap: accessing Author inside the loop fires one query per post
var posts = await dbContext.BlogPosts.ToListAsync();   // 1 query
foreach (var post in posts)
{
    Console.WriteLine(post.Author.DisplayName);       // N queries (one per post!)
}

// ✅ Correct: Include() loads Author in the same query
var posts = await dbContext.BlogPosts
    .Include(p => p.Author)
    .ToListAsync();   // 1 query, Author included
foreach (var post in posts)
{
    Console.WriteLine(post.Author.DisplayName);   // no extra queries
}

Raw SQL When LINQ Isn't Enough

Sometimes LINQ can't express what you need. Complex window functions, CTEs, database-specific features -- they all have their place. EF Core gives you an escape hatch without abandoning your entity model.

FromSqlRaw() executes raw SQL and maps the results to your entity type. Use it when you need SQL that EF Core can't generate from LINQ alone:

// FromSqlRaw: use positional parameters (never string interpolation -- SQL injection risk)
var topPosts = await dbContext.BlogPosts
    .FromSqlRaw("SELECT * FROM BlogPosts WHERE ViewCount > {0} ORDER BY ViewCount DESC", 1000)
    .AsNoTracking()
    .ToListAsync();

// FromSqlInterpolated: string interpolation syntax, but safely parameterized by EF Core
int threshold = 1000;
var topPostsSafe = await dbContext.BlogPosts
    .FromSqlInterpolated($"SELECT * FROM BlogPosts WHERE ViewCount > {threshold} ORDER BY ViewCount DESC")
    .AsNoTracking()
    .ToListAsync();

FromSqlInterpolated() is the safer version. Despite looking like string interpolation, EF Core intercepts the interpolation and creates parameterized SQL under the hood. You still can't pass column names or table names as parameters -- those must be baked into the string directly (with careful validation).

You can also compose LINQ on top of raw SQL results. FromSqlInterpolated() returns an IQueryable<T>, so you can chain Where(), OrderBy(), Select(), and other LINQ operators after it. EF Core wraps your raw SQL as a subquery and generates the additional SQL around it.

Note: In EF Core 8+, FromSql(FormattableString) is the new preferred method -- it accepts a FormattableString directly and parameterizes automatically, replacing FromSqlInterpolated. Both work in EF Core 10.

For arbitrary SQL that doesn't map to entities -- aggregations, stored procedure calls, schema queries -- use dbContext.Database.ExecuteSqlRawAsync() or ExecuteSqlInterpolatedAsync(). For reading scalar values or non-entity result sets, dbContext.Database.SqlQueryRaw<T>() (introduced in EF Core 7) maps results to any type with matching column names.

Compiled Queries for High-Performance Hot Paths

Every time EF Core executes a LINQ query, it compiles the expression tree into a SQL string. For most queries on most endpoints, this overhead is invisible. For high-throughput hot paths -- a lookup called 10,000 times per second, a tight inner loop in a batch processor -- it adds up.

Compiled queries precompile the LINQ expression once at startup. Subsequent calls reuse the compiled plan with zero translation overhead.

// Define compiled queries as static fields -- compiled once, reused everywhere
public static class BlogQueries
{
    // EF.CompileAsyncQuery returns a delegate you call like a regular async method
    public static readonly Func<AppDbContext, int, Task<BlogPost?>> GetPostById =
        EF.CompileAsyncQuery(
            (AppDbContext ctx, int id) =>
                ctx.BlogPosts
                    .AsNoTracking()
                    .FirstOrDefault(p => p.Id == id));

    public static readonly Func<AppDbContext, string, IAsyncEnumerable<BlogPostSummary>> GetPublishedByCategory =
        EF.CompileAsyncQuery(
            (AppDbContext ctx, string category) =>
                ctx.BlogPosts
                    .Where(p => p.IsPublished && p.Category == category)
                    .OrderByDescending(p => p.PublishedAt)
                    .Select(p => new BlogPostSummary(p.Id, p.Title, p.Author.DisplayName, p.PublishedAt!.Value)));
}

// Usage in a service -- no LINQ compilation overhead at call time
public async Task<BlogPost?> GetPostByIdAsync(int id)
{
    return await BlogQueries.GetPostById(_dbContext, id);
}

Compiled queries have constraints. The main remaining constraint is you can't compose additional LINQ operators after calling the compiled delegate, and queries with dynamic filtering shapes should use regular LINQ (whose translation is cached anyway). Compiled queries were once restricted from using Include(), but this limitation was lifted in EF Core 8 -- in EF Core 10, Include() works in compiled queries.

For queries that need dynamic filtering or variable Includes, plain LINQ is still the right tool. EF Core's internal query cache already memoizes compiled plans for identical expression trees across calls, so you only pay the compilation cost once per unique query shape -- even without EF.CompileAsyncQuery. Compiled queries skip even that cache lookup.

Projecting to DTOs vs. Include()

Here's a question worth pausing on: when should you use Select() projections vs. Include() for loading related data?

The short answer: prefer Select() when you know exactly what data the caller needs. Use Include() when you need the full entity graph for update operations or when the shape of the result isn't predetermined.

Select() generates narrow SQL. Only the columns you request come back. This is especially valuable for entities with large text fields, binary data, or wide tables. A blog post entity might have a body column with 50KB of HTML. If you're just rendering a list of post titles and dates, loading the body is pure waste.

Include() is simpler when you need the full entity. It's the right choice when you're loading something you might later call SaveChangesAsync() on. Tracked entities loaded via Include participate in change tracking; projected DTOs don't.

The pattern that works well in practice: use Select() + DTOs for reads (list endpoints, detail views, reports) and use Include() for edit/update workflows where you need the full tracked entity.

Explore more about LINQ projection patterns in LINQ Projection in C# and for a broader foundation, the LINQ in C# Complete Guide is worth bookmarking.

Bringing It All Together

Mastering ef core linq integration is about understanding one mental model: you're building expression trees that EF Core translates to SQL. The surface area is large, but it all flows from that one idea. Everything runs in the database until you materialize. Keep filtering and projection inside IQueryable<T>, use Include() for related data you need tracked, use Select() to project to narrow DTOs for reads, and reach for AsNoTracking() on every read-only path.

The specific topics we covered here -- filtering, projections, eager loading, split queries, N+1 prevention, raw SQL, and compiled queries -- each target a specific failure mode you'll encounter as your application scales. Know which tool fits which problem and you'll write queries that stay fast under load.

FAQ

What is the difference between IQueryable and IEnumerable in EF Core?

IQueryable<T> holds an expression tree that EF Core translates to SQL. The query runs in the database when materialized. IEnumerable<T> executes in C# memory. Once you call AsEnumerable() or ToList(), filtering and projection happen in-process, not in SQL. This means accidentally switching from IQueryable to IEnumerable mid-query can silently load your entire table into memory before filtering.

When should I use AsNoTracking()?

Use AsNoTracking() any time you load entities purely to read them -- API responses, view models, reports, projections. Change tracking adds memory and CPU overhead that's wasted if you never call SaveChangesAsync() on the loaded data. It's one of the easiest performance improvements in EF Core and should be your default for list queries and reports. For single-entity lookups (like FindAsync) the overhead is smaller but AsNoTracking() is still appropriate when you won't modify the result.

How do I fix the N+1 query problem in EF Core?

Load related entities upfront with Include() instead of accessing navigation properties in a loop. Enable EF Core query logging during development to spot N+1 -- repeated identical parameterized queries in the log are a giveaway. For read scenarios, projecting with Select() that traverses navigation properties is another way to load related data without N+1, because EF Core translates it to a JOIN in the SQL.

What is AsSplitQuery() and when should I use it?

AsSplitQuery() tells EF Core to generate multiple SQL statements instead of one big JOIN when you have multiple collection Include() calls. Without it, EF Core generates a JOIN that can produce cartesian explosion -- the result set row count multiplies for every collection combination. Use AsSplitQuery() when you're including two or more collections and the single-query result set is growing unreasonably large.

What is the difference between FromSqlRaw and FromSqlInterpolated?

Both execute raw SQL and map results to your entity type. FromSqlRaw() uses positional parameter placeholders ({0}, {1}). FromSqlInterpolated() uses C# string interpolation syntax but safely converts interpolated values to SQL parameters -- there's no SQL injection risk with it. Prefer FromSqlInterpolated() for readability. Never use plain string concatenation to build SQL strings.

When should I use compiled queries?

Use compiled queries for high-throughput hot paths where the same fixed-shape query runs thousands of times per second. They skip the LINQ-to-SQL compilation step entirely. The main constraint is that compiled queries can't be composed with additional LINQ operators after compilation. The Include() restriction was lifted in EF Core 8, so compiled queries can include navigations in EF Core 10. They're best for simple, parameterized lookups and list queries on hot endpoints.

How does EF Core handle LINQ joins compared to Include()?

Include() is EF Core's navigation-property-aware way to load related data -- it knows about your entity relationships and tracks loaded entities automatically. LINQ Join() is a lower-level explicit join that works like SQL JOIN but doesn't benefit from relationship metadata. For loading entities along their defined relationships, use Include(). Use explicit LINQ joins when you need to join data that doesn't have a navigation property defined, or when projecting to a DTO from multiple entities that EF Core wouldn't otherwise relate. See LINQ Joins in C# for deep coverage of LINQ join patterns.