LINQ is one of the great new technologies in .NET. As opposed to the frustrations of raw text in XML, I absolutely love how it is strongly typed. I am not going to spend any time discussing its use and features, as there is enough of that on the internet. Instead I will discuss some unexpected behaviours that I recently uncovered.
Without wasting the reader’s time, let me jump straight to the problem. Here is a sample of the filter I would like to apply:
var docs = from doc in db.GetTable()
where doc.Name == interfaceName
&& (filter.Failed.HasValue && doc.Failed.HasValue &&
filter.Failed.Value == doc.Failed.Value)
orderby doc.Name
select doc;
Background: “Failed” is a Nullable bit value both in the code and in the database. I only want to compare and filter if a values has been provided.
This seems like a perfectly acceptable piece of code. The expected C# behaviour for “&&” is that when the first section fails, the following section will not even be evaluated, and therefore it must surely work.
So I thought.
On executing the statement I got the exception “Nullable object must have a value”, originating from line 4 above. I remembered that LINQ translates its logical items to a form of expression tree, so it would seem that all values are always evaluated, regardless of the expected C# behaviour. We need to get rid of “Failed.HasValue”.
No problem, update the filter:
var docs = from doc in db.GetTable()
where doc.Name == interfaceName
&& (filter.Failed.HasValue && doc.Failed.HasValue &&
filter.Failed == doc.Failed)
orderby doc.Name
select doc;
No exception, the query runs through. Only one problem, my result set is empty.
So I evaluate my code again. The first problem is that the query section “filter.Failed == doc.Failed” on line 4 is always evaluated, regardless of whether the first two statements are false.
If that is not enough there is a second problem, regarding what happens when filtering. Once again, take a look at line 4. As it is always evaluated, it would seem that when the filter condition is “null” it evaluates to “null == doc.Failed”, where “doc.Failed” can be true, false, or DBNull.Value. This is clearly not right.
Where to from here? Writing C# functions inside the match is not the answer, as you need to implement translations supported by SQL for the specific section.
The quick and dirty answer is as follows:
// Why not use var? Because the return type keeps changing.
IQueryable docs = from doc in db.GetTable()
where doc.Name == interfaceName
select doc;
if (filter.Failed.HasValue)
docs = from doc in docs
where filter.Failed == doc.Failed
select doc;
// more filter blocks here
// Finally, remember to sort.
docs = from doc in docs
orderby doc.Name
select doc;
This works fine, but as one might have more than one item to filter on this process might result in a lot of code that needs to be duplicated all over the place. The statement “doc.Failed.HasValue” is also not evaluated, but that is something that can be safely ignored.
On the Microsoft webpage at http://msdn.microsoft.com/en-us/library/bb882535.aspx they give the following advice for handling null values (thanks to Marius for providing the link):
// When the parent item might be null,
// the "?:" syntax is not converted to query trees.
(p == null ? null : p.CategoryID)
// When comparing nullable DB values to non-nullable objects,
// box it in a nullable container.
o.EmployeeID equals (int?)e.EmployeeID
That being said, the final product looks as follows:
IQueryable docs = from doc in db.GetTable()
where doc.Name == interfaceName
select doc;
if (filter != null)
{
docs = from doc in docs
where (filter.Failed.HasValue ? filter.Failed == doc.Failed : true)
// && (...) -- more filters here
select doc;
}
// Finally, remember to sort.
docs = from doc in docs
orderby doc.Name
select doc;
[Edit: Just for fun I created a follow-up quiz for this article.]
Peet Brits 