C# LINQ

Master Language Integrated Query (LINQ) for querying and transforming data in C#. This skill covers query syntax, method syntax, deferred execution, performance optimization, and advanced LINQ patterns from C# 8-12.

LINQ Query Syntax vs Method Syntax

LINQ supports two syntaxes: query syntax (SQL-like) and method syntax (fluent). Both compile to the same code.

Query Syntax

var students = new List<Student> { new Student { Name = "Alice", Grade = 85, Age = 20 }, new Student { Name = "Bob", Grade = 92, Age = 21 }, new Student { Name = "Charlie", Grade = 78, Age = 20 } };

// Query syntax - SQL-like var topStudents = from student in students where student.Grade >= 80 orderby student.Grade descending select new { student.Name, student.Grade };

foreach (var student in topStudents) { Console.WriteLine($"{student.Name}: {student.Grade}"); }

Method Syntax

// Method syntax - fluent API var topStudents = students .Where(s => s.Grade >= 80) .OrderByDescending(s => s.Grade) .Select(s => new { s.Name, s.Grade });

// Method syntax is more flexible for complex queries var result = students .Where(s => s.Age >= 20) .GroupBy(s => s.Age) .Select(g => new { Age = g.Key, AverageGrade = g.Average(s => s.Grade), Count = g.Count() }) .OrderBy(x => x.Age);

When to Use Each Syntax

// Query syntax better for joins var query1 = from student in students join course in courses on student.Id equals course.StudentId where course.Grade > 80 select new { student.Name, course.Title };

// Method syntax better for chaining and complex logic var query2 = students .Where(s => s.Age >= 20) .SelectMany(s => s.Courses) .Where(c => c.Grade > 80) .Distinct() .Take(10);

// Mixed approach var query3 = (from s in students where s.Age >= 20 select s) .Take(10) .ToList(); // Force execution

Deferred Execution

LINQ queries use deferred execution - they don't execute until enumerated.

Understanding Deferred Execution

var numbers = new List<int> { 1, 2, 3, 4, 5 };

// Query is defined but NOT executed var query = numbers.Where(n => n > 2);

// Add more numbers numbers.Add(6); numbers.Add(7);

// Query executes NOW when enumerated foreach (var num in query) // Gets: 3, 4, 5, 6, 7 { Console.WriteLine(num); }

// Query executes AGAIN (sees current state) var count = query.Count(); // 5

// Force immediate execution with ToList(), ToArray(), etc. var snapshot = numbers.Where(n => n > 2).ToList(); numbers.Add(8); Console.WriteLine(snapshot.Count); // Still 5, not 6

Deferred vs Immediate Execution

public class DeferredExecutionExample { public void Demonstrate() { var data = new List<int> { 1, 2, 3, 4, 5 };

    // Deferred - query not executed yet
    var deferred = data.Where(x => x > 2);

    // Immediate - query executed now
    var immediate = data.Where(x => x > 2).ToList();

    // Modify source
    data.Add(6);

    Console.WriteLine(deferred.Count());  // 4 (includes 6)
    Console.WriteLine(immediate.Count()); // 3 (snapshot before 6 was added)
}

// Dangerous: query is rebuilt each iteration
public void DangerousPattern()
{
    var data = GetData(); // Expensive

    // ❌ BAD - GetData() called multiple times
    foreach (var item in GetData().Where(x => x.IsActive))
    {
        Process(item);
    }

    // ✅ GOOD - GetData() called once
    var items = GetData().Where(x => x.IsActive).ToList();
    foreach (var item in items)
    {
        Process(item);
    }
}

}

IEnumerable vs IQueryable

IEnumerable executes in memory (LINQ to Objects). IQueryable translates to expression trees for remote execution (LINQ to SQL, EF).

IEnumerable<T> - In-Memory

public class InMemoryQueries { public void QueryInMemory() { var products = new List<Product> { new Product { Id = 1, Name = "Laptop", Price = 999 }, new Product { Id = 2, Name = "Mouse", Price = 25 }, new Product { Id = 3, Name = "Keyboard", Price = 75 } };

    // IEnumerable - executes in memory
    IEnumerable<Product> query = products
        .Where(p => p.Price > 50)
        .OrderBy(p => p.Name);

