C# LINQ

LINQ (Language Integrated Query) is a powerful feature in C# that enables developers to write expressive, readable, and concise code for querying and manipulating data from various sources such as collections, databases, XML, and more. By integrating query capabilities directly into the C# language, LINQ provides a unified approach to data access, improving productivity and code maintainability.

Table of Contents

  1. Introduction to LINQ
    2. - What is LINQ?
    - Benefits of Using LINQ
    - LINQ Providers
  2. LINQ Syntax: Query Syntax vs. Method Syntax
    3. - Query Syntax
    - Method Syntax
    - Equivalent Queries
  3. LINQ Operators
    4. - Filtering: Where
    - Projection: Select, SelectMany
    - Sorting: OrderBy, OrderByDescending, ThenBy
    - Grouping: GroupBy
    - Joining: Join, GroupJoin
    - Set Operations: Distinct, Union, Intersect, Except
    - Aggregation: Count, Sum, Average, Max, Min, Aggregate
    - Quantifier: Any, All, Contains
    - Partitioning: Take, TakeWhile, Skip, SkipWhile
    - Element Operators: First, FirstOrDefault, Single, SingleOrDefault, Last, LastOrDefault
  4. Deferred vs. Immediate Execution
    5. - Deferred Execution
    - Immediate Execution
    - Examples
  5. LINQ to Objects
    6. - Using LINQ with Collections
    - Examples
  6. LINQ to SQL
    7. - Introduction
    - Mapping Classes to Database
    - Querying with LINQ to SQL
    - Example
  7. LINQ to XML
    8. - Introduction
    - Querying and Modifying XML with LINQ
    - Example
  8. Custom LINQ Providers
    9. - What are Custom LINQ Providers?
    - Creating Custom Providers
    - Example
  9. Advanced LINQ Topics
    10. - Expression Trees
    - IQueryable vs. IEnumerable
    - Lambda Expressions
    - Extension Methods
  10. Best Practices
    11. - Use Method Syntax for Complex Queries
    - Optimize Performance with Deferred Execution
    - Avoid Multiple Enumerations
    - Utilize Anonymous Types When Appropriate
    - Be Cautious with GroupBy and Join for Large Data Sets
  11. Common Mistakes
    12. - Forgetting to Execute Queries
    - Misunderstanding Deferred Execution
    - Using LINQ with Non-Thread-Safe Collections
    - Overusing Anonymous Types
  12. Real-World Example: LINQ in a Data Processing Application
    13. - Problem Statement
    - Data Setup
    - LINQ Queries
    - Explanation
  13. Summary

1. Introduction to LINQ

What is LINQ?

LINQ (Language Integrated Query) is a set of features in C# that provides query capabilities directly within the language syntax. It allows developers to query various data sources using a consistent syntax, whether the data resides in collections, databases, XML documents, or other formats.

Key Points:
- Integration into C#: LINQ is fully integrated into C#, enabling compile-time checking and IntelliSense support.

- Unified Query Syntax: Provides a consistent approach to querying different data sources.

- Strongly Typed: Ensures type safety, reducing runtime errors.

Benefits of Using LINQ

- Readability: Queries are written in a declarative manner, making the code more understandable.

- Conciseness: Reduces the amount of boilerplate code needed for data operations.

- Maintainability: Easier to modify and maintain queries due to their clear structure.

- Type Safety: Compile-time checking prevents many common errors.

- Integration with IDE: Enhanced tooling support like IntelliSense and refactoring.

LINQ Providers

LINQ operates through various providers that determine the data source it interacts with. Each provider translates LINQ queries into the appropriate form for the data source.

- LINQ to Objects: Queries in-memory collections like arrays, lists, etc.
- LINQ to SQL: Queries SQL databases by translating LINQ queries into SQL.
- LINQ to XML: Queries and manipulates XML documents.
- LINQ to Entities (Entity Framework): Queries databases using Entity Framework.
- Custom LINQ Providers: Allows developers to create their own providers for different data sources.

2. LINQ Syntax: Query Syntax vs. Method Syntax

LINQ provides two primary syntaxes for writing queries: Query Syntax and Method Syntax. Both are functionally equivalent but offer different styles to suit developer preferences.

Query Syntax

Query syntax resembles SQL and is often more readable for those familiar with SQL.

Example: Query Syntax 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> numbers = new List<int> { 1, 2, 3, 4, 5 };

        var evenNumbers = from num in numbers
                          where num % 2 == 0
                          select num;

        foreach(var num in evenNumbers)
        {
            Console.WriteLine(num); // Output: 2, 4
        }
    }
}

Sample Output:
2
4

Method Syntax

Method syntax uses extension methods and lambda expressions, providing a more functional programming style.

Example: Method Syntax 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> numbers = new List<int> { 1, 2, 3, 4, 5 };

        var evenNumbers = numbers.Where(num => num % 2 == 0);

        foreach(var num in evenNumbers)
        {
            Console.WriteLine(num); // Output: 2, 4
        }
    }
}

Sample Output:
2
4

Equivalent Queries

Both syntaxes can achieve the same results. Developers can choose based on readability and personal preference.

Example: Equivalent Queries 
// Query Syntax
var querySyntax = from num in numbers
                  where num > 3
                  select num;

// Method Syntax
var methodSyntax = numbers.Where(num => num > 3);

3. LINQ Operators

LINQ provides a rich set of operators that allow for complex querying and data manipulation. These operators can be categorized based on their functionality.

Filtering: Where

Filters a sequence based on a predicate.

Example: Where Operator 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<string> fruits = new List<string> { "Apple", "Banana", "Cherry", "Date", "Elderberry" };

        var longFruits = fruits.Where(fruit => fruit.Length > 5);

        foreach(var fruit in longFruits)
        {
            Console.WriteLine(fruit); // Output: Banana, Cherry, Elderberry
        }
    }
}

