2.2. Database Interaction

LINQ to SQL introduces LINQ functionality for Microsoft SQL Server 2000 and 2005. Thanks to the IQueryable<T> interface, it's theoretically possible to create providers for other databases. Although it's not in the scope of this book, see articles from Matt Warren at his blog site for more information: http://blogs.msdn.com/mattwar/default.aspx.

LINQ to SQL defines new C# attributes, properties, and classes to let us interact with SQL Server databases by mapping database objects to objects in our programs. Three basic steps are required:

The next three sections provide examples of each step.

2.2.1. Mapping a Class to a Database Table

Mapping a class to a database table allows us to use LINQ against the table. The Table attribute, defined in the System.Data.Linq namespace, informs LINQ about how to map a class to a database table.

The following code simply declares a new public class named Person and associates it with the Person database table.

[Table(Name="Person")]
public class Person

The Table attribute's Name property is optional. LINQ uses the class name as the default table name.

2.2.2. Mapping Fields and Properties to Table Columns

Mapping fields and properties to table columns makes the columns available to LINQ. Figure 2-1 shows the Person table's structure. We want to make all the columns available to LINQ as properties of the Person class. Note that the first column is the primary key. It's also an IDENTITY column, so SQL Server automatically sets its value.

Figure 2-1. The Person table's structure

For each column we want to use with LINQ, we need to declare a property and decorate it with the Column attribute. Since we're using properties, we also declare private fields for the underlying data. For the first column, ID, we declare a field, _ID, and a property, ID.

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

    [Column(Name="ID", Storage="_ID", DbType="int NOT NULL IDENTITY",
              IsPrimaryKey=true, IsDbGenerated=true)]
    public int ID
    {
        get { return _ID; }
        set { _ID = value; }
    }
}

The Column attribute has 11 properties (see Table 2-1), all of which are optional. We've used five of them. Name specifies the column name. DBType specifies not only the column's data type (int) but also that it's not nullable and is an IDENTITY column. The IsPrimaryKey property indicates that the column is part of the table's primary key. IsDbGenerated indicates that the column's value is generated by the database (which is true for all IDENTITY columns).

By default, LINQ uses a property's set and get accessors, but we can override this with the Storage property. For example, if we add the Storage property to the Column attribute for ID as follows, LINQ will use the underlying private field, _ID, instead of the accessors.

[Column(Name="ID", Storage="_ID", DbType="int NOT NULL IDENTITY",
             Id=true, IsDbGenerated=true)]
   public int ID
   {
       get { return _ID; }
       set { _ID = value; }
   }
}

Now declare the private fields and public properties for the rest of the columns. The full class code is in Listing 2-1.

Example 2-1. The Person Class Smapped to the Person Table in the People Database

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

    [Column(Name="ID", Storage="_ID", DbType="int NOT NULL IDENTITY",
              IsPrimaryKey=true, IsDbGenerated=true)]
    public int ID
    {
        get { return _ID; }
        set { _ID = value; }
    }
}

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

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

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

2.2.3. Creating a Data Context

A data context is an object of type System.Data.Linq.DataContext. It supports database retrieval and update for objects known to LINQ. It handles the database connection and implements the SQO for database access. To use tables in LINQ, they must not only be mapped but must also be available in a data context. You can make them available in two ways.

One way is to create a data context and then use it to create an object that LINQ can use as a table. For example, the two lines

DataContext PeopleDataContext = new DataContext(connString);
Table<Person> People = PeopleDataContext.GetTable<Person>();

create a data context, PeopleDataContext, and a Table collection, People (for the Person database table), available in that context.

A new generic collection class, Table<T>, in the System.Data.Linq namespace, is used to represent database tables. We used the data context's GetTable<T> method to create a People object of type Table<Person> in the PeopleDataContext context. The argument to the DataContext constructor is the same thing you provide an ADO.NET connection. Here is an example:

String connString = @"
    Data Source=.;
    Initial Catalog=People;
    Integrated Security=True
";

The result is that our database is known to LINQ as PeopleDataContext and the Person table is known as People.

The other—and recommended—way is to use a strongly-typed data context, like the following:

public partial class PeopleDataContext : DataContext
{
   public Table<Person> People;

   public PeopleDataContext(String connString) : base(connString) {}
}

In this example we declare a class, PeopleDataContext, to represent the data context. The class has a field, People, for the database table Person.

The constructor calls the DataContext base constructor with the connection string.

To use the strongly typed context, we'd instantiate it before performing our first query, like this:

PeopleDataContext people = new PeopleDataContext(connString);

In this case our database is known to LINQ as people and the Person table is known as People.

We've now written all we need for LINQ to manage the Person database table as the People collection. Querying it will be similar to what we did in Chapter 1 to query in-memory objects.

2.2.4. Querying a Database with LINQ to SQL

The only difference in querying a database with respect to an in-memory object is that we need to instantiate our data context before our first query. In Listing 2-2, the first line of code in Main() does this.

Example 2-2. The Main Class Containing the Code to Query the Database

class Program
{
   static void Main(string[] args)
   {
      PeopleDataContext people = new PeopleDataContext();

      var query =
          from p in people.People
          from s in people.Salaries
          where p.ID == s.ID
          select new { p.LastName, p.FirstName, s.Year, s.SalaryYear };

      foreach(var row in query)
      {
          Console.WriteLine(
              "Name: {0}, {1} - Year: {2}",
              row.LastName,row.FirstName,row.Year);

Console.WriteLine("Salary: {0}", row.SalaryYear);
      }
   }
}

The DataContext is the two-way channel by which LINQ queries the database and the results are turned into objects. Figure 2-2 shows the output of the code in Listing 2-2.

