DataRow Field Operators

In addition to the DataRow-specific comparer class for the set-type operators, there is a need for some DataRow-specific operators. These operators are defined in the System.Data.DataSetExtensions.dll assembly, in the static System.Data.DataRowExtensions class.

You have no doubt noticed that in virtually every example thus far, I have used the Field<T> operator to extract a DataColumn object's value from a DataRow. There are two purposes for this operator: correct equality comparisons and null value handling.

With DataRow objects, we have a problem. Their DataColumn values do not get compared properly for equality when they are accessed with the DataRow object's indexer if the column is a value-type. The reason is that because the column's data type could be any type, the indexer returns an object of type System.Object. This allows the indexer to return an integer, a string, or whatever data type is necessary for that column. This means that if a column is of type int, it is a value-type, and it must get packaged into an object of type Object. This packaging is known in the Microsoft .NET Framework as boxing. Pulling the value-type back out of the object is known as unboxing. This boxing is where the problem lies.

Let's take a look at some sample code. First, let's take the example of comparing an integer literal to another integer literal of the same value, as shown in Listing 10-7.

Example. Comparing 3 to 3

Console.WriteLine("(3 == 3) is {0}.", (3 == 3));

The following is the result of this code:

(3 == 3) is True.

There is absolutely no surprise there. But what happens when an integer gets boxed? Let's examine the code in listing 10-8 and look at the results.

Example. Comparing 3 Cast to an Object to Another 3 Cast to an Object

Console.WriteLine("((Object)3 == (Object)3) is {0}.", ((Object)3 == (Object)3));

And the following are the results:

((Object)3 == (Object)3) is False.

Uh oh, what happened? What happened is that by casting the literal integer 3 to an object of type Object, two objects were created, and the references (addresses) of each object were compared, and those are not equal. When you access DataColumn objects using the DataRow object's indexer, if any of the columns are a value-type, the column values will get boxed and will not compare for equality properly.

To demonstrate this, I'll create a more complex example that actually uses DataColumn objects. In the example, I have two arrays, each of a different class type. One is the same basic array of students I have been using. The other is an array of class designations with foreign keys into the students array. Here is the StudentClass class.

Example. A Simple Class with Two Public Properties

class StudentClass
{
    public int Id;
    public string Class;
}

Now that I have a different class type, I am going to need another method to convert this array to an object of type DataTable. Here is that method:

static DataTable GetDataTable2(StudentClass[] studentClasses)
{
  DataTable table = new DataTable();

  table.Columns.Add("Id", typeof(Int32));
  table.Columns.Add("Class", typeof(string));

  foreach (StudentClass studentClass in studentClasses)
  {
    table.Rows.Add(studentClass.Id, studentClass.Class);
  }

  return (table);
}

This method is nothing more than a copy of the existing common GetTableData method that has been modified to work with arrays of StudentClass objects. Obviously, if you were going to be working from arrays in real production code, you would want something more abstract than creating a method for each class type for which you need a DataTable object. Perhaps a generic extension method would be a nice approach. But as I mentioned at the beginning of the examples, you will typically be performing LINQ to DataSet queries on data from databases, not arrays, so I won't worry about that here.

For the example, I'll build a sequence of DataRow objects from each array and try to join them using their common Id column, which I will retrieve by indexing into the DataRow with the column name, which is Id. Listing 10-9 shows the code.

Example. Joining Two Value-Type Columns by Indexing into the DataRow

Student[] students = {
  new Student { Id = 1, Name = "Joe Rattz" },
  new Student { Id = 7, Name = "Anthony Adams" },
  new Student { Id = 13, Name = "Stacy Sinclair" },
  new Student { Id = 72, Name = "Dignan Stephens" }
};

StudentClass[] classDesignations = {
  new StudentClass { Id = 1, Class = "Sophmore" },
  new StudentClass { Id = 7, Class = "Freshman" },
  new StudentClass { Id = 13, Class = "Graduate" },
  new StudentClass { Id = 72, Class = "Senior" }
};

