// ❌ O(n²) — Contains on List is O(n)
var seen = new List<int>();
foreach (var n in nums)
    if (!seen.Contains(n)) seen.Add(n); // O(n) per call!

// ✅ O(n) — Contains on HashSet is O(1)
var seen = new HashSet<int>();
foreach (var n in nums)
    seen.Add(n); // duplicates auto-ignored

// GENERICS — type-safe reusable containers
public class Pair<T, U> { public T First; public U Second; }
var p = new Pair<string, int> { First = "age", Second = 30 };

// DELEGATES — type-safe function pointer
public delegate int Transform(int x);
Transform square = x => x * x;   // lambda assigned to delegate
Console.WriteLine(square(5));     // 25

// Func / Action / Predicate (built-in generic delegates)
Func<int, int, int>    add  = (a, b) => a + b;   // returns value
Action<string>         log  = s => Console.WriteLine(s);
Predicate<int>         isEven = n => n % 2 == 0;

// EVENTS
public class Button {
    public event EventHandler Clicked;
    public void Click() => Clicked?.Invoke(this, EventArgs.Empty);
}
var btn = new Button();
btn.Clicked += (s, e) => Console.WriteLine("Clicked!");

var nums = new[] { 1, 2, 3, 4, 5, 6, 7, 8 };

// Filtering & Projecting
var evens    = nums.Where(x => x % 2 == 0);          // [2,4,6,8]
var squares  = nums.Select(x => x * x);               // [1,4,9,16...]
var flatList = new[]{ new[]{1,2}, new[]{3,4} }
               .SelectMany(x => x);                   // [1,2,3,4]

// Aggregation
int  sum   = nums.Sum();                              // 36
int  max   = nums.Max();                              // 8
int  count = nums.Count(x => x > 4);                 // 4
bool any   = nums.Any(x => x > 10);                  // false
bool all   = nums.All(x => x > 0);                   // true

// Sorting
var asc  = nums.OrderBy(x => x);
var desc = nums.OrderByDescending(x => x);

// Grouping
var words = new[] { "cat", "car", "bat", "bar" };
var byLetter = words.GroupBy(w => w[0]);
// Key 'c' → ["cat","car"], Key 'b' → ["bat","bar"]

// First / Single / Default
var first  = nums.First(x => x > 3);     // 4 — throws if none
var firstOr = nums.FirstOrDefault(x => x > 100); // 0 (default)

// Set operations
var a = new[] { 1, 2, 3, 4 };
var b = new[] { 3, 4, 5, 6 };
a.Union(b)     // [1,2,3,4,5,6]
a.Intersect(b) // [3,4]
a.Except(b)    // [1,2]

// Joins
var users   = new[] { (1,"Alice"), (2,"Bob") };
var orders  = new[] { (1,"Laptop"), (1,"Phone"), (2,"Desk") };
var joined  = users.Join(orders,
    u => u.Item1, o => o.Item1,
    (u, o) => $"{u.Item2} bought {o.Item2}");

// Basic async/await pattern
public async Task<string> FetchUserAsync(int id)
{
    // await releases the thread while waiting — non-blocking
    var response = await httpClient.GetStringAsync($"/users/{id}");
    return response;
}

// Task.WhenAll — parallel async operations
var tasks = ids.Select(id => FetchUserAsync(id));
var results = await Task.WhenAll(tasks);  // all run in parallel

// Task.WhenAny — first one wins (e.g., race/timeout)
var winner = await Task.WhenAny(slowTask, Task.Delay(2000));

// CancellationToken — cooperative cancellation
public async Task ProcessAsync(CancellationToken ct)
{
    for (int i = 0; i < 100; i++) {
        ct.ThrowIfCancellationRequested();
        await DoWorkAsync(i, ct);
    }
}

// ConfigureAwait(false) — avoid deadlocks in libraries
// Don't use ConfigureAwait in ASP.NET Core (no sync context)
var data = await repo.GetAsync().ConfigureAwait(false);

// ValueTask — avoids allocation when result is often sync
public ValueTask<int> GetCachedAsync(int key)
{
    if (_cache.TryGetValue(key, out var v)) return ValueTask.FromResult(v);
    return new ValueTask<int>(FetchAsync(key));
}

// ❌ String concatenation in loop = O(n²) heap allocations
string result = "";
for (int i = 0; i < 10000; i++) result += i; // creates 10000 strings!

// ✅ StringBuilder = single mutable buffer
var sb = new StringBuilder();
for (int i = 0; i < 10000; i++) sb.Append(i);
string result = sb.ToString();

