Practical Recipes

.NET How-To Cookbook

The "how do I…?" page. Copy-paste recipes for the things you actually need at work: C# strings, LINQ, async and JSON · modern C# features · ASP.NET Core upload, JWT, CORS and caching · EF Core · T-SQL paging, upserts and window functions · testing and dotnet CLI tricks.

25+ copy-paste recipes C# · ASP.NET Core · EF Core · T-SQL Every card = one "How do I…?"
01

C# Essentials — How To

The everyday questions: how do I format a string, transform a list, call three APIs at once, read JSON? These four cards are the recipes you'll reach for weekly. Each is self-contained — copy, paste, adapt.

How To

Work with strings — format, split, join, build

Interpolation with format specifiers covers 90% of formatting. For building strings in loops, use StringBuilder — concatenating with + in a loop creates a new string every pass.

var name = "Spyros"; var price = 1234.5m; var when = DateTime.Now;

// interpolation + format specifiers
Console.WriteLine($"Hello {name}, total {price:C2}");     // €1.234,50 (culture-aware)
Console.WriteLine($"{when:yyyy-MM-dd HH:mm}");            // 2026-07-02 10:30
Console.WriteLine($"{price,12:N2}");                      // right-aligned in 12 chars

// split & join
var csv    = "red, green , blue";
var parts  = csv.Split(',', StringSplitOptions.TrimEntries |
                            StringSplitOptions.RemoveEmptyEntries);
var joined = string.Join(" | ", parts);                   // "red | green | blue"

// building in a loop → StringBuilder, not +
var sb = new StringBuilder();
foreach (var p in parts) sb.AppendLine($"- {p}");
var report = sb.ToString();

// comparisons: ALWAYS say how you're comparing
var same = "ADMIN".Equals("admin", StringComparison.OrdinalIgnoreCase);   // true
How To

Transform collections — the LINQ one-liners

LINQ is "SQL for objects". These are the eight calls that solve most collection problems. Remember: LINQ is lazy — nothing runs until you enumerate (ToList, foreach, First…).

var orders = GetOrders();   // List<Order>

var big      = orders.Where(o => o.Total > 100).ToList();          // filter
var names    = orders.Select(o => o.CustomerName).ToList();        // transform
var sorted   = orders.OrderByDescending(o => o.Total)
                     .ThenBy(o => o.CustomerName).ToList();        // sort, 2 keys
var byCity   = orders.GroupBy(o => o.City)                         // group
                     .Select(g => new { City = g.Key,
                                        Count = g.Count(),
                                        Sum   = g.Sum(o => o.Total) });
var lookup   = orders.ToDictionary(o => o.Id);                     // fast lookup by key
var any      = orders.Any(o => o.Total > 1000);                    // exists? (stops early)
var page     = orders.Skip(20).Take(10).ToList();                  // pagination
var chunks   = orders.Chunk(50);                                   // batches of 50 (.NET 6+)

// DistinctBy / MaxBy / MinBy (.NET 6+) — no more GroupBy tricks
var perCustomer = orders.DistinctBy(o => o.CustomerId);
var biggest     = orders.MaxBy(o => o.Total);
How To

Run async work — sequential, parallel, cancellable

The three shapes of async you actually need: await one thing, await many things at once with Task.WhenAll, and support cancellation. Never block with .Result or .Wait() — that's the classic deadlock recipe. Full guide: Threading & Multitasking In Depth.

// 1. Sequential — each await finishes before the next starts
var user   = await GetUserAsync(id);
var orders = await GetOrdersAsync(user.Id);

// 2. Parallel — all three run AT THE SAME TIME (total = slowest, not sum)
var userTask    = GetUserAsync(id);
var ordersTask  = GetOrdersAsync(id);
var reviewsTask = GetReviewsAsync(id);
await Task.WhenAll(userTask, ordersTask, reviewsTask);
var (u, o, r) = (userTask.Result, ordersTask.Result, reviewsTask.Result);
//               ↑ safe HERE: tasks already completed

// 3. Cancellation — accept a token, pass it all the way down
public async Task<Report> BuildReportAsync(CancellationToken ct)
{
    var data = await FetchDataAsync(ct);        // pass it down
    ct.ThrowIfCancellationRequested();          // check between steps
    return Transform(data);
}

// ✘ NEVER: var x = GetUserAsync(id).Result;   // deadlock bait + blocks a thread
How To

Read & write JSON with System.Text.Json

