2.3. DataContext: Advanced Features

We've focused our attention on the basic features provided by LINQ to SQL (in the System.Data.Linq.dll assembly). Data contexts have even more features. In the next sections you'll see how to define relationships between entities, and the benefits of doing that.

2.3.1. Defining Relationships Between Entities

The first feature we'll look at regards relationships between tables. A relational database such as Microsoft SQL Server provides the capability to define a relationship between two tables using primary and foreign keys. For example, a table containing a list of orders could have a foreign key pointing to a customers table. Using this relationship we can easily retrieve all the orders made by a specific customer. Moreover, we can define the rules to apply to the rows of related tables when some action occurs. For example, we can inform the database to remove every order row for a customer when the related customer is removed.

The relationships between objects are defined in a different way. Usually a class contains a collection of related objects from another class.

LINQ to SQL provides a relational-like way to define a relationship between two entity classes. Thanks to new generic types such as EntitySet<T> and EntityRef<T>, it's possible to define the class members that are involved in relationships.

The steps to implement relationships between entity classes are as follows:

In the People database, the Person table has a foreign key, IDRole, pointing to the primary key of the Role table. Using the LINQ to SQL Association attribute (in the System.Data.Linq namespace) with the Role and Person class definitions, we can specify this kind of relationship between these tables in our code. Let's apply these steps to the Role and Person classes. Listing 2-8 gives the code for the parent entity class, Role.

Example 2-8. The Role Entity Class

[Table(Name="Role")]
public class Role
{
   private int _ID;
   private string _Description;
   private EntitySet<Person> _People;

   public Role() {
       _People = new EntitySet<Person>(
           new Action<Person>(Attach_Person),
           new Action<Person>(Detach_Person));
   }

[Association(Name="FK_Person_Role",
       Storage="_People",
       OtherKey="IDRole")]
   public EntitySet<Person> People
   {
       get { return _People; }
       set { _People.Assign(value); }
   }

   private void Attach_Person(Person entity) {
       entity.Role = this;
   }

   private void Detach_Person(Person entity) {
       entity.Role = null;
   }

   [Column(Storage = "_ID", Name = "ID",
       DbType = "Int NOT NULL IDENTITY",
       IsPrimaryKey = true,
       IsDbGenerated = true,
       CanBeNull = false)]
   public int ID
   {
       get
       {
           return this._ID;
       }
   }
   [Column(Name="RoleDescription",
           Storage="_Description",
           DbType="nvarchar NOT NULL",
           CanBeNull = false)]
   public string RoleDescription
   {
       get { return _Description; }
       set { _Description = value; }
   }
}

The Role entity class represents the parent table. That's why it has

private EntitySet<Person> _People;

which contains the People objects that belong to a role.

The Role class has to define a public property that encapsulates the access code to the EntitySet<Person> private field. Here is the code snippet for it:

[Association(Name="FK_Person_Role",
             Storage="_People",
             OtherKey="IDRole")]
public EntitySet<Person> People

public EntitySet<Person> People
{
    get { return _People; }
    set { _People.Assign(value); }
}

The Assign method of EntitySet<T> sets the new value in the collection so that the new object is monitored by LINQ to SQL and by its change-tracking service.

The Association attribute informs LINQ to SQL about the relationship name, the private field used to store the Person objects collection, and the foreign key in the related table. The Association attribute provides the ThisKey property too (see Table 2-2 for the full list of properties). It represents the parent-table key related to the OtherKey. In our example, ThisKey has been omitted because it coincides with the primary key and the LINQ to SQL is able to infer its name automatically.

The next step is to initialize the private field using the Role class constructor:

public Role() {
   _People = new EntitySet<Person>(
       new Action<Person>(Attach_Person),
       new Action<Person>(Detach_Person));
}

We pass two delegate methods to the EntitySet<T> constructor. The Attach_Person method will set the related Role object to the new Person object. The Detach_Person method will set to null the related Role object in the Person object:

private void Attach_Person(Person entity) {
    entity.Role = this;
}

private void Detach_Person(Person entity) {
    entity.Role = null;
}

In the child entity class related to the Person database table, we add a private EntityRef<Role> field so we'll be able to retrieve its role simply:

Console.WriteLine(person.Role.RoleDescription);

Next we have to add a public property containing the accessors to get and set the private field value. In accordance with the steps listed earlier, we have to use the Association attribute even with this public property. Here we should define the same name used in the earlier example because LINQ to SQL has to know that we are going to define the other side of the relation. Moreover, using the ThisKey property we can specify the column name of the child entity class related to the foreign key column of the database table.

Listing 2-9 shows how to modify the Person class to define the relationship with the Role class:

Example 2-9. The Person Class Modified to Include the Relationship with the Role Class

[Table(Name="Person")]
public class Person
{
   private int _ID;
   private int _IDRole;
   private string _lastName;
   private string _firstName;

   public Person() {
       _Role = default(EntityRef<Role>);
   }

   [Column(Name="FirstName",
           Storage="_firstName",
           DbType="nvarchar NOT NULL",
           CanBeNull = false)]
   public string FirstName
   {
      get { return _firstName; }
      set { _firstName = value; }
   }

   [Column(Name="LastName",
           Storage="_lastName",
           DbType="nvarchar NOT NULL",
           CanBeNull = false)]
   public string LastName
   {
       get { return _lastName; }
       set { _lastName = value; }
   }

   [Column(Name="IDRole",
           Storage="_IDRole",
           DbType="int NOT NULL",
           CanBeNull = false)]
   public int IDRole
   {
       get { return _IDRole; }
       set { _IDRole = value; }

}