// ✅ Span<T> — slice without allocation (stack-allocated view)
string s = "Hello, World!";
ReadOnlySpan<char> world = s.AsSpan(7, 5); // "World" — no copy!

// ✅ stackalloc — allocate arrays on stack (avoid GC)
Span<int> buf = stackalloc int[256]; // fine for small fixed-size buffers

// record — immutable value-semantic class (C# 9+)
public record Point(int X, int Y); // auto equality, ToString, deconstruct
var p1 = new Point(1, 2);
var p2 = p1 with { Y = 5 };  // non-destructive mutation

// REPOSITORY PATTERN (D in SOLID — depend on abstraction)
public interface IUserRepository {
    Task<User> GetByIdAsync(int id);
}
public class SqlUserRepository : IUserRepository { ... }
public class MockUserRepository : IUserRepository { ... }  // for tests

// FACTORY PATTERN
public static class ShapeFactory {
    public static IShape Create(string type) => type switch {
        "circle"   => new Circle(),
        "square"   => new Square(),
        _          => throw new ArgumentException($"Unknown: {type}")
    };
}

// STRATEGY PATTERN
public interface ISortStrategy { void Sort(int[] data); }
public class QuickSort  : ISortStrategy { ... }
public class MergeSort  : ISortStrategy { ... }
public class Sorter {
    private ISortStrategy _strategy;
    public Sorter(ISortStrategy s) => _strategy = s;
    public void Sort(int[] d) => _strategy.Sort(d);
}

// PATTERN MATCHING (C# 8+)
object obj = 42;
if (obj is int n && n > 0) Console.WriteLine($"Positive: {n}");

// Switch expression
string Classify(int n) => n switch {
    0        => "zero",
    < 0      => "negative",
    > 0 and < 10 => "small",
    _        => "large"
};

// Property pattern
record Point(int X, int Y);
string Describe(Point p) => p switch {
    { X: 0, Y: 0 } => "origin",
    { X: 0 }        => "on Y axis",
    { Y: 0 }        => "on X axis",
    _               => "other"
};

// NULLABLE REFERENCE TYPES (C# 8+)
string? name = null;        // nullable — compiler warns on deref
string  name2 = "Alice";    // non-nullable — compiler enforces init

// Null-coalescing
string result  = name ?? "default";
string result2 = name ??= "computed and assigned";

// Null-conditional
int? len = name?.Length;  // null if name is null, else Length

// INIT-ONLY & REQUIRED (C# 9/11)
public class Config {
    public required string Host { get; init; }  // must be set at creation
    public int Port { get; init; } = 443;
}
var cfg = new Config { Host = "example.com" };

// ✅ Catch specific exceptions, not Exception
try { var n = int.Parse(userInput); }
catch (FormatException ex) { /* bad format */ }
catch (OverflowException ex) { /* too large */ }

// ❌ Never swallow exceptions silently
catch (Exception) { }  // BAD — hides bugs

// ✅ throw (not throw ex) to preserve stack trace
catch (Exception ex) {
    _logger.LogError(ex, "Failed to process");
    throw;  // re-throws with original stack trace intact
}

// ✅ Custom exceptions for domain errors
public class InsufficientFundsException : Exception {
    public decimal Amount { get; }
    public InsufficientFundsException(decimal amount)
        : base($"Insufficient funds: need {amount}") => Amount = amount;
}

// ✅ IDisposable with using — guaranteed cleanup
using var conn = new SqlConnection(connectionString);
// conn.Dispose() called even if exception is thrown

// Common exception types to know
// NullReferenceException    — accessing member on null
// ArgumentNullException     — null passed to method
// ArgumentOutOfRangeException — index beyond bounds
// InvalidOperationException — method invalid for current state
// NotImplementedException   — placeholder (never ship this!)
// OperationCanceledException — CancellationToken triggered

// Garbage Collector — 3 generations
// Gen 0: short-lived objects (local vars) — collected most often
// Gen 1: survived Gen 0, buffer zone
// Gen 2: long-lived objects (statics, caches) — collected rarely
// LOH:   Large Object Heap — objects >= 85KB, collected with Gen 2

// GC.Collect() — avoid in production! GC knows best.

// Finalize vs Dispose
public class Resource : IDisposable {
    private bool _disposed;
    public void Dispose() {
        Dispose(true);
        GC.SuppressFinalize(this);  // tell GC: skip finalizer
    }
    protected virtual void Dispose(bool disposing) {
        if (_disposed) return;
        if (disposing) { /* free managed resources */ }
        /* free unmanaged resources (handles, etc.) */
        _disposed = true;
    }
    ~Resource() => Dispose(false);  // finalizer safety net
}

