LINQ to SQL

Introduction

LINQ to SQL is a component of the .NET Framework that provides a runtime infrastructure for managing relational data as objects. It's an Object-Relational Mapping (ORM) implementation that allows developers to model a relational database using .NET classes, then query the database using LINQ and manipulate the data using an object-oriented approach.

LINQ to SQL bridges the gap between the object-oriented programming world and relational databases by:

Automatically generating SQL queries from LINQ expressions
Mapping database tables to .NET classes
Tracking changes to objects and translating them to SQL commands
Providing a simple, consistent programming model for data access

While newer technologies like Entity Framework have gained more popularity in recent years, LINQ to SQL remains a lightweight and efficient option for SQL Server database access.

Getting Started with LINQ to SQL

Setting Up Your Project

To start using LINQ to SQL, you'll need:

A SQL Server database
Visual Studio with .NET Framework support
References to System.Data.Linq in your project

Let's walk through the basic setup:

csharp
// Add these namespaces to your file
using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;

Creating the Data Model

LINQ to SQL maps database tables to .NET classes. There are two ways to create this mapping:

Using the Visual Studio LINQ to SQL Designer (.dbml file)
Using attributes to manually map your classes

Using the LINQ to SQL Designer

Right-click on your project in the Solution Explorer
Select "Add" > "New Item" > "LINQ to SQL Classes"
Name your file (e.g., NorthwindDB.dbml)
From the Server Explorer, drag tables from your database onto the designer surface

Visual Studio will automatically generate the classes for you.

Manual Mapping with Attributes

csharp
[Table(Name = "Customers")]
public class Customer
{
    [Column(IsPrimaryKey = true)]
    public string CustomerID { get; set; }
    
    [Column]
    public string CompanyName { get; set; }
    
    [Column]
    public string ContactName { get; set; }
    
    [Column]
    public string Country { get; set; }
    
    [Association(OtherKey = "CustomerID")]
    public EntitySet<Order> Orders { get; set; }
    
    public Customer()
    {
        Orders = new EntitySet<Order>();
    }
}

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

Creating the DataContext

The DataContext class is the main entry point for LINQ to SQL operations. It represents your connection to the database and provides methods for retrieving and submitting changes:

csharp
public class NorthwindDataContext : DataContext
{
    public Table<Customer> Customers;
    public Table<Order> Orders;
    
    public NorthwindDataContext(string connectionString) : base(connectionString) { }
}

Querying Data with LINQ to SQL

One of the most powerful features of LINQ to SQL is the ability to write database queries using C# or VB.NET syntax. The LINQ provider translates your code into SQL at runtime.

Basic Queries

Let's see some simple query examples:

csharp
// Create the DataContext
string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
NorthwindDataContext db = new NorthwindDataContext(connectionString);

// Query all customers
var allCustomers = from c in db.Customers
                  select c;
                  
// Display results
foreach (var customer in allCustomers)
{
    Console.WriteLine($"Customer: {customer.CompanyName} from {customer.Country}");
}

// Output:
// Customer: Alfreds Futterkiste from Germany
// Customer: Ana Trujillo Emparedados y helados from Mexico
// Customer: Antonio Moreno Taquería from Mexico
// ...

Filtering Data

csharp
// Find customers from a specific country
var germanCustomers = from c in db.Customers
                      where c.Country == "Germany"
                      select c;

// Using method syntax instead of query syntax
var ukCustomers = db.Customers
                   .Where(c => c.Country == "UK")
                   .OrderBy(c => c.CompanyName);

Console.WriteLine("UK Customers:");
foreach (var customer in ukCustomers)
{
    Console.WriteLine($"- {customer.CompanyName}");
}

// Output:
// UK Customers:
// - Around the Horn
// - B's Beverages
// - Consolidated Holdings
// - Eastern Connection
// - Island Trading
// - North/South
// - Seven Seas Imports

Projections

You can select specific properties or create new objects:

csharp
// Select only specific properties
var customerNames = from c in db.Customers
                   select new { c.CustomerID, c.CompanyName };

// Create a custom projection
var customerSummaries = from c in db.Customers
                       select new 
                       {
                           ID = c.CustomerID,
                           Company = c.CompanyName,
                           Location = $"{c.City}, {c.Country}",
                           OrderCount = c.Orders.Count
                       };

foreach (var summary in customerSummaries)
{
    Console.WriteLine($"{summary.Company} ({summary.Location}) - {summary.OrderCount} orders");
}

// Output:
// Alfreds Futterkiste (Berlin, Germany) - 6 orders
// Ana Trujillo Emparedados y helados (México D.F., Mexico) - 4 orders
// ...

Joining Data

csharp
// Join customers with their orders
var customerOrders = from c in db.Customers
                    join o in db.Orders on c.CustomerID equals o.CustomerID
                    where o.OrderDate > new DateTime(1998, 1, 1)
                    select new 
                    {
                        Customer = c.CompanyName,
                        OrderID = o.OrderID,
                        OrderDate = o.OrderDate
                    };

// Output example results
foreach (var item in customerOrders.Take(5))
{
    Console.WriteLine($"Order {item.OrderID}: {item.Customer} ordered on {item.OrderDate:d}");
}

