LINQ — Querying Anything

using System;
using System.Collections.Generic;
using System.Linq;

record Employee(string Name, string Dept, decimal Salary, int Years);

var staff = new List<Employee>
{
    new("Ada",  "Engineering", 145_000m, 6),
    new("Bob",  "Sales",        78_000m, 2),
    new("Cleo", "Engineering", 132_000m, 4),
    new("Dan",  "Sales",        95_000m, 7),
    new("Eve",  "Design",       88_000m, 1),
};

// Method syntax — what you will write 95% of the time
var seniorEngineers = staff
    .Where(e => e.Dept == "Engineering" && e.Years >= 5)
    .OrderByDescending(e => e.Salary)
    .Select(e => e.Name)
    .ToList();                              // [ "Ada" ]

// Query syntax — same thing, SQL-ish
var query = from e in staff
            where e.Salary > 90_000m
            orderby e.Name
            select new { e.Name, e.Dept };

foreach (var row in query)
    Console.WriteLine($"{row.Name} - {row.Dept}");

// Single values
decimal payroll = staff.Sum(e => e.Salary);
decimal average = staff.Average(e => e.Salary);
var topEarner   = staff.MaxBy(e => e.Salary);          // .NET 6+
bool anyDesign  = staff.Any(e => e.Dept == "Design");
bool allPaid    = staff.All(e => e.Salary > 50_000m);
var firstSales  = staff.FirstOrDefault(e => e.Dept == "Sales");   // null if none

// Grouping and joining
var byDept = staff
    .GroupBy(e => e.Dept)
    .Select(g => new
    {
        Dept  = g.Key,
        Count = g.Count(),
        Total = g.Sum(e => e.Salary),
        Top   = g.OrderByDescending(e => e.Salary).First().Name,
    })
    .OrderByDescending(x => x.Total);

foreach (var d in byDept)
    Console.WriteLine($"{d.Dept}: {d.Count} people, {d.Total:C}, top={d.Top}");

record Budget(string Dept, decimal Cap);
var budgets = new List<Budget> { new("Engineering", 300_000m), new("Sales", 200_000m) };

// Join two sequences on a key
var overBudget = staff
    .GroupBy(e => e.Dept)
    .Join(budgets,
          g => g.Key,
          b => b.Dept,
          (g, b) => new { b.Dept, Spend = g.Sum(e => e.Salary), b.Cap })
    .Where(x => x.Spend > x.Cap);

// Flattening
var words = new[] { "hello world", "linq is nice" };
var allWords = words.SelectMany(s => s.Split(' ')).Distinct().ToArray();

// Set operations and paging
var page2 = staff.OrderBy(e => e.Name).Skip(2).Take(2).ToList();
var names = staff.Select(e => e.Name).Except(new[] { "Bob" });

// Deferred execution: a LINQ query is a RECIPE, not a result.
var list = new List<int> { 1, 2, 3 };
var evens = list.Where(n => n % 2 == 0);     // nothing has run yet

list.Add(4);
Console.WriteLine(string.Join(",", evens));  // "2,4" — it re-ran over the new list

// Materialize with ToList/ToArray to freeze the result and avoid re-querying.
var snapshot = list.Where(n => n % 2 == 0).ToList();

// The same query against a database is translated to SQL (IQueryable),
// so the filtering happens on the server:
//   var users = db.Users.Where(u => u.IsActive).OrderBy(u => u.Name).Take(10).ToList();
//   -> SELECT TOP 10 ... WHERE IsActive = 1 ORDER BY Name

// This is why the order matters. Wrong:
//   db.Users.ToList().Where(u => u.IsActive)   // downloads EVERY user, then filters
// Right:
//   db.Users.Where(u => u.IsActive).ToList()   // filters in the database

// Custom extension methods plug straight into the chain
public static class Extensions
{
    public static IEnumerable<T> WhereNotNull<T>(this IEnumerable<T?> source)
        where T : class
        => source.Where(x => x is not null)!;
}