Sample Output:
Banana
Cherry
Elderberry

Projection: Select, SelectMany

Transforms each element in a sequence.

Example: Select Operator 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> numbers = new List<int> { 1, 2, 3 };

        var squares = numbers.Select(num => num * num);

        foreach(var square in squares)
        {
            Console.WriteLine(square); // Output: 1, 4, 9
        }
    }
}

Sample Output:
1
4
9

Example: SelectMany Operator
Flattens a sequence of sequences. 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<List<int>> numberGroups = new List<List<int>>
        {
            new List<int> {1, 2},
            new List<int> {3, 4},
            new List<int> {5, 6}
        };

        var allNumbers = numberGroups.SelectMany(group => group);

        foreach(var num in allNumbers)
        {
            Console.WriteLine(num); // Output: 1, 2, 3, 4, 5, 6
        }
    }
}

Sample Output:
1
2
3
4
5
6

Sorting: OrderBy, OrderByDescending, ThenBy

Sorts elements in a sequence.

Example: OrderBy and ThenBy Operators 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    public class Student
    {
        public string Name { get; set; }
        public double GPA { get; set; }
    }

    static void Main()
    {
        List<Student> students = new List<Student>
        {
            new Student { Name = "Alice", GPA = 3.5 },
            new Student { Name = "Bob", GPA = 3.8 },
            new Student { Name = "Charlie", GPA = 3.5 },
            new Student { Name = "David", GPA = 3.9 }
        };

        var sortedStudents = students.OrderBy(s => s.GPA).ThenBy(s => s.Name);

        foreach(var student in sortedStudents)
        {
            Console.WriteLine($"{student.Name}: {student.GPA}");
            // Output:
            // Alice: 3.5
            // Charlie: 3.5
            // Bob: 3.8
            // David: 3.9
        }
    }
}

Sample Output:
Alice: 3.5
Charlie: 3.5
Bob: 3.8
David: 3.9

Grouping: GroupBy

Groups elements based on a key.

Example: GroupBy Operator 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    public class Employee
    {
        public string Department { get; set; }
        public string Name { get; set; }
    }

    static void Main()
    {
        List<Employee> employees = new List<Employee>
        {
            new Employee { Department = "HR", Name = "Alice" },
            new Employee { Department = "IT", Name = "Bob" },
            new Employee { Department = "HR", Name = "Charlie" },
            new Employee { Department = "IT", Name = "David" },
            new Employee { Department = "Finance", Name = "Eve" }
        };

        var grouped = employees.GroupBy(emp => emp.Department);

        foreach(var group in grouped)
        {
            Console.WriteLine($"Department: {group.Key}");
            foreach(var emp in group)
            {
                Console.WriteLine($" - {emp.Name}");
            }
        }
        // Output:
        // Department: HR
        //  - Alice
        //  - Charlie
        // Department: IT
        //  - Bob
        //  - David
        // Department: Finance
        //  - Eve
    }
}

Sample Output:
Department: HR
- Alice
- Charlie
Department: IT
- Bob
- David
Department: Finance
- Eve

Joining: Join, GroupJoin

Combines elements from two sequences based on a key.

Example: Join Operator 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    public class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
    }

    public class Order
    {
        public int OrderId { get; set; }
        public int CustomerId { get; set; }
        public string Product { get; set; }
    }

    static void Main()
    {
        List<Customer> customers = new List<Customer>
        {
            new Customer { CustomerId = 1, Name = "Alice" },
            new Customer { CustomerId = 2, Name = "Bob" },
            new Customer { CustomerId = 3, Name = "Charlie" }
        };

        List<Order> orders = new List<Order>
        {
            new Order { OrderId = 101, CustomerId = 1, Product = "Laptop" },
            new Order { OrderId = 102, CustomerId = 2, Product = "Smartphone" },
            new Order { OrderId = 103, CustomerId = 1, Product = "Tablet" },
            new Order { OrderId = 104, CustomerId = 4, Product = "Monitor" }
        };

        var customerOrders = customers.Join(
            orders,
            customer => customer.CustomerId,
            order => order.CustomerId,
            (customer, order) => new
            {
                CustomerName = customer.Name,
                order.Product
            });

        foreach(var co in customerOrders)
        {
            Console.WriteLine($"{co.CustomerName} ordered {co.Product}");
            // Output:
            // Alice ordered Laptop
            // Alice ordered Tablet
            // Bob ordered Smartphone
        }
    }
}

Sample Output:
Alice ordered Laptop
Alice ordered Tablet
Bob ordered Smartphone

Set Operations: Distinct, Union, Intersect, Except

Performs set-based operations on sequences.

Example: Distinct Operator 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<string> fruits = new List<string> { "Apple", "Banana", "Apple", "Cherry", "Banana" };

        var uniqueFruits = fruits.Distinct();

        foreach(var fruit in uniqueFruits)
        {
            Console.WriteLine(fruit); // Output: Apple, Banana, Cherry
        }
    }
}

Sample Output:
Apple
Banana
Cherry

Example: Union Operator Combines two sequences and removes duplicates.

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

class Program
{
    static void Main()
    {
        List<int> first = new List<int> { 1, 2, 3 };
        List<int> second = new List<int> { 3, 4, 5 };

        var union = first.Union(second);

        foreach(var num in union)
        {
            Console.WriteLine(num); // Output: 1, 2, 3, 4, 5
        }
    }
}

Sample Output:
1
2
3
4
5

Example: Intersect Operator
Finds common elements between two sequences.

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

class Program
{
    static void Main()
    {
        List<int> first = new List<int> { 1, 2, 3, 4 };
        List<int> second = new List<int> { 3, 4, 5, 6 };

        var intersect = first.Intersect(second);

        foreach(var num in intersect)
        {
            Console.WriteLine(num); // Output: 3, 4
        }
    }
}

