EF Core LINQ Queries — Beginner to Advanced

Introduction

LINQ (Language Integrated Query) is how you talk to ShopNest's database in C# — filter orders, sum revenue, paginate product lists, and join customers to orders without writing raw SQL for every screen.

This lesson covers query syntax vs method syntax, filtering, projection, sorting, grouping, joins, aggregates, pagination, raw SQL escape hatches, and performance patterns like AsNoTracking().

After this article you will

• Write LINQ method syntax fluently for ShopNest order queries
• Use Where, Select, Include, GroupBy, and aggregates
• Paginate with Skip/Take and project to DTOs
• Run FromSqlRaw when LINQ is not enough
• Avoid lazy-loading N+1 traps on order lists

Prerequisites

• Article 15 — EF Core CRUD Operations
• ShopNest DbContext with Products/Orders from Articles 13–15
• SQL Server or LocalDB configured

Concept deep-dive

Query vs method syntax

// Method syntax (preferred in teams)
var pending = await _db.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .OrderByDescending(o => o.CreatedAt)
    .Select(o => new OrderListDto { Id = o.Id, Total = o.Total })
    .ToListAsync();

// Query syntax (same IL, personal preference)
var q = from o in _db.Orders
        where o.Status == OrderStatus.Pending
        orderby o.CreatedAt descending
        select new OrderListDto { Id = o.Id, Total = o.Total };

Filtering & aggregates

Where, Any, All, Contains for filters. Aggregates: CountAsync, SumAsync, AverageAsync, Min/Max — always async in production.

Eager loading & joins

var order = await _db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Lines).ThenInclude(l => l.Product)
    .FirstOrDefaultAsync(o => o.Id == id);

// Pagination
var page = await _db.Orders
    .AsNoTracking()
    .OrderBy(o => o.Id)
    .Skip((pageNum - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

AsNoTracking() for read-only grids — no change tracking overhead. Lazy loading can cause N+1: one query per order line — prefer explicit Include.

Raw SQL

var topCustomers = await _db.Customers
    .FromSqlRaw("SELECT * FROM Customers WHERE TotalSpend > {0}", 50000)
    .AsNoTracking()
    .ToListAsync();

Hands-on — ShopNest E-Commerce Order System

1. Add Order, OrderLine, Customer entities to ShopNest DbContext.
2. OrdersController.Index: paginated list with status filter query string.
3. Dashboard action: Sum of today's revenue, count of pending orders.
4. Enable EF SQL logging in Development to inspect generated SQL.

public async Task<OrderDashboardVm> GetDashboardAsync()
{
    var today = DateTime.UtcNow.Date;
    return new OrderDashboardVm
    {
        PendingCount = await _db.Orders.CountAsync(o => o.Status == OrderStatus.Pending),
        TodayRevenue = await _db.Orders
            .Where(o => o.CreatedAt >= today && o.Status == OrderStatus.Paid)
            .SumAsync(o => o.Total)
    };
}

Common errors & best practices

• Client-side evaluation: calling .ToList() before Where pulls entire table into memory.
• SELECT *: project to DTOs — never return full entities to API consumers.
• Lazy loading in loops: enable SQL log and watch query count explode.

Interview questions

Q1: LINQ query vs method syntax?
A: Same expression tree; method syntax is more common in C# codebases.

Q2: Include vs lazy loading?
A: Include eager-loads in one query; lazy loading fires extra queries per navigation access — dangerous in loops.

Q3: When AsNoTracking?
A: Read-only lists, reports, API GET — any time you won't call SaveChanges on results.

Q4: Skip/Take pagination problem?
A: Offset pagination slows on large tables — keyset pagination (WHERE Id > lastId) scales better (Article 20).

Summary

• LINQ translates to SQL — keep filters server-side
• Include/ThenInclude prevent N+1 on order details
• AsNoTracking and DTO projection improve read performance
• FromSqlRaw for reports LINQ cannot express cleanly

Previous: EF Core CRUD Operations
Next: EF Core Relationships

FAQ