    // All filtering happens in C# code
    foreach (var product in query)
    {
        Console.WriteLine($"{product.Name}: ${product.Price}");
    }
}

}

IQueryable<T> - Expression Trees

public class QueryableExamples { private readonly DbContext _context;

// IQueryable - translates to SQL
public async Task<List<Product>> GetExpensiveProductsAsync()
{
    // Query builds expression tree
    IQueryable<Product> query = _context.Products
        .Where(p => p.Price > 50)
        .OrderBy(p => p.Name);

    // SQL generated and executed here
    return await query.ToListAsync();
    // SQL: SELECT * FROM Products WHERE Price > 50 ORDER BY Name
}

// Composable queries
public IQueryable<Product> GetActiveProducts()
{
    return _context.Products.Where(p => p.IsActive);
}

public async Task<List<Product>> GetExpensiveActiveProductsAsync()
{
    // Compose queries - still generates single SQL
    var products = await GetActiveProducts()
        .Where(p => p.Price > 100)
        .ToListAsync();

    // SQL: SELECT * FROM Products WHERE IsActive = 1 AND Price > 100
    return products;
}

}

Mixing IEnumerable and IQueryable

public class MixingQueries { private readonly DbContext _context;

public async Task<List<ProductDto>> GetProductsDangerousAsync()
{
    // ❌ BAD - ToList() brings ALL products to memory first
    var products = await _context.Products.ToListAsync();

    // Then filters in memory (inefficient)
    return products
        .Where(p => p.Price > 100) // In memory
        .Select(p => new ProductDto { Name = p.Name })
        .ToList();
}

public async Task<List<ProductDto>> GetProductsEfficientAsync()
{
    // ✅ GOOD - everything in SQL
    return await _context.Products
        .Where(p => p.Price > 100) // In SQL
        .Select(p => new ProductDto { Name = p.Name }) // In SQL
        .ToListAsync(); // Execute once
}

public async Task<List<Product>> ComplexFilterAsync()
{
    // ✅ GOOD - SQL where possible, memory when necessary
    return await _context.Products
        .Where(p => p.Price > 50) // SQL
        .ToListAsync() // Execute SQL
        .ContinueWith(t => t.Result
            .Where(p => ComplexInMemoryCheck(p)) // C# predicate
            .ToList()
        );
}

private bool ComplexInMemoryCheck(Product product)
{
    // Logic that can't be translated to SQL
    return product.Name.Split(' ').Length > 2;
}

}

Common LINQ Operators

Where - Filtering

var numbers = Enumerable.Range(1, 100);

// Simple filter var evens = numbers.Where(n => n % 2 == 0);

// Multiple conditions var filtered = numbers.Where(n => n > 10 && n < 50 && n % 3 == 0);

// Filter with index var everyThird = numbers.Where((n, index) => index % 3 == 0);

// Complex filtering var products = GetProducts() .Where(p => p.IsActive) .Where(p => p.Price >= 10 && p.Price <= 100) .Where(p => p.Category.StartsWith("Electronics"));

Select - Projection

var students = GetStudents();

// Simple projection var names = students.Select(s => s.Name);

// Anonymous types var summary = students.Select(s => new { s.Name, s.Grade, Status = s.Grade >= 80 ? "Pass" : "Fail" });

// Projection with index var indexed = students.Select((s, i) => new { Index = i, Student = s });

// DTOs var dtos = students.Select(s => new StudentDto { FullName = $"{s.FirstName} {s.LastName}", GradePoint = s.Grade / 100.0 });

SelectMany - Flattening

var departments = new List<Department> { new Department { Name = "IT", Employees = new[] { new Employee { Name = "Alice" }, new Employee { Name = "Bob" } } }, new Department { Name = "HR", Employees = new[] { new Employee { Name = "Charlie" } } } };

// Flatten nested collections var allEmployees = departments.SelectMany(d => d.Employees);

// With result selector var employeesWithDept = departments.SelectMany( dept => dept.Employees, (dept, emp) => new { Department = dept.Name, Employee = emp.Name } );

// Multiple levels var orders = GetOrders(); var allItems = orders .SelectMany(o => o.OrderLines) .SelectMany(ol => ol.Items);

GroupBy - Grouping

