Published / updated29 Aug 2026

Querying with LINQ

Before you start

You need: migrations (Article 03) and LINQ (Track 03 Article 06).

Time: about 50 minutes, plus the practice.

Learning objective

Write LINQ that translates to the SQL you intended, and recognise from the generated SQL when it has not.

Topics

• IQueryable versus IEnumerable
• Deferred execution
• Filtering, ordering, paging
• Projection with Select
• Aggregates
• What translates and what does not
• Client evaluation
• Raw SQL when LINQ is the wrong tool

IQueryable versus IEnumerable

This distinction decides whether work happens in the database or in your process.

// IQueryable — builds an expression tree, nothing has run yet
IQueryable<Student> query = _context.Students
    .Where(s => s.SchoolId == schoolId)
    .Where(s => s.ClassName == "10th")
    .OrderBy(s => s.Name);

// Execution happens here
List<Student> students = await query.ToListAsync(ct);

SELECT [s].[Id], [s].[PublicId], [s].[SchoolId], [s].[Name], ...
FROM [Student] AS [s]
WHERE [s].[SchoolId] = @__schoolId_0 AND [s].[ClassName] = N'10th'
ORDER BY [s].[Name]

Two Where calls became one WHERE with an AND. Nothing ran until ToListAsync.

The failure mode:

// Disaster: ToList() runs the query, THEN filters in memory
List<Student> students = _context.Students
    .ToList()                                  // <- every student in the database
    .Where(s => s.SchoolId == schoolId)
    .ToList();

ToList() converts IQueryable to IEnumerable, so everything after it is LINQ to Objects running in your process. On 400 rows nobody notices; on 400,000 the application runs out of memory.

Rule: ToList, ToArray, AsEnumerable, First, Single, Count execute. Everything before them composes; everything after them is in memory.

Deferred execution

IQueryable<Student> query = _context.Students.Where(s => s.SchoolId == schoolId);

query = query.Where(s => s.Status == StudentStatus.Active);   // still no SQL

if (!string.IsNullOrWhiteSpace(className))
{
    query = query.Where(s => s.ClassName == className);       // conditionally added
}

query = query.OrderBy(s => s.ClassName).ThenBy(s => s.Name);

List<Student> students = await query.ToListAsync(ct);         // one SQL statement

Composing conditionally is the main practical benefit — it replaces the string-building a raw-SQL search needs.

The trap is re-execution:

IQueryable<Student> query = _context.Students.Where(s => s.SchoolId == schoolId);

int count = await query.CountAsync(ct);         // query 1
List<Student> list = await query.ToListAsync(ct); // query 2 — runs again

Two round trips. That is sometimes what you want for paging; if not, materialise once and use the list.

Filtering

List<Student> students = await _context.Students
    .Where(s => s.SchoolId == schoolId)
    .Where(s => s.Status == StudentStatus.Active)
    .Where(s => s.ClassName == "10th" || s.ClassName == "9th")
    .Where(s => s.Name.Contains(term))
    .Where(s => s.DateOfBirth >= new DateTime(2009, 1, 1))
    .ToListAsync(ct);

LINQ	SQL
Contains(term)	LIKE '%term%'
StartsWith(term)	LIKE 'term%'
EndsWith(term)	LIKE '%term'
list.Contains(s.Id)	IN (...)
s.Address == null	IS NULL

StartsWith can use an index; Contains cannot, because a leading wildcard forces a scan. Prefer StartsWith when the search semantics allow it.

SchoolId first, in every query. A tenant filter that a single method forgets is a cross-tenant leak, and EF Core will not remind you.

Ordering and paging

List<Student> page = await _context.Students
    .Where(s => s.SchoolId == schoolId && s.Status == StudentStatus.Active)
    .OrderBy(s => s.ClassName)
    .ThenBy(s => s.Section)
    .ThenBy(s => s.Name)
    .ThenBy(s => s.Id)              // deterministic tiebreaker
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync(ct);

Skip/Take become OFFSET/FETCH NEXT.

The final ThenBy(s => s.Id) is not optional. Without a unique tiebreaker, rows with equal sort keys can appear in different positions between queries — so the same student shows on page 2 and page 3 while another is never shown at all.

Skip without OrderBy throws in EF Core, which is the right behaviour.

Projection

List<StudentListDto> students = await _context.Students
    .Where(s => s.SchoolId == schoolId && s.Status == StudentStatus.Active)
    .Select(s => new StudentListDto
    {
        PublicId = s.PublicId,
        RollNumber = s.RollNumber,
        Name = s.Name,
        ClassSection = s.ClassName + " - " + s.Section,
        ParentPhone = s.ParentPhone
    })
    .OrderBy(dto => dto.Name)
    .ToListAsync(ct);

SELECT [s].[PublicId], [s].[RollNumber], [s].[Name],
       [s].[ClassName] + N' - ' + [s].[Section], [s].[ParentPhone]