// Reflection (use sparingly — slow!)
var type   = typeof(MyClass);
var method = type.GetMethod("MyMethod");
method.Invoke(instance, new object[] { arg1 });

// DEPENDENCY INJECTION lifetimes
builder.Services.AddTransient<IEmailService, SmtpEmailService>();
// Transient  — new instance every time (stateless services)
// Scoped     — one per HTTP request (DbContext, current user)
// Singleton  — one for entire app lifetime (caches, config)

builder.Services.AddScoped<IUserRepo, SqlUserRepo>();
builder.Services.AddSingleton<ICache, MemoryCache>();

// MIDDLEWARE pipeline (order matters!)
app.UseExceptionHandler("/error");   // 1. catch all unhandled
app.UseHttpsRedirection();           // 2. force HTTPS
app.UseAuthentication();             // 3. who are you?
app.UseAuthorization();              // 4. what can you do?
app.MapControllers();                // 5. route to controller

// MINIMAL API (ASP.NET 6+)
app.MapGet("/users/{id}", async (int id, IUserRepo repo) =>
    await repo.GetByIdAsync(id) is User u
        ? Results.Ok(u)
        : Results.NotFound());

// FILTERS order: Authorization → Resource → Action → Result → Exception
// Use IActionFilter for cross-cutting concerns (logging, validation)

Generics let you write type-safe, reusable code without duplicating logic for every type. The compiler enforces types at compile time, not at runtime, so you get zero boxing overhead for value types.

// Generic class — works for any type T
public class Stack<T>
{
    private readonly List<T> _items = new();
    public void Push(T item) => _items.Add(item);
    public T Pop()
    {
        var last = _items[^1];
        _items.RemoveAt(_items.Count - 1);
        return last;
    }
    public int Count => _items.Count;
}

var ints    = new Stack<int>();
var strings = new Stack<string>();
ints.Push(42);
strings.Push("hello");

// Generic method
public T Max<T>(T a, T b) where T : IComparable<T>
    => a.CompareTo(b) >= 0 ? a : b;

Console.WriteLine(Max(3, 7));         // 7
Console.WriteLine(Max("apple","fig")); // fig

// Constraints
public class Repository<T> where T : class, IEntity, new()
{
    // T must be: reference type, implement IEntity, have parameterless ctor
    public T CreateDefault() => new T();
}

// Generic interface + covariance (out = read-only, covariant)
public interface IReader<out T> { T Read(); }

// Multiple type params
public class Pair<TKey, TValue>
{
    public TKey   Key   { get; init; }
    public TValue Value { get; init; }
}
var p = new Pair<string, int> { Key = "age", Value = 30 };

A delegate is a type-safe function pointer — a variable that holds a reference to a method. Delegates are the backbone of events, callbacks, and LINQ.

// Custom delegate declaration
public delegate int MathOp(int x, int y);

// Assign a method
MathOp add = (a, b) => a + b;
MathOp mul = (a, b) => a * b;

Console.WriteLine(add(3, 4)); // 7
Console.WriteLine(mul(3, 4)); // 12

// Multicast — delegate chains multiple methods
Action<string> log = Console.WriteLine;
log += s => File.AppendAllText("log.txt", s + "\n"); // adds second handler
log("hello"); // calls BOTH Console.WriteLine AND file write

// Built-in generic delegates (prefer these over custom ones)
Func<int, int, int> sum  = (a, b) => a + b;   // returns int
Action<string>      print = Console.WriteLine;   // returns void
Predicate<int>      isOdd = n => n % 2 != 0;    // returns bool

// Delegate as method parameter (callback pattern)
public void ProcessItems(List<int> items, Action<int> onEach)
{
    foreach (var item in items)
        onEach(item);
}

ProcessItems(new List<int> { 1, 2, 3 }, n => Console.WriteLine(n * 10));
// prints: 10, 20, 30

// Delegate with return value — chaining only keeps last return
Func<int, int> doubleIt = x => x * 2;
Func<int, int> addTen   = x => x + 10;
// You can't multicast Func usefully — use Action for side effects

Lambdas are inline anonymous functions. They're syntactic sugar over delegates — every lambda compiles to a delegate or expression tree under the hood.

// Expression lambda — single expression, implicit return
Func<int, int> square = x => x * x;

// Statement lambda — block body
Func<int, int> absolute = x =>
{
    if (x < 0) return -x;
    return x;
};

// Multiple parameters
Func<int, int, int> max = (a, b) => a > b ? a : b;

// No parameters
Action greet = () => Console.WriteLine("Hello!");