var sales = GetSales();

// Simple grouping var byCategory = sales.GroupBy(s => s.Category);

foreach (var group in byCategory) { Console.WriteLine($"{group.Key}: {group.Count()} items"); }

// Grouping with projection var categoryTotals = sales .GroupBy(s => s.Category) .Select(g => new { Category = g.Key, TotalSales = g.Sum(s => s.Amount), AverageSale = g.Average(s => s.Amount), Count = g.Count() });

// Multiple key grouping var grouped = sales.GroupBy(s => new { s.Category, s.Region });

// GroupBy with custom comparer var byNameIgnoreCase = students.GroupBy( s => s.Name, StringComparer.OrdinalIgnoreCase );

Join - Combining Collections

var customers = GetCustomers(); var orders = GetOrders();

// Inner join var customerOrders = from c in customers join o in orders on c.Id equals o.CustomerId select new { c.Name, o.OrderDate, o.Total };

// Method syntax var customerOrders2 = customers.Join( orders, c => c.Id, o => o.CustomerId, (c, o) => new { c.Name, o.OrderDate, o.Total } );

// Left outer join var leftJoin = from c in customers join o in orders on c.Id equals o.CustomerId into customerOrders from co in customerOrders.DefaultIfEmpty() select new { Customer = c.Name, OrderTotal = co?.Total ?? 0 };

// Multiple joins var fullData = from c in customers join o in orders on c.Id equals o.CustomerId join od in orderDetails on o.Id equals od.OrderId select new { c.Name, o.OrderDate, od.Product };

Aggregation Operations

Basic Aggregations

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

// Count int count = numbers.Count(); int evenCount = numbers.Count(n => n % 2 == 0);

// Sum int sum = numbers.Sum(); decimal totalPrice = products.Sum(p => p.Price);

// Average double avg = numbers.Average(); double avgGrade = students.Average(s => s.Grade);

// Min/Max int min = numbers.Min(); int max = numbers.Max(); var cheapest = products.MinBy(p => p.Price); // C# 9+ var mostExpensive = products.MaxBy(p => p.Price); // C# 9+

// Any/All bool hasEvens = numbers.Any(n => n % 2 == 0); bool allPositive = numbers.All(n => n > 0);

// First/Last/Single var first = numbers.First(); var firstEven = numbers.First(n => n % 2 == 0); var firstOrNull = numbers.FirstOrDefault(n => n > 100); // 0 var single = numbers.Single(n => n == 5); var last = numbers.Last();

Advanced Aggregations

public class AggregationExamples { public void AdvancedAggregates() { var sales = GetSales();

    // Aggregate - custom accumulator
    var total = sales.Aggregate(0m, (acc, sale) => acc + sale.Amount);

    // Complex aggregation
    var stats = sales.Aggregate(
        new { Sum = 0m, Count = 0 },
        (acc, sale) => new
        {
            Sum = acc.Sum + sale.Amount,
            Count = acc.Count + 1
        },
        acc => new
        {
            acc.Sum,
            acc.Count,
            Average = acc.Sum / acc.Count
        }
    );

    // Grouped aggregations
    var categorySummary = sales
        .GroupBy(s => s.Category)
        .Select(g => new
        {
            Category = g.Key,
            Count = g.Count(),
            Total = g.Sum(s => s.Amount),
            Average = g.Average(s => s.Amount),
            Min = g.Min(s => s.Amount),
            Max = g.Max(s => s.Amount)
        });
}

}

Set Operations

Distinct, Union, Intersect, Except

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

// Distinct - remove duplicates var unique = new[] { 1, 2, 2, 3, 3, 3 }.Distinct(); // 1, 2, 3

// DistinctBy - C# 9+ var customers = GetCustomers(); var uniqueByEmail = customers.DistinctBy(c => c.Email);

// Union - combine and remove duplicates var union = list1.Union(list2); // 1, 2, 3, 4, 5, 6, 7, 8

// Concat - combine without removing duplicates var concatenated = list1.Concat(list2); // 1, 2, 3, 4, 5, 4, 5, 6, 7, 8

// Intersect - common elements var intersection = list1.Intersect(list2); // 4, 5

// Except - elements in first but not second var difference = list1.Except(list2); // 1, 2, 3