DataTable dt1 = GetDataTable(students);
IEnumerable<DataRow> seq1 = dt1.AsEnumerable();
DataTable dt2 = GetDataTable2(classDesignations);
IEnumerable<DataRow> seq2 = dt2.AsEnumerable();

string anthonysClass = (from s in seq1
                        where s.Field<string>("Name") == "Anthony Adams"
                        from c in seq2
                        where c["Id"] == s["Id"]
                        select (string)c["Class"]).
                       SingleOrDefault<string>();

Console.WriteLine("Anthony's Class is: {0}",
  anthonysClass != null ? anthonysClass : "null");

There are a couple of things worth pointing out about that query. First notice the line that is bold. There, I am indexing into the DataRow object to get the columns' values. Since the column value data types are strings, they will get boxed, which means there will be a problem determining equality. Additionally, you can see that I am using the Field<T> operator in this example when I compare the Name field to the name "Anthony Adams". Ignore this for now. Just realize that I am calling the Field<T> operator to prevent a boxing problem with the Name field that I am in the midst of demonstrating with the Id field. Also, notice that this query is combining the query expression syntax with the standard dot notation syntax. As you can see, I am performing a join on the two DataTable objects too. Let's run the code and see the results:

Anthony's Class is: null

The string anthonysClass is null. That is because the join failed to find a record in seq2 that had an equal value for the Id field. This is because of the boxing of the Id field when it is retrieved using the DataRow indexer. Now, you could handle the unboxing yourself by changing the line

where c["Id"] == s["Id"]

to

where (int)c["Id"] == (int)s["Id"]

Listing 10-10 is the entire example with that line replaced.

Example. Using Casting to Make the Test for Equality Correct

Student[] students = {
  new Student { Id = 1, Name = "Joe Rattz" },
  new Student { Id = 7, Name = "Anthony Adams" },
  new Student { Id = 13, Name = "Stacy Sinclair" },
  new Student { Id = 72, Name = "Dignan Stephens" }
};

StudentClass[] classDesignations = {
  new StudentClass { Id = 1, Class = "Sophmore" },
  new StudentClass { Id = 7, Class = "Freshman" },
  new StudentClass { Id = 13, Class = "Graduate" },
  new StudentClass { Id = 72, Class = "Senior" }
};

DataTable dt1 = GetDataTable(students);
IEnumerable<DataRow> seq1 = dt1.AsEnumerable();
DataTable dt2 = GetDataTable2(classDesignations);
IEnumerable<DataRow> seq2 = dt2.AsEnumerable();

string anthonysClass = (from s in seq1
                        where s.Field<string>("Name") == "Anthony Adams"
                        from c in seq2
                        where (int)c["Id"] == (int)s["Id"]
                        select (string)c["Class"]).
                       SingleOrDefault<string>();

Console.WriteLine("Anthony's Class is: {0}",
  anthonysClass != null ? anthonysClass : "null");

If you run that code, you will get this result:

Anthony's Class is: Freshman

So that solves the boxing problem. However, there is still one other problem. When you attempt to retrieve a column's value using the DataRow object's indexer, remember, the column's value gets returned as an object of type Object. So to compare it to any value or assign it to a variable will require casting it to another data type as I did previously by casting it to an int. Since DataSet objects use DBNull.Value as the value for a column that is null, if that column's value is DBNull.Value, casting it to another data type will throw an exception.

Fortunately, LINQ to DataSet has made both of these problems—boxed value comparisons and null handling—disappear, thanks to the Field<T> and SetField<T> operators. Listing 10-11 shows the previous example using the Field<T> operator.

Example. Using the Field Operator

Student[] students = {
  new Student { Id = 1, Name = "Joe Rattz" },
  new Student { Id = 7, Name = "Anthony Adams" },
  new Student { Id = 13, Name = "Stacy Sinclair" },
  new Student { Id = 72, Name = "Dignan Stephens" }
};