// Closures — lambda captures outer variables by reference
int multiplier = 3;
Func<int, int> triple = x => x * multiplier;  // closes over 'multiplier'
Console.WriteLine(triple(5)); // 15
multiplier = 10;
Console.WriteLine(triple(5)); // 50 — captured by reference, not value!

// Classic closure pitfall in loops
var funcs = new List<Func<int>>();
for (int i = 0; i < 3; i++)
{
    int captured = i;              // ✅ capture a copy
    funcs.Add(() => captured);
}
funcs.ForEach(f => Console.Write(f() + " ")); // 0 1 2  ✅

// Expression trees — lambdas as data (used by EF Core, ORM queries)
Expression<Func<int, bool>> expr = x => x > 5;
// EF Core translates this to SQL: WHERE x > 5
// Regular Func<int,bool> would execute in memory — can't translate!

Events are a restricted form of multicast delegates. The event keyword enforces encapsulation — only the declaring class can invoke the event; external code can only subscribe/unsubscribe.

// 1. Standard event pattern
public class OrderService
{
    // Declare event using built-in EventHandler<T>
    public event EventHandler<OrderEventArgs>? OrderPlaced;

    public void PlaceOrder(Order order)
    {
        // ... process order ...
        OnOrderPlaced(new OrderEventArgs(order));
    }

    // Protected virtual — allows subclasses to override
    protected virtual void OnOrderPlaced(OrderEventArgs e)
        => OrderPlaced?.Invoke(this, e);  // null-safe invoke
}

public class OrderEventArgs : EventArgs
{
    public Order Order { get; }
    public OrderEventArgs(Order o) => Order = o;
}

// 2. Subscribe and unsubscribe
var svc = new OrderService();

// Subscribe with +=
svc.OrderPlaced += (sender, e) => Console.WriteLine($"Order placed: {e.Order.Id}");

// Named handler — needed to later unsubscribe
void SendEmail(object? sender, OrderEventArgs e) =>
    EmailService.Send(e.Order.CustomerEmail, "Order confirmation");

svc.OrderPlaced += SendEmail;
svc.OrderPlaced -= SendEmail;  // unsubscribe — prevents memory leaks!

// 3. Custom delegate event (non-standard, but valid)
public delegate void DataReceivedHandler(byte[] data);
public event DataReceivedHandler? DataReceived;

// 4. Action-based event (simpler, no EventArgs)
public event Action<string>? StatusChanged;
StatusChanged?.Invoke("Processing...");

Extension methods add new methods to existing types without modifying them or using inheritance. LINQ is entirely built on extension methods over IEnumerable<T>.

// Rules: static class, static method, first param = "this TypeName name"
public static class StringExtensions
{
    // Extend string with IsNullOrEmpty shorthand
    public static bool IsEmpty(this string s)
        => string.IsNullOrWhiteSpace(s);

    // Extend string with truncation
    public static string Truncate(this string s, int maxLength)
        => s.Length <= maxLength ? s : s[..maxLength] + "…";

    // Capitalize first letter
    public static string Capitalize(this string s)
        => s is { Length: > 0 } ? char.ToUpper(s[0]) + s[1..] : s;
}

// Usage — called like instance methods
string name = "  ";
Console.WriteLine(name.IsEmpty());           // true

string title = "The quick brown fox jumps";
Console.WriteLine(title.Truncate(10));       // "The quick…"
Console.WriteLine("hello".Capitalize());     // "Hello"

// Extend IEnumerable<T>
public static class EnumerableExtensions
{
    public static IEnumerable<T> WhereNotNull<T>(this IEnumerable<T?> source)
        where T : class
        => source.Where(x => x is not null)!;

    // Batch items into chunks
    public static IEnumerable<IEnumerable<T>> Batch<T>(
        this IEnumerable<T> source, int size)
    {
        var batch = new List<T>(size);
        foreach (var item in source)
        {
            batch.Add(item);
            if (batch.Count == size)
            {
                yield return batch;
                batch = new List<T>(size);
            }
        }
        if (batch.Count > 0) yield return batch;
    }
}

// Extend your own domain types
public static class OrderExtensions
{
    public static decimal Total(this IEnumerable<OrderLine> lines)
        => lines.Sum(l => l.Quantity * l.UnitPrice);

    public static bool IsOverdue(this Order order)
        => order.DueDate < DateTime.UtcNow && !order.IsPaid;
}

LINQ (Language Integrated Query) lets you query any IEnumerable<T> using a consistent syntax — in-memory collections, databases (EF Core), XML, and more.

