Code Trivia #3

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;
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s