FROM [Student] AS [s]
WHERE [s].[SchoolId] = @__schoolId_0 AND [s].[Status] <> 1
ORDER BY [s].[Name]

Only five columns are fetched. Three benefits, and the third is the important one:

• Less data over the wire
• Possibly covered by an index
• Projected results are not tracked — the change tracker stays empty, so SaveChanges is fast and memory does not grow

Projecting into a DTO also means a new PasswordHash column on the entity never reaches a screen automatically.

Projection across a relationship works too:

List<ExamResultDto> results = await _context.ExamResults
    .Where(r => r.SchoolId == schoolId && r.ExamId == examId)
    .Select(r => new ExamResultDto
    {
        StudentName = r.Student.Name,
        RollNumber = r.Student.RollNumber,
        SubjectName = r.Exam.Subject.Name,
        MarksObtained = r.MarksObtained,
        MaxMarks = r.Exam.MaxMarks,
        IsAbsent = r.IsAbsent
    })
    .ToListAsync(ct);

EF Core generates the joins. No Include is needed — and using Include here would fetch whole entities you do not want.

Project first, reach for Include second. Article 6 covers when Include is genuinely required.

Aggregates

int count = await _context.Students
    .CountAsync(s => s.SchoolId == schoolId && s.Status == StudentStatus.Active, ct);

bool exists = await _context.Students
    .AnyAsync(s => s.SchoolId == schoolId && s.RollNumber == rollNumber, ct);

decimal totalPaid = await _context.FeePayments
    .Where(p => p.SchoolId == schoolId && p.FeeAccountId == accountId && !p.IsCancelled)
    .SumAsync(p => p.Amount, ct);

decimal? average = await _context.ExamResults
    .Where(r => r.ExamId == examId && !r.IsAbsent)
    .AverageAsync(r => r.MarksObtained, ct);

AnyAsync beats CountAsync(...) > 0 — it stops at the first match instead of counting everything.

SumAsync over no rows returns 0 for a non-nullable property. AverageAsync over no rows throws unless the selector is nullable — which is why MarksObtained is decimal? here.

Grouping

List<ClassSummaryDto> summary = await _context.ExamResults
    .Where(r => r.SchoolId == schoolId && r.ExamId == examId)
    .GroupBy(r => r.Student.ClassName)
    .Select(g => new ClassSummaryDto
    {
        ClassName = g.Key,
        TotalStudents = g.Count(),
        AbsentCount = g.Count(r => r.IsAbsent),
        AverageMarks = g.Where(r => !r.IsAbsent).Average(r => r.MarksObtained),
        HighestMarks = g.Max(r => r.MarksObtained)
    })
    .ToListAsync(ct);

EF Core translates common grouping to GROUP BY. Complex grouping — especially selecting whole entities per group — may fail to translate, or silently materialise everything first in older versions. Check the generated SQL for any GroupBy you write.

What does not translate

EF Core translates a large subset of LINQ. When it cannot, it throws:

The LINQ expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly.

Common causes:

// A custom C# method — EF Core cannot turn this into SQL
.Where(s => IsEligibleForPromotion(s))

// A constructor call in a predicate
.Where(s => new StudentValidator().IsValid(s))

// String formatting
.Where(s => $"{s.ClassName}-{s.Section}" == "10th-A")

// DateTime arithmetic that has no SQL equivalent
.Where(s => (DateTime.Now - s.DateOfBirth).TotalDays > 5000)

Rewrite in terms the provider understands:

// Concatenation translates; interpolation does not
.Where(s => s.ClassName + "-" + s.Section == "10th-A")

// Compute the boundary in C#, compare in SQL
DateTime cutoff = DateTime.UtcNow.AddDays(-5000);
.Where(s => s.DateOfBirth < cutoff)

The second rewrite is also sargable — the column stands alone on one side, so an index can be used. The same rule as raw SQL applies here.

Client evaluation

EF Core 3.0 and later throw rather than silently evaluating a Where in memory. That change was deliberate: earlier versions would quietly download the whole table and filter locally, and applications degraded invisibly as data grew.

To evaluate on the client deliberately, say so:

List<Student> students = await _context.Students
    .Where(s => s.SchoolId == schoolId)      // in SQL
    .ToListAsync(ct);

List<Student> eligible = students
    .Where(s => IsEligibleForPromotion(s))   // in memory, explicitly
    .ToList();

Now the boundary is visible in the code, and a reviewer can see how many rows cross it.

Client evaluation is always allowed in the final Select, since projection happens after the rows arrive.

Split queries

A query with several collection Includes produces a cartesian product:

List<Student> students = await _context.Students
    .Include(s => s.ExamResults)
    .Include(s => s.Attendances)
    .Where(s => s.SchoolId == schoolId)
    .ToListAsync(ct);

A student with 20 results and 200 attendance rows yields 4,000 rows for one student, with every student column repeated. This is "cartesian explosion", and it is a common cause of an EF Core query that is inexplicably slow.

