Tutorial 5: Configuring relationships One-To-One and One-To-Many between entities

Description Article
Table of Contents
Summary

Entity relationships are implemented in the code through the use of the generic types EntitySet<T> and EntitiyRef<T> whith are used to define the class members involved in the relationships.. Consequently, objects that refer to each other using property references or collections of references can be navigated using “dot” notation.

Description

A relationship defined in the code should reflect:

  1. An existent Primary Key/Foreign Key defined in the relational database;
  2. A configuration using a LInq association atribute or association node on a XML external mapping file

If no Primary Key/Foreign Key exists in the database then a JOIN operator must be used when querying otherwise sub-members can be accessed directly.

An association is configured according the properties described in the table below:

PROPERTY DESCRIPTION
DeleteOnNull If set then child objects are deleted when their parent is deleted or the FK is set to NULL, avoiding ORPHAN objects when deleting a parent. This is automatically set to true when the Cascade option in the DeleteRule of the relationship defined in SQL Server is specified.
DeleteRule Specifies the delete behavior associated with the foreign key. For example, it is possible to add cascade records’ deletions when using this property. The delete rule is inferred from the “Delete Rule” configured on the relationship in the database.
IsForeignKey When set to true this property indicates that the column represents the foreign key.
IsUnique When set to true this property indicates that there is a 1:1 relationship between entities. In the XML file should be used as "Cardinality="one|many"
Name Identifies the name of the relation. Usually its value is the same as the name of the foreign key constraint relation name defined in the database.
OtherKey Identifies a list of parent entity class keys separated by commas. If the keys are not specified, LINQ to SQL infers them, and assumes they are equal to the primary keys defined in the parent entity class.
Storage Storage contains the name of the private field defined in the class. When specifying this property, LINQ to SQL will use the class’s field to access data instead of using the related get and set accessors.
ThisKey Identifies a list of keys of this entity class, separated by commas. If the keys are not specified, LINQ to SQL assumes they are equal to the primary keys defined in this class.
Member Identifies the name of the class property that encapsulates the private field specified on Storage.
Operations
Configuring a one-to-many relationship using Linq attributes

The relationship is configured as described:

In the parent class:

1) The association attribute is added to the collection property providing information about the relation to the child table in the database
2) The EntitySet collection private field is initialized in the constructor indicating two delegates:
i. the attach method is internally called when adding a new child object to the parent’s collection
ii. the deattach method is internally called to use when removing a child object on the parent’s collection

In the child class (ONLY IN CASE OF BI-DIRECTIONAL NAVIGATION):

1) The EntityRef private field is initialized to default in the class constructor
2) The association attribute is added on the property of the relationship providing information about the relation to the parent table in the database:
2.1) The get acessor returns the object currently referenced as the parent entity
2.2) The set acessor:
i. checks if the new value is different from the currently referenced entitiy or if is still not been loaded or assigned. If this is the scenario, the current entity reference is removed before adding the new object.
ii. this child object is added to the parent’s collection to maintain referential integrity

How to do it
//Parent Class

[Table(Name="dbo.Customers")]
public partial class Customer : INotifyPropertyChanging, INotifyPropertyChanged
{
	private string _CustomerID;

	private EntitySet<Order> _Orders;

    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnCustomerIDChanging(string value);
    partial void OnCustomerIDChanged();
	...
    #endregion

	public Customer()
	{
	this._Orders = new EntitySet<Order>(new Action<Order>(this.attach_Orders), new Action<Order>(this.detach_Orders));
	OnCreated();
	}

	[Column(Storage="_CustomerID", DbType="NChar(5) NOT NULL", CanBeNull=false, IsPrimaryKey=true)]
	public string CustomerID
	{
		get
		{
			return this._CustomerID;
		}
		set
		{
			if ((this._CustomerID != value))
			{
				this.OnCustomerIDChanging(value);
				this.SendPropertyChanging();
				this._CustomerID = value;
				this.SendPropertyChanged("CustomerID");
				this.OnCustomerIDChanged();
			}
		}
	}

	[Association(Name="Customer_Order", Storage="_Orders", ThisKey="CustomerID", OtherKey="CustomerID")]
	public EntitySet<Order> Orders
	{
		get
		{
			return this._Orders;
		}
		set
		{
			this._Orders.Assign(value);
		}
	}

	private void attach_Orders(Order entity)
	{
		this.SendPropertyChanging();
		entity.Customer = this;
	}