// Set operations with custom comparer var products1 = GetProducts(); var products2 = GetMoreProducts(); var uniqueProducts = products1.Union(products2, new ProductComparer());

Custom Equality Comparers

public class ProductComparer : IEqualityComparer<Product> { public bool Equals(Product? x, Product? y) { if (x == null || y == null) return false; return x.Name.Equals(y.Name, StringComparison.OrdinalIgnoreCase); }

public int GetHashCode(Product obj)
{
    return obj.Name.ToUpperInvariant().GetHashCode();
}

}

// Usage var distinct = products.Distinct(new ProductComparer());

Ordering and Pagination

Ordering

var products = GetProducts();

// OrderBy - ascending var ascending = products.OrderBy(p => p.Price);

// OrderByDescending var descending = products.OrderByDescending(p => p.Price);

// ThenBy - secondary sort var sorted = products .OrderBy(p => p.Category) .ThenByDescending(p => p.Price) .ThenBy(p => p.Name);

// Reverse var reversed = products.OrderBy(p => p.Price).Reverse();

// Custom comparer var customSort = products.OrderBy(p => p.Name, StringComparer.OrdinalIgnoreCase);

Pagination

public class PaginationExamples { public PagedResult<Product> GetPage(int pageNumber, int pageSize) { var query = _context.Products .Where(p => p.IsActive) .OrderBy(p => p.Name);

    var total = query.Count();

    var items = query
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .ToList();

    return new PagedResult<Product>
    {
        Items = items,
        PageNumber = pageNumber,
        PageSize = pageSize,
        TotalCount = total,
        TotalPages = (int)Math.Ceiling(total / (double)pageSize)
    };
}

// Efficient pagination with keyset
public List<Product> GetNextPage(int? lastId, int pageSize)
{
    var query = _context.Products.Where(p => p.IsActive);

    if (lastId.HasValue)
    {
        query = query.Where(p => p.Id > lastId.Value);
    }

    return query
        .OrderBy(p => p.Id)
        .Take(pageSize)
        .ToList();
}

}

Custom LINQ Operators

Extension Methods

public static class LinqExtensions { // WhereIf - conditional filtering public static IEnumerable<T> WhereIf<T>( this IEnumerable<T> source, bool condition, Func<T, bool> predicate) { return condition ? source.Where(predicate) : source; }

// Batch - split into chunks
public static IEnumerable<List<T>> Batch<T>(
    this IEnumerable<T> source,
    int batchSize)
{
    var batch = new List<T>(batchSize);

    foreach (var item in source)
    {
        batch.Add(item);

        if (batch.Count == batchSize)
        {
            yield return batch;
            batch = new List<T>(batchSize);
        }
    }

    if (batch.Count > 0)
    {
        yield return batch;
    }
}

// ForEach
public static void ForEach<T>(this IEnumerable<T> source, Action<T> action)
{
    foreach (var item in source)
    {
        action(item);
    }
}

// DistinctBy (built-in in C# 9+)
public static IEnumerable<T> DistinctBy<T, TKey>(
    this IEnumerable<T> source,
    Func<T, TKey> keySelector)
{
    var seenKeys = new HashSet<TKey>();

    foreach (var item in source)
    {
        if (seenKeys.Add(keySelector(item)))
        {
            yield return item;
        }
    }
}

}

// Usage var filtered = products .WhereIf(filterByPrice, p => p.Price > 100) .WhereIf(filterByCategory, p => p.Category == "Electronics");

var batches = items.Batch(100); foreach (var batch in batches) { ProcessBatch(batch); }

Performance Considerations

Materialization

public class PerformanceExamples { // ❌ BAD - Multiple enumerations public void MultipleEnumerations(IEnumerable<Product> products) { var query = products.Where(p => p.Price > 100);

    Console.WriteLine(query.Count()); // Enumeration 1
    Console.WriteLine(query.Sum(p => p.Price)); // Enumeration 2

    foreach (var p in query) // Enumeration 3
    {
        Console.WriteLine(p.Name);
    }
}

