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
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 removedAnonymous
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 materialAnonymous
February 11, 2007
Winter has finally set in with single digit temps and minus degrees wind chills but still no snow. WPF/AvalonAnonymous
February 15, 2007
Greetings fellow data junkies! My name is Erick Thompson, a PM at Microsoft who is working on drivingAnonymous
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 removedAnonymous
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 BAnonymous
October 25, 2008
The comment has been removedAnonymous
May 30, 2011
Row.Value_Condition != DBNull.Value.ToString() this work properlyAnonymous
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.