Type safety - LINQ to DataSets Part 2

 

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

Comments

• Anonymous
  February 06, 2007
  Why even bother with datasets?  I was hoping that MS was going to wise up and mark this functionality as Deprecated or Obsolete in the next version of ADO.NET.  (Isn't the Entity Framework supposed to replace DataSets?) Every scenario I have examined that uses datasets has had major issues and generally horrid software design.  They are the most embarassing and backwards part of .NET.

• Anonymous
  February 06, 2007
  I'm really looking forward to your VB.NET sample.

• Anonymous
  February 09, 2007
  The comment has been removed

• Anonymous
  February 09, 2007
  Here are a few good links to Orcas material (some old and some new). There is a lot of good and bad material

• Anonymous
  February 11, 2007
  Winter has finally set in with single digit temps and minus degrees wind chills but still no snow. WPF/Avalon

• Anonymous
  February 15, 2007
  Greetings fellow data junkies! My name is Erick Thompson, a PM at Microsoft who is working on driving

• Anonymous
  July 24, 2007
  what is the vb.net equvelent for that code ?

• Anonymous
  August 12, 2007
  Will i be able to work with LINQ in my visual c# express edition 2005 version? When i tried to include using System.Linq name space and tried to run the application it showed an error Error 1 The type or namespace name 'Linq' does not exist in the namespace 'System' (are you missing an assembly reference?) Can you please help me out??

• Anonymous
  September 21, 2007
  The comment has been removed

• Anonymous
  September 21, 2007
  I figured it out...  A reference to System.Core is needed in the progect...

• Anonymous
  January 14, 2008
  Thanks Nick. I got to solve the problem through your post.

• Anonymous
  July 20, 2008
  Compare two large data tables A and B  using linq and get the records which exist  in dataTable A but does not exist in B

• Anonymous
  October 25, 2008
  The comment has been removed

• Anonymous
  May 30, 2011
  Row.Value_Condition != DBNull.Value.ToString() this work properly

• Anonymous
  February 17, 2012
  helpful, but examples are not clearly defined .

• Anonymous
  February 17, 2012
  what is generic type parameter  ?

• Anonymous
  February 17, 2012
  what do you mean by typed dataset?

• Anonymous
  November 08, 2013
  There seems to be a typo in the example code.       .Where(dr => dr.Field<datetime>("OrderDate") == null) should use "datetime?" instead.  As written, it will return a datetime, which can never be null.