How to: Map database relationships

You can encode as property references in your entity class any data relationships that will always be the same. In the Northwind sample database, for example, because customers typically place orders, there is always a relationship in the model between customers and their orders.

LINQ to SQL defines an AssociationAttribute attribute to help represent such relationships. This attribute is used together with the EntitySet<TEntity> and EntityRef<TEntity> types to represent what would be a foreign key relationship in a database. For more information, see the Association Attribute section of Attribute-Based Mapping.

Most relationships are one-to-many, as in the example later in this topic. You can also represent one-to-one and many-to-many relationships as follows:

• One-to-one: Represent this kind of relationship by including EntitySet<TEntity> on both sides.

  For example, consider a Customer-SecurityCode relationship, created so that the customer's security code will not be found in the Customer table and can be accessed only by authorized persons.

• Many-to-many: In many-to-many relationships, the primary key of the link table (also named the junction table) is often formed by a composite of the foreign keys from the other two tables.

  For example, consider an Employee-Project many-to-many relationship formed by using link table EmployeeProject. LINQ to SQL requires that such a relationship be modeled by using three classes: Employee, Project, and EmployeeProject. In this case, changing the relationship between an Employee and a Project can appear to require an update of the primary key EmployeeProject. However, this situation is best modeled as deleting an existing EmployeeProject and the creating a new EmployeeProject.

  Note

  Relationships in relational databases are typically modeled as foreign key values that refer to primary keys in other tables. To navigate between them you explicitly associate the two tables by using a relational join operation.

  Objects in LINQ to SQL, on the other hand, refer to each other by using property references or collections of references that you navigate by using dot notation.

Example 1

In the following one-to-many example, the Customer class has a property that declares the relationship between customers and their orders. The Orders property is of type EntitySet<TEntity>. This type signifies that this relationship is one-to-many (one customer to many orders). The OtherKey property is used to describe how this association is accomplished, namely, by specifying the name of the property in the related class to be compared with this one. In this example, the CustomerID property is compared, just as a database join would compare that column value.

[Table(Name = "Customers")]
public partial class Customer
{
    [Column(IsPrimaryKey = true)]
    public string CustomerID;
    // ...
    private EntitySet<Order> _Orders;
    [Association(Storage = "_Orders", OtherKey = "CustomerID")]
    public EntitySet<Order> Orders
    {
        get { return this._Orders; }
        set { this._Orders.Assign(value); }
    }
}

<Table(Name:="Customers")> _
Public Class Customer
    <Column(IsPrimaryKey:=True)> _
    Public CustomerID As String
    ' ...
    Private _Orders As EntitySet(Of Order)
    <Association(Storage:="_Orders", OtherKey:="CustomerID")> _
    Public Property Orders() As EntitySet(Of Order)
        Get
            Return Me._Orders
        End Get
        Set(ByVal value As EntitySet(Of Order))
            Me._Orders.Assign(value)
        End Set
    End Property
End Class

Example 2

You can also reverse the situation. Instead of using the Customer class to describe the association between customers and orders, you can use the Order class. The Order class uses the EntityRef<TEntity> type to describe the relationship back to the customer, as in the following code example.

[Table(Name = "Orders")]
public class Order
{
    [Column(IsPrimaryKey = true)]
    public int OrderID;
    [Column]
    public string CustomerID;
    private EntityRef<Customer> _Customer;
    [Association(Storage = "_Customer", ThisKey = "CustomerID")]
    public Customer Customer
    {
        get { return this._Customer.Entity; }
        set { this._Customer.Entity = value; }
    }
}

<Table(Name:="Orders")> _
Public Class Order
    <Column(IsPrimaryKey:=True)> _
    Public OrderID As Integer
    <Column()> _
    Public CustomerID As String
    Private _Customer As EntityRef(Of Customer)
    <Association(Storage:="Customer", ThisKey:="CustomerID")> _
    Public Property Customer() As Customer
        Get
            Return Me._Customer.Entity
        End Get
        Set(ByVal value As Customer)
            Me._Customer.Entity = value
        End Set
    End Property
End Class

See also

• How to: Customize Entity Classes by Using the Code Editor
• The LINQ to SQL Object Model