The built-in serializer — no packages needed. Reuse one JsonSerializerOptions instance (it caches type metadata; creating it per call is a known performance trap).

using System.Text.Json;

public record Customer(int Id, string Name, DateTime Since, List<string> Tags);

// one shared options instance — reuse it everywhere
private static readonly JsonSerializerOptions Options = new()
{
    PropertyNamingPolicy = JsonNamingPolicy.CamelCase,   // Name → "name"
    WriteIndented        = true,                          // pretty print
    DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull
};

// object → JSON string
var json = JsonSerializer.Serialize(customer, Options);

// JSON string → object (throws on malformed JSON — try/catch at boundaries)
var back = JsonSerializer.Deserialize<Customer>(json, Options)!;

// files, streams
await using var fs = File.OpenRead("customers.json");
var list = await JsonSerializer.DeserializeAsync<List<Customer>>(fs, Options);

// just need ONE field? Don't build a class — probe with JsonDocument
using var doc = JsonDocument.Parse(json);
var name = doc.RootElement.GetProperty("name").GetString();
Section 1 wrap — the habits
  • String building in loops → StringBuilder; comparisons → always pass a StringComparison.
  • LINQ is lazy — chain freely, enumerate once.
  • Independent async calls → Task.WhenAll, never sequential awaits; never .Result.
  • One shared JsonSerializerOptions, camelCase for web APIs.
02

Modern C# — the Cool Features

C# has evolved fast. If you learned it a few versions ago, these are the features that make today's code half the length — and which you'll meet in every modern codebase, including all the tutorials on this site.

Cool Feature

Records & with-expressions — data classes in one line

A record gives you constructor, properties, value equality, ToString and non-destructive copies — for free. Perfect for DTOs, results, and anything immutable.

// this ONE line…
public record Product(int Id, string Name, decimal Price);

// …replaces ~40 lines of class + ctor + Equals + GetHashCode + ToString

var p1 = new Product(1, "Keyboard", 89.99m);
var p2 = new Product(1, "Keyboard", 89.99m);

Console.WriteLine(p1 == p2);        // True — VALUE equality (classes: False)
Console.WriteLine(p1);              // Product { Id = 1, Name = Keyboard, Price = 89.99 }

// "with" = copy, changing only some properties (original untouched)
var discounted = p1 with { Price = 69.99m };

// records can have bodies too — methods, validation, extra members
public record Money(decimal Amount, string Currency)
{
    public Money Add(Money other) =>
        Currency == other.Currency
            ? this with { Amount = Amount + other.Amount }
            : throw new InvalidOperationException("Currency mismatch");
}
Cool Feature

Pattern matching — if/else chains, deleted

Switch expressions return a value directly, and patterns can match on type, properties, ranges and even list shapes. The compiler warns when you miss a case.

// switch EXPRESSION — returns a value, no break, no fallthrough
var shipping = order.Total switch
{
    < 0        => throw new ArgumentException("negative total"),
    < 50       => 5.99m,
    < 150      => 2.99m,
    _          => 0m                       // _ = default case
};

// property patterns — match on the SHAPE of the object
var discount = customer switch
{
    { IsVip: true }                  => 0.20m,
    { Orders.Count: > 10 }           => 0.10m,
    { Since.Year: < 2020 }           => 0.05m,
    _                                => 0m
};

// type patterns + declaration in one step
decimal Area(object shape) => shape switch
{
    Circle    { Radius: var r }          => MathF.PI * r * r,
    Rectangle { Width: var w, Height: var h } => w * h,
    _ => throw new NotSupportedException()
};

// is-patterns for guard clauses
if (result is { Errors.Count: 0, Value: not null } ok)
    Process(ok.Value);
Cool Feature

Null-safety operators — goodbye NullReferenceException

Four tiny operators plus required members remove almost every null crash. Enable <Nullable>enable</Nullable> (default in new projects) and the compiler tracks nullability for you.

// ?.  — safe navigation: stops (returns null) instead of crashing
var city = order?.Customer?.Address?.City;        // string? — null if ANY link is null

// ??  — fallback value when null
var display = city ?? "Unknown";

// ??= — assign only if currently null (lazy init)
_cache ??= LoadCache();

// !   — "trust me, it's not null" (use sparingly, you're overriding the compiler)
var user = FindUser(id)!;

