Nulls - LINQ to DataSets Part 3
In previous posts I have spent time talking about LINQ to DataSet, and how it can
help you write better code, and how you can write some very interesting queries with DataSet. Today I am going to talk more about nulls, which is one of the areas in which there is a lot of confusion. The key thing to remember is that nulls work very differently in LINQ than they do in SQL!
Let us take another look at one of the example queries from a previous post. This query looks for orders with no OrderDate, perhaps because the order has not been finalized yet.
var query = orderDataTable.AsEnumerable()
.Where(dr => dr.Field<datetime>("OrderDate") == null)
.Select(dr => dr.Field<int>("OrderID"));
In this particular query, the Field<T> method returns a nullable type, which is great, as you no longer have to deal with DBNull. You can now use null equality like you do everywhere else in your code. This is one of the ways that LINQ really helps bridge the impedance mismatch between data and code.
What if you wanted to find all the orders placed in 2006? The following query (as a query expression) might be a good start.
var query = from dataRow in orderDataTable.AsEnumerable()
where dataRow.Field<DateTime>("OrderDate").Year == 2006
select dataRow.Field<int>("OrderID");
If you run this code, and any of your orders do not have an OrderDate, guess what happens? You get an ever so pleasant NullReferenceException. This is the problem with nulls. When the Field<T> method returns a null, the CLR attempts to call a method, and we all know you cannot call a method on a null object!
IsNull
What we have to do is make sure that we do not ever call a method on a null object. How should we do this? This is yet another place where the expressiveness of LINQ is really showcased. Because the where predicate is simply an expression, we can modify the expression so that we do not call the Year getter method if the value is null.
There are many solutions to this problem. We could wrap the Method<T> and null check inside of an if-then-else block, but that certainly is not very readable! Fortunately for us, we have the IsNull method that will check for a null value. In fact, the first query can use this method.
var query = orderDataTable.AsEnumerable()
.Where(dr => dr.IsNull("OrderDate") == true)
.Select(dr => dr.Field<int>("OrderID"));
Is that not a lot more readable? However, that is only half of the problem. We still need to have the logic that will execute the Year getter method only when OrderDate is not null.
var query = from dataRow in orderDataTable.AsEnumerable()
where
dataRow.IsNull("OrderDate") == false
&&
dataRow.Field<DateTime>("OrderDate").Year == 2006
select dataRow.Field<int>("OrderID");
Now we are cooking! This query will run without exceptions and produce the correct results. We are looking golden.
Visual Basic
In Visual Basic, the AND operator does not short circuit, so in the above example the Year property getter method will still be called. There is another operator however, the AndAlso operator, which behaves like the C# && operator, and this could be used to avoid the NullReferenceException. The query using this approach would look like the following.
Dim query = From dataRow In orderDataTable _
Where _
dataRow.IsNull("OrderDate") = false _
AndAlso
dataRow.Field(Of DateTime)("OrderDate").Year = 2006 _
Select dataRow.Field(Of int)("OrderID")
All that typing certainly is a drag. If you are using VB.NET, you have a powerful new version of an existing operator that you can use to make it much easier! This function is the ternary operator IIF.
IIF
IIF takes three arguments, if the first argument is true, then the second argument is returned, otherwise the third argument is returned. The cool part is that all three arguments can be methods! With this, the above code is converted into the much nicer version below.
Dim query = From dataRow In orderDataTable _
Where IIF(dataRow.IsNull("OrderDate") = false, dataRow.Field(Of DateTime)("OrderDate").Year = 2006, false) _
Select dataRow.Field<of int>("OrderID")
The possibilities for this new operator are endless both in LINQ and in your own code!
Coming soon…
Whatever you want! Leave a comment, send an email! Let me know what questions you have, concerns you’d like to share, or anything else.
Erick Thompson
Program Manager, ADO.NET