	private void detach_Orders(Order entity)
	{
		this.SendPropertyChanging();
		entity.Customer = null;
	}
}

// Child Class

[Table(Name="dbo.Orders")]
public partial class Order : INotifyPropertyChanging, INotifyPropertyChanged
{
	private int _OrderID;

	private string _CustomerID;

	private EntityRef<Customer> _Customer;

    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnOrderIDChanging(int value);
    partial void OnOrderIDChanged();
    partial void OnCustomerIDChanging(string value);
    partial void OnCustomerIDChanged();
    ...
    #endregion

	[Column(Storage="_OrderID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
	public int OrderID
	{
		get
		{
			return this._OrderID;
		}
		set
		{
			if ((this._OrderID != value))
			{
				this.OnOrderIDChanging(value);
				this.SendPropertyChanging();
				this._OrderID = value;
				this.SendPropertyChanged("OrderID");
				this.OnOrderIDChanged();
			}
		}
	}

	[Column(Storage="_CustomerID", DbType="NChar(5)")]
	public string CustomerID
	{
		get
		{
			return this._CustomerID;
		}
		set
		{
			if ((this._CustomerID != value))
			{
				if (this._Customer.HasLoadedOrAssignedValue)
				{
					throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
				}
				this.OnCustomerIDChanging(value);
				this.SendPropertyChanging();
				this._CustomerID = value;
				this.SendPropertyChanged("CustomerID");
				this.OnCustomerIDChanged();
			}
		}
	}

	[Association(Name="Customer_Order", Storage="_Customer", ThisKey="CustomerID", OtherKey="CustomerID", IsForeignKey=true)]
	public Customer Customer
	{
		get
		{
			return this._Customer.Entity;
		}
		set
		{
			Customer previousValue = this._Customer.Entity;
			if (((previousValue != value)
				|| (this._Customer.HasLoadedOrAssignedValue == false)))
			{
				this.SendPropertyChanging();
				if ((previousValue != null))
				{
					this._Customer.Entity = null;
					previousValue.Orders.Remove(this);
				}
				this._Customer.Entity = value;
				if ((value != null))
				{
					value.Orders.Add(this);
					this._CustomerID = value.CustomerID;
				}
				else
				{
					this._CustomerID = default(string);
				}
				this.SendPropertyChanged("Customer");
			}
		}
	}
	...
}
Configuring a one-to-one relationship using Linq attributes

The relationship is configured as described:

In the parent class:

1) The association attribute is added on the property of the relatioship providing information about the relation to the child table in the database.
2) The corresponding private field is initialized to default in the class constructor as an EntityRef type.

In the child class (ONLY IN CASE OF BI-DIRECTIONAL NAVIGATION):

1) The association attribute is added to the property of the relationship providing information about the relation to the parent table in the database:
1.1) The get acessor returns the obect currently referenced as the parent entity
1.2) The set acessor:
i. checks if the new value is different from the currently referenced entitiy or if was still not been loaded or assigned. If this is the scenario, the current entity reference is removed before adding the new object.
ii. the new object value is assigned to the parent relationship property to maintain referential integrity and the entitiy identifier is populated

How to do it
//Parent Class

Table(Name="dbo.Orders")]
public partial class Order : INotifyPropertyChanging, INotifyPropertyChanged
{

	private int _OrderID;

	private EntityRef<Order_Detail> _Order_Details;

    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnOrderIDChanging(int value);
    partial void OnOrderIDChanged();
	...
    #endregion

	public Order()
	{
		this._Order_Details = default(EntityRef<Order_Detail>);
		OnCreated();
	}