// required — compiler FORCES callers to set it (no more half-built objects)
public class EmailMessage
{
    public required string To      { get; init; }
    public required string Subject { get; init; }
    public string? Cc              { get; init; }   // optional, explicitly nullable
}
var msg = new EmailMessage { To = "a@b.com", Subject = "Hi" };  // Cc can be skipped
// new EmailMessage { To = "a@b.com" }  ❌ compile error: Subject missing
Cool Feature

C# 12 sugar — primary constructors & collection expressions

Two small features that remove a surprising amount of ceremony: constructor parameters directly on the class, and [...] literals for every collection type.

// PRIMARY CONSTRUCTOR — the DI boilerplate killer
// before: field + constructor + assignment (8 lines)
// after:
public class OrderService(IOrderRepository repo, ILogger<OrderService> log)
{
    public async Task<Order?> GetAsync(int id)
    {
        log.LogInformation("Fetching order {Id}", id);
        return await repo.FindAsync(id);          // params usable everywhere
    }
}

// COLLECTION EXPRESSIONS — one literal syntax for everything
int[]        nums   = [1, 2, 3];
List<string> names  = ["Ann", "Bob"];
Span<int>    span   = [9, 8, 7];

// spread operator: combine collections inline
int[] evens = [2, 4], odds = [1, 3];
int[] all   = [..evens, ..odds, 5];               // [2, 4, 1, 3, 5]

// you've seen it in this site's tutorials:
private readonly List<Product> _products = [ new() { Id = 1, ... } ];
errors["name"] = ["Name is required."];
Section 2 wrap — modernize your defaults
  • DTOs and immutable data → record; modified copies → with.
  • Value-producing branching → switch expressions; shape checks → property patterns.
  • ?. ?? ??= required — the null-safety toolkit.
  • Services with DI → primary constructors; collections → [...] with spread.
03

ASP.NET Core — How To

The web recipes every project needs sooner or later: accepting file uploads, protecting endpoints with JWT, letting the browser call you (CORS), caching responses, running work in the background, and telling the load balancer you're alive. Basics first? Start with the Minimal API guide and REST Best Practices. For async messaging see MassTransit; for Azure Table Storage, Service Bus and Functions see Azure for .NET In Depth.

Watch Out — middleware order matters

The pipeline runs in the order you register it. The safe order for the pieces on this page: UseExceptionHandlerUseCorsUseAuthenticationUseAuthorizationUseOutputCache → endpoints. CORS after auth, or auth before CORS preflights? Get it backwards and you'll chase "mysterious" 401s and blocked preflight requests.

How To

Accept file uploads — safely

Three non-negotiables: cap the size, allowlist the extensions, and never use the client's file name (it can contain ../../ path tricks). Generate your own name; keep uploads outside wwwroot.

app.MapPost("/api/upload",
    async Results<Ok<string>, BadRequest<string>> (IFormFile file) =>
{
    const long MaxBytes = 5 * 1024 * 1024;                    // 5 MB cap
    string[] allowed = [".jpg", ".png", ".pdf"];              // extension allowlist

    if (file.Length == 0 || file.Length > MaxBytes)
        return TypedResults.BadRequest("File must be 1 byte – 5 MB.");

    var ext = Path.GetExtension(file.FileName).ToLowerInvariant();
    if (!allowed.Contains(ext))
        return TypedResults.BadRequest($"Only {string.Join(", ", allowed)} allowed.");

    // NEVER trust file.FileName for the saved name — generate your own
    var safeName = $"{Guid.NewGuid()}{ext}";
    var folder   = Path.Combine(AppContext.BaseDirectory, "uploads");
    Directory.CreateDirectory(folder);

    await using var stream = File.Create(Path.Combine(folder, safeName));
    await file.CopyToAsync(stream);

    return TypedResults.Ok(safeName);
}).DisableAntiforgery();    // .NET 8+: form endpoints need this (or a real token)

// test it:
// curl -F "file=@photo.jpg" http://localhost:5088/api/upload
How To

Protect endpoints with JWT — the minimal recipe

Package: Microsoft.AspNetCore.Authentication.JwtBearer. Three parts: validate incoming tokens, issue tokens at login, protect endpoints. Keep the key in user-secrets/env vars — never in source.

// 1. VALIDATE — Program.cs
var key = builder.Configuration["Jwt:Key"]!;        // dotnet user-secrets set "Jwt:Key" "..."
builder.Services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
    .AddJwtBearer(o => o.TokenValidationParameters = new()
    {
        ValidIssuer      = "myapp",
        ValidAudience    = "myapp-clients",
        IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(key)),
        ValidateIssuerSigningKey = true,
    });
