Serious Technobabble

Nullable booleans and comparisons

Testing if a nullable boolean is true is quite simple in C#, there are many ways, sorted here in my preference order.

bool? nullableBool = true;
var option1 = nullableBool == true;
var option2 = nullableBool.Equals(true)
var option3 = nullableBool.HasValue && nullableBool.Value;
var option4 = nullableBool ?? false;

All options return true in this case, false for nullableBool = false; and also false for nullableBool = null;

However, when using this expressions is labmdas inside a LINQ to SQL projection they will behave differently! LINQ to SQL it is not smart enough to translate to T-SQL in a way that the current ANSI_NULLS setting does not affect the query result. To avoid NULL = 1 comparisons, don’t use option1 or option2 there!

Huh? Please elaborate

Consider a table in Microsoft SQL Server, with a nullable bit column, like this

CREATE TABLE [dbo].[Banana] (
 [Id] INT,
 [IsYellow] bit NULL,
)

I will not get into whether you should have bit NULL columns or not (you probably shouldn’t) but if you do, and you are using LINQ to SQL to query your RDBMS, your generated Banana entity will have an IsYellow property, of  bool? C#type. Makes sense!

The following simplified code, though a little bit pointless in this case, is a perfectly valid situation if you wanted to avoid a nullable in your DTO, or you are projecting a boolean for any other reason.

ctx.Bananas.Select(x => new 
                      { 
                          Id = x.Id,
                          IsYellow = x.IsYellow == true
                      });

… but it will generate the following ANSI_NULL OFF dependent T-SQL code:

SELECT [t0].[Id], 
    (CASE 
        WHEN [t0].[IsYellow] = @p0 THEN 1
        WHEN NOT ([t0].[IsYellow] = @p0) THEN 0
        ELSE NULL
     END) AS [IsYellow]
FROM [Banana] AS [t0]

If there are any rows with NULL IsYelow, this code will will generate an “InvalidOperationException: The null value cannot be assigned to a member with type System.Boolean which is a non-nullable value type.” This happens because the  CASE will fall through the ELSE clause for any NULL value, thus returning a NULL in the result set, unasignable to our regular non-nullable boolean.

Note that the condition [t0].[IsYellow] = @p0, where @p0 is true and [IsYellow] is NULL, is neither true nor false according to the SQL 92 standard, this is the behavior when  ANSI_NULLS are are set to ON, wich is the default and will be actually forced ON in next versions of the MS SQL Server.

The last option will work as expected. For example:

ctx.Bananas.Select(x => new 
                      { 
                          Id = x.Id,
                          IsYellow = x.IsYellow ?? false
                      });

Would generate a safer NULL checking T-SQL code, like:

SELECT [t0].[Id], COALESCE([t0].[IsYellow],@p0) AS [IsYellow]
FROM [Banana] AS [t0]

You may also want to read:

• The SET ANSI_NULLS Reference in the MSDN

The following very simple T-SQL query will return the average value for all values in the Rating column:

SELECT AVG(Rating) AS AvgRating FROM CarRating;

Doing this in LINQ to SQL is quite trivial; The Average method returns the average from the numeric sequence:

 var avgRating = Ctx.CarRatings.Select(x => x.Rating).Average();

The problem:

In the following example, we select an additional aggregate to get the maximum value of the whole Rating column in addition to the average:

SELECT AVG(Rating) AS Average, MAX(Rating) AS Maximum FROM CarRating;

In this case however, writing a LINQ to SQL equivalent is not that easy, one option would be splitting the query in two LINQ sentences. This is the most readable way to express what we want (and I like that), but hits the database twice and in some circumstances, specially if your table is big, you may not be able to live with that.

var avgRating = Ctx.CarRatings.Select(x => x.Rating).Average();
var maxRating = Ctx.CarRatings.Select(x => x.Rating).Max();
// Voila

The hack:

I refused to believe that there is no -good- way to express this simple SQL sentence in LINQ to SQL without hitting the DB twice. The only solution I have found so far, and the intertubes seem to agree, is to use a group by clause in the sentence. You would say; group by what? Well… that’s exactly the problem, it does not really make any sense, but if you group by a constant (!)…

var ratings = Ctx.CarRatings
                 .GroupBy(uselessConstant => 0)
                 .Select(r => new
                    { 
                      Avg = r.Average(x => x.Rating),
                      Max = r.Max(x => x.Rating)
                    })
                 .FirstOrDefault();
// Voila (WTF!)

This works, but remember that good code is readable code, naming the .GroupBy() lambda with something horrible like uselessConstantThatAllowsSelectingTwo-AggregateColumnsWithoutHittingTheDbTwice, is better that breaking the WTF-o-meter in a code-review or future maintenance. A comment may also be opportune in this case, perhaps even a link to this post ;)

Any comments or thoughts are welcome! Specially if you have a better way to solve this.