   [Column(Storage = "_ID",
   Name = "ID",
   DbType = "Int NOT NULL IDENTITY",
   IsPrimaryKey = true,
   CanBeNull = false)]
    public int ID
    {
        get
        {
            return this._ID;
        }
    }
    private EntityRef<Role> _Role;

    [Association(Name="FK_Person_Role",
                 Storage="_Role",
                 ThisKey="IDRole",
                 OtherKey="ID",
                 IsForeignKey=true)]
    public Role Role
    {
        get { return _Role.Entity; }
        set {
            Role previousValue = this._Role.Entity;
            if (((previousValue != value)
                        || (this._Role.HasLoadedOrAssignedValue == false)))
            {
                if ((previousValue != null))
                {
                    this._Role.Entity = null;
                    previousValue.People.Remove(this);
                }
                this._Role.Entity = value;
                if ((value != null))
                {
                    value.People.Add(this);
                    this._IDRole = value.ID;
                }
                else
                {
                    this._IDRole = default(int);

}
            }
        }
    }
}

The following class constructor uses the default keyword applied to a generic type to initialize its value:

public Person() {
    _Role = default(EntityRef<Role>);
}

The default keyword applied to a struct data type such as the EntityRef<T> initializes every single field within the struct, setting the reference data types to null and the numeric-value data types to zero.

In the set accessor we check if the value specified is different from the one within the Entity property of the _Role field or if the _Role field is still not been loaded or assigned. If so, we have to remove the old one before adding the new value. Finally, the Person object pointed by the this keyword is added to the People collection to maintain referential integrity:

public Role Role
{
    get { return _Role.Entity; }
    set {
        Role previousValue = this._Role.Entity;
        if (((previousValue != value)
                    || (this._Role.HasLoadedOrAssignedValue == false)))
        {
            if ((previousValue != null))
            {
                this._Role.Entity = null;
                previousValue.People.Remove(this);
            }
            this._Role.Entity = value;
            if ((value != null))
            {
                value.People.Add(this);
                this._IDRole = value.ID;

}
            else
            {
                this._IDRole = default(int);
            }
        }
    }
}

2.3.2. Using Two Related Entity Classes

Now that we've defined the relationship between two entity classes we can use it to query and modify data.

The code in Listing 2-10 retrieves a single Person object and then uses its role.

Example 2-10. Retrieving a Person and Using Its Role Property

PeopleDataContext people = new PeopleDataContext();

people.Log = Console.Out;

var query =
    from p in people.People
    where p.ID == 1
    select p;

foreach(var row in query)
{


    Console.WriteLine(
        "Full Name: {0} {1} Role: {2}",
        row.FirstName,
        row.LastName,
       row.Role.RoleDescription);
}

We don't need to the join the two tables to access the role. LINQ to SQL generates two SQL queries to retrieve both the Person and related Role data. See the two SQL statements in Figure 2-7.

Figure 2-7. The output of Listing 2-10

Note in Figure 2-7 that the @p0 parameter is used in both the queries. They are two different queries, so the @p0 parameter has different values. The @p0 parameter used in the first query is the value specified in the LINQ query. The @p0 parameter used in the second query is the value specified with the ThisKey property of the Association attribute used in the Role property decoration.

In Listing 2-11 we'll retrieve a role and then use its People property to add a new person.

Example 2-11. Adding a New Person to the Database Starting from a Role

PeopleDataContext people = new PeopleDataContext();

people.Log = Console.Out;

Role role = people.Roles.Single(r => r.ID == 1);

Person person = new Person();
person.FirstName = "From";
person.LastName = "Relationship";
role.People.Add(person);

people.SubmitChanges();

Since there's a relationship between the two entity classes, we don't have to specify the IDRole for the Person object. It will be assigned by the Attach_Person delegate function when a new Person object is added to the people collection of the Role entity class. Figure 2-8 shows the INSERT statement generated by LINQ to SQL. The last SELECT statement returns the new Person's ID so that the Person object in the code can be aligned with database-related record.

Figure 2-8. The INSERT statement generated by LINQ to SQL

Deleting a row and every row related to it is really simple when a relationship is defined between two entity classes. Listing 2-12 deletes a role and all its related Person records.

Example 2-12. Deleting a Role and All of Its Related Person Records

PeopleDataContext people = new PeopleDataContext();

people.Log = Console.Out;

Role role = new Role();
role.RoleDescription = "Administrator";

Person person = new Person();
person.FirstName = "From";
person.LastName = "Code";

role.People.Add(person);

people.Roles.InsertOnSubmit(role);

people.SubmitChanges();

Role admin = people.Roles.Single(r => r.ID == role.ID);
people.Roles.DeleteOnSubmit(admin);
people.SubmitChanges();

It creates a new role as follows:

Role role = new Role();
role.RoleDescription = "Administrator";

then adds a new person to it:

Person person = new Person();
person.FirstName = "From";
person.LastName = "Code";

role.People.Add(person);
people.Roles.InsertOnSubmit(role);

The code uses the new role's identifier to retrieve the new row added to the database:

Role admin = people.Roles.Single(r => r.ID == role.ID);
people.Roles.DeleteOnSubmit(admin);
people.SubmitChanges();

We have provided two delegate functions responding to the Add and the Remove events of the Person entity class. In the body of the Detach_Person delegate function we have set the Role value to null, raising a call to the set accessor of the Role property. It's in the body of Detach_Person that you will find the Remove() method of the Person row related to the role. The Remove() method applied by the parent class will call the Detach delegate function for each child row related to it. This process will be performed once for each Person object related to the Role object.