2.6. LINQ to DataSet

In the previous section, you saw how LINQ to SQL supports ADO.NET transactions. This is not the only integration between the "old" ADO.NET library and the "new" LINQ to SQL. In fact, LINQ to SQL can use ADO.NET DataSets with LINQ to DataSet.

With some limitations, LINQ to DataSet allows developers to use DataSets as normal data sources using the usual LINQ query syntax.

Listing 2-27 shows a simple example that uses a LINQ query to fill a typed dataset.

Example 2-27. Filling a Typed DataTable with the CopyToDataTable Method

dsPeople ds = new dsPeople();
dsPeople.RoleRow row = ds.Role.NewRoleRow();
row.ID = 1;
row.RoleDescription = "Manager";
ds.Role.AddRoleRow(row);

row = ds.Role.NewRoleRow();
row.ID = 2;
row.RoleDescription = "Developer";
ds.Role.AddRoleRow(row);

var q = from role in ds.Role
        select role;

dsPeople.RoleDataTable t = new dsPeople.RoleDataTable();
q.CopyToDataTable(t, LoadOption.OverwriteChanges);

dsPeople is a typed DataSet added to the Visual Studio project. When you use Visual Studio to create your DataSet objects you can use the DataSet Designer tool, which makes it possible to drag and drop tables from Server Explorer (the same way as when using Linq to SQL Classes Designer). In the dsPeople data set I added the Role table. This operation has automatically created a typed DataSet that contains the Role table together with some other methods and objects.

The prefilled DataSet object is used in the LINQ query to retrieve all its records. Then the CopyToDataTable method, provided as an extension of the IEnumerable<T> interface, is used to fill a typed DataTable object. This last object will be used in the next code snippet to perform another LINQ query to filter the records.

The CopyToDataTable method has been added to load a DataTable from a Linq to DataSet query. It provides two versions: the former returns a new DataTable and doesn't accept parameters while the latter fills an existing DataTable provided as parameter, plus a second parameter indicating the LoadOption options (that is, overwrite records).

In Listing 2-28 we use the filled dsPeople DataSet just like any data source, and a LINQ query to retrieve a role.

Example 2-28. A Typed DataSet Is Queryable Just Like Any Other Data Source.

var query = from r in t
             where r.ID == 1
             select r;

foreach(var row in query)
{
    Console.WriteLine("Role: {0} {1}", row.ID, row.RoleDescription);
}

The Role property contained in the ds DataSet is iterated by using the Rows collection to look for the row whose identifier is equal to 1.

For our LINQ to DataSet examples you have to leave uncommented the #region regions, both for the code filling the DataSet and for the code querying it.

LINQ to DataSet adds support for untyped DataSets as well. In this case the code is a bit more complex to write because LINQ has to acquire more information from the query. Listing 2-29 shows how an untyped data set can be filled using a LINQ query.

Example 2-29. Filling an Untyped Data Table Using a LINQ Query and the CopyToDataTable Method

dsPeople ds = new dsPeople();
dsPeople.RoleRow row = ds.Role.NewRoleRow();
row.ID = 1;
row.RoleDescription = "Manager";
ds.Role.AddRoleRow(row);

row = ds.Role.NewRoleRow();
row.ID = 2;
row.RoleDescription = "Developer";
ds.Role.AddRoleRow(row);

var q = from role in ds.Role
        select role;

DataTable dtRole = q.CopyToDataTable();

The CopyToDataTable extended method iterates through the results of the query, creating a new DataTable object filled with DataColumn objects and values.

Querying an untyped data set is a bit more complex because we have to use the Field<T> class to specify the column's data type; see Listing 2-30.

Example 2-30. The Code Using an Untyped Data Set Is More Complex and Less Readable.

var query = from p in dtRole.AsEnumerable()
            where p.Field<string>("RoleDescription") == "Manager"
            select p;

foreach (var record in query)
{
    Console.WriteLine("Role: {0} {1}",
      record.Field<int>("ID"),
      record.Field<string>("RoleDescription"));
}

First of all, the DataTable class doesn't provide an implementation of the IEnumerable and IQueryable interfaces, so the dtPerson object cannot be used in the LINQ query directly. We have to use the AsEnumerable extended method, which generates an IEnumerable<T> composed of DataTable rows.

Second, we need to use the Field<T> generic method to specify the data type of the DataTable column we are going to manage. This is necessary because when using the classic syntax to access a DataTable row (e.g. p["LastName"]) we should cast the return type and also use the IsNull method of the DataRow class to check if the data is null. The Field<T> generic method does all this automatically, plus checks null values when the column accepts nulls (e.g., using Field<string?>).

As stated previously, LINQ to DataSet has some limitations that should be removed eventually. For example, the CopyToDataTable method doesn't understand relationships and cannot produce multiple data tables. Moreover, there is no way to use LINQ to DataSet to update database rows after they are retrieved by a LINQ query into a data set object.