List<Student> students = await _context.Students
    .Include(s => s.ExamResults)
    .Include(s => s.Attendances)
    .Where(s => s.SchoolId == schoolId)
    .AsSplitQuery()
    .ToListAsync(ct);

AsSplitQuery issues one statement per collection and stitches the results together. Three small queries instead of one enormous one.

The trade: split queries are not a single snapshot, so concurrent writes can produce inconsistent results. For read-mostly data that is acceptable; inside a transaction it is not.

Raw SQL

When LINQ is the wrong tool, drop to SQL without leaving EF Core.

List<Student> students = await _context.Students
    .FromSqlInterpolated($@"
        SELECT s.*
        FROM   dbo.Student AS s
        WHERE  s.SchoolId = {schoolId}
          AND  s.Name LIKE {"%" + term + "%"}")
    .AsNoTracking()
    .ToListAsync(ct);

FromSqlInterpolated looks like string interpolation and is not — EF Core turns each hole into a parameter. FromSqlRaw with a concatenated string is injectable; the interpolated form is not.

// Injectable — never do this
.FromSqlRaw("SELECT * FROM dbo.Student WHERE Name LIKE '%" + term + "%'")

// Safe
.FromSqlRaw("SELECT * FROM dbo.Student WHERE Name LIKE @p0", "%" + term + "%")

For a non-entity result, SqlQuery<T> on the database facade:

List<int> ids = await _context.Database
    .SqlQuery<int>($"SELECT Id FROM dbo.Student WHERE SchoolId = {schoolId}")
    .ToListAsync(ct);

And for statements returning nothing:

int affected = await _context.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE dbo.Student SET Status = 1 WHERE SchoolId = {schoolId} AND PublicId = {publicId}",
    ct);

When a report is easier to express in SQL than LINQ, write the SQL. That is what the Dapper track is for, and mixing the two in one application is a reasonable design.

Errors you will hit

Message	Cause	Fix
The LINQ expression could not be translated	Used a C# method EF Core cannot turn into SQL	Rewrite it, or materialise first with ToList()
The query runs 801 times	N+1 from lazy loading or a loop	Include, or one projected query
Sequence contains no elements	First/Single on an empty result	Use the OrDefault form
A filter is applied in memory, not SQL	ToList() called too early	Filter before materialising
A second operation was started on this context	Concurrent queries on one DbContext	DbContext is not thread-safe — await each

Enable SQL logging and read what EF Core actually sends. Most EF Core problems are invisible in the C# and obvious in the generated SQL.

Common mistakes

• ToList() before Where, loading the whole table
• Forgetting the SchoolId filter in one query
• Skip/Take without a deterministic OrderBy
• Count() > 0 instead of Any()
• AverageAsync over a possibly-empty set with a non-nullable selector
• Returning entities where a projection was needed
• Multiple collection Includes without AsSplitQuery
• Re-executing an IQueryable unintentionally
• Assuming a complex GroupBy translates without checking
• FromSqlRaw with a concatenated string
• Never reading the generated SQL

Practice

The course exercise is build CRUD queries; this article covers the read half.

1. Write a paged, filtered student search. Print ToQueryString() and confirm the SQL matches what you would have written.
2. Add ToList() before the Where and compare row counts fetched, using SQL Profiler or logging.
3. Remove the final ThenBy(s => s.Id) from a paged query over data with duplicate names. Page through and find a row that appears twice or never.
4. Write the same query returning entities, then returning a DTO. Compare the generated SQL and the number of columns.
5. Write a class-wise exam summary with GroupBy. Read the SQL and confirm it is a real GROUP BY.
6. Write a Where calling a custom C# method. Record the exact translation exception, then rewrite it two ways: translatable, and explicit client evaluation.
7. Write .Where(s => (DateTime.Now - s.DateOfBirth).TotalDays > 5000). Fix it to be sargable and compare the plans.
8. Load students with two collection Includes. Count the rows returned. Add AsSplitQuery and compare.
9. Use AnyAsync and CountAsync(...) > 0 for the same check. Compare the SQL.
10. Write a FromSqlInterpolated search and confirm from the log that the value became a parameter.
11. Write the same with FromSqlRaw and concatenation, then pass ' OR 1=1 -- and confirm the vulnerability.

Then run the AI drill — ask an assistant to explain a query, giving it one with ToList() in the middle. Check whether it identifies that the filter moved to memory. Verify against exercise 2.

You can now

• Write LINQ that translates to the SQL you intended
• Read the generated SQL from the log
• Recognise and fix an N+1
• Say why ToList() in the wrong place moves filtering into memory
• Choose AsNoTracking for read-only queries

Review questions

1. What is the difference between IQueryable and IEnumerable here, and why does it matter?
2. Why must a paged query have a deterministic ORDER BY?
3. Why does EF Core throw on client evaluation rather than doing it silently?
4. What is cartesian explosion, and what fixes it?

Next: CRUD operations