Sample Output:
3
4

Example: Except Operator
Finds elements in the first sequence that are not in the second. 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> first = new List<int> { 1, 2, 3, 4 };
        List<int> second = new List<int> { 3, 4, 5, 6 };

        var except = first.Except(second);

        foreach(var num in except)
        {
            Console.WriteLine(num); // Output: 1, 2
        }
    }
}

Sample Output:
1
2

Aggregation: Count, Sum, Average, Max, Min, Aggregate

Performs calculations on a sequence.

Example: Count and Sum Operators 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> numbers = new List<int> { 1, 2, 3, 4, 5 };

        int count = numbers.Count();
        int sum = numbers.Sum();

        Console.WriteLine($"Count: {count}"); // Output: 5
        Console.WriteLine($"Sum: {sum}");     // Output: 15
    }
}

Sample Output:
Count: 5
Sum: 15

Example: Average, Max, Min Operators 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<double> scores = new List<double> { 85.5, 90.0, 78.5, 92.0 };

        double average = scores.Average();
        double max = scores.Max();
        double min = scores.Min();

        Console.WriteLine($"Average: {average}"); // Output: Average: 86.75
        Console.WriteLine($"Max: {max}");         // Output: Max: 92
        Console.WriteLine($"Min: {min}");         // Output: Min: 78.5
    }
}

Sample Output:
Average: 86.75
Max: 92
Min: 78.5

Example: Aggregate Operator
Performs a custom aggregation on a sequence. 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<string> words = new List<string> { "Hello", "World", "LINQ" };

        string concatenated = words.Aggregate((current, next) => current + " " + next);

        Console.WriteLine(concatenated); // Output: Hello World LINQ
    }
}

Sample Output:
Hello World LINQ

Quantifier: Any, All, Contains

Checks conditions across a sequence.

Example: Any and All Operators 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> numbers = new List<int> { 2, 4, 6, 8 };

        bool anyOdd = numbers.Any(num => num % 2 != 0);
        bool allEven = numbers.All(num => num % 2 == 0);

        Console.WriteLine($"Any Odd Numbers: {anyOdd}"); // Output: Any Odd Numbers: False
        Console.WriteLine($"All Even Numbers: {allEven}"); // Output: All Even Numbers: True
    }
}

Sample Output:
Any Odd Numbers: False
All Even Numbers: True

Example: Contains Operator

Checks if a sequence contains a specific element. 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<string> fruits = new List<string> { "Apple", "Banana", "Cherry" };

        bool hasBanana = fruits.Contains("Banana");
        bool hasGrape = fruits.Contains("Grape");

        Console.WriteLine($"Contains Banana: {hasBanana}"); // Output: Contains Banana: True
        Console.WriteLine($"Contains Grape: {hasGrape}");   // Output: Contains Grape: False
    }
}

Sample Output:
Contains Banana: True
Contains Grape: False

Partitioning: Take, TakeWhile, Skip, SkipWhile

Splits a sequence into subsets.

Example: Take and Skip Operators 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> numbers = Enumerable.Range(1, 10).ToList();

        var firstThree = numbers.Take(3);
        var lastSeven = numbers.Skip(3);

        Console.WriteLine("First Three:");
        foreach(var num in firstThree)
        {
            Console.WriteLine(num); // Output: 1, 2, 3
        }

        Console.WriteLine("\nLast Seven:");
        foreach(var num in lastSeven)
        {
            Console.WriteLine(num); // Output: 4, 5, 6, 7, 8, 9, 10
        }
    }
}

Sample Output:
First Three:
1
2
3
Last Seven:
4
5
6
7
8
9
10

Element Operators: First, FirstOrDefault, Single, SingleOrDefault, Last, LastOrDefault

Retrieves specific elements from a sequence.

Example: First and FirstOrDefault Operators 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<string> names = new List<string> { "Alice", "Bob", "Charlie" };

        string firstName = names.First();
        string firstOrDefault = names.FirstOrDefault(name => name.StartsWith("D"));

        Console.WriteLine($"First Name: {firstName}");           // Output: Alice
        Console.WriteLine($"First Or Default: {firstOrDefault}"); // Output: (null)
    }
}

Sample Output:
First Name: Alice
First Or Default:

Example: Single and SingleOrDefault Operators 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> singleItem = new List<int> { 42 };
        List<int> emptyList = new List<int>();
        List<int> multipleItems = new List<int> { 1, 2, 3 };

        int single = singleItem.Single(); // 42
        int singleOrDefaultEmpty = emptyList.SingleOrDefault(); // 0 (default int)
        
        Console.WriteLine($"Single: {single}");                       // Output: 42
        Console.WriteLine($"Single Or Default (Empty): {singleOrDefaultEmpty}"); // Output: 0

        // The following line will throw an exception because there are multiple elements
        // int singleMultiple = multipleItems.Single();
    }
}

Sample Output:
Single: 42
Single Or Default (Empty): 0

Example: Last and LastOrDefault Operators 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<string> cities = new List<string> { "New York", "Los Angeles", "Chicago" };

        string lastCity = cities.Last();
        string lastOrDefault = cities.LastOrDefault(city => city.StartsWith("A"));

        Console.WriteLine($"Last City: {lastCity}");               // Output: Chicago
        Console.WriteLine($"Last Or Default: {lastOrDefault}");   // Output: (null)
    }
}

Sample Output:
Last City: Chicago
Last Or Default:

4. Deferred vs. Immediate Execution

Understanding how LINQ executes queries is crucial for writing efficient and predictable code. LINQ queries can exhibit deferred or immediate execution based on the operators used.

Deferred Execution