var products = new List<Product>
{
    new("Laptop",  1200, "Electronics"),
    new("Phone",    800, "Electronics"),
    new("Desk",     350, "Furniture"),
    new("Monitor",  600, "Electronics"),
    new("Chair",    250, "Furniture"),
};

// ── Method syntax (most common) ──
var cheapElectronics = products
    .Where(p => p.Category == "Electronics" && p.Price < 1000)
    .OrderBy(p => p.Price)
    .Select(p => new { p.Name, p.Price });

// ── Query syntax (SQL-like, compiles to same thing) ──
var same = from p in products
           where p.Category == "Electronics" && p.Price < 1000
           orderby p.Price
           select new { p.Name, p.Price };

// ── Aggregation ──
decimal avgPrice = products.Average(p => p.Price);      // 640
decimal totalRevenue = products.Sum(p => p.Price);
Product mostExpensive = products.MaxBy(p => p.Price)!;  // Laptop

// ── Grouping ──
var byCategory = products
    .GroupBy(p => p.Category)
    .Select(g => new {
        Category = g.Key,
        Count    = g.Count(),
        AvgPrice = g.Average(p => p.Price)
    });
// Electronics: 3 items, avg $866.67
// Furniture:   2 items, avg $300

// ── Joins ──
var orders   = new[] { (ProductName:"Laptop", Qty:2), (ProductName:"Desk", Qty:1) };
var invoices = products.Join(orders,
    p => p.Name,
    o => o.ProductName,
    (p, o) => new { p.Name, Total = p.Price * o.Qty });

// ── Deferred vs immediate execution ──
var query = products.Where(p => p.Price > 500);  // NOT executed yet
var list  = query.ToList();                        // Executed HERE

// Modifying the source after defining query matters!
products.Add(new("GPU", 900, "Electronics"));
var count1 = query.Count();   // 4 — includes new GPU (deferred)
var count2 = list.Count;      // 3 — snapshot, doesn't include GPU

C# has two kinds of nullability: nullable value types (int?) which wrap structs in a Nullable<T> container, and nullable reference types (C# 8+) which are a compile-time annotation system.

// ── Nullable VALUE types (int?, double?, DateTime?) ──
int? age = null;
int? score = 42;

// HasValue / Value
if (age.HasValue) Console.WriteLine(age.Value);

// GetValueOrDefault
int safe = age.GetValueOrDefault(0);  // 0 if null

// Null-coalescing operator ??
int display = age ?? -1;           // -1 if null
age ??= 18;                        // assign only if currently null

// Null-conditional ?.
int? len = someString?.Length;     // null if someString is null

// ── Nullable REFERENCE types (C# 8+ #nullable enable) ──
#nullable enable
string  name   = "Alice";     // non-nullable — compiler warns if null assigned
string? middle = null;        // nullable — OK to be null

// Compiler tracks null state through flow analysis
if (middle != null)
    Console.WriteLine(middle.Length);  // safe — compiler knows it's not null

// Null-forgiving operator ! — suppresses compiler warning (use sparingly)
string definitelyNotNull = middle!.ToUpper();  // ⚠️ you're promising it's not null

// ── Practical patterns ──
// TryGet pattern returns null instead of throwing
public User? FindUser(int id) => _db.Users.FirstOrDefault(u => u.Id == id);

var user = FindUser(42);
var email = user?.Email ?? "unknown@example.com";  // safe chain

// Pattern matching with nulls
if (FindUser(id) is User u)
    Console.WriteLine(u.Name);  // u is guaranteed non-null here

// Record with optional fields
public record CreateOrderRequest(
    string ProductId,
    int    Quantity,
    string? CouponCode = null   // optional
);

dynamic bypasses compile-time type checking — member resolution happens at runtime via the DLR (Dynamic Language Runtime). Useful for COM interop, JSON parsing, and interop with Python/JavaScript runtimes.

// Basic dynamic — no compile-time checking
dynamic d = 42;
Console.WriteLine(d + 8);    // 50

d = "hello";
Console.WriteLine(d.Length); // 5 — resolved at runtime

d = new List<int> { 1, 2, 3 };
Console.WriteLine(d.Count);  // 3

// ❌ Runtime error — not compile error
// d.NonExistentMethod(); // throws RuntimeBinderException

// ── ExpandoObject — dynamic property bags ──
dynamic person = new System.Dynamic.ExpandoObject();
person.Name = "Alice";
person.Age  = 30;
person.Greet = (Action)(() => Console.WriteLine($"Hi, I'm {person.Name}"));

Console.WriteLine(person.Name); // Alice
person.Greet();                  // Hi, I'm Alice

