How do you represent a null value in a Data Access Layer (C#)

Question

Hi all:

Just wondering if there is any best practice for DAL to represent null value (from a nullable column).

We currently have our own in-house DAL and are representing null value with int.MinValue. However, this rise concerns with developers thinking that when comparing values, we are purposely imposing another "added" value onto int.MinValue and the fact that int.MinValue is used for null value in a DAL layer is confusing. So other come up with the NullValue from .net 2.0, but we found out that there are performance and syntax issue related to this approach too:

• zorched.net: .NET Nullable Types and DBNull Expose Design Flaws
• BCL Team Blog: Nullable Performance
• megasolutions.net: Webservice with optional parameters
• dotnet2themax.com: Inconsistent behavior of C# nullable types

So, what's your take in this? How does other well-known ORM take care of null values? Is there even a best practice around this issue?

Answer 1

I use nullable types like int? and so forth.

Answer 2

To represent a nullable integer, I'd use Nullable<int> (aka int?) every time. That's exactly what it's designed for. You've provided several links, but could you state exactly where your issue with the syntax of nullable types is, and where you've seen significant performance issues (bearing in mind that the database call is likely to be much more expensive than any conversions going on in-process).

EDIT: I notice that the penultimate link is to a newsgroup conversation involving me. My reason for steering clear of nullable types in this situation is because the question was around a web service which may need to communicate effectively-null values to a platform which doesn't have nullable value types. I don't think that applies to your situation as you've described it.

Answer 3

Hi Jon:

Thanks for the quick reply.

We are still in deciding which one to use, do I don't know how nullable is going to perform on our end, but I agreed that database access is going to be the bottleneck.

The issue I see with using nullable is actually because of the different behaviour under build-in .net operator outlined in here.

If I use nullable type and if I want to correctly compare two integer, I'll have to litter my codebase with Nullable.Compare syntax. I am not sure if that's the greatest solution.

Answer 4

In my data access layer, it transforms null to values that make sense for the context. The purpose of my DAL is to interface the data with the application, so the DAL has to know what the data represents and the designer can then infer a good new value. Maybe zero is the correct answer to a null integer field, maybe an error message could go in a text field.

That way when my application reads data, it gets data, and when it displays to the end user, they see that there was zero of something and where the text should be says "no text avaliable" Stuff like that.

Using int? just pushes the null problem one level higher, and that's now how I use my DAL.

Answer 5

Added as an answer, because it's too long to be a comment.

If you have to litter your codebase with Nullable.Compare, you're doing it wrong. You shouldn't be comparing two Nullable<int> values unless you already know that they're both non-null.

The only exception is in places where it's completely unavoidable, e.g. when aggregating or sorting. If you're using standard methods (e.g. Linq) to do this, you don't need to implement anything special to handle this, because Nullable<T> implements IComparable<T>.

There's a reason that, in SQL Server, any expression containing a NULL term evaluates to NULL. If one of the terms in an expression is NULL, the result of the expression is meaningless - unless the expression explicitly indicates (via ISNULL or COALESCE) what to do if the term is NULL. That's a decision that should be made in or around the expression, not globally.

For instance, consider this seemingly straightforward logic:

if (qtyOrdered > qtyInStock)
{
   Reorder(qtyInStock - qtyOrdered);
}

That will throw an exception if qtyInStock is null. What should this logic be doing if qtyInStock is null? We don't know: that's a question that has to be addressed in the design of the business logic, and it hasn't been addressed yet.

But there's one thing we almost certainly do know: if qtyInStock is null, generating a new order for Int.MinValue - qtyOrdered items is the wrong answer. And that's what logic like this will do if you use a magic number to indicate nulls. The logic is broken, and it needs to be fixed, not hidden.

Answer 6

C.J. Date argues that nulls should not exist in an RDBMS context, in part because of these issues. How do you avoid them? By redesigning them away: split up your data in various ways so that columns that would be null simply become irrelevant and are dropped from the logical design in such a situation. In other words, redesign your tables and columns so that every column is constrained to be NOT NULL but also has a meaningful value (no replacement custom sentinels like int.MinValue).

It is a lot of work, and people will happily argue whether it is worth it, but if you care about the performance and purity of your implementation that much, then maybe it is what you want.