builder.Services.AddAuthorization();
// pipeline (order!):
app.UseAuthentication();
app.UseAuthorization();

// 2. ISSUE — login endpoint returns a token
app.MapPost("/api/login", (LoginDto login) =>
{
    // ...verify credentials against your user store first!...
    var token = new JwtSecurityToken(
        issuer: "myapp", audience: "myapp-clients",
        claims: [ new Claim(ClaimTypes.Name, login.Username),
                  new Claim(ClaimTypes.Role, "Admin") ],
        expires: DateTime.UtcNow.AddMinutes(30),            // SHORT expiry
        signingCredentials: new SigningCredentials(
            new SymmetricSecurityKey(Encoding.UTF8.GetBytes(key)),
            SecurityAlgorithms.HmacSha256));
    return Results.Ok(new { token = new JwtSecurityTokenHandler().WriteToken(token) });
});

// 3. PROTECT
app.MapDelete("/api/products/{id}", ...).RequireAuthorization();
app.MapGet("/api/admin/stats", ...).RequireAuthorization(p => p.RequireRole("Admin"));

// client sends:  Authorization: Bearer eyJhbGciOi...
How To

Enable CORS — let your frontend call your API

Browser says "blocked by CORS policy"? The browser blocks cross-origin calls unless your API explicitly allows that origin. Name exact origins — AllowAnyOrigin in production is an open door.

// Program.cs
const string Frontend = "frontend";

builder.Services.AddCors(options =>
    options.AddPolicy(Frontend, policy => policy
        .WithOrigins("http://localhost:5173",          // dev (Vite/React)
                     "https://myapp.com")              // production
        .AllowAnyHeader()
        .AllowAnyMethod()));

var app = builder.Build();
app.UseCors(Frontend);          // BEFORE UseAuthentication / endpoints

// or per endpoint-group:
app.MapGroup("/api/public").RequireCors(Frontend);

// Debug checklist when it still fails:
// 1. Origin must match EXACTLY (scheme + host + port — no trailing slash)
// 2. The error is only in the BROWSER — curl/Postman never hit CORS
// 3. Preflight = the OPTIONS request you see before POST/PUT
// 4. Credentials (cookies)? add .AllowCredentials() — and then
//    AllowAnyOrigin is forbidden by the spec
How To

Cache responses — output caching (.NET 8+)

If the product list changes every few minutes but is requested every few milliseconds, don't hit the database every time. Output caching stores the rendered response and replays it.

// Program.cs
builder.Services.AddOutputCache(options =>
{
    // a named policy: 60s lifetime, vary by the query string
    options.AddPolicy("Products", p => p
        .Expire(TimeSpan.FromSeconds(60))
        .SetVaryByQuery("page", "pageSize", "search"));
});

var app = builder.Build();
app.UseOutputCache();                       // after UseCors, before endpoints

// apply per endpoint…
app.MapGet("/api/products", GetProducts).CacheOutput("Products");

// …and evict when data changes:
app.MapPost("/api/products",
    async (CreateProductDto dto, IOutputCacheStore cache, CancellationToken ct) =>
{
    var created = await CreateAsync(dto);
    await cache.EvictByTagAsync("products", ct);     // pair with .Tag("products")
    return TypedResults.Created($"/api/products/{created.Id}", created);
});

// Rule of thumb: cache GETs that are expensive + frequently read
// + tolerably stale. Never cache per-user data without VaryByHeader.
How To

Run background work — BackgroundService + PeriodicTimer

Cleanup jobs, email queues, sync tasks — anything that runs on a schedule inside your API process. PeriodicTimer is the modern loop; scoped services must be resolved per tick. For persistent jobs, retries, dashboard and cron, see Hangfire & Quartz In Depth.

public class CleanupWorker(IServiceScopeFactory scopes,
                           ILogger<CleanupWorker> log) : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var timer = new PeriodicTimer(TimeSpan.FromMinutes(15));

        while (await timer.WaitForNextTickAsync(stoppingToken))
        {
            try
            {
                // BackgroundService is a singleton — DbContext is scoped.
                // Create a scope per tick to resolve scoped services:
                using var scope = scopes.CreateScope();
                var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();

                var removed = await db.Sessions
                    .Where(s => s.ExpiresAt < DateTime.UtcNow)
                    .ExecuteDeleteAsync(stoppingToken);       // EF 7+ bulk delete

                log.LogInformation("Cleanup removed {Count} sessions", removed);
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                log.LogError(ex, "Cleanup tick failed");      // log & keep looping
            }
        }
    }
}