StudentClass[] classDesignations = {
  new StudentClass { Id = 1, Class = "Sophmore" },
  new StudentClass { Id = 7, Class = "Freshman" },
  new StudentClass { Id = 13, Class = "Graduate" },
  new StudentClass { Id = 72, Class = "Senior" }
};

DataTable dt1 = GetDataTable(students);
IEnumerable<DataRow> seq1 = dt1.AsEnumerable();
DataTable dt2 = GetDataTable2(classDesignations);
IEnumerable<DataRow> seq2 = dt2.AsEnumerable();

string anthonysClass = (from s in seq1
                        where s.Field<string>("Name") == "Anthony Adams"
                        from c in seq2
                        where c.Field<int>("Id") == s.Field<int>("Id")
                        select (string)c["Class"]).
                       SingleOrDefault<string>();

Console.WriteLine("Anthony's Class is: {0}",
  anthonysClass != null ? anthonysClass : "null");

This code is the same as the previous example except I call the Field<T> operator instead of casting the field as an int. Here are the results:

Anthony's Class is: Freshman

Field<T>

As I just demonstrated in Listing 10-11, the Field<T> operator allows you to obtain the value of a column from a DataRow object and handles the casting of DBNull.Value and boxed value comparison problems I previously discussed.

Prototypes

The Field operator has six prototypes I cover.

The first prototype returns the column's value for the DataColumn and version specified.

Example. The First Field Prototype

public static T Field (
  this DataRow first,
  System.Data.DataColumn column,
  System.Data.DataRowVersion version);

The second prototype returns the column's value for the column with the name and version specified.

Example. The Second Field Prototype

public static T Field (
  this DataRow first,
  string columnName,
  System.Data.DataRowVersion version);

The third prototype returns the column's value for the column with the ordinal and version specified.

Example. The Third Field Prototype

public static T Field (
  this DataRow first,
  int ordinal,
  System.Data.DataRowVersion version);

The fourth prototype returns the column's current value only for the DataColumn specified.

Example. The Fourth Field Prototype

public static T Field (
  this DataRow first,
  System.Data.DataColumn column);

The fifth prototype returns the column's current value only for the column with the specified name.

Example. The Fifth Field Prototype

public static T Field (
  this DataRow first,
  string columnName);

The sixth prototype returns the column's current value only for the column with the specified ordinal.

Example. The Sixth Field Prototype

public static T Field (
  this DataRow first,
  int ordinal);

As you may have noticed, the first three prototypes allow you to specify which DataRowVersion of the DataColumn object's value you want to retrieve.

Examples

At this point, you have seen the Field<T> operator called many times, and in different ways. But just so you can see each prototype in action, Listing 10-12 shows a trivial example of each.

Example. An Example of Each Field Operator Prototype

Student[] students = {
  new Student { Id = 1, Name = "Joe Rattz" },
  new Student { Id = 7, Name = "Anthony Adams" },
  new Student { Id = 13, Name = "Stacy Sinclair" },
  new Student { Id = 72, Name = "Dignan Stephens" }
};

DataTable dt1 = GetDataTable(students);
IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

int id;

//  Using prototype 1.
id = (from s in seq1
      where s.Field<string>("Name") == "Anthony Adams"
      select s.Field<int>(dt1.Columns[0], DataRowVersion.Current)).
     Single<int>();
Console.WriteLine("Anthony's Id retrieved with prototype 1 is: {0}", id);

//  Using prototype 2.
id = (from s in seq1
      where s.Field<string>("Name") == "Anthony Adams"
      select s.Field<int>("Id", DataRowVersion.Current)).
     Single<int>();
Console.WriteLine("Anthony's Id retrieved with prototype 2 is: {0}", id);

//  Using prototype 3.
id = (from s in seq1
      where s.Field<string>("Name") == "Anthony Adams"
      select s.Field<int>(0, DataRowVersion.Current)).
     Single<int>();