// Output:
// Order 10835: QUICK-Stop ordered on 1/15/1998
// Order 10838: Linea Piu ordered on 1/19/1998
// Order 10839: Tradição Hipermercados ordered on 1/19/1998
// Order 10841: Supermercados Unidos ordered on 1/20/1998
// Order 10842: Tortuga Restaurante ordered on 1/20/1998

LINQ to SQL sets up relationships between your classes that mirror the foreign key relationships in your database:

csharp
// Find a customer
var customer = db.Customers.FirstOrDefault(c => c.CustomerID == "ALFKI");

// Access related orders through navigation property
Console.WriteLine($"{customer.CompanyName} has {customer.Orders.Count} orders:");

foreach (var order in customer.Orders)
{
    Console.WriteLine($"- Order {order.OrderID} placed on {order.OrderDate:d}");
}

// Output:
// Alfreds Futterkiste has 6 orders:
// - Order 10643 placed on 8/25/1997
// - Order 10692 placed on 10/3/1997
// - Order 10702 placed on 10/13/1997
// - Order 10835 placed on 1/15/1998
// - Order 10952 placed on 3/16/1998
// - Order 11011 placed on 4/9/1998

Modifying Data with LINQ to SQL

LINQ to SQL not only fetches data but also manages updates, inserts, and deletes.

Inserting Data

csharp
// Create a new customer
Customer newCustomer = new Customer
{
    CustomerID = "NEWID",
    CompanyName = "New Test Company",
    ContactName = "John Smith",
    Country = "USA",
    City = "Seattle"
};

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

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

Console.WriteLine($"Added customer: {newCustomer.CompanyName}");

Updating Data

csharp
// Find a customer to update
var customerToUpdate = db.Customers.SingleOrDefault(c => c.CustomerID == "NEWID");

if (customerToUpdate != null)
{
    // Update properties
    customerToUpdate.ContactName = "Jane Doe";
    customerToUpdate.Phone = "555-1234";
    
    // Submit changes
    db.SubmitChanges();
    Console.WriteLine("Customer updated successfully");
}

Deleting Data

csharp
// Find a customer to delete
var customerToDelete = db.Customers.SingleOrDefault(c => c.CustomerID == "NEWID");

if (customerToDelete != null)
{
    // Mark for deletion
    db.Customers.DeleteOnSubmit(customerToDelete);
    
    // Submit changes
    db.SubmitChanges();
    Console.WriteLine("Customer deleted successfully");
}

Transaction Support

LINQ to SQL supports transactions, which are crucial for maintaining data integrity:

csharp
using (TransactionScope transaction = new TransactionScope())
{
    try
    {
        // Add a new customer
        Customer newCust = new Customer
        {
            CustomerID = "TRAN1",
            CompanyName = "Transaction Test Company"
        };
        db.Customers.InsertOnSubmit(newCust);
        
        // Add a new order for this customer
        Order newOrder = new Order
        {
            CustomerID = "TRAN1",
            OrderDate = DateTime.Now
        };
        db.Orders.InsertOnSubmit(newOrder);
        
        // Save both changes in a transaction
        db.SubmitChanges();
        
        // Complete the transaction
        transaction.Complete();
        Console.WriteLine("Transaction completed successfully");
    }
    catch (Exception ex)
    {
        // Transaction will automatically roll back if not completed
        Console.WriteLine($"Error occurred: {ex.Message}");
        // No need to explicitly roll back
    }
}

Real-World Example: Customer Order System

Let's build a simple customer order management system to demonstrate LINQ to SQL in a practical context:

csharp
// Define our DataContext
public class OrderSystemDataContext : DataContext
{
    public Table<Customer> Customers;
    public Table<Order> Orders;
    public Table<Product> Products;
    public Table<OrderDetail> OrderDetails;
    
    public OrderSystemDataContext(string connectionString) : base(connectionString) { }
}

// Handler for the "Get Monthly Sales Report" feature
public static void GenerateMonthlySalesReport(OrderSystemDataContext db, int year, int month)
{
    var startDate = new DateTime(year, month, 1);
    var endDate = startDate.AddMonths(1);
    
    var monthlySales = from o in db.Orders
                       where o.OrderDate >= startDate && o.OrderDate < endDate
                       join od in db.OrderDetails on o.OrderID equals od.OrderID
                       join p in db.Products on od.ProductID equals p.ProductID
                       group od by new { p.ProductName, p.CategoryID } into g
                       select new
                       {
                           Product = g.Key.ProductName,
                           Category = g.Key.CategoryID,
                           Quantity = g.Sum(od => od.Quantity),
                           Revenue = g.Sum(od => od.Quantity * od.UnitPrice * (decimal)(1 - od.Discount))
                       };
                       
    Console.WriteLine($"Monthly Sales Report for {startDate:MMMM yyyy}");
    Console.WriteLine("================================================");
    
    decimal totalRevenue = 0;
    foreach (var item in monthlySales.OrderByDescending(s => s.Revenue))
    {
        Console.WriteLine($"{item.Product,-35} | {item.Quantity,5} units | ${item.Revenue,10:F2}");
        totalRevenue += item.Revenue;
    }
    
    Console.WriteLine("------------------------------------------------");
    Console.WriteLine($"Total Monthly Revenue: ${totalRevenue:F2}");
}

