Projection, Filtering, Ordering, Pagination, Distinctness, and NULL Semantics

Learning outcomes

A ServiceHub queue screen needs 20 work orders, ordered predictably, with only five display fields. Returning full tracked entities and paginating on a non-unique timestamp works on a tiny seed but wastes materialization and can skip/duplicate rows under concurrent changes. This lesson treats result shape and ordering as part of query correctness, not UI decoration.

1. Projection is a data-contract decision

// Existing course model — deliberately not redesigned for Chapter 08.public sealed partial class WorkOrder{    public int Id { get; private set; }    public WorkOrderPublicId PublicId { get; private set; }    public string WorkOrderNumber { get; private set; } = string.Empty;    public string CustomerName { get; private set; } = string.Empty;    public string Summary => _summary;    public WorkOrderPriority Priority { get; private set; }    public DateTimeOffset OpenedUtc { get; private set; }    // Chapter 04 also maps shadow DateTime "CreatedUtc" -> created_utc    // so the SQLite lab can compare/order a UTC timestamp server-side.    public int? AssignedTechnicianId { get; private set; }    public Technician? AssignedTechnician { get; private set; }    public ServiceAddress ServiceAddress { get; private set; } = null!;    public Guid Revision { get; private set; }}public enum WorkOrderPriority{    Low = 1,    Normal = 2,    High = 3}

var queue = db.WorkOrders    .AsNoTracking()    .Where(w => w.Priority >= WorkOrderPriority.Normal)    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Select(w => new WorkOrderQueueRow(        w.Id,        w.WorkOrderNumber,        w.CustomerName,        w.Priority,        EF.Property<DateTime>(w, "CreatedUtc")));public sealed record WorkOrderQueueRow(    int Id,    string Number,    string Customer,    WorkOrderPriority Priority,    DateTime CreatedUtc);

A projection to a DTO/record that contains only scalars avoids materializing a full WorkOrder graph and does not create tracked WorkOrder entity instances. If a projection includes an entity instance, tracking rules can still apply to that entity unless the query is no-tracking.

2. SQL should contain only the projected columns

SELECT w.work_order_id,       w.work_order_number,       w.customer_name,       w.priority,       w.created_utcFROM work_orders AS wWHERE w.priority >= 2ORDER BY w.created_utc, w.work_order_id;

Inspect the real SQL because provider mappings can add conversions. Projection reduces network payload and materialization work; it does not guarantee that the database predicate or sort is cheap. Indexes and plans still decide database cost.

3. Offset pagination requires a fully unique order

const int pageSize = 20;var pageNumber = 3;var page = await db.WorkOrders    .AsNoTracking()    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id) // tie-breaker makes ordering fully unique    .Skip((pageNumber - 1) * pageSize)    .Take(pageSize)    .Select(w => new {        w.Id,        w.WorkOrderNumber,        CreatedUtc = EF.Property<DateTime>(w, "CreatedUtc")    })    .ToListAsync(ct);

SELECT w.work_order_id, w.work_order_number, w.created_utcFROM work_orders AS wORDER BY w.created_utc, w.work_order_idLIMIT @__pageSize_0 OFFSET @__offset_1;

Microsoft’s pagination guidance explicitly warns that ordering must be fully unique. Relational databases do not promise row order without ORDER BY, and ordering only by a timestamp that can tie leaves page membership unstable.

4. Deliberately wrong: order only by OpenedUtc

// WRONG if multiple work orders can share CreatedUtc.var page = await db.WorkOrders    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .Skip(40)    .Take(20)    .ToListAsync(ct);

If several rows share the boundary timestamp, the database is free to order those tied rows differently between requests. A concurrent insert/delete before the offset can also shift later pages. The repair is not “add Id because EF likes Id”; it is to define a stable business ordering and include enough columns to make it unique.

5. Keyset pagination turns the previous key into a predicate

var lastCreatedUtc = cursor.CreatedUtc;var lastId = cursor.Id;var next = await db.WorkOrders    .AsNoTracking()    .Where(w => EF.Property<DateTime>(w, "CreatedUtc") > lastCreatedUtc ||               (EF.Property<DateTime>(w, "CreatedUtc") == lastCreatedUtc && w.Id > lastId))    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Take(20)    .Select(w => new {        w.Id,        w.WorkOrderNumber,        CreatedUtc = EF.Property<DateTime>(w, "CreatedUtc")    })    .ToListAsync(ct);

This is commonly called keyset or seek pagination. It avoids asking the database to process/skips an ever-growing offset and is more stable against changes to earlier rows. It does not naturally provide “jump directly to page 481”; offset pagination may still be appropriate when random page access is a hard requirement.

6. Distinct changes relational set semantics

var customers = await db.WorkOrders    .AsNoTracking()    .Where(w => w.Priority == WorkOrderPriority.High)    .Select(w => w.CustomerName)    .Distinct()    .OrderBy(name => name)    .ToListAsync(ct);

SELECT DISTINCT w.customer_nameFROM work_orders AS wWHERE w.priority = 3ORDER BY w.customer_name;

Distinct applies to the projected relational row shape. Distinct full entities is very different from distinct customer names because many more columns participate.

7. SQL NULL is three-valued logic

SQL comparisons can evaluate to TRUE, FALSE, or UNKNOWN when NULL participates. C# boolean expressions normally have two-valued semantics. EF Core may add null checks so translated LINQ preserves expected C# results.

int excludedTechnicianId = 42;var query = db.WorkOrders    .Where(w => w.AssignedTechnicianId != excludedTechnicianId)    .Select(w => new { w.Id, w.AssignedTechnicianId });Console.WriteLine(query.ToQueryString());

SELECT w.work_order_id, w.assigned_technician_idFROM work_orders AS wWHERE w.assigned_technician_id <> @__excludedTechnicianId_0   OR w.assigned_technician_id IS NULL;

The exact compensation depends on nullable operands and provider translation. Do not memorize this sample string; inspect the SQL generated for the actual expression.

8. Collation and case are database semantics

Whether CustomerName == "contoso" matches "Contoso" depends on the database/provider’s collation and comparison behavior. EF cannot make every engine’s string semantics identical. Avoid casually forcing ToLower() around columns as a portability fix; that can alter index usability and culture semantics. Define the required comparison policy and test it on the production-like database.

9. Hands-on lab: build the queue contract

1. Seed at least several work orders with identical CreatedUtc values so ordering ties are real.
2. Project a five-column queue DTO and verify SQL does not select unrelated columns.
3. Run offset pagination first with only CreatedUtc, then with CreatedUtc, Id.
4. Implement a next-page keyset cursor from the final row of page 1.
5. Capture ToQueryString() for both pagination forms.
6. Run a nullable technician comparison and inspect EF’s null compensation.
7. Record the database collation/string-comparison assumptions used by the lab; do not generalize them to other providers.

10. Production judgment and bridge

Treat projection and deterministic ordering as part of the API contract. Choose offset versus keyset from navigation requirements and measured plans, not fashion. Make NULL/collation semantics explicit in tests. Lesson 3 now goes one level deeper: how EF parameterizes values, caches query shapes, and rejects unsupported expressions outside the top-level projection.

Authoritative references

• Pagination - EF Core — unique ordering, offset and keyset guidance
• Efficient Querying - EF Core — projection, result limiting, index evidence
• Query null semantics - EF Core — C# versus SQL NULL compensation
• Tracking vs No-Tracking - EF Core — projection/entity tracking behavior
• Collations and Case Sensitivity - EF Core — provider/database string semantics and index implications