csharp-linq

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&#x3C;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&#x3C;List&#x3C;Product>> GetExpensiveProductsAsync()
{
    // Query builds expression tree
    IQueryable&#x3C;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&#x3C;Product> GetActiveProducts()
{
    return _context.Products.Where(p => p.IsActive);
}

public async Task&#x3C;List&#x3C;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&#x3C;List&#x3C;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&#x3C;List&#x3C;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&#x3C;List&#x3C;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&#x3C;Product>
    {
        Items = items,
        PageNumber = pageNumber,
        PageSize = pageSize,
        TotalCount = total,
        TotalPages = (int)Math.Ceiling(total / (double)pageSize)
    };
}

// Efficient pagination with keyset
public List&#x3C;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&#x3C;List&#x3C;T>> Batch&#x3C;T>(
    this IEnumerable&#x3C;T> source,
    int batchSize)
{
    var batch = new List&#x3C;T>(batchSize);

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

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

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

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

// DistinctBy (built-in in C# 9+)
public static IEnumerable&#x3C;T> DistinctBy&#x3C;T, TKey>(
    this IEnumerable&#x3C;T> source,
    Func&#x3C;T, TKey> keySelector)
{
    var seenKeys = new HashSet&#x3C;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&#x3C;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&#x3C;List&#x3C;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&#x3C;List&#x3C;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&#x3C;List&#x3C;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&#x3C;Func&#x3C;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&#x3C;T> ApplyFilter&#x3C;T>(
    IQueryable&#x3C;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&#x3C;Func&#x3C;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

Safety Notice

Copy this and send it to your AI assistant to learn

Source Transparency

Related Skills

android-jetpack-compose

fastapi-async-patterns

storybook-story-writing

atomic-design-fundamentals