Deferred execution means that the evaluation of a query is delayed until its results are actually iterated over. This allows for optimized query execution and dynamic data retrieval.

Characteristics:
- Lazy Evaluation: Query is not executed until you iterate over the results.
- Multiple Enumerations: Query is executed each time you iterate over it.
- Up-to-Date Results: Reflects the current state of the data source during iteration.

Example: Deferred Execution 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> numbers = new List<int> { 1, 2, 3 };

        var query = numbers.Where(n => n > 1);

        numbers.Add(4);

        foreach(var num in query)
        {
            Console.WriteLine(num); // Output: 2, 3, 4
        }
    }
}

Sample Output:
2
3
4

Explanation:
- The `query` is defined before adding `4` to the `numbers` list.
- Due to deferred execution, when the query is iterated over, it includes the newly added `4`.

Immediate Execution

Immediate execution means that the query is executed at the point of its definition, and the results are stored in memory.

Characteristics:
- Eager Evaluation: Query is executed immediately.
- Single Enumeration: Results are fixed and do not reflect changes in the data source after execution.
- Stored Results: Useful when you need to reuse the results multiple times without re-executing the query.

Example: Immediate Execution 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> numbers = new List<int> { 1, 2, 3 };

        var query = numbers.Where(n => n > 1).ToList();

        numbers.Add(4);

        foreach(var num in query)
        {
            Console.WriteLine(num); // Output: 2, 3
        }
    }
}

Sample Output:
2
3

Explanation:
- The `ToList()` method forces immediate execution of the query.
- Adding `4` to the `numbers` list does not affect the `query` results.

Examples of Deferred and Immediate Execution

Deferred Execution Example: 
var deferredQuery = numbers.Where(n => n > 2); // Not executed
numbers.Add(5);
foreach(var num in deferredQuery)
{
    Console.WriteLine(num); // Includes 5
}

Immediate Execution Example: 
var immediateQuery = numbers.Where(n => n > 2).ToArray(); // Executed immediately
numbers.Add(5);
foreach(var num in immediateQuery)
{
    Console.WriteLine(num); // Does not include 5
}

5. LINQ to Objects

LINQ to Objects allows you to perform queries on in-memory collections such as arrays, lists, dictionaries, and other IEnumerable<T> types. It leverages LINQ operators to filter, project, sort, group, and manipulate data efficiently.

Using LINQ with Collections

LINQ to Objects is the most commonly used LINQ provider, enabling powerful data manipulation directly on in-memory data structures.

Example: LINQ to Objects with a List 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<Person> people = new List<Person>
        {
            new Person { Name = "Alice", Age = 30, City = "New York" },
            new Person { Name = "Bob", Age = 25, City = "Los Angeles" },
            new Person { Name = "Charlie", Age = 35, City = "Chicago" },
            new Person { Name = "David", Age = 28, City = "New York" }
        };

        // Query: Select names of people aged over 28
        var query = from person in people
                    where person.Age > 28
                    select person.Name;

        foreach(var name in query)
        {
            Console.WriteLine(name); // Output: Alice, Charlie
        }
    }

    public class Person
    {
        public string Name { get; set;}
        public int Age { get; set;}
        public string City { get; set;}
    }
}

Sample Output:
Alice
Charlie

Examples

Example 1: Filtering and Projection 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        int[] numbers = { 1, 2, 3, 4, 5, 6 };

        var evenSquares = numbers.Where(n => n % 2 == 0)
                                 .Select(n => n * n);

        foreach(var square in evenSquares)
        {
            Console.WriteLine(square); // Output: 4, 16, 36
        }
    }
}

Sample Output:
4
16
36



Example 2: Grouping and Aggregation 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    public class Sale
    {
        public string Product { get; set;}
        public string Category { get; set;}
        public double Amount { get; set;}
    }

    static void Main()
    {
        List<Sale> sales = new List<Sale>
        {
            new Sale { Product = "Laptop", Category = "Electronics", Amount = 1200 },
            new Sale { Product = "Smartphone", Category = "Electronics", Amount = 800 },
            new Sale { Product = "Desk", Category = "Furniture", Amount = 300 },
            new Sale { Product = "Chair", Category = "Furniture", Amount = 150 },
            new Sale { Product = "Headphones", Category = "Electronics", Amount = 200 }
        };

        var totalByCategory = sales.GroupBy(s => s.Category)
                                   .Select(g => new
                                   {
                                       Category = g.Key,
                                       TotalAmount = g.Sum(s => s.Amount)
                                   });

        foreach(var category in totalByCategory)
        {
            Console.WriteLine($"{category.Category}: {category.TotalAmount}");
            // Output:
            // Electronics: 2200
            // Furniture: 450
        }
    }
}

Sample Output:
Electronics: 2200
Furniture: 450

6. LINQ to SQL

LINQ to SQL is a LINQ provider that enables querying and manipulating data in SQL Server databases using LINQ syntax. It provides an Object Relational Mapping (ORM) framework, allowing developers to interact with the database using C# objects instead of writing raw SQL queries.

Introduction

- ORM Framework: Maps database tables to C# classes and records to objects.
- Ease of Use: Simplifies database operations with strongly-typed classes and LINQ queries.
- Integration: Seamlessly integrates with Visual Studio for generating data models.

Mapping Classes to Database

LINQ to SQL uses a DataContext to manage the connection to the database and track changes.

Example: Mapping Classes Assuming a SQL database with tables `Customers` and `Orders`.

Customer Class: 
public class Customer
{
    public int CustomerID { get; set; }
    public string Name { get; set; }
    public string City { get; set; }

    // Navigation property
    public virtual ICollection<Order> Orders { get; set; }
}

Order Class: 
public class Order
{
    public int OrderID { get; set; }
    public int CustomerID { get; set; }
    public DateTime OrderDate { get; set; }
    public double TotalAmount { get; set; }