Console.WriteLine("Anthony's Id retrieved with prototype 3 is: {0}", id);

//  Using prototype 4.
id = (from s in seq1
      where s.Field<string>("Name") == "Anthony Adams"
      select s.Field<int>(dt1.Columns[0])).
     Single<int>();
Console.WriteLine("Anthony's Id retrieved with prototype 4 is: {0}", id);

//  Using prototype 5.
id = (from s in seq1
      where s.Field<string>("Name") == "Anthony Adams"
      select s.Field<int>("Id")).
     Single<int>();
Console.WriteLine("Anthony's Id retrieved with prototype 5 is: {0}", id);

//  Using prototype 6.
id = (from s in seq1
      where s.Field<string>("Name") == "Anthony Adams"
      select s.Field<int>(0)).
     Single<int>();
Console.WriteLine("Anthony's Id retrieved with prototype 6 is: {0}", id);

Nothing here is very significant. I declare the array of students and create a DataTable object from it just like in most examples. I then obtain a sequence of DataRow objects too. I then, one by one, work my way through each Field<T> operator prototype using it to obtain the field named Id. Notice that in each query of the Id field, I am also using the Field<T> operator in the Where operator portion of the query. Here are the results:

Anthony's Id retrieved with prototype 1 is: 7
Anthony's Id retrieved with prototype 2 is: 7
Anthony's Id retrieved with prototype 3 is: 7
Anthony's Id retrieved with prototype 4 is: 7
Anthony's Id retrieved with prototype 5 is: 7
Anthony's Id retrieved with prototype 6 is: 7

Before moving on to the SetField<T> operator, I want to provide an example demonstrating one of the prototypes that allows you to specify the DataRowVersion of the DataColumn object's value to retrieve. To provide an example, I will have to modify one of the DataColumn object's values using the SetField<T> operator. Although I haven't discussed the SetField<T> operator yet, just ignore it for now. I will be covering it in the next section.

Also, since this chapter is meant to explain the LINQ to DataSet operators and is not meant to be a detailed discussion of how the DataSet class works, I will only briefly cover a couple of additional DataSet methods I am calling in the example. Listing 10-13 is the code.

Example. The Field Operator Prototype with a Specified DataRowVersion

Student[] students = {
  new Student { Id = 1, Name = "Joe Rattz" },
  new Student { Id = 7, Name = "Anthony Adams" },
  new Student { Id = 13, Name = "Stacy Sinclair" },
  new Student { Id = 72, Name = "Dignan Stephens" }
};

DataTable dt1 = GetDataTable(students);
IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

DataRow row = (from s in seq1
               where s.Field<string>("Name") == "Anthony Adams"
               select s).Single<DataRow>();
row.AcceptChanges();
row.SetField("Name", "George Oscar Bluth");

Console.WriteLine("Original value = {0} : Current value = {1}",
  row.Field<string>("Name", DataRowVersion.Original),
  row.Field<string>("Name", DataRowVersion.Current));
row.AcceptChanges();
Console.WriteLine("Original value = {0} : Current value = {1}",
  row.Field<string>("Name", DataRowVersion.Original),
  row.Field<string>("Name", DataRowVersion.Current));

In this example, I obtain a sequence from the array of students as I typically do. I then query for a single DataRow object on which I can make some changes. The first code of interest is the AcceptChanges method that I call after obtaining the DataRow object. I call this method to make the DataRow object accept the current value for each DataColumn object within it as the original version. Without that, there would be no original version of the DataColumn objects' values, and merely attempting to access the field's original version causes an exception to be thrown. In this way, the DataRow object is ready to begin tracking DataColumn object value changes. I need this to be able to obtain different DataRowVersion versions of the DataRow object's DataColumn values.

Once I call the AcceptChanges method the first time, I set a field using the SetField operator. I then display the original version and current version of the Name DataColumn value to the console. At this point, the original version should be "Anthony Adams", and the current version should be "George Oscar Bluth". This allows you to see the different versions you can obtain from a DataRow object.