// Cast to IDictionary to inspect properties
var dict = (IDictionary<string, object?>)person;
foreach (var kv in dict)
    Console.WriteLine($"{kv.Key}: {kv.Value}");

// ── JSON parsing (before System.Text.Json — still used in scripts) ──
dynamic json = Newtonsoft.Json.JsonConvert.DeserializeObject(
    "{\"name\":\"Bob\",\"score\":99}");
Console.WriteLine(json.name);   // Bob
Console.WriteLine(json.score);  // 99

// ── COM Interop (Excel automation) ──
dynamic excel = Activator.CreateInstance(
    Type.GetTypeFromProgID("Excel.Application")!);
excel.Visible = true;
excel.Workbooks.Add();

// ── Prefer var over dynamic ──
var typed = new { Name = "Alice" };  // ✅ anonymous type — still compile-time safe
dynamic dyn = new { Name = "Alice" };  // ❌ loses type safety for no benefit

Exceptions are the C# mechanism for propagating errors up the call stack. Understanding when to throw, catch, and re-throw is critical for writing robust production code.

// ── Basic structure ──
try
{
    int result = Divide(10, 0);
}
catch (DivideByZeroException ex)    // most specific first
{
    Console.WriteLine($"Math error: {ex.Message}");
}
catch (ArgumentException ex) when (ex.ParamName == "b")  // filter with 'when'
{
    Console.WriteLine("Bad argument b");
}
catch (Exception ex)                // most general last
{
    _logger.LogError(ex, "Unexpected error");
    throw;                          // ✅ re-throw — preserves original stack trace
    // ❌ throw ex;                 // BAD — resets stack trace to this line!
}
finally
{
    // Always runs — success OR exception
    // Ideal for cleanup: close files, release locks, etc.
    CleanupResources();
}

// ── Custom domain exceptions ──
public class InsufficientFundsException : Exception
{
    public decimal Requested { get; }
    public decimal Available { get; }

    public InsufficientFundsException(decimal requested, decimal available)
        : base($"Cannot withdraw {requested:C}. Only {available:C} available.")
    {
        Requested = requested;
        Available = available;
    }

    // Serialization constructor (needed for remoting/certain frameworks)
    protected InsufficientFundsException(
        System.Runtime.Serialization.SerializationInfo info,
        System.Runtime.Serialization.StreamingContext context) : base(info, context) { }
}

// ── Exception filters (C# 6+) — don't catch, just observe ──
try { await riskyOperation(); }
catch (HttpRequestException ex) when (ex.StatusCode == HttpStatusCode.ServiceUnavailable)
{
    // Only catches 503 errors — other HttpRequestException propagates normally
    await Task.Delay(1000);
    await RetryAsync();
}

// ── Aggregate exceptions (Task.WhenAll) ──
try { await Task.WhenAll(task1, task2, task3); }
catch (AggregateException agg)
{
    foreach (var inner in agg.Flatten().InnerExceptions)
        Console.WriteLine(inner.Message);
}

// ── Result pattern (avoid exceptions for expected failures) ──
public record Result<T>(T? Value, string? Error, bool IsSuccess);

public Result<User> FindUser(int id)
{
    var user = _db.Find(id);
    return user != null
        ? new Result<User>(user, null, true)
        : new Result<User>(null, "User not found", false);
}
// No exception thrown for expected "not found" — use exception only for unexpected!

Async/await is C#'s cooperative multitasking model. await releases the current thread back to the pool while waiting for I/O — your server can handle thousands of concurrent requests without thousands of threads.

// ── How it works ──
public async Task<string> FetchDataAsync(string url)
{
    // await suspends this method and returns the thread to the pool
    // When the HTTP response arrives, a thread resumes from here
    var response = await _httpClient.GetStringAsync(url);
    return response.ToUpper();  // runs on a pool thread after resuming
}

// ── Return types ──
async Task         DoWork()          { ... }  // fire-and-forget (rare)
async Task<T>      GetValue()        { ... }  // returns a value
async ValueTask<T> GetCachedValue()  { ... }  // low-allocation when often sync

// ── Sequential vs parallel ──
// Sequential — each awaits the previous (total time = sum)
var user   = await GetUserAsync(userId);
var orders = await GetOrdersAsync(userId);

// Parallel — both kick off simultaneously (total time = max)
var userTask   = GetUserAsync(userId);
var ordersTask = GetOrdersAsync(userId);
await Task.WhenAll(userTask, ordersTask);
var user   = userTask.Result;    // already completed — safe to access .Result
var orders = ordersTask.Result;