    // Navigation property
    public virtual Customer Customer { get; set; }
}

Querying with LINQ to SQL

Example: Querying Customers 
using System;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Collections.Generic;

[Table(Name = "Customers")]
public class Customer
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int CustomerID { get; set; }

    [Column]
    public string Name { get; set; }

    [Column]
    public string City { get; set; }

    private EntitySet<Order> orders;
    [Association(Storage = "orders", OtherKey = "CustomerID")]
    public EntitySet<Order> Orders
    {
        get { return orders; }
        set { orders.Assign(value); }
    }

    public Customer()
    {
        orders = new EntitySet<Order>();
    }
}

[Table(Name = "Orders")]
public class Order
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int OrderID { get; set; }

    [Column]
    public int CustomerID { get; set; }

    [Column]
    public DateTime OrderDate { get; set; }

    [Column]
    public double TotalAmount { get; set; }

    private EntityRef<Customer> customer;
    [Association(Storage = "customer", ThisKey = "CustomerID")]
    public Customer Customer
    {
        get { return customer.Entity; }
        set { customer.Entity = value; }
    }
}

public class SalesDataContext : DataContext
{
    public Table<Customer> Customers;
    public Table<Order> Orders;

    public SalesDataContext(string connection) : base(connection) { }
}

class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";
        SalesDataContext db = new SalesDataContext(connectionString);

        // Query: Select customers from "New York"
        var customersInNY = from customer in db.Customers
                            where customer.City == "New York"
                            select customer;

        foreach(var customer in customersInNY)
        {
            Console.WriteLine($"Customer: {customer.Name}, City: {customer.City}");
        }

        // Insert: Add a new customer
        Customer newCustomer = new Customer
        {
            Name = "Eve",
            City = "Chicago"
        };
        db.Customers.InsertOnSubmit(newCustomer);
        db.SubmitChanges();
        Console.WriteLine("Added new customer: Eve");
    }
}

Sample Output:
Customer: Alice, City: New York
Customer: David, City: New York
Added new customer: Eve

Explanation:
- DataContext: `SalesDataContext` manages the connection and provides access to tables.
- Mapping: Attributes like `[Table]` and `[Column]` map classes and properties to database tables and columns.
- Querying: LINQ queries select customers from a specific city.
- Inserting Data: New customers can be added and submitted to the database.

7. LINQ to XML

LINQ to XML provides an easy and efficient way to work with XML data using LINQ queries. It allows for querying, modifying, and creating XML documents in a declarative manner.

Introduction

- XML Manipulation: Simplifies working with XML data structures.
- In-Memory Representation: Loads XML data into `XDocument` or `XElement` objects for manipulation.
- Integration with LINQ: Enables powerful querying capabilities using LINQ syntax.

Querying and Modifying XML with LINQ

Example: Querying XML Data 
using System;
using System.Linq;
using System.Xml.Linq;

class Program
{
    static void Main()
    {
        string xmlData = @"
            <Books>
                <Book>
                    <Title>LINQ in Action</Title>
                    <Author>Fabrice Marguerie</Author>
                    <Price>29.99</Price>
                </Book>
                <Book>
                    <Title>Pro C# 7</Title>
                    <Author>Andrew Troelsen</Author>
                    <Price>39.99</Price>
                </Book>
                <Book>
                    <Title>Effective C#</Title>
                    <Author>Bill Wagner</Author>
                    <Price>24.99</Price>
                </Book>
            </Books>";

        XDocument doc = XDocument.Parse(xmlData);

        // Query: Select titles of books priced over $25
        var expensiveBooks = from book in doc.Descendants("Book")
                             where (double)book.Element("Price") > 25
                             select book.Element("Title").Value;

        Console.WriteLine("Expensive Books:");
        foreach(var title in expensiveBooks)
        {
            Console.WriteLine(title);
            // Output:
            // LINQ in Action
            // Pro C# 7
        }

        // Modify: Update the price of a specific book
        var bookToUpdate = doc.Descendants("Book")
                              .FirstOrDefault(b => b.Element("Title").Value == "Effective C#");

        if(bookToUpdate != null)
        {
            bookToUpdate.Element("Price").Value = "27.99";
            Console.WriteLine("\nUpdated XML:");
            Console.WriteLine(doc);
            // The price of "Effective C#" is now 27.99
        }
    }
}

Sample Output:
Expensive Books:
LINQ in Action
Pro C# 7
Updated XML:
<Books>
<Book>
<Title>LINQ in Action</Title>
<Author>Fabrice Marguerie</Author>
<Price>29.99</Price>
</Book>
<Book>
<Title>Pro C# 7</Title>
<Author>Andrew Troelsen</Author>
<Price>39.99</Price>
</Book>
<Book>
<Title>Effective C#</Title>
<Author>Bill Wagner</Author>
<Price>27.99</Price>
</Book>
</Books>

Creating XML Documents

Example: Creating an XML Document with LINQ to XML 
using System;
using System.Linq;
using System.Xml.Linq;

class Program
{
    static void Main()
    {
        XDocument doc = new XDocument(
            new XElement("Employees",
                new XElement("Employee",
                    new XElement("Name", "Alice"),
                    new XElement("Department", "HR"),
                    new XElement("Salary", 50000)
                ),
                new XElement("Employee",
                    new XElement("Name", "Bob"),
                    new XElement("Department", "IT"),
                    new XElement("Salary", 60000)
                )
            )
        );

        Console.WriteLine("Generated XML:");
        Console.WriteLine(doc);
    }
}

Sample Output:
Generated XML:
<Employees>
<Employee>
<Name>Alice</Name>
<Department>HR</Department>
<Salary>50000</Salary>
</Employee>
<Employee>
<Name>Bob</Name>
<Department>IT</Department>
<Salary>60000</Salary>
</Employee>
</Employees>

