Back in the first post of this LINQ to DataSet series, I spent some time talking about what LINQ to DataSet is, and how it can be used to supplement the existing query capabilities in terms of what kind of queries you can write. Today, I will talk about how LINQ provides more than just increased capabilities; it also helps you to write more robust code!

 

Let’s take one of the examples from the previous post.

 

var query = from dataRow in customerDataTable.AsEnumerable()

            where r.Field<string>("LastName") == "Smith"

            select r.Field<string>(“FirstName”);

 

What is that Field method in the expression shown above; and what is it doing?

 

One of the key features of LINQ is that it is type safe, so it becomes much easier to write queries that are type checked at compile time. It is much nicer to get an error when compiling than from a runtime exception at a client site!

 

Field<T> method

However, the DataSet is not typed by default. When you retrieve a value from a DataTable, the value is returned as an object. The Field<T> method returns the value of the column, returning it as the generic type parameter, thus enabling type checking.

 

That is not all the Field<T> method does! When the DataSet was first created, there was no concept of nullable value types in the CLR, so a new value type was defined: DBNull. This was used to represent null values for DataColumns that contain a value type, because you could not have have a null value type. The world has moved on, and we now have nullable types, and so it is now much more natural to write a query using null, as opposed to having to check for DBNull everywhere. The other feature offered by the Field<T> method is that it will convert a value type that has a value of DBNull.Value to a nullable type with a value of null.

 

var query = orderDataTable.AsEnumerable()

    .Where(dr => dr.Field<datetime>("OrderDate") == null)

    .Select(dr => dr.Field<int>("OrderID"));

 

As a nice bonus, you can use the Field<T> method in your non-LINQ code as well. If you do not have the option to use a typed DataSet, this a great way to reduce your typing, both in terms of errors and on the keyboard!

 

Typed DataSet

The Typed DataSet is another story. With this little gem, you already have fully typed access to your data, so you do not need to jump through hoops in order to use it in a LINQ to DataSet query.

 

EmployeesTable employees = new EmployeesTable();

var query = employees

    .Select(emp => new {

                       EmployeeID = emp.ID,

                       EmployeeName = emp.Name,

                       Employee = emp}

                       )

                       .OrderBy(e => e.EmployeeName);

 

As you can see, the lack of all the generic method calls certainly makes for more readable code! However, one thing to keep in mind is that the typed DataSet does not have the same logic for handling nulls as the Field<T> method. If you attempt to access a field that has a value of DBNull, you will get an exception from the property getter, which does not work very well with LINQ. There are ways to work around this problem, which I will explore later.

 

Coming soon…

 

In future LINQ to DataSet posts, I will talk more about how to handle nulls, and talk about some cool features of VB.NET that make the whole process easier.

 

Erick Thompson

Program Manager, ADO.NET

 

LINQ to DataSet Part 1

 

LINQ to DataSet Part 3