Figure 2-2. The output is similar to examples from Chapter1 but this time data is retrieved from a SQL Server database.

The DataContext class transforms the LINQ query into a SQL query. The Log property of the DataContext class is an easy way to determine the SQL query sent to the database. See the code snippet in Listing 2-3:

Example 2-3. Displaying the SQL Query Sent by the Data Context

PeopleDataContext people = new PeopleDataContext();

people.Log = Console.Out;

var query =
    from p in people.People
    from s in people.Salaries
    where p.ID == s.ID
    select new { p.LastName, p.FirstName, s.Year, s.SalaryYear };

foreach(var row in query)
{
    Console.WriteLine(
        "Name: {0}, {1} - Year: {2}",
        row.LastName, row.FirstName, row.Year);

Console.WriteLine("Salary: {0}", row.SalaryYear);
}

The second line redirects the log to the console, as shown in Figure 2-3.

Figure 2-3. A SQL query sent by a data context

There is another way to see the SQL sent by LINQ. Use the GetCommand method of DataContext to see SELECT statements. The method requires the LINQ query as an argument and returns an object of DBCommand class. You can use its CommandText property to retrieve the SQL command used by LINQ to SQL to retrieve records from the database. On the other hand, the GetChangeSet method is used for INSERT, UPDATE, and DELETE statements. It returns an object of ChangeSet class that contains Inserts, Updates, and Deletes properties. They give access to a list of objects that is changed after an object associated with a database table is modified. Listing 2-4 shows these two methods in action.

Example 2-4. Using GetQueryText and GetChangeText Methods to View SQL Statements

PeopleDataContext people = new PeopleDataContext();

var query =
    from p in people.People
    from s in people.Salaries
    where p.ID == s.ID
    select new { p.LastName, p.FirstName, s.Year, s.SalaryYear };

Console.WriteLine(
    people.GetCommand(query).CommandText);
Console.WriteLine();

foreach(var row in query)
{
    Console.WriteLine(
        "Name: {0}, {1} - Year: {2}",
        row.LastName, row.FirstName, row.Year);
    Console.WriteLine("Salary: {0}", row.SalaryYear);
}

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

people.People. InsertOnSubmit (person);

Console.WriteLine();
Console.WriteLine(people.GetChangeSet().ToString());

As you can see from the output shown in Figure 2-4, the ChangeSet object contains an inserted object. Using the Inserts property you can access to the new Person object added to the People collection.

Figure 2-4. Two other ways to retrieve the SQL statements built by LINQ

The first LINQ to SQL beta releases had the GetChangeText and GetQueryText methods that returned the SQL statement used to insert, update, or delete the record (the former); and the select command (the latter). Actually they have changed their scope to Internal. You can still call it using Reflection.

2.2.5. Adding, Modifying, and Deleting Rows

As you can see in Listing 2-4, adding a new row consists of creating an object of a class that maps to a table, setting its values, and calling the InsertOnSubmit method on the appropriate Table<T> instance.

This is a classic object-oriented approach—adding an object to a collection—but this time you're adding a row to a database table! You don't write any SQL; everything is handled transparently by the data context. However, nothing happens in the database until you call the SubmitChanges method shown in Listing 2-5.

Example 2-5. The SubmitChanges Method Propagates Changes to the Database.

PeopleDataContext people = new PeopleDataContext();

   Person person = new Person();
   person.IDRole = 1;
   person.FirstName = "From";
   person.LastName = "Code";
   people.People.InsertOnSubmit(person);

   people.SubmitChanges();

   var query =
       from p in people.People
       select p;

   foreach(var row in query)
   {
       Console.WriteLine(

"Name: {0}, {1}", row.LastName, row.FirstName);
}

After the InsertOnSubmit method has modified the Person table with a new row, the SubmitChanges method will contact the database and will execute the related SQL statement. Also, the method will be able to substitute the generic @p0, @p1, and @p2 placeholders with the related value contained in the object. The output in Figure 2-5 shows that a new row has been added into the database.

Figure 2-5. A new row has been added from the code.

Let's learn to modify and delete rows. To modify a row, we first have to retrieve the row and change the values, as in Listing 2-6.

Example 2-6. Modifying a Row with LINQ to SQL

PeopleDataContext people = new PeopleDataContext();

people.Log = Console.Out;

var person = people.People.Single(p => p.ID == 5);

person.FirstName = "Name";
person.LastName = "Modified";

people.SubmitChanges();

In the code snippet, using the Single method we retrieve the unique row whose ID is equal to 5. Then we change some attributes and call SubmitChanges() to update the database table. Figure 2-6 shows the SQL generated by LINQ.

Figure 2-6. The UPDATE statement built by the LINQ to SQL

As you can see, LINQ produces an UPDATE statement containing only the columns changed in the code. The first SELECT demonstrates that the Single method searches for the Person with an ID equal to 5.

Deleting a row is an easier process but it also involves a round trip to the database to first retrieve the row. We can use the Remove method of the DataContext class, and specify the object previously retrieved from a query. Listing 2-7 shows the code.

Example 2-7. Using the Remove() Method to Delete a Row from the Database

PeopleDataContext people = new PeopleDataContext();

   people.Log = Console.Out;

   // Select the record to remove
   var person = from p in people.People
                where p.ID == 5
                select p;

people.People.DeleteOnSubmit(person.Single<Person>());

people.SubmitChanges();

The code retrieves a new Person object using the Single<T> generic method to retrieve only one record. Finally, the object is passed to the DeleteOnSubmit method and the changes are submitted with the SubmitChanges() method call.