// Program.cs
builder.Services.AddHostedService<CleanupWorker>();
How To

Add health checks — tell the world you're alive

Load balancers, Kubernetes and uptime monitors all ask the same question: "are you OK?" Give them an endpoint that actually checks your dependencies, not just returns 200.

// Program.cs
builder.Services.AddHealthChecks()
    .AddCheck("self", () => HealthCheckResult.Healthy())
    // packages exist for everything: AspNetCore.HealthChecks.SqlServer, .Redis, …
    .AddSqlServer(builder.Configuration.GetConnectionString("Default")!,
                  name: "database", failureStatus: HealthStatus.Unhealthy);

var app = builder.Build();

app.MapHealthChecks("/health/live",  new()   // "is the process up?"
{
    Predicate = r => r.Name == "self"
});
app.MapHealthChecks("/health/ready", new()   // "can I serve traffic?" (checks DB too)
{
    Predicate = _ => true
});

// GET /health/ready  →  200 "Healthy"  |  503 "Unhealthy"
//
// Kubernetes wiring:
//   livenessProbe:  httpGet: { path: /health/live,  port: 8080 }
//   readinessProbe: httpGet: { path: /health/ready, port: 8080 }
Section 3 wrap — the production checklist
  • Uploads: size cap + extension allowlist + server-generated names.
  • JWT: short expiry, key in secrets, RequireAuthorization() per endpoint/group.
  • CORS: exact origins, remember it's browser-only, preflight = OPTIONS.
  • Cache hot GETs with expiry + eviction tags; background work via BackgroundService with a scope per tick.
  • /health/live and /health/ready before you deploy anywhere serious.
04

EF Core — How To

Entity Framework Core turns your LINQ into SQL. These recipes cover the lifecycle: set it up, evolve the schema with migrations, query it efficiently (the difference between a fast app and a slow one usually lives here), and write changes safely.

How To

Set up EF Core + migrations — the full ritual

The commands you'll run on every project. A migration is a C# file describing a schema change — your database schema becomes version-controlled code.

# packages
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef        # once per machine

// DbContext + registration
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order>   Orders   => Set<Order>();
}