Modifying XML Documents

Example: Adding and Removing Elements 
using System;
using System.Linq;
using System.Xml.Linq;

class Program
{
    static void Main()
    {
        XDocument doc = XDocument.Load("employees.xml");

        // Add a new employee
        XElement newEmployee = new XElement("Employee",
                                new XElement("Name", "Charlie"),
                                new XElement("Department", "Finance"),
                                new XElement("Salary", 55000));
        doc.Root.Add(newEmployee);

        // Remove an employee
        XElement employeeToRemove = doc.Descendants("Employee")
                                       .FirstOrDefault(e => e.Element("Name").Value == "Bob");
        if(employeeToRemove != null)
        {
            employeeToRemove.Remove();
        }

        doc.Save("updated_employees.xml");
        Console.WriteLine("XML Updated Successfully.");
    }
}

Sample Output:
XML Updated Successfully.

Explanation:
- Adding Elements: A new `Employee` element is created and added to the root.
- Removing Elements: An existing `Employee` element is located and removed.
- Saving Changes: The modified XML is saved to a new file.

8. Custom LINQ Providers

Custom LINQ providers allow developers to create their own implementations of LINQ to interact with non-standard data sources. This is an advanced topic that involves understanding expression trees and implementing interfaces to handle query translation and execution.

What are Custom LINQ Providers?

A Custom LINQ Provider enables LINQ queries to be executed against custom data sources by translating LINQ expressions into the appropriate commands for those sources.

Use Cases:
- Interacting with APIs: Querying RESTful services using LINQ.
- Custom File Formats: Querying data from proprietary file formats.
- In-Memory Data Structures: Creating optimized querying mechanisms for complex in-memory data.

Creating Custom Providers

Creating a custom LINQ provider involves several steps:
1. Implementing IQueryable<T>: Represents the queryable data source.
2. Implementing IQueryProvider: Responsible for creating and executing queries.
3. Handling Expression Trees: Parses and translates LINQ expressions into commands understood by the data source.
4. Executing Queries: Retrieves data based on the translated commands.

Example: Simple Custom LINQ Provider Below is a simplified example of a custom LINQ provider that operates on a hypothetical data source.

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

public class MyQueryable<T> : IQueryable<T>
{
    private Expression expression;
    private MyQueryProvider provider;

    public MyQueryable(MyQueryProvider provider)
    {
        this.provider = provider;
        this.expression = Expression.Constant(this);
    }

    public MyQueryable(MyQueryProvider provider, Expression expression)
    {
        this.provider = provider;
        this.expression = expression;
    }

    public Type ElementType => typeof(T);

    public Expression Expression => expression;

    public IQueryProvider Provider => provider;

    public IEnumerator<T> GetEnumerator()
    {
        return ((IEnumerable<T>)provider.Execute(expression)).GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return GetEnumerator();
    }
}

public class MyQueryProvider : IQueryProvider
{
    public IQueryable CreateQuery(Expression expression)
    {
        Type elementType = TypeSystem.GetElementType(expression.Type);
        try
        {
            var queryableType = typeof(MyQueryable<>).MakeGenericType(elementType);
            return (IQueryable)Activator.CreateInstance(queryableType, new object[] { this, expression });
        }
        catch (Exception ex)
        {
            throw new InvalidOperationException(ex.Message);
        }
    }

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
    {
        return new MyQueryable<TElement>(this, expression);
    }

    public object Execute(Expression expression)
    {
        // Simplified execution: parse expression tree and return data
        // In real scenarios, this would translate the expression to the data source's query language
        return new List<T>() { default(T) };
    }

    public TResult Execute<TResult>(Expression expression)
    {
        return (TResult)Execute(expression);
    }
}

public static class TypeSystem
{
    public static Type GetElementType(Type seqType)
    {
        Type ienum = FindIEnumerable(seqType);
        if(ienum == null) return seqType;
        return ienum.GetGenericArguments()[0];
    }

    private static Type FindIEnumerable(Type seqType)
    {
        if(seqType == null || seqType == typeof(string))
            return null;
        if(seqType.IsGenericType)
        {
            foreach(Type arg in seqType.GetGenericArguments())
            {
                Type ienum = typeof(IEnumerable<>).MakeGenericType(arg);
                if(ienum.IsAssignableFrom(seqType))
                {
                    return ienum;
                }
            }
        }
        Type[] ifaces = seqType.GetInterfaces();
        if(ifaces != null && ifaces.Length > 0)
        {
            foreach(Type iface in ifaces)
            {
                Type ienum = FindIEnumerable(iface);
                if(ienum != null) return ienum;
            }
        }
        if(seqType.BaseType != null && seqType.BaseType != typeof(object))
        {
            return FindIEnumerable(seqType.BaseType);
        }
        return null;
    }
}

class Program
{
    static void Main()
    {
        MyQueryProvider provider = new MyQueryProvider();
        IQueryable<int> queryableData = new MyQueryable<int>(provider);

        var query = from num in queryableData
                    where num > 10
                    select num;

        foreach(var num in query)
        {
            Console.WriteLine(num); // Output: 0 (default int value from Execute method)
        }
    }
}

Sample Output:
0

Explanation:
- MyQueryable<T>: Implements IQueryable<T> to represent the queryable data source.
- MyQueryProvider: Implements IQueryProvider to handle query creation and execution.
- TypeSystem: Helper class to determine the element type of a sequence.
- Execution Logic: The `Execute` method is simplified to return a default list. In a real provider, this would translate and execute the query against the data source.
- Usage: Demonstrates creating a custom queryable and executing a simple LINQ query.

Note: Building a fully functional custom LINQ provider is complex and involves deep knowledge of expression trees and query translation mechanisms.

