2.4. Other LINQ to SQL Features

In this section we'll cover the following:

• Using SQLMetal to produce entity classes and associations automatically

• Using the INotifyPropertyChanging interface to communicate with LINQ about changes

• Using the optimistic concurrency and database transactions

• Using stored procedures

• Creating a database from a program

2.4.1. SQLMetal

LINQ to SQL has a command-line tool called SQLMetal that generates entity classes, properties, and associations automatically. Table 2-3 lists the SQLMetal options.

The following command uses SQLMetal to generate the entity classes to access to the People database within a Microsoft SQL Server 2005 database using Windows Integrated Security:

sqlmetal /server:pc-ferracchiati /database:People /pluralize /code:People.cs

If you want to use SQL Server security you have to add two more options to the command, specifying username and password:

sqlmetal /server:pc-ferracchiati /database:People /user:sa
         /password:sapass /pluralize /code:People.cs

You can also generate entity classes simply by specifying a database's data (.MDF) file:

sqlmetal /pluralize /code:People.cs c:datapeople.mdf

2.4.2. The INotifyPropertyChanging Interface

By opening up the code produced by the SQLMetal tool, we can see some minor differences between it and the code we wrote. There are four types of constructor accepting different connection attributes, such as a connection string and an IDBConnection object, but the big difference is the use of the INotifyPropertyChanging and INotifyPropertyChanged:

[Table(Name="Person")]
public partial class Person : INotifyPropertyChanging, INotifyPropertyChanged
{
  private int _ID;

Both the INotifyPropertyChanging interface and the INotifyPropertyChanged interface are in the System.ComponentModel namespace. Both interfaces require two events:

public event PropertyChangedEventHandler PropertyChanging;
public event PropertyChangedEventHandler PropertyChanged;

They also require virtual methods to handle the interfaces:

protected virtual void SendPropertyChanging() {
  if ((this.PropertyChanging != null)) {
    this.PropertyChanging(this, emptyChangingEventArgs);
  }
}

protected virtual void SendPropertyChanged(string propertyName) {
  if ((this.PropertyChanged != null)) {
    this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
  }
}

The emptyChangingEventArgs field is a private static class's field defined in the class as an object of the PropertyChangingEventArgs class created providing an empty string as parameter. In the generated code, each set accessor of a column calls two methods. The SendPropertyChanging method is called just before the variable is set to the provided value. The SendPropertyChanged method is called just after the variable is set.

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

The use of INotifyPropertyChanging and INotifyPropertyChanged is not mandatory. In fact, the code we wrote works very well. But these interfaces help LINQ change tracking. The SendPropertyChanging and SendPropertyChanged methods significantly improve change tracking because LINQ doesn't have to check changes manually. If you don't use these two interfaces and you don't inform LINQ about row changes, it will use two copies of the same object to understand if something is changed. There will be two objects representing each table, wasting memory and cycles when you call SubmitChanges().

2.4.3. Optimistic Concurrency and Database Transactions

What we have done to this point works well only if we are the only ones working on a set of data. If an application uses a LINQ query to retrieve data from a table already accessed by another user and then it tries to modify some rows, it could get an exception. This is because LINQ to SQL uses optimistic concurrency.

LINQ to SQL tracks changes to our objects after they are retrieved by a query and filled by a foreach statement or a call to a caching method such as ToList(). If another user has retrieved a row from the database and already changed its contents, when we try to submit our changes we'll get an exception. In fact, LINQ's change-tracking service discovers that the row has been changed from its original state (as of when we retrieved it) and raises the exception. To test the optimistic concurrency feature, write and execute the code in Listing 2-13.

Example 2-13. Testing the Optimistic Concurrency Feature

PeopleDataContext people = new PeopleDataContext();

Person p = people.People.Single(person => person.ID == 1);

p.LastName = "Optimistic";
p.FirstName = "Concurrency";

Console.ReadLine();

people.SubmitChanges();

The code simply retrieves the Person row whose identifier is equal to 1, changes some attributes, and submits the changes after a key is pressed.

This allows us to execute another instance of the same application that retrieves the same row before we press a key in the other instance of the application. Pressing a key in the first application will modify the row, whereas pressing a key in the second application will cause the exception shown in Figure 2-9.

Figure 2-9. The exception thrown by LINQ when the optimistic concurrency is violated

Concurrency is managed by the DataContext class. When we call SubmitChanges(), the data context creates a local transaction using the ReadCommit isolation level; that is, using optimistic concurrency.

This is the default. When we decorate the properties of the entity classes we can indicate which of them participate in optimistic concurrency. Using the UpdateCheck property of the Column attribute we can specify Never and LINQ will ignore the column during concurrency checking.

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

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

After we modify the Person entity class as shown, the code in Listing 2-13 will work without exceptions because the two columns don't participate in optimistic concurrency checking.

LINQ to SQL provides an advanced technique to manage update conflicts. When we call SubmitChanges(), we can specify a ConflictMode enum value to change the way optimistic concurrency is managed by LINQ.

Using ConflictMode.ContinueOnConflict the ChangeConflictException is filled with some attributes that we can use to personalize the way optimistic concurrency is managed. Using a try statement we can catch the ChangeConflictException and then use the ResolveAll() method provided by the ChangeConflicts property of the DataContext class to specify one of three values from the RefreshMode enumeration that in turn specify three different ways to resolve update conflicts:

KeepChanges

The old values contained in the object are refreshed with the new values changed by the other client. A new SubmitChanges() call is executed automatically and the current values within the object are used to update the row.

KeepCurrentValues

This rolls back each change made by the other client to the original database state. A new SubmitChanges() call is executed automatically and the current values within the object are used to update the row.

OverwriteCurrentValues

The object replaces its data with the new state of the row in the database.

The code in Listing 2-14 calls the ResolveAll method with KeepChanges after an optimistic concurrency exception has been detected.

Example 2-14. A try Statement to Manage Optimistic Concurrency Conflict

PeopleDataContext people = new PeopleDataContext();

Person p = people.People.Single(person => person.ID == 1);

p.IDRole = 2;

try
{
    people.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException cce)
{
    people.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
}

Sometimes we need to lock a row until we've finished managing it. This can be done by using a transaction and the pessimistic concurrency feature.

.NET 2.0 provides the TransactionScope class in the System.Transactions namespace. A simple way to implement pessimistic concurrency is with a using statement. Within a using block we can instantiate a TransactionScope and, as the last operation, call its Complete() method (see Listing 2-15).

Example 2-15. Implementing Pessimistic Concurrency with TransactionScope

PeopleDataContext people = new PeopleDataContext();

using (TransactionScope t = new TransactionScope())
{
    Person p = people.People.Single(person => person.ID == 1);

    p.LastName = "Pessimistic";
    p.FirstName = "Concurrency";

    Console.ReadLine();

    people.SubmitChanges();

    t.Complete();
}

We can test the pessimistic concurrency by executing two separate application instances (like in Listing 2-13). Both transactions attempt to lock the same row, and SQL Server decides which one (the "deadlock victim") to terminate (see Figure 2-10).

Figure 2-10. Pessimistic concurrency deadlock resolution

LINQ to SQL is able to integrate itself even with the old ADO.NET application code. We can use the DataContext class with SqlTransaction classes, but we'll have to do much more work to implement the local transaction. In the code snippet in Listing 2-16 a new Role is added to the related table using an ADO.NET local transaction.

Example 2-16. Using an ADO.NET Local Transaction with LINQ to SQL

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;

Role r = new Role();
r.RoleDescription = "Integration with old ADO.NET apps";
people.Roles.InsertOnSubmit(r);

people.Connection.Open();
people.Transaction = people.Connection.BeginTransaction();

try
{
    people.SubmitChanges();
    people.Transaction.Commit();
}
catch (Exception ex)
{
    people.Transaction.Rollback();
    throw ex;
}
finally
{
    if (people.Connection.State == System.Data.ConnectionState.Open)
        people.Connection.Close();
    people.Transaction = null;
}

As you can see in Listing 2-16, we have to pay attention to some things, such as manually opening and closing the connection and calling Commit() or Rollback() (when everything is fine or something goes wrong, respectively).

2.4.4. Stored Procedures

LINQ to SQL automatically produces SQL statements to select rows, insert rows, and so on. We often prefer to use existing stored procedures or create new ones to access data and improve application performance. Stored procedures are SQL statement procedures that are precompiled and stored within the SQL Server database. When you call a stored procedure, the database server simply executes it without doing other operations such as checking SQL syntax within it. In many cases calling a stored procedure to retrieve rows works better than using dynamic SQL.

LINQ to SQL provides the ExecuteCommand method of the DataContext class to call stored procedures. This method has two parameters: the SQL command to execute and the collection of parameters that can be used in the SQL command.

Within the class inheriting from the DataContext we can add a method to call the ExecuteCommand() method that provides the stored procedure name and its parameters:

public void InsertRole(Role r)
{
this.ExecuteCommand("exec uspInsertRole @description={0}", r.RoleDescription);
}

The uspInsertRole stored procedure simply adds a new role, accepting its description as a parameter (Role's identifier is auto-incremented by the server since it is of the identity type). The ExecuteCommand() method will substitute each placeholder specified in the command with the related parameter contained in the collection.

Up to this point we have written all the necessary code to execute our stored procedure instead of executing the code generated by LINQ to SQL. In Listing 2-17 a new role is added to the related table and the Log property is used to show the code called by LINQ.

Example 2-17. A New Role Is Added and the Stored Procedure Is Called Automatically.

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;

Role r = new Role();
r.RoleDescription = "By SP";
people.Roles.InsertOnSubmit(r);

people.SubmitChanges();

When you executing the code you will obtain the result shown in Figure 2-11, which displays how LINQ calls the stored procedure automatically.

Figure 2-11. The framework uses our stored procedure instead of generating the code to insert a new role.

To update rows using a stored procedure we can specify an update method in the class that inherits from the DataContext class. The prototype of the update method is similar to the one used to insert a record, except using the Update word instead of the Insert word:

public void UpdateRole(Role newRole)
{
    int iRowsAffected = this.uspUpdateRole(
       newRole.ID, newRole.RoleDescription);

    if (iRowsAffected < 1)
       throw new ChangeConflictException();
}

Moreover, we have to check the return value of uspUpdateRole() because if it is less than 1 an optimistic concurrency error has occurred. In that case we must throw a new ChangeConflictException exception. The uspUpdateRole() method is responsible to call the stored procedure in the People database. Its internal code is similar to other methods you will see soon in a few pages.

Listing 2-18 shows the code that will call the update stored procedure automatically.

Example 2-18. Updating a Role Calling a Stored Procedure Instead of Using the LINQ to SQL Update-Generated Statement

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;

Role r = people.Roles.Single(role => role.ID == 1);
r.RoleDescription = "By Update stored procedure";

people.SubmitChanges();

To delete a row by using a stored procedure we have to add a new method in the class that inherits from the DataContext:

public void DeleteRole(Role r)
{
    this.ExecuteCommand("exec uspDeleteRole @id={0}", r.ID);
}

In this way the code in Listing 2-19 will call a stored procedure to remove each role from the database.

Example 2-19. Using a Stored Procedure Instead of an Autogenerated Delete Statement to Delete All the Roles That Have a Particular Description

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;

var query = people.Roles
            .Where(role =>
            role.RoleDescription == "By Update stored procedure ")
            .Select(role => role);

foreach (Role r in query)
    people.Roles.DeleteOnSubmit(r);

people.SubmitChanges();

By using SQLMetal with the /sprocs option we can generate entity classes containing methods that have the same name as a stored procedure. Based on the syntax of the stored procedure, the generated code could return a single value or a collection of objects.

The simplest case is a stored procedure that computes scalar operations using the COUNT operator:

create procedure uspCountPerson
as
    declare @count int
    set @count = (select count(ID) from person)
    return @count

Executing the SQLMetal application with the /sprocs option, the generated code will contain the following method:

[Function(Name="dbo.uspCountPerson")]
public int uspCountPerson() {
  IExecuteResults result =
    ExecuteMethodCall(this,
      ((MethodInfo)(MethodInfo.GetCurrentMethod())));
  return ((int)(result.ReturnValue));
}

The method will be decorated with the Function attribute where the stored procedure name will be specified. The method name will be similar or equal to the stored procedure name. SQLMetal will infer the method return type by analyzing the SQL statement that the stored procedure uses. For this reason there are some situations SQLMetal tool can't handle. If the stored procedure uses temporary tables or dynamic SQL (by calling the sp_executesql system-stored procedure), the tool will not be able to infer the result's type. Therefore, it will not able to define a related method with a valid return type. These kinds of stored procedures cannot be used with LINQ to SQL. Finally, the body of the generated method contains a call to the ExecuteMethodCall method provided by the DataContext class. This method has two parameters indicating the MethodInfo object for the current method (useful for discovering the stored procedure name by reflection) and a collection of parameters that have to be passed to the stored procedure. Listing 2-20 uses this method to call the related stored procedure.

Example 2-20. Using the Method Associated with a Stored Procedure to Retrieve the Number of Person Rows in the Database

PeopleDataContext people = new PeopleDataContext();

Console.WriteLine("Person count = {0}",
                   people.uspCountPerson());

If we have a stored procedure selecting a set of rows, we can use the same technique to produce a method, calling that stored procedure and returning a collection of objects:

create procedure uspGetRoleDescription
    @description varchar(50)
as
    SELECT ID, RoleDescription
    FROM Role
    WHERE RoleDescription LIKE @description

The stored procedure in the example returns a set of role rows in which the role description is like a provided parameter. Since the selected columns have been specified in the Role class we can define a method that calls this stored procedure and returns a collection of Role objects.

[StoredProcedure(Name = "dbo.uspGetRoleDescription")]
public IEnumerable<Role> uspGetRoleDescription(
  [Parameter(Name = "@description")] string description)
{
  IQueryResults<Role> result =
    ExecuteMethodCall<Role>(this,
      ((MethodInfo)(MethodInfo.GetCurrentMethod())),
                    description);
   return ((IEnumerable<Role>)(result));
}

When the stored procedure accepts parameters, we have to decorate each related method parameter with the Parameter attribute where we specify its name. The ExecuteMethodCall<T> method uses the properties contained in the class specified as parameter T to fill the object with the column value returned by the stored procedure. Finally, the IQueryResult<T> interface is converted to IEnumerable<T> in order to be used by the iterator reading its records.

Listing 2-21 shows the code that calls this method.

Example 2-21. Using the uspGetRoleDescription Method to Retrieve Roles Rows

PeopleDataContext people = new PeopleDataContext();

foreach(Role r in people.uspGetRoleDescription("M%"))
{
    Console.WriteLine("Role: {0} {1}", r.ID.ToString(),
        r.RoleDescription);
}

The last case supported by LINQ to SQL is for stored procedures using OUTPUT parameters:

create procedure uspGetTotalSalaryAmountPerYear
    @year int,
    @amount money output
as
    set @amount = (select sum(SalaryYear)
                   from Salary
                   where year=@year)
    select @amount

The stored procedure above computes the total money amount for the salary in a specified year. When the SQLMetal tool encounters this stored procedure it will produce the following method:

[Function(Name = "dbo.uspGetTotalSalaryAmountPerYear")]
public ISingleResult<uspGetTotalSalaryAmountPerYearResult>
uspGetTotalSalaryAmountPerYear(
[Parameter(DbType = "Int")] System.Nullable<int> year,
[Parameter(DbType = "Money")]
ref System.Nullable<decimal> amount)

{
    IExecuteResult result = this.ExecuteMethodCall(this,
      ((MethodInfo)(MethodInfo.GetCurrentMethod())),
                    year,
                    amount);
    amount = ((System.Nullable<decimal>)
             (result.GetParameterValue(1)));

return ((ISingleResult<uspGetTotalSalaryAmountPerYearResult>)
(result.ReturnValue));
}

First the OUTPUT parameter is transformed into a ref method parameter. Then the ISingleResult is used with GetParameterValue() to set the value of the ref variable. The SQLMetal tool generates a new class to contain the retrieved records: the uspGetTotalSalaryAmountPerYear class.

Listing 2-22 shows the code necessary to execute this method and retrieve the total money amount for the year 2004.

Example 2-22. Using the Method Related to the Stored Procedure to Retrieve the Total Money Amount for the Year 2004

PeopleDataContext people = new PeopleDataContext();

decimal? total = 0;
int year = 2004;

people.UspGetTotalSalaryAmountPerYear(year, ref total);

Console.WriteLine(total.ToString());

2.4.5. User-Defined Functions

LINQ to SQL also supports user-defined functions (UDFs), which return both scalar values and result sets.

Using the SQLMetal tool's /functions option, we can obtain a new method in the class that inherits from the DataContext class; the new method is decorated with attributes for building a SQL statement that calls a UDF.

The following UDF returns the initials of the person whose identifier is specified as an argument:

create function udfGetInitials(@id    int)
returns varchar(2)
as
begin
    declare @initials varchar(2)
    set @initials = (SELECT LEFT(FirstName,1) + LEFT(LastName,1)
                     FROM Person
                     WHERE ID = @id)

    return @initials
end

Executing the SQLMetal tool to generate entity classes and user-defined function code, we obtain the following method code:

[Function(Name = "dbo.udfGetInitials", IsComposable = true)]
public string udfGetInitials([Parameter(DbType = "Int")] Nullable<int> id)
{
    return ((string)(this.ExecuteMethodCall(this,
        ((MethodInfo)(MethodInfo.GetCurrentMethod())),
        id).ReturnValue));
}

First the Function attribute is used to decorate the method and inform LINQ that it is associated with the UDF specified with the Name parameter. The IsComposable flag set to true indicates to LINQ to SQL that this is a UDF, not a stored procedure.

Finally, the method's approach is similar to the one seen during stored procedure calling: the ExecuteMethodCall returns an IExecuteResult result. The ReturnValue property is casted to string and returned to the method caller.

Listing 2-23 shows a code snippet in which the UDF is called within a LINQ query to obtain the initials of each person present in the Person table.

Example 2-23. The UDF Is Transformed into a Method That Can be Called as Usual from Our Code.

PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;

var query = from p in people.People
            select new {p.ID, Initials = people.UdfGetInitials(p.ID)};

foreach(var row in query)
    Console.WriteLine("PersonID: {0} - Initials: {1}",
                      row.ID, row.Initials);

Figure 2-12 shows the output from Listing 2-23.

Figure 2-12. The output shows how the UDF calculates the person's initials.

As Figure 2-12 shows, the SELECT statement built by LINQ contains an inline call to the UDF. That's because we have used the related method within our LINQ query. If we use the method outside a query we will obtain a simple statement like this one:

SELECT dbo.udfGetInitials(@p0)

2.4.6. Database Creation

Since Microsoft has released a free version of Microsoft SQL Server 2005 called Express Edition, we can easily create an application that stores data using a database instead of XML files or some other data storage. In fact, SQL Server Express Edition can be distributed without limits, allowing us to install and use it even with desktop client applications. Focusing on that feature, a way to create a database on the fly could be really useful.

LINQ to SQL provides a method of the DataContext class called CreateDatabase. Using the attributes specified in the entity classes, where each column is decorated with options such as column name, column database data type, and so on, LINQ is able to create a new database.

Listing in Listing 2-24 shows how you can use CreateDatabase() to create a new database.

Example 2-24. Creating a New Database with the CreateDatabase() Method

PeopleDataContext people = new PeopleDataContext(

@"Data Source=.;Initial Catalog=PeopleFromCode;Integrated
       Security=True");

   if (people.DatabaseExists())
       people.DeleteDatabase();

   people.CreateDatabase();

Note the connection string that points to a nonexistent database. The DataContext class uses the connection string to discover whether the database already exists. Otherwise it uses the catalog option specified in the connection string as database name and creates it. Using the DatabaseExists and DeleteDatabase methods we can check if the database already exists and if so, drop it.

There are some limitations when using the CreateDatabase method to create a database:

• Because stored procedures, UDFs, and triggers are not defined in the entity classes as structure, they are not reproduced.

• Despite the fact that associations could be declared into entity classes, the method is not able to create foreign keys and constraints.

• The application must impersonate a user who has rights to create the database.