Then, just to make it interesting, I call the AcceptChanges method a second time and again display the original and current version of the DataColumn object's value. This time, the original and current version values should both be "George Oscar Bluth", because I have told the DataRow object to accept the changes as the current version. Let's examine the results:

Original value = Anthony Adams : Current value = George Oscar Bluth
Original value = George Oscar Bluth : Current value = George Oscar Bluth

That works like a charm. Remember, though, without calling the AcceptChanges method the first time, I could have changed the value of the DataColumn object all day long and there would not have been an original version.

You may recall that I mentioned that one of the additional benefits of using the Field<T> operator is that it also nicely handles the situation when fields are null. Let's take a look at the example in Listing 10-14 where a student's name has a null value, but I am not using the Field<T> operator:

Example. An Example Without the Field Operator When There Is a null Present

Student[] students = {
  new Student { Id = 1, Name = "Joe Rattz" },
  new Student { Id = 7, Name = null },
  new Student { Id = 13, Name = "Stacy Sinclair" },
  new Student { Id = 72, Name = "Dignan Stephens" }
};

DataTable dt1 = GetDataTable(students);
IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

string name = seq1.Where(student => student.Field<int>("Id") == 7)
  .Select(student => (string)student["Name"])
  .Single();
Console.WriteLine("Student's name is '{0}'", name);

That is a fairly simple example. Notice that I set the Name member of the Student record of the student whose Id is 7 to null. Also notice that instead of using the Field<T> operator, I just index into the DataRow and cast the value to a string. Let's take a look at the results:

Unhandled Exception: System.InvalidCastException: Unable to cast object of type
'System.DBNull' to type 'System.String'.
...

So what happened? What happened is that the DataColumn object's value is DBNull, and you can't cast that to a string. There are some rather verbose solutions I could take to alleviate this complication, but this is what the Field<T> operator is designed to simplify for you. Let's take a look at the same example, except this time I use the Field<T> operator to obtain the DataColumn object's value. Listing 10-15 is the code.

Example. An Example with the Field Operator When There Is a null Present

Student[] students = {
  new Student { Id = 1, Name = "Joe Rattz" },
  new Student { Id = 7, Name = null },
  new Student { Id = 13, Name = "Stacy Sinclair" },
  new Student { Id = 72, Name = "Dignan Stephens" }
};

DataTable dt1 = GetDataTable(students);
IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

string name = seq1.Where(student => student.Field<int>("Id") == 7)
  .Select(student => student.Field<string>("Name"))
  .Single();

Console.WriteLine("Student's name is '{0}'", name);

OK, this is the same code except I use the Field<T> operator instead of casting it to a string. Let's look at the results:

Student's name is ''

This is much easier to deal with.

SetField<T>

Just as with the retrieval of DataColumn objects, null adversely affects the setting of DataColumn objects. To assist with this issue, the SetField<T> operator was created. It handles the case where a DataColumn object's value is set with a nullable data type whose value is null.

Prototypes

The SetField<T> operator has three prototypes I cover.

The first prototype allows you to set a column's current value for the DataColumn specified.

Example. The First SetField Prototype

public static void SetField (
  this DataRow first,
  System.Data.DataColumn column,
  T value);

The second prototype allows you to set a column's current value for the column with the specified name.

Example. The Second SetField Prototype

public static void SetField (
  this DataRow first,
  string columnName,
  T value);

The third prototype allows you to set a column's current value for the column with the specified ordinal.

Example. The Third SetField Prototype

public static void SetField (
  this DataRow first,
  int ordinal,
  T value);

Examples

As an example of the SetField<T> operator, shown in Listing 10-16, first I display the sequence of DataRow objects that contain the students. Next, I query one of the students by name from the sequence of DataRow objects and change that name using the SetField<T> operator. I then display the sequence of DataRow objects after the change has been made. Rinse and repeat for each prototype.

