C# LINQ to SQL

Introduction

LINQ to SQL is an Object-Relational Mapping (ORM) technology that bridges the gap between the object-oriented world of C# and the relational database world of SQL. It's a component of the .NET Framework that provides a runtime infrastructure for managing relational data as objects without losing their relational capabilities.

LINQ to SQL allows you to:

• Query a SQL database using LINQ syntax directly in your C# code
• Create, retrieve, update, and delete data in your database
• Automatically map database tables to C# classes
• Work with your data as .NET objects rather than SQL commands and result sets

This guide will help you understand how to use LINQ to SQL in your C# applications, starting from basics and moving to more advanced concepts.

Setting up LINQ to SQL

Prerequisites

Before starting with LINQ to SQL, you need:

1. Visual Studio (any recent version)
2. A SQL Server database (Express edition is fine for learning)
3. .NET Framework (LINQ to SQL is part of .NET Framework) or .NET Core with the Microsoft.EntityFrameworkCore.SqlServer package

Creating a LINQ to SQL Data Context

The first step in using LINQ to SQL is to create a data context class that serves as the main conduit between your application and the database:

csharp

using System.Data.Linq;
using System.Data.Linq.Mapping;

[Database]
public class NorthwindDataContext : DataContext
{
    public Table<Customer> Customers;
    public Table<Order> Orders;
    
    public NorthwindDataContext(string connection) : base(connection) { }
}

[Table(Name = "Customers")]
public class Customer
{
    [Column(IsPrimaryKey = true)]
    public string CustomerID;
    
    [Column]
    public string CompanyName;
    
    [Column]
    public string ContactName;
    
    [Column]
    public string City;
    
    [Column]
    public string Country;
}

[Table(Name = "Orders")]
public class Order
{
    [Column(IsPrimaryKey = true)]
    public int OrderID;
    
    [Column]
    public string CustomerID;
    
    [Column]
    public DateTime OrderDate;
    
    [Column]
    public DateTime? ShippedDate;
}

This code creates:

1. A NorthwindDataContext class that connects to the database
2. Two entity classes (Customer and Order) mapped to database tables
3. Table properties in the context that represent database tables

Basic LINQ to SQL Queries

Retrieving Data

Let's see how to retrieve data using LINQ to SQL:

csharp

// Create a data context
string connectionString = @"Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
NorthwindDataContext db = new NorthwindDataContext(connectionString);

// Query all customers
var allCustomers = from customer in db.Customers
                  select customer;
                  
// Display the results
foreach (var customer in allCustomers)
{
    Console.WriteLine($"ID: {customer.CustomerID}, Name: {customer.CompanyName}, City: {customer.City}");
}

// Output:
// ID: ALFKI, Name: Alfreds Futterkiste, City: Berlin
// ID: ANATR, Name: Ana Trujillo Emparedados y helados, City: México D.F.
// ...and so on

Filtering Data

We can use LINQ's where clause to filter results:

csharp

// Find customers from Germany
var germanCustomers = from customer in db.Customers
                     where customer.Country == "Germany"
                     select customer;
                     
foreach (var customer in germanCustomers)
{
    Console.WriteLine($"{customer.CompanyName} from {customer.City}, {customer.Country}");
}

// Output:
// Alfreds Futterkiste from Berlin, Germany
// Blauer See Delikatessen from Mannheim, Germany
// ...and so on

Sorting Data

LINQ provides orderby for sorting results:

csharp

// Get customers sorted by company name
var sortedCustomers = from customer in db.Customers
                     orderby customer.CompanyName
                     select customer;
                     
// Get customers sorted by country, then by city
var sortedByLocation = from customer in db.Customers
                      orderby customer.Country, customer.City
                      select customer;

Projecting Data

You can select specific columns or create new objects from your data:

csharp

// Select only the names and cities
var customerLocations = from customer in db.Customers
                       select new { 
                           Name = customer.CompanyName, 
                           Location = customer.City + ", " + customer.Country 
                       };
                       
foreach (var item in customerLocations)
{
    Console.WriteLine($"{item.Name} is located in {item.Location}");
}

// Output:
// Alfreds Futterkiste is located in Berlin, Germany
// Ana Trujillo Emparedados y helados is located in México D.F., Mexico
// ...and so on

Working with Relationships

LINQ to SQL also handles relationships between tables:

csharp

// Add relationship to the Customer class
[Table(Name = "Customers")]
public class Customer
{
    // Existing properties
    
    [Association(OtherKey = "CustomerID")]
    public EntitySet<Order> Orders { get; set; }
    
    public Customer()
    {
        Orders = new EntitySet<Order>();
    }
}

// Add relationship to the Order class
[Table(Name = "Orders")]
public class Order
{
    // Existing properties
    
    private EntityRef<Customer> _customer;
    
    [Association(Storage = "_customer", ThisKey = "CustomerID")]
    public Customer Customer
    {
        get { return _customer.Entity; }
        set { _customer.Entity = value; }
    }
}

// Now you can navigate between related objects:
var customer = db.Customers.FirstOrDefault(c => c.CustomerID == "ALFKI");
Console.WriteLine($"Customer: {customer.CompanyName}");
Console.WriteLine($"Order count: {customer.Orders.Count}");