// ── Task.WhenAny — first one wins ──
var dataTask    = FetchFromPrimaryAsync();
var timeoutTask = Task.Delay(TimeSpan.FromSeconds(5));
var winner = await Task.WhenAny(dataTask, timeoutTask);
if (winner == timeoutTask) throw new TimeoutException("Primary too slow");

// ── CancellationToken — cooperative cancellation ──
public async Task<List<User>> GetUsersAsync(CancellationToken ct = default)
{
    await Task.Delay(100, ct);      // throws OperationCanceledException if cancelled
    ct.ThrowIfCancellationRequested();
    return await _repo.GetAllAsync(ct);
}

// Caller creates and controls the token
using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(10));
var users = await GetUsersAsync(cts.Token);

// ── Common mistakes ──
// ❌ Blocking async — causes deadlock in ASP.NET
var result = GetDataAsync().Result;      // DEADLOCK!
var result2 = GetDataAsync().GetAwaiter().GetResult();  // also blocks

// ✅ Async all the way down
var result3 = await GetDataAsync();      // correct

// ❌ async void (except event handlers) — exceptions are unobservable
async void BadFire() { await DoWork(); } // exceptions vanish!

// ✅ Return Task instead
async Task GoodFire() { await DoWork(); }

// ── ConfigureAwait(false) — for library code ──
// Avoids capturing the synchronization context (prevents deadlocks in certain frameworks)
var data = await _repo.GetAsync().ConfigureAwait(false);
// Note: NOT needed in ASP.NET Core (no sync context there)

-- Basic SELECT
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 80000
ORDER BY salary DESC
LIMIT 10;

-- LIKE — pattern matching
WHERE email LIKE '%@gmail.com'   -- ends with
WHERE name  LIKE 'J%'            -- starts with
WHERE name  LIKE '_ohn'          -- single char wildcard

-- IN / NOT IN
WHERE department IN ('Sales', 'Marketing', 'HR')
WHERE status NOT IN ('archived', 'deleted')

-- BETWEEN (inclusive)
WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31'
WHERE salary BETWEEN 50000 AND 100000

-- NULL checks — always use IS NULL, not = NULL
WHERE manager_id IS NULL
WHERE manager_id IS NOT NULL

-- CASE expression
SELECT name,
    CASE
        WHEN salary > 100000 THEN 'Senior'
        WHEN salary > 60000  THEN 'Mid'
        ELSE 'Junior'
    END AS level
FROM employees;

-- INNER JOIN — only rows that match in BOTH tables
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN — all rows from left table, NULLs for no match on right
SELECT e.name, o.order_total
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id;
-- employees without orders still appear (order_total = NULL)

-- RIGHT JOIN — opposite of LEFT (prefer LEFT JOIN with swapped tables)

-- FULL OUTER JOIN — all rows from both tables
SELECT e.name, o.order_total
FROM employees e
FULL OUTER JOIN orders o ON e.id = o.employee_id;

-- SELF JOIN — join a table to itself
SELECT a.name AS employee, b.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.id;

-- CROSS JOIN — cartesian product (every combo) — use carefully!
SELECT c.color, s.size
FROM colors c CROSS JOIN sizes s;  -- 5 colors × 3 sizes = 15 rows

-- Multiple JOINs
SELECT o.id, c.name, p.product_name, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products  p ON o.product_id  = p.id
WHERE o.status = 'shipped';

-- GROUP BY
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_sal
FROM employees
GROUP BY department;

-- HAVING — filter on aggregated values (WHERE = before agg, HAVING = after)
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5        -- only depts with > 5 people
  AND AVG(salary) > 70000; -- AND avg salary above 70k

-- Aggregate functions
COUNT(*)           -- total rows (includes NULLs)
COUNT(salary)      -- non-NULL salaries only
COUNT(DISTINCT dept) -- distinct dept count
SUM(salary)
AVG(salary)
MAX(salary)
MIN(hire_date)
STRING_AGG(name, ', ')  -- concat values (SQL Server / Postgres)

-- ROLLUP — subtotals + grand total
SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY ROLLUP(department, job_title);
-- Produces: dept+title rows, dept subtotals, grand total

-- ROW_NUMBER — unique sequential number per partition
SELECT name, salary, department,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;
-- rn=1 is the highest earner in each dept

-- Get top earner per department
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn
    FROM employees
) t WHERE rn = 1;

-- RANK vs DENSE_RANK (ties handling)
-- Salaries: 100k, 100k, 80k
-- RANK:       1,    1,   3   (gap after tie)
-- DENSE_RANK: 1,    1,   2   (no gap)
SELECT name, salary,
    RANK()       OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- LAG / LEAD — access previous/next row values