Example. An Example of Each SetField Operator Prototype

Student[] students = {
  new Student { Id = 1, Name = "Joe Rattz" },
  new Student { Id = 7, Name = "Anthony Adams" },
  new Student { Id = 13, Name = "Stacy Sinclair" },
  new Student { Id = 72, Name = "Dignan Stephens" }
};

DataTable dt1 = GetDataTable(students);
IEnumerable<DataRow> seq1 = dt1.AsEnumerable();

Console.WriteLine("{0}Results before calling any prototype:",
  System.Environment.NewLine);

foreach (DataRow dataRow in seq1)
{
  Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),
    dataRow.Field<string>("Name"));
}

//  Using prototype 1.
(from s in seq1
 where s.Field<string>("Name") == "Anthony Adams"
 select s).Single<DataRow>().SetField(dt1.Columns[1], "George Oscar Bluth");

Console.WriteLine("{0}Results after calling prototype 1:",
  System.Environment.NewLine);

foreach (DataRow dataRow in seq1)
{
  Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),
    dataRow.Field<string>("Name"));
}

//  Using prototype 2.
(from s in seq1
 where s.Field<string>("Name") == "George Oscar Bluth"
 select s).Single<DataRow>().SetField("Name", "Michael Bluth");

Console.WriteLine("{0}Results after calling prototype 2:",
    System.Environment.NewLine);

foreach (DataRow dataRow in seq1)
{
  Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),
    dataRow.Field<string>("Name"));
}

//  Using prototype 3.
(from s in seq1
 where s.Field<string>("Name") == "Michael Bluth"
 select s).Single<DataRow>().SetField("Name", "Tony Wonder");

Console.WriteLine("{0}Results after calling prototype 3:",
  System.Environment.NewLine);

foreach (DataRow dataRow in seq1)
{
  Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),
    dataRow.Field<string>("Name"));
}

This code is not quite as bad as it looks. After I obtain the sequence of students and display them, there is a block of code that gets repeated three times, once for each prototype. Each block contains a LINQ query that retrieves the field and updates its value, displays a header line to the console, and then displays each row in the sequence to the console to show the change just made to the field.

There are a couple noteworthy things in this example. In each LINQ query where I query the DataRow on its Name field, again, I am mixing query expression syntax and standard dot notation syntax in the query. Also, I am using the Field<T> operator to find the record that I am going to set with the SetField<T> operator. After obtaining the sequence of DataRow objects of students, I work my way through the SetField<T> operator prototypes one by one. Throughout the example, I query the previously changed element by its value, and change it again. For example, for the first prototype, I just query the element whose Name field is "Anthony Adams" and set it to "George Oscar Bluth". For the second prototype, I query the element whose Name field is "George Oscar Bluth" and change it to something else, which I will query for on the next prototype. Of course, after each element value update, I display the sequence to the console so you can verify that the element's value did indeed change.

One of the things that I think is neat about this example is that I query the element and update its value in a single statement. This is so powerful one might think it is an illusion, but rest assured, there is no magician present here.

Here are the results:

Results before calling any prototype:
Student Id = 1 is Joe Rattz
Student Id = 7 is Anthony Adams
Student Id = 13 is Stacy Sinclair
Student Id = 72 is Dignan Stephens

Results after calling prototype 1:
Student Id = 1 is Joe Rattz
Student Id = 7 is George Oscar Bluth
Student Id = 13 is Stacy Sinclair
Student Id = 72 is Dignan Stephens

Results after calling prototype 2:
Student Id = 1 is Joe Rattz
Student Id = 7 is Michael Bluth
Student Id = 13 is Stacy Sinclair
Student Id = 72 is Dignan Stephens

Results after calling prototype 3:
Student Id = 1 is Joe Rattz
Student Id = 7 is Tony Wonder
Student Id = 13 is Stacy Sinclair
Student Id = 72 is Dignan Stephens

As you can see, the Name field of the appropriate element is updated each time.