// Example usage:
public static void Main()
{
    string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
    using (var db = new OrderSystemDataContext(connectionString))
    {
        // Find top customers by order value
        var topCustomers = from c in db.Customers
                          join o in db.Orders on c.CustomerID equals o.CustomerID
                          join od in db.OrderDetails on o.OrderID equals od.OrderID
                          group od by new { c.CustomerID, c.CompanyName } into g
                          select new
                          {
                              CustomerID = g.Key.CustomerID,
                              CompanyName = g.Key.CompanyName,
                              TotalSpent = g.Sum(od => od.Quantity * od.UnitPrice * (decimal)(1 - od.Discount))
                          };
                          
        Console.WriteLine("Top 5 Customers by Revenue:");
        foreach (var customer in topCustomers.OrderByDescending(c => c.TotalSpent).Take(5))
        {
            Console.WriteLine($"{customer.CompanyName}: ${customer.TotalSpent:F2}");
        }
        
        // Generate a monthly report
        Console.WriteLine("\nGenerating Monthly Report...\n");
        GenerateMonthlySalesReport(db, 1997, 7);
    }
}

// Output example:
// Top 5 Customers by Revenue:
// QUICK-Stop: $110277.31
// Ernst Handel: $104874.98
// Save-a-lot Markets: $104361.95
// Rattlesnake Canyon Grocery: $51097.80
// Hungry Owl All-Night Grocers: $49979.89
//
// Generating Monthly Report...
//
// Monthly Sales Report for July 1997
// ================================================
// Côte de Blaye                     |    17 units | $  2071.20
// Thüringer Rostbratwurst           |    20 units | $   946.00
// Tarte au sucre                    |    20 units | $   753.80
// ...
// ------------------------------------------------
// Total Monthly Revenue: $12704.21

Best Practices and Considerations

When working with LINQ to SQL, keep these best practices in mind:

Use compiled queries for repetitive operations:

csharp
private static Func<NorthwindDataContext, string, IQueryable<Customer>> 
    customersByCountry = CompiledQuery.Compile(
        (NorthwindDataContext db, string country) => 
            from c in db.Customers 
            where c.Country == country 
            select c);

// Later usage:
var germanCustomers = customersByCountry(db, "Germany");

Handle database concurrency conflicts:

csharp
try
{
    db.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
    foreach (ObjectChangeConflict conflict in db.ChangeConflicts)
    {
        // Choose database values
        conflict.Resolve(RefreshMode.OverwriteCurrentValues);
    }
}

Use lazy loading judiciously - Avoid issues with the "N+1 selects" problem by using DataLoadOptions:

csharp
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Customer>(c => c.Orders);
db.LoadOptions = options;

Consider using stored procedures for complex operations - LINQ to SQL supports mapping to stored procedures:

csharp
[Function(Name = "CustOrderHist")]
[ResultType(typeof(OrderHistoryResult))]
public IEnumerable<OrderHistoryResult> GetCustomerOrderHistory([Parameter] string customerID)
{
    IExecuteResult result = this.ExecuteMethodCall(
        this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
    return (IEnumerable<OrderHistoryResult>)result.ReturnValue;
}

Limitations of LINQ to SQL

While LINQ to SQL is powerful, it has some limitations to be aware of:

Only works with SQL Server databases
Limited support for complex inheritance scenarios
No built-in migration support
Microsoft has shifted focus to Entity Framework for future development

Summary

LINQ to SQL provides a streamlined approach to database access in .NET applications by enabling developers to:

Map database tables to .NET classes
Query databases using familiar LINQ syntax instead of SQL
Track changes to objects and persist them back to the database
Manage relationships between tables through navigation properties
Work with database operations within the comfortable object-oriented paradigm

Though it's been superseded by Entity Framework in terms of Microsoft's focus, LINQ to SQL remains a lightweight, efficient ORM that's particularly well-suited for smaller applications with SQL Server databases.

Additional Resources

Exercises

Create a LINQ to SQL data model for a library management system with tables for Books, Authors, and Borrowers.
Write a query to find all books borrowed in the last 30 days along with borrower information.
Implement a method that adds a new book and associates it with existing authors.
Create a report showing the most popular books based on borrowing frequency.
Implement optimistic concurrency handling for updating book information.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Getting Started with LINQ to SQL​

Setting Up Your Project​

Creating the Data Model​

Using the LINQ to SQL Designer​

Manual Mapping with Attributes​

Creating the DataContext​

Querying Data with LINQ to SQL​

Basic Queries​

Filtering Data​

Projections​

Joining Data​

Using Navigation Properties​

Modifying Data with LINQ to SQL​

Inserting Data​

Updating Data​

Deleting Data​

Transaction Support​

Real-World Example: Customer Order System​

Best Practices and Considerations​

Limitations of LINQ to SQL​

Summary​

Additional Resources​

Exercises​