MSDN Documentation

Types of Relationships

LINQ to SQL supports common relational database concepts like one-to-many and many-to-one relationships. These are typically defined through foreign key constraints in your database schema.

Defining Relationships in the DBML Designer

When using the SQLMetal tool or the visual designer to generate your LINQ to SQL data context from a database, relationships are automatically detected based on foreign key constraints.

In the DBML designer, relationships are visually represented by lines connecting tables. You can double-click these lines to view and edit the relationship properties, such as the association name and the type of relationship.

Accessing Related Data

Once relationships are defined, LINQ to SQL makes it straightforward to navigate between related entities.

One-to-Many Navigation

If you have a `Customer` entity with a collection of `Orders` (e.g., `Customer.Orders`), you can easily iterate through the orders associated with a specific customer:

var customer = db.Customers.FirstOrDefault(c => c.CustomerID == 1);
if (customer != null)
{
    foreach (var order in customer.Orders)
    {
        Console.WriteLine($"Order ID: {order.OrderID}, Order Date: {order.OrderDate}");
    }
}
                

Many-to-One Navigation

Conversely, if you have an `Order` entity with a reference to its `Customer` (e.g., `Order.Customer`), you can access customer details directly:

var order = db.Orders.FirstOrDefault(o => o.OrderID == 100);
if (order != null)
{
    Console.WriteLine($"Customer Name: {order.Customer.CustomerName}");
    Console.WriteLine($"Customer City: {order.Customer.City}");
}
                

Deferred Loading vs. Eager Loading

LINQ to SQL supports both deferred loading (lazy loading) and eager loading for related data.

Deferred Loading (Default)

By default, LINQ to SQL uses deferred loading. This means that related data is only retrieved from the database when you explicitly access the related collection or object. This can improve initial query performance but may lead to the "N+1 select" problem if not managed carefully.

Eager Loading (Using `LoadWith`)

To avoid the N+1 problem and retrieve related data in a single query, you can use the `LoadWith` extension method (available via `System.Data.Linq.DataLoadOptions`):

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Customer>(c => c.Orders); // Eagerly load Orders for Customers
db.LoadOptions = dlo;

var customersWithOrders = db.Customers.ToList(); // This query now includes Orders
foreach (var customer in customersWithOrders)
{
    Console.WriteLine($"Customer: {customer.CustomerName}, Number of Orders: {customer.Orders.Count}");
}
                

Association Attributes

When defining entities manually or if the DBML designer doesn't infer relationships correctly, you can use attributes like [Association] to explicitly define the relationships in your entity classes.

[Table(Name = "Customers")]
public class Customer
{
    [Column(IsPrimaryKey = true)]
    public int CustomerID;

    [Column]
    public string CustomerName;

    [Association(OtherKey = "CustomerID", ThisKey = "CustomerID")]
    public EntitySet<Order> Orders;
}

[Table(Name = "Orders")]
public class Order
{
    [Column(IsPrimaryKey = true)]
    public int OrderID;

    [Column]
    public int CustomerID; // Foreign Key

    [Association(ThisKey = "CustomerID", OtherKey = "CustomerID")]
    public Customer Customer;
}
                

ThisKey refers to the column(s) in the current table, and OtherKey refers to the column(s) in the related table.