// Program.cs — Scoped by default: one context per HTTP request. Correct.
builder.Services.AddDbContext<AppDbContext>(o =>
    o.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

# the migration cycle — repeat every time entities change:
dotnet ef migrations add AddProductTable      # 1. capture the change as code
dotnet ef database update                     # 2. apply it to the database
dotnet ef migrations script --idempotent      # 3. SQL script for production deploys

# undo the last (un-applied) migration:
dotnet ef migrations remove
How To

Query efficiently — the 4 rules that make EF fast

EF is rarely slow — queries are. Almost every "EF is slow" story is one of these four fixes waiting to happen.

// RULE 1 — reading only? AsNoTracking (skips change-tracking bookkeeping)
var products = await db.Products.AsNoTracking()
    .Where(p => p.Price > 50).ToListAsync();

// RULE 2 — project to exactly what you need (SELECT 3 columns, not 20)
var rows = await db.Orders
    .Select(o => new OrderRowDto(o.Id, o.Customer.Name, o.Total))
    .ToListAsync();     // JOIN done in SQL, no Include needed for projections

// RULE 3 — filter/sort/page in SQL, never in memory
// ✘ BAD:  (await db.Products.ToListAsync()).Where(p => p.Price > 50)
//         ↑ fetches the ENTIRE table, then filters in C#
// ✔ GOOD: db.Products.Where(p => p.Price > 50)      ← WHERE runs in SQL
var page = await db.Products
    .Where(p => p.Price > 50)
    .OrderBy(p => p.Name).ThenBy(p => p.Id)
    .Skip(20).Take(10)
    .ToListAsync();

// RULE 4 — related data: Include for entities, projection when possible
var order = await db.Orders
    .Include(o => o.Lines).ThenInclude(l => l.Product)
    .FirstOrDefaultAsync(o => o.Id == id);

// See what SQL you generated (dev only):
builder.Services.AddDbContext<AppDbContext>(o => o
    .UseSqlServer(cs)
    .LogTo(Console.WriteLine, LogLevel.Information));
How To

Write changes — updates, bulk ops, transactions

The classic read-modify-save, the new bulk operations that skip loading entities entirely (EF 7+), and multi-step transactions when several changes must succeed or fail together.

// classic update: load → modify → save (SaveChanges = one implicit transaction)
var product = await db.Products.FindAsync(id);
if (product is null) return TypedResults.NotFound();
product.Price = 79.99m;
await db.SaveChangesAsync();

// EF 7+ BULK ops — one SQL statement, no entities loaded (much faster)
await db.Products
    .Where(p => p.Category == "Legacy")
    .ExecuteUpdateAsync(s => s
        .SetProperty(p => p.Discontinued, true)
        .SetProperty(p => p.Price, p => p.Price * 0.5m));

await db.Sessions
    .Where(s => s.ExpiresAt < DateTime.UtcNow)
    .ExecuteDeleteAsync();

// explicit transaction — several steps, all-or-nothing
await using var tx = await db.Database.BeginTransactionAsync();
try
{
    db.Orders.Add(order);
    await db.SaveChangesAsync();

    stockItem.Quantity -= order.Quantity;      // second step
    await db.SaveChangesAsync();

    await tx.CommitAsync();
}
catch { await tx.RollbackAsync(); throw; }
Section 4 wrap — the EF habits
  • Schema changes = migrations, always: migrations adddatabase update.
  • Reads: AsNoTracking + project to DTOs + filter/page in SQL.
  • Mass updates/deletes: ExecuteUpdate/ExecuteDelete — one statement, no loading.
  • Multi-step writes: explicit transaction; single SaveChanges is already atomic.
05

T-SQL — How To

Sometimes the right answer is SQL itself — a report, a data fix, a query EF can't express well. These are the five T-SQL patterns that separate "I can SELECT" from "I can solve it in the database". For schema design, normalization and the full deep-dive, see the T-SQL Deep-Dive (Section 2: Database Design).

How To

Paginate — OFFSET/FETCH with a total count

This is exactly what EF's Skip/Take compiles to. ORDER BY is mandatory — and should end with a unique column, or rows can jump between pages.

DECLARE @Page INT = 2, @PageSize INT = 10;

SELECT  p.Id,
        p.Name,
        p.Price,
        COUNT(*) OVER () AS TotalCount     -- total rows, same query, no 2nd trip
FROM    dbo.Products AS p
WHERE   p.Price >= 50
ORDER BY p.Name, p.Id                       -- unique tie-breaker = stable pages
OFFSET  (@Page - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

-- Performance note: OFFSET still *reads* the skipped rows.
-- For "infinite scroll" on big tables, keyset pagination beats OFFSET:
SELECT TOP (@PageSize) Id, Name, Price
FROM   dbo.Products
WHERE  Id > @LastSeenId                    -- seek, not skip
ORDER BY Id;
How To

Upsert — insert or update in one statement

"Insert if new, update if exists." Two patterns: MERGE (declarative, one statement) and UPDATE-then-INSERT (simpler to reason about; the locking hints close the race window).

-- Pattern A: MERGE
MERGE dbo.Products AS target
USING (VALUES (@Sku, @Name, @Price)) AS src (Sku, Name, Price)
      ON target.Sku = src.Sku
WHEN MATCHED THEN
    UPDATE SET Name = src.Name, Price = src.Price, UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED THEN
    INSERT (Sku, Name, Price) VALUES (src.Sku, src.Name, src.Price);

-- Pattern B: UPDATE first, INSERT if nothing updated
BEGIN TRAN;
    UPDATE dbo.Products WITH (UPDLOCK, HOLDLOCK)   -- lock the "gap" too
    SET    Name = @Name, Price = @Price
    WHERE  Sku = @Sku;

    IF @@ROWCOUNT = 0
        INSERT dbo.Products (Sku, Name, Price)
        VALUES (@Sku, @Name, @Price);
COMMIT;

-- Which one? MERGE = compact, but read its caveats before heavy use.
-- Pattern B = boring and predictable. Boring wins surprisingly often.
How To

Window functions — top-N per group, running totals, deltas

Window functions compute across related rows without collapsing them like GROUP BY. Three killer use-cases in one card.

-- 1. TOP-N PER GROUP: latest 3 orders per customer
WITH Ranked AS (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY o.CustomerId
                              ORDER BY o.OrderDate DESC) AS rn
    FROM dbo.Orders AS o
)
SELECT * FROM Ranked WHERE rn <= 3;

-- 2. RUNNING TOTAL: cumulative revenue per month
SELECT  OrderMonth,
        Revenue,
        SUM(Revenue) OVER (ORDER BY OrderMonth
                           ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM    dbo.MonthlyRevenue;

-- 3. DELTA vs PREVIOUS ROW: month-over-month change
SELECT  OrderMonth,
        Revenue,
        Revenue - LAG(Revenue) OVER (ORDER BY OrderMonth) AS ChangeVsPrevMonth,
        FORMAT(1.0 * Revenue / NULLIF(LAG(Revenue) OVER (ORDER BY OrderMonth), 0)
               - 1, 'P1') AS PctChange
FROM    dbo.MonthlyRevenue;

-- PARTITION BY = "restart per group" · ORDER BY = "in this order"
How To

CTEs — readable queries & walking hierarchies

A CTE (WITH ... AS) names a subquery so complex logic reads top-to-bottom. The recursive form walks trees — org charts, categories, BOMs — in one statement.

-- CTEs as building blocks: each step has a NAME
WITH RecentOrders AS (
    SELECT * FROM dbo.Orders
    WHERE  OrderDate >= DATEADD(MONTH, -3, SYSUTCDATETIME())
),
CustomerTotals AS (
    SELECT CustomerId, SUM(Total) AS Spent, COUNT(*) AS Cnt
    FROM   RecentOrders
    GROUP BY CustomerId
)
SELECT c.Name, t.Spent, t.Cnt
FROM   CustomerTotals AS t
JOIN   dbo.Customers  AS c ON c.Id = t.CustomerId
WHERE  t.Spent > 1000
ORDER BY t.Spent DESC;

-- RECURSIVE CTE: full org chart under employee 1
WITH OrgChart AS (
    SELECT Id, Name, ManagerId, 0 AS Depth          -- anchor: the root
    FROM   dbo.Employees WHERE Id = 1
    UNION ALL
    SELECT e.Id, e.Name, e.ManagerId, oc.Depth + 1  -- recurse: direct reports
    FROM   dbo.Employees AS e
    JOIN   OrgChart AS oc ON e.ManagerId = oc.Id
)
SELECT REPLICATE('  ', Depth) + Name AS Tree
FROM   OrgChart
OPTION (MAXRECURSION 32);                           -- safety rail vs cycles
How To

Make queries fast — indexes & sargability

Two ideas explain most slow queries: the missing/covering index, and predicates that can't use an index because a function wraps the column ("non-sargable").

-- Covering index: everything the query needs, so no table lookups at all
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON dbo.Orders (CustomerId, OrderDate DESC)          -- WHERE/ORDER BY columns
INCLUDE (Total, Status);                            -- SELECT-only columns

-- SARGABILITY: keep the COLUMN clean; put functions on the CONSTANT side
-- ✘ index useless (function wraps the column):
WHERE YEAR(OrderDate) = 2026
WHERE UPPER(Email) = 'A@B.COM'
WHERE Total * 1.24 > 100
-- ✔ same logic, index-friendly:
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
WHERE Email = 'a@b.com'          -- default collation is case-insensitive anyway
WHERE Total > 100 / 1.24

-- See what a query actually does:
SET STATISTICS IO, TIME ON;      -- logical reads = the number to shrink
-- + "Include Actual Execution Plan" (Ctrl+M in SSMS):
--   Index SEEK  = good (jump straight to rows)
--   Index/Table SCAN on big tables = the red flag
Section 5 wrap — the SQL toolbox
  • Paging: OFFSET/FETCH + unique tie-breaker; big tables → keyset pagination.
  • Upserts: MERGE or UPDATE-then-INSERT with UPDLOCK, HOLDLOCK.
  • ROW_NUMBER/SUM OVER/LAG = top-N per group, running totals, deltas.
  • CTEs name your steps; recursive CTEs walk trees.
  • Fast = covering indexes + sargable predicates + reading execution plans.
06

Testing & Tooling — Cool Things

The recipes that make you look senior: tests that catch regressions before users do, HTTP clients that don't leak sockets, and the dotnet CLI tricks most developers never discover.

How To

Write tests with xUnit — units AND the whole API

[Fact] = one test, [Theory] = one test, many inputs. And WebApplicationFactory boots your entire API in memory — real routing, real DI, no server to deploy.

dotnet new xunit -n MyApi.Tests
dotnet add MyApi.Tests reference MyApi
dotnet add MyApi.Tests package Microsoft.AspNetCore.Mvc.Testing

// UNIT test — Fact & Theory
public class PriceCalculatorTests
{
    [Fact]
    public void Vip_customers_get_20_percent_off()
    {
        var result = PriceCalculator.Apply(100m, isVip: true);
        Assert.Equal(80m, result);
    }

    [Theory]
    [InlineData(49.99, 5.99)]     // under 50 → full shipping
    [InlineData(50.00, 2.99)]     // boundary!
    [InlineData(150.0, 0)]        // free over 150
    public void Shipping_tiers(decimal total, decimal expected) =>
        Assert.Equal(expected, Shipping.For(total));
}

// INTEGRATION test — the WHOLE API in memory
public class ApiTests(WebApplicationFactory<Program> factory)
    : IClassFixture<WebApplicationFactory<Program>>
{
    [Fact]
    public async Task Get_products_returns_200_with_json()
    {
        var client   = factory.CreateClient();
        var response = await client.GetAsync("/api/products");

        response.EnsureSuccessStatusCode();
        Assert.Contains("application/json",
            response.Content.Headers.ContentType!.ToString());
    }
}
// needed once at the bottom of Program.cs:  public partial class Program { }
How To

Call other APIs — IHttpClientFactory + retries

new HttpClient() per request leaks sockets; one static client caches DNS forever. The factory fixes both — and .NET 8's resilience package adds retry/circuit-breaker in one line.

dotnet add package Microsoft.Extensions.Http.Resilience

// TYPED CLIENT — config in one place, inject anywhere
public class GitHubClient(HttpClient http)
{
    public async Task<GitHubUser?> GetUserAsync(string login) =>
        await http.GetFromJsonAsync<GitHubUser>($"/users/{login}");
}

// Program.cs
builder.Services.AddHttpClient<GitHubClient>(client =>
{
    client.BaseAddress = new Uri("https://api.github.com");
    client.DefaultRequestHeaders.UserAgent.ParseAdd("MyApp/1.0");
    client.Timeout = TimeSpan.FromSeconds(10);
})
.AddStandardResilienceHandler();    // ← retry + circuit breaker + timeout, done

// use it — just another dependency:
app.MapGet("/api/github/{login}", async (string login, GitHubClient gh) =>
{
    var user = await gh.GetUserAsync(login);
    return user is null ? Results.NotFound() : Results.Ok(user);
});

// ✘ NEVER in request code: using var http = new HttpClient();
//   (socket exhaustion under load — the classic production incident)
Cool Things

dotnet CLI power moves

The terminal tricks that save real time daily — auto-restart on save, secrets that never touch git, dependency audits, and code formatting in CI.

# auto-restart on every file save (live-reload for APIs)
dotnet watch

# secrets OUTSIDE the repo (per-project, maps into IConfiguration)
dotnet user-secrets init
dotnet user-secrets set "Jwt:Key" "super-secret-value"
dotnet user-secrets list
# read in code exactly like appsettings: builder.Configuration["Jwt:Key"]

# what needs updating? what's vulnerable?
dotnet list package --outdated
dotnet list package --vulnerable

# enforce formatting (fails CI when style drifts)
dotnet format --verify-no-changes

# project templates you may not know exist
dotnet new gitignore              # proper .gitignore for .NET
dotnet new editorconfig           # style rules for the whole team
dotnet new list                   # see everything installed

# faster local builds
dotnet build -c Release --nologo -v q
dotnet test  --filter "Category!=Slow"

# self-contained single file — ship ONE .exe, no runtime install needed
dotnet publish -c Release -r win-x64 /p:PublishSingleFile=true --self-contained
Section 6 wrap — the senior habits
  • Logic → [Theory] with boundary cases; endpoints → WebApplicationFactory in-memory tests.
  • Outbound HTTP → typed clients from IHttpClientFactory + AddStandardResilienceHandler().
  • Secrets → dotnet user-secrets, never appsettings in git.
  • dotnet watch, list package --vulnerable, format — free quality, zero effort.
Mastery Move — where these recipes lead

This cookbook is the "how"; the deep understanding lives in the full guides:

1. REST API Best Practices — the ASP.NET recipes here (JWT, caching, uploads) bolt straight onto the Products API you build there.

2. T-SQL Deep-Dive — window functions, CTEs and indexing each get a full treatment.

3. C# Deep-Dive — async internals, LINQ under the hood, and the language features behind every snippet on this page.

Bookmark this page. It's designed for the moment mid-task when you think "wait, how do I…?" — find the card, copy the code, keep moving. 🚀