// ✅ GOOD - Single enumeration
public void SingleEnumeration(IEnumerable<Product> products)
{
    var list = products.Where(p => p.Price > 100).ToList();

    Console.WriteLine(list.Count);
    Console.WriteLine(list.Sum(p => p.Price));

    foreach (var p in list)
    {
        Console.WriteLine(p.Name);
    }
}

}

Database Query Optimization

public class QueryOptimization { private readonly DbContext _context;

// ❌ BAD - N+1 query problem
public async Task<List<OrderDto>> GetOrdersBadAsync()
{
    var orders = await _context.Orders.ToListAsync();

    return orders.Select(o => new OrderDto
    {
        Id = o.Id,
        // Triggers separate query for each order!
        CustomerName = o.Customer.Name,
        ItemCount = o.OrderLines.Count
    }).ToList();
}

// ✅ GOOD - Eager loading
public async Task<List<OrderDto>> GetOrdersGoodAsync()
{
    return await _context.Orders
        .Include(o => o.Customer)
        .Include(o => o.OrderLines)
        .Select(o => new OrderDto
        {
            Id = o.Id,
            CustomerName = o.Customer.Name,
            ItemCount = o.OrderLines.Count
        })
        .ToListAsync();
}

// ✅ BETTER - Project in SQL
public async Task<List<OrderDto>> GetOrdersBestAsync()
{
    return await _context.Orders
        .Select(o => new OrderDto
        {
            Id = o.Id,
            CustomerName = o.Customer.Name,
            ItemCount = o.OrderLines.Count
        })
        .ToListAsync();
}

}

Expression Trees

Understanding LINQ's expression tree compilation.

public class ExpressionTreeExamples { public void ExpressionTreeDemo() { // Lambda as delegate Func<int, bool> isEvenDelegate = n => n % 2 == 0;

    // Lambda as expression tree
    Expression<Func<int, bool>> isEvenExpression = n => n % 2 == 0;

    // Examine expression structure
    var binary = (BinaryExpression)isEvenExpression.Body;
    Console.WriteLine(binary.NodeType); // Modulo

    // Compile and execute
    var compiled = isEvenExpression.Compile();
    bool result = compiled(4); // true
}

// Building expressions dynamically
public IQueryable<T> ApplyFilter<T>(
    IQueryable<T> query,
    string propertyName,
    object value)
{
    var parameter = Expression.Parameter(typeof(T), "x");
    var property = Expression.Property(parameter, propertyName);
    var constant = Expression.Constant(value);
    var equality = Expression.Equal(property, constant);
    var lambda = Expression.Lambda<Func<T, bool>>(equality, parameter);

    return query.Where(lambda);
}

}

Best Practices

• Use Method Syntax for Complex Queries: More flexible than query syntax

• ToList/ToArray When Needed: Materialize queries you'll enumerate multiple times

• Avoid Multiple Enumerations: Cache results when reusing queries

• Project Early: Select only needed properties, especially with EF

• Use AsNoTracking: For read-only EF queries

• Batch Database Queries: Use Include for related data

• Avoid LINQ in Loops: Pull queries out of loops when possible

• Use IQueryable for Composition: Build queries gradually

• Consider Compiled Queries: For frequently-used EF queries

• Profile Your Queries: Use logging to see generated SQL

Common Pitfalls

• Multiple Enumerations: Not materializing queries leads to re-execution

• N+1 Queries: Forgetting to Include related entities in EF

• Premature Materialization: Calling ToList() too early limits query composition

• Mixing IEnumerable and IQueryable: Forces in-memory evaluation

• Client-Side Evaluation: Using methods that can't translate to SQL

• Ignored Where Clauses: Forgetting queries build incrementally

• Inefficient Ordering: Ordering before filtering

• Large Result Sets: Not using pagination or Take()

• Closure Capturing: Variables captured in lambdas evaluated when query runs

• Exception Swallowing: FirstOrDefault returns null, not an exception

When to Use

Use this skill when:

• Querying collections in C#

• Working with Entity Framework or LINQ to SQL

• Transforming data with projections

• Filtering and sorting data

• Performing aggregations and grouping

• Joining multiple data sources

• Implementing pagination

• Building dynamic queries

• Optimizing query performance

• Working with expression trees

Resources

• LINQ Documentation

• 101 LINQ Samples

• LINQ Performance Tips

• Expression Trees

• Entity Framework LINQ