	[Column(Storage="_OrderID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
	public int OrderID
	{
		get
		{
			return this._OrderID;
		}
		set
		{
			if ((this._OrderID != value))
			{
				this.OnOrderIDChanging(value);
				this.SendPropertyChanging();
				this._OrderID = value;
				this.SendPropertyChanged("OrderID");
				this.OnOrderIDChanged();
			}
		}
	}

	[Association(Name="Order_Order_Detail", Storage="_Order_Details", ThisKey="OrderID", OtherKey="OrderID", IsUnique=true, IsForeignKey=false)]
	public Order_Detail Order_Details
	{
		get
		{
			return this._Order_Details.Entity;
		}
		set
		{
			Order_Detail previousValue = this._Order_Details.Entity;
			if (((previousValue != value)
						|| (this._Order_Details.HasLoadedOrAssignedValue == false)))
			{
				this.SendPropertyChanging();
				if ((previousValue != null))
				{
					this._Order_Details.Entity = null;
					previousValue.Order = null;
				}
				this._Order_Details.Entity = value;
				if ((value != null))
				{
					value.Order = this;
				}
				this.SendPropertyChanged("Order_Details");
			}
		}
	}
	...
}

// Child Class

[Table(Name="dbo.[Order Details]")]
public partial class Order_Detail : INotifyPropertyChanging, INotifyPropertyChanged
{
	private int _OrderID;

	private EntityRef<Order> _Order;

    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnOrderIDChanging(int value);
    partial void OnOrderIDChanged();
    ...
    #endregion

	public Order_Detail()
	{
		this._Order = default(EntityRef<Order>);
		OnCreated();
	}

	[Column(Storage="_OrderID", DbType="Int NOT NULL", IsPrimaryKey=true)]
	public int OrderID
	{
		get
		{
			return this._OrderID;
		}
		set
		{
			if ((this._OrderID != value))
			{
				if (this._Order.HasLoadedOrAssignedValue)
				{
					throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
				}
				this.OnOrderIDChanging(value);
				this.SendPropertyChanging();
				this._OrderID = value;
				this.SendPropertyChanged("OrderID");
				this.OnOrderIDChanged();
			}
		}
	}

	[Association(Name="Order_Order_Detail", Storage="_Order", ThisKey="OrderID", OtherKey="OrderID", IsForeignKey=true)]
	public Order Order
	{
		get
		{
			return this._Order.Entity;
		}
		set
		{
			Order previousValue = this._Order.Entity;
			if (((previousValue != value)
						|| (this._Order.HasLoadedOrAssignedValue == false)))
			{
				this.SendPropertyChanging();
				if ((previousValue != null))
				{
					this._Order.Entity = null;
					previousValue.Order_Details = null;
				}
				this._Order.Entity = value;
				if ((value != null))
				{
					value.Order_Details = this;
					this._OrderID = value.OrderID;
				}
				else
				{
					this._OrderID = default(int);
				}
				this.SendPropertyChanged("Order");
			}
		}
	}
	...
}
Configuring a one-to-many relationship using a XML external file

The same code shown above should be used excluding the decorator attributes that are overrided if an external XML file is used.

How to do it
<?xml version="1.0" encoding="utf-8"?>
<Database Name="Northwind" Class="NorthwindDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
  <Connection/>
  <Table Name="dbo.Customers" Member="Customers">
    <Type Name="Customer">
      <Column Name="CustomerID" Type="System.String" DbType="NChar(5) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Association Name="Customer_Order" Member="Orders" ThisKey="CustomerID" OtherKey="CustomerID" Type="Order" />
    </Type>
  </Table>
  <Table Name="dbo.Orders" Member="Orders">
    <Type Name="Order">
      <Column Name="OrderID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
      <Column Name="CustomerID" Type="System.String" DbType="NChar(5)" CanBeNull="true" />
      <Association Name="Customer_Order" Member="Customer" ThisKey="CustomerID" OtherKey="CustomerID" Type="Customer" IsForeignKey="true" />
    </Type>
  </Table>
</Database>
Configuring a one-to-one relationship using a XML external file

The same code shown above should be used excluding the decorator attributes that are overrided if an external XML file is used.

How to do it
<?xml version="1.0" encoding="utf-8"?>
<Database Name="Northwind" Class="NorthwindDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
  <Connection/>
  <Table Name="dbo.Customers" Member="Customers">
    <Type Name="Customer">
      <Column Name="CustomerID" Type="System.String" DbType="NChar(5) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Association Name="Customer_Order" Member="Orders" ThisKey="CustomerID" OtherKey="CustomerID" Type="Order", Cardinality="One" />
    </Type>
  </Table>
  <Table Name="dbo.Orders" Member="Orders">
    <Type Name="Order">
      <Column Name="OrderID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
      <Column Name="CustomerID" Type="System.String" DbType="NChar(5)" CanBeNull="true" />
      <Association Name="Order_Order_Detail" Member="Order_Details" ThisKey="OrderID" OtherKey="OrderID" Type="Order_Detail" Cardinality="One" />
    </Type>
  </Table>
  <Table Name="dbo.[Order Details]" Member="Order_Details">
    <Type Name="Order_Detail">
      <Column Name="OrderID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Association Name="Order_Order_Detail" Member="Order" ThisKey="OrderID" OtherKey="OrderID" Type="Order" IsForeignKey="true" />
    </Type>
  </Table>
</Database>