foreach (var order in customer.Orders)
{
    Console.WriteLine($"Order ID: {order.OrderID}, Date: {order.OrderDate}");
}

// Output:
// Customer: Alfreds Futterkiste
// Order count: 6
// Order ID: 10643, Date: 8/25/1997
// ...and so on

Modifying Data

Creating New Records

Adding new records with LINQ to SQL is straightforward:

csharp

// Create a new customer
Customer newCustomer = new Customer
{
    CustomerID = "DEMO1",
    CompanyName = "Demo Company",
    ContactName = "John Smith",
    City = "Seattle",
    Country = "USA"
};

// Add to the DataContext
db.Customers.InsertOnSubmit(newCustomer);

// Create a new order for this customer
Order newOrder = new Order
{
    CustomerID = "DEMO1",
    OrderDate = DateTime.Now
};

// Add to the DataContext
db.Orders.InsertOnSubmit(newOrder);

// Submit all changes to the database
db.SubmitChanges();

Console.WriteLine("New records added successfully!");

Updating Records

Updating data is also simple:

csharp

// Find the customer to update
Customer customerToUpdate = db.Customers.FirstOrDefault(c => c.CustomerID == "DEMO1");
if (customerToUpdate != null)
{
    // Update properties
    customerToUpdate.CompanyName = "Updated Demo Company";
    customerToUpdate.City = "Portland";
    
    // Submit changes
    db.SubmitChanges();
    Console.WriteLine("Customer updated successfully!");
}

Deleting Records

To delete records:

csharp

// Find the customer to delete
Customer customerToDelete = db.Customers.FirstOrDefault(c => c.CustomerID == "DEMO1");
if (customerToDelete != null)
{
    // Remove the customer
    db.Customers.DeleteOnSubmit(customerToDelete);
    
    // Submit changes
    db.SubmitChanges();
    Console.WriteLine("Customer deleted successfully!");
}

Advanced LINQ to SQL Features

Joins

LINQ to SQL makes joining tables easy:

csharp

// Join customers and orders
var customerOrders = from c in db.Customers
                    join o in db.Orders on c.CustomerID equals o.CustomerID
                    where c.Country == "USA"
                    select new { 
                        Customer = c.CompanyName,
                        OrderID = o.OrderID,
                        OrderDate = o.OrderDate
                    };
                    
foreach (var item in customerOrders)
{
    Console.WriteLine($"Customer: {item.Customer}, Order: {item.OrderID}, Date: {item.OrderDate.ToShortDateString()}");
}

Grouping

Group data to perform aggregations:

csharp

// Group orders by customer
var ordersByCustomer = from o in db.Orders
                      group o by o.CustomerID into g
                      select new { 
                          CustomerID = g.Key, 
                          OrderCount = g.Count() 
                      };
                      
foreach (var item in ordersByCustomer)
{
    Console.WriteLine($"Customer ID: {item.CustomerID}, Total Orders: {item.OrderCount}");
}

Aggregate Functions

LINQ provides aggregate functions like Count(), Sum(), Min(), Max(), and Average():

csharp

// Get order statistics
var orderStats = new
{
    TotalOrders = db.Orders.Count(),
    EarliestOrder = db.Orders.Min(o => o.OrderDate),
    LatestOrder = db.Orders.Max(o => o.OrderDate),
    AverageShippingDelay = db.Orders
        .Where(o => o.ShippedDate.HasValue)
        .Average(o => (o.ShippedDate.Value - o.OrderDate).TotalDays)
};

Console.WriteLine($"Total Orders: {orderStats.TotalOrders}");
Console.WriteLine($"Date Range: {orderStats.EarliestOrder.ToShortDateString()} to {orderStats.LatestOrder.ToShortDateString()}");
Console.WriteLine($"Average Shipping Delay: {orderStats.AverageShippingDelay:F2} days");

Stored Procedures and Functions

LINQ to SQL can map to stored procedures and functions:

csharp

[Database]
public class NorthwindDataContext : DataContext
{
    // Existing properties
    
    [Function(Name = "CustOrderHist")]
    public ISingleResult<CustOrderHistResult> GetCustomerOrderHistory(
        [Parameter(Name = "CustomerID")] string customerID)
    {
        IExecuteResult result = this.ExecuteMethodCall(
            this, 
            (MethodInfo)(MethodInfo.GetCurrentMethod()), 
            customerID);
        return (ISingleResult<CustOrderHistResult>)result.ReturnValue;
    }
}

[Table]
public class CustOrderHistResult
{
    [Column]
    public string ProductName;
    
    [Column]
    public int Total;
}

// Usage:
var orderHistory = db.GetCustomerOrderHistory("ALFKI");
foreach (var item in orderHistory)
{
    Console.WriteLine($"Product: {item.ProductName}, Quantity: {item.Total}");
}

Real-World Example: Customer Order Management System

Let's create a simple customer order management system to demonstrate LINQ to SQL in action:

csharp

using System;
using System.Data.Linq;
using System.Linq;