SELECT name, salary, hire_date,
    LAG(salary)  OVER (ORDER BY hire_date) AS prev_salary,
    LEAD(salary) OVER (ORDER BY hire_date) AS next_salary,
    salary - LAG(salary) OVER (ORDER BY hire_date) AS salary_change
FROM employees;

-- Running total (cumulative sum)
SELECT name, salary, hire_date,
    SUM(salary) OVER (ORDER BY hire_date
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS running_total
FROM employees;

-- Moving average (last 3 rows)
SELECT date, revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    AS moving_avg_3
FROM daily_sales;

-- CTE (WITH clause) — named subquery, runs once
WITH high_earners AS (
    SELECT id, name, salary FROM employees WHERE salary > 100000
),
dept_totals AS (
    SELECT dept_id, SUM(salary) AS total FROM high_earners GROUP BY dept_id
)
SELECT d.name, dt.total
FROM departments d
JOIN dept_totals dt ON d.id = dt.dept_id;

-- Correlated subquery (runs once per outer row — avoid on large tables)
SELECT name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id
);  -- each row's dept avg is computed fresh

-- EXISTS vs IN
-- EXISTS is faster when inner result is large (short-circuits)
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
-- IN is fine for small, fixed lists
WHERE dept_id IN (SELECT id FROM departments WHERE budget > 1M)

-- RECURSIVE CTE — org chart traversal
WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL          -- start: top-level
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.depth + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id -- recurse down
)
SELECT * FROM org ORDER BY depth, name;

-- B-Tree index (default) — range + equality
CREATE INDEX idx_emp_dept ON employees(department);
CREATE INDEX idx_emp_dept_sal ON employees(department, salary); -- composite

-- Covering index — includes all queried columns (no table lookup)
CREATE INDEX idx_cover ON employees(department) INCLUDE (name, salary);

-- EXPLAIN / EXPLAIN ANALYZE — see the query plan
EXPLAIN SELECT * FROM employees WHERE dept = 'Eng';
-- Look for: Seq Scan (bad on large) vs Index Scan (fast)

-- Common anti-patterns that BREAK indexes:
-- ❌ Function on indexed column
WHERE YEAR(hire_date) = 2023   -- index on hire_date ignored!
-- ✅ Fix: range scan
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'

-- ❌ Leading wildcard
WHERE name LIKE '%ohn'  -- can't use index, scans all rows
-- ✅ Trailing wildcard only (or use full-text search)
WHERE name LIKE 'Joh%'

-- ❌ Implicit type conversion
WHERE employee_id = '123'  -- string vs int mismatch
-- ✅ Match types
WHERE employee_id = 123

-- Index types summary
-- Clustered: data rows sorted by index key (1 per table, PK by default)
-- Non-Clustered: separate structure pointing to data rows (many allowed)
-- Unique: enforces uniqueness, used by optimizer like PK
-- Full-Text: for LIKE '%word%' queries on large text columns
-- Partial/Filtered: index on subset of rows (WHERE is_active = 1)

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
    -- Both succeed or both roll back
COMMIT;

-- SAVEPOINT — partial rollback
BEGIN;
SAVEPOINT before_risky;
    UPDATE ... ; -- risky operation
    -- if error:
ROLLBACK TO before_risky;
COMMIT;

-- Isolation levels (strictest to most permissive)
-- READ UNCOMMITTED — dirty reads possible (almost never use)
-- READ COMMITTED   — default in most DBs; no dirty reads
-- REPEATABLE READ  — no phantom reads within transaction
-- SERIALIZABLE     — fully isolated, as if serial execution (slowest)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Nth highest salary
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET (N-1);  -- N=2 → 2nd highest

-- Employees earning more than their manager
SELECT e.name FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- Find duplicates
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;

-- Delete duplicates, keep one
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY email
);

-- Gaps in sequential IDs (missing numbers)
SELECT t1.id + 1 AS missing
FROM table t1
WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.id = t1.id + 1)
  AND t1.id < (SELECT MAX(id) FROM table);

-- Running total
SELECT date, revenue,
    SUM(revenue) OVER (ORDER BY date) AS cumulative
FROM sales;

-- Year-over-year growth
SELECT year, revenue,
    LAG(revenue) OVER (ORDER BY year) AS prev_year,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY year))
          / LAG(revenue) OVER (ORDER BY year), 2) AS yoy_growth_pct
FROM annual_revenue;

-- Pivot (SQL Server)
SELECT * FROM (SELECT dept, year, salary FROM emp) src
PIVOT (SUM(salary) FOR year IN ([2022],[2023],[2024])) pvt;