We have a database containing one hierarchical table representing a categories tree. The table holds just two records, one the root of the tree (IdParent=NULL) and the other is a child of the first.
Using LINQ to SQL or the Entity Framework you could write the following code:
// Table Category contains 2 records
//
// Record 1: Id=1 | IdParent=NULL
// Record 2: Id=2 | IdParent=1
static int GetChildrenCount(int? idParent)
{
int recordCount = -1;
using (var context = new ExamplesDataContext())
{
recordCount = context.Categories
.Where(c => c.IdParent == idParent)
.Count();
}
return recordCount;
}
static void Main(string[] args)
{
// Count root categories
Console.WriteLine(GetChildrenCount(null));
// Count children of category 1
Console.WriteLine(GetChildrenCount(1));
// Count root categories
using (var context = new ExamplesDataContext())
{
int recordCount = context.Categories
.Where(c => c.IdParent == null)
.Count();
Console.WriteLine(recordCount);
}
}
What would be the output of this snippet?
Update:
This snippet would output the following;
// 0 // 1 // 1
This situation is caused by a design decision in LINQ to SQL where a comparison with the literal null translates to the appropriate SQL version (is null or is not null), but for a non literal comparison (comparing against a variable that holds null) the comparison is directly mapped to an equals comparison with null which as specified by default in SQL Server results in a comparison result of false.
As a workaround you can construct the where condition in GetChildrenCount like this:
static int GetChildrenCount(int? idParent)
{
int recordCount = -1;
using (var context = new ExamplesDataContext())
{
recordCount = context.Categories
.Where(c => (c.IdParent == idParent || (idParent == null && c.IdParent == null)))
.Count();
}
return recordCount;
}
Exceptional Code 