9. Advanced LINQ Topics

Expression Trees

Expression trees represent code in a tree-like data structure, where each node is an expression. They are essential for providers like LINQ to SQL and Entity Framework, which translate LINQ queries into SQL.

Example: Building and Inspecting an Expression Tree 
using System;
using System.Linq.Expressions;

class Program
{
    static void Main()
    {
        Expression<Func<int,bool>> expr = num => num > 5;

        Console.WriteLine(expr); // Output: num => (num > 5)

        // Inspect the expression tree
        var parameter = expr.Parameters[0];
        var body = expr.Body as BinaryExpression;

        Console.WriteLine($"Left: {body.Left}");   // Output: num
        Console.WriteLine($"Operator: {body.NodeType}"); // Output: GreaterThan
        Console.WriteLine($"Right: {body.Right}"); // Output: 5
    }
}

Sample Output:
num => (num > 5)
Left: num
Operator: GreaterThan
Right: 5

IQueryable vs. IEnumerable

IEnumerable<T>:
- Designed for in-memory collections.
- Supports deferred execution.
- Queries are executed on the client side.

IQueryable<T>: - Designed for remote data sources (e.g., databases).
- Supports deferred execution.
- Queries are translated into the data source's query language (e.g., SQL).

Key Differences:
- Execution: `IQueryable<T>` allows for query translation and execution on the data source, optimizing performance.
- Expression Trees: `IQueryable<T>` uses expression trees to represent queries, enabling providers to translate them appropriately.
- Flexibility: `IEnumerable<T>` is simpler but less flexible for remote data sources.

Example: IQueryable vs. IEnumerable 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<int> numbers = Enumerable.Range(1, 10).ToList();

        IQueryable<int> queryable = numbers.AsQueryable().Where(n => n > 5);
        IEnumerable<int> enumerable = numbers.Where(n => n > 5);

        Console.WriteLine("IQueryable Results:");
        foreach(var num in queryable)
        {
            Console.WriteLine(num); // Output: 6, 7, 8, 9, 10
        }

        Console.WriteLine("\nIEnumerable Results:");
        foreach(var num in enumerable)
        {
            Console.WriteLine(num); // Output: 6, 7, 8, 9, 10
        }
    }
}

Sample Output:
IQueryable Results:
6
7
8
9
10
IEnumerable Results:
6
7
8
9
10

Lambda Expressions

Lambda expressions provide a concise way to represent anonymous methods using the `=>` operator. They are extensively used in LINQ for defining predicates, projections, and more.

Example: Using Lambda Expressions with LINQ 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List<string> names = new List<string> { "Alice", "Bob", "Charlie", "David" };

        // Using lambda with Where
        var filteredNames = names.Where(name => name.StartsWith("A") || name.StartsWith("C"));

        foreach(var name in filteredNames)
        {
            Console.WriteLine(name); // Output: Alice, Charlie
        }
    }
}

Sample Output:
Alice
Charlie

Extension Methods

LINQ is built upon extension methods, allowing you to add new methods to existing types without modifying their source code. This enables a fluent interface for building queries.

Example: Creating and Using an Extension Method 
using System;
using System.Linq;
using System.Collections.Generic;

public static class Extensions
{
    public static IEnumerable<T> MyWhere<T>(this IEnumerable<T> source, Func<T, bool> predicate)
    {
        foreach(var item in source)
        {
            if(predicate(item))
                yield return item;
        }
    }
}

class Program
{
    static void Main()
    {
        List<int> numbers = Enumerable.Range(1, 10).ToList();

        // Using custom extension method
        var evenNumbers = numbers.MyWhere(n => n % 2 == 0);

        foreach(var num in evenNumbers)
        {
            Console.WriteLine(num); // Output: 2, 4, 6, 8, 10
        }
    }
}

Sample Output:
2
4
6
8
10

Explanation:
- Extension Method: `MyWhere` mimics the behavior of the built-in `Where` operator.
- Usage: Enables chaining methods in a fluent style.

10. Best Practices

Adhering to best practices ensures that your LINQ queries are efficient, readable, and maintainable.

Use Method Syntax for Complex Queries

While query syntax is readable for simple queries, method syntax offers more flexibility and is better suited for complex operations.

Example: Complex Query with Method Syntax 
var result = people.Where(p => p.Age > 25)
                   .OrderByDescending(p => p.Age)
                   .ThenBy(p => p.Name)
                   .Select(p => new { p.Name, p.Age });

Optimize Performance with Deferred Execution

Understand when queries are executed and manage deferred execution to avoid unnecessary computations and multiple enumerations.

Example: Avoiding Multiple Enumerations 
var query = numbers.Where(n => n > 10);

// Enumerate once and store the results
var results = query.ToList();

foreach(var num in results)
{
    Console.WriteLine(num);
}

Avoid Multiple Enumerations

Enumerating a query multiple times can lead to performance issues, especially with large data sets or expensive queries.

Example: 
var query = numbers.Where(n => n > 5);

// Avoid doing this if 'query' is expensive
int count = query.Count();
var first = query.First();

Solution: 
var results = query.ToList();
int count = results.Count;
var first = results.First();

Utilize Anonymous Types When Appropriate

Anonymous types are useful for projections where defining a new class would be unnecessary.

Example: 
var projection = people.Select(p => new { p.Name, p.Age });

foreach(var person in projection)
{
    Console.WriteLine($"{person.Name} is {person.Age} years old.");
}

Be Cautious with GroupBy and Join for Large Data Sets

Operations like `GroupBy` and `Join` can be resource-intensive with large collections. Optimize by filtering data before grouping or joining.

Example: 
var optimizedGroup = largeCollection
                     .Where(item => item.IsActive)
                     .GroupBy(item => item.Category);

11. Common Mistakes

Avoiding common pitfalls can save time and prevent bugs in your LINQ queries.

Forgetting to Execute Queries

LINQ queries are not executed until you iterate over them. Forgetting to execute a query can lead to unexpected results.

Mistake Example: 
var query = numbers.Where(n => n > 5);
// Not iterating or calling ToList(), so query is not executed

Solution: 
var results = query.ToList();
// Now the query is executed

Misunderstanding Deferred Execution

Assuming a query has executed when it hasn't, leading to bugs when the underlying data changes.

Mistake Example: 
var query = numbers.Where(n => n > 5);
numbers.Add(10); // Modifies the data source
foreach(var num in query)
{
    Console.WriteLine(num); // Includes 10
}
Solution:
Be aware of when queries are executed and use immediate execution methods if needed.

Using LINQ with Non-Thread-Safe Collections

Using LINQ with collections that are not thread-safe can lead to race conditions and data corruption.

Mistake Example: 
List<int> numbers = new List<int> { 1, 2, 3 };

// Accessing from multiple threads without synchronization
var query = numbers.Where(n => n > 1);

Solution:
Use thread-safe collections or synchronize access when using LINQ in multi-threaded environments.

Overusing Anonymous Types

While anonymous types are powerful, overusing them can lead to code that is hard to maintain and debug.

Mistake Example: 
var projection = people.Select(p => new { p.Name, p.Age, p.Address, p.Phone, p.Email, p.Department });
Solution:
Use named types (classes or structs) when projections become too complex or are reused.

12. Real-World Example: LINQ in a Data Processing Application

Problem Statement:
Develop a data processing application that reads employee data, filters employees based on specific criteria, groups them by department, and calculates aggregate statistics.

Data Setup:
Assume we have a list of employees with properties: `Name`, `Age`, `Department`, and `Salary`.

Example: 
using System;
using System.Linq;
using System.Collections.Generic;

class Program
{
    public class Employee
    {
        public string Name { get; set;}
        public int Age { get; set;}
        public string Department { get; set;}
        public double Salary { get; set;}
    }

    static void Main()
    {
        List<Employee> employees = new List<Employee>
        {
            new Employee { Name = "Alice", Age = 30, Department = "HR", Salary = 50000 },
            new Employee { Name = "Bob", Age = 25, Department = "IT", Salary = 60000 },
            new Employee { Name = "Charlie", Age = 35, Department = "HR", Salary = 55000 },
            new Employee { Name = "David", Age = 28, Department = "IT", Salary = 62000 },
            new Employee { Name = "Eve", Age = 40, Department = "Finance", Salary = 70000 },
            new Employee { Name = "Frank", Age = 45, Department = "Finance", Salary = 72000 },
            new Employee { Name = "Grace", Age = 27, Department = "IT", Salary = 58000 }
        };

        // Step 1: Filter employees older than 30
        var filtered = employees.Where(e => e.Age > 30);

        // Step 2: Group by Department
        var grouped = filtered.GroupBy(e => e.Department);

        // Step 3: Select department and average salary
        var departmentStats = grouped.Select(g => new
        {
            Department = g.Key,
            AverageSalary = g.Average(e => e.Salary),
            Employees = g.Select(e => e.Name)
        });

        // Step 4: Display results
        foreach(var dept in departmentStats)
        {
            Console.WriteLine($"Department: {dept.Department}");
            Console.WriteLine($" Average Salary: {dept.AverageSalary}");
            Console.WriteLine(" Employees:");
            foreach(var name in dept.Employees)
            {
                Console.WriteLine($"  - {name}");
            }
            Console.WriteLine();
        }

        // Output:
        // Department: HR
        //  Average Salary: 52500
        //  Employees:
        //   - Charlie
        //
        // Department: Finance
        //  Average Salary: 71000
        //  Employees:
        //   - Eve
        //   - Frank
    }
}

Sample Output:
Department: HR
Average Salary: 52500
Employees:
- Charlie
Department: Finance
Average Salary: 71000
Employees:
- Eve
- Frank

Explanation:
1. Filtering: Selects employees older than 30.
2. Grouping: Groups the filtered employees by their department.
3. Aggregation: Calculates the average salary for each department and lists the employee names.
4. Output: Displays the department name, average salary, and employee names.

This example demonstrates how LINQ can be used to perform complex data processing tasks in a readable and maintainable manner.

13. Summary

LINQ (Language Integrated Query) is a transformative feature in C# that brings powerful querying capabilities directly into the language. By enabling developers to write expressive and concise queries against diverse data sources, LINQ enhances code readability, maintainability, and efficiency.

Key Takeaways:
- Unified Querying: LINQ provides a consistent syntax for querying various data sources like collections, databases, and XML.

- Flexible Syntax: Developers can choose between query syntax and method syntax based on their preferences and the complexity of the query.

- Rich Operators: A comprehensive set of operators allows for filtering, projection, sorting, grouping, joining, and aggregating data.

- Deferred and Immediate Execution: Understanding execution strategies ensures optimal performance and predictable behavior.

- Integration with LINQ Providers: LINQ to Objects, LINQ to SQL, LINQ to XML, and others extend LINQ’s functionality to different data sources.

- Advanced Features: Expression trees, IQueryable vs. IEnumerable, lambda expressions, and extension methods provide deep flexibility and power.

- Best Practices: Following best practices leads to efficient, readable, and maintainable LINQ queries.

- Common Pitfalls: Awareness of common mistakes helps avoid bugs and performance issues.

- Real-World Applications: LINQ is instrumental in building data-driven applications, performing complex data transformations, and integrating with various frameworks and services.

By mastering LINQ, you can leverage its full potential to write elegant, efficient, and maintainable code, significantly improving productivity and the quality of C# applications.

Previous: C# Attributes | Next: C# using directive

<
>