public class OrderManagementSystem
{
    private NorthwindDataContext db;
    
    public OrderManagementSystem(string connectionString)
    {
        db = new NorthwindDataContext(connectionString);
    }
    
    public void DisplayCustomerDashboard(string customerId)
    {
        var customer = db.Customers.FirstOrDefault(c => c.CustomerID == customerId);
        if (customer == null)
        {
            Console.WriteLine("Customer not found!");
            return;
        }
        
        Console.WriteLine("==== CUSTOMER DASHBOARD ====");
        Console.WriteLine($"Customer: {customer.CompanyName}");
        Console.WriteLine($"Contact: {customer.ContactName}");
        Console.WriteLine($"Location: {customer.City}, {customer.Country}");
        
        var recentOrders = customer.Orders
            .OrderByDescending(o => o.OrderDate)
            .Take(5);
            
        Console.WriteLine("\nRecent Orders:");
        foreach (var order in recentOrders)
        {
            string status = order.ShippedDate.HasValue ? 
                $"Shipped on {order.ShippedDate.Value.ToShortDateString()}" : 
                "Not shipped";
                
            Console.WriteLine($"Order #{order.OrderID} - {order.OrderDate.ToShortDateString()} - {status}");
        }
        
        var orderStats = new
        {
            TotalOrders = customer.Orders.Count,
            PendingShipment = customer.Orders.Count(o => !o.ShippedDate.HasValue),
            AverageDeliveryTime = customer.Orders
                .Where(o => o.ShippedDate.HasValue)
                .Average(o => (o.ShippedDate.Value - o.OrderDate).TotalDays)
        };
        
        Console.WriteLine("\nOrder Statistics:");
        Console.WriteLine($"Total Orders: {orderStats.TotalOrders}");
        Console.WriteLine($"Pending Shipment: {orderStats.PendingShipment}");
        Console.WriteLine($"Avg. Delivery Time: {orderStats.AverageDeliveryTime:F1} days");
    }
    
    public void ProcessNewOrder(string customerId, DateTime orderDate)
    {
        // Validate customer exists
        var customer = db.Customers.FirstOrDefault(c => c.CustomerID == customerId);
        if (customer == null)
        {
            Console.WriteLine("Cannot process order: Customer not found!");
            return;
        }
        
        // Create new order
        Order newOrder = new Order
        {
            CustomerID = customerId,
            OrderDate = orderDate
        };
        
        // Add to context and save
        db.Orders.InsertOnSubmit(newOrder);
        db.SubmitChanges();
        
        Console.WriteLine($"Order #{newOrder.OrderID} created successfully for {customer.CompanyName}");
    }
    
    public void ShipOrder(int orderId, DateTime shippedDate)
    {
        // Find the order
        var order = db.Orders.FirstOrDefault(o => o.OrderID == orderId);
        if (order == null)
        {
            Console.WriteLine("Order not found!");
            return;
        }
        
        // Update shipping date
        order.ShippedDate = shippedDate;
        db.SubmitChanges();
        
        Console.WriteLine($"Order #{order.OrderID} marked as shipped on {shippedDate.ToShortDateString()}");
        
        // Notify customer (in a real app, this would send an email)
        var customer = order.Customer;
        Console.WriteLine($"Notification sent to {customer.CompanyName}");
    }
}

// Usage:
static void Main()
{
    string connectionString = @"Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
    var system = new OrderManagementSystem(connectionString);
    
    // Display a customer's dashboard
    system.DisplayCustomerDashboard("ALFKI");
    
    // Process a new order
    system.ProcessNewOrder("ALFKI", DateTime.Today);
    
    // Ship an order
    system.ShipOrder(10248, DateTime.Today);
}

Summary

LINQ to SQL provides a powerful way to interact with SQL databases using familiar C# syntax. In this guide, we covered:

• Setting up LINQ to SQL with a data context
• Creating entity classes mapped to database tables
• Querying data using LINQ syntax
• Filtering, sorting, and projecting data
• Working with relationships between tables
• Creating, updating, and deleting records
• Advanced features like joins, grouping, and aggregate functions
• A real-world example of a customer order management system

LINQ to SQL simplifies database access by allowing you to work with strongly-typed objects while still leveraging the power of SQL Server. It bridges the gap between object-oriented programming and relational databases, making your code more maintainable and reducing the risk of SQL injection errors.

Additional Resources

To deepen your knowledge of LINQ to SQL, check out these resources:

1. Microsoft Documentation on LINQ to SQL
2. LINQ to SQL: .NET Language-Integrated Query for Relational Data
3. C# LINQ Tutorial

Exercises

To practice your LINQ to SQL skills, try these exercises:

1. Create a LINQ to SQL data context for a database of your choice
2. Write a query that performs a complex join between three tables
3. Implement a paging system that retrieves records in batches of 10
4. Create a method that performs a bulk update based on certain criteria
5. Write a query that uses grouping and aggregates to generate a sales report

Remember, the best way to learn LINQ to SQL is through practice. Start with simple queries and gradually work your way up to more complex operations as you become more comfortable with the syntax and capabilities.