Better Entities with Nullable Types

There is an impedance mismatch between objects and normalized relational databases. Everyone knows this. Almost everyone has tried to fix it—think object-oriented databases—and some people have gotten famous off of proposed solutions. (Deservedly so or not.) Well, truth be told Microsoft is working on an ADO.NET Entity Framework to address this issue. The issue phrased using my words is getting data from databases into objects makes for better programming but is time consuming, tedious, and not altogether convenient. From what I know of LINQ, LINQ for Data, and the Entity Framework, they may really be on to something.

In the meantime, you have applications to write. To that end, this article is designed to give you some ideas about how to take a lot of the work out of creating an entity framework (or data access layer, if you prefer) by using some of the newer features in .NET such as generics and nullable types and some staples, like Reflection.

Using Nullable Fields for Entities

One part of the impedance mismatch is that databases support nulls for things like integers, but the .NET framework didn't. Hence, when anyone reads a database column, you have to check for Nothing. This is a pain in the neck and often leads to bugs when populating entities from a database table.

Note: An entity is traditionally a database table. An entity class is generally a class that represents a database table. Most software domains have several entity classes for persisted data and two or three times as many non-entity classes. These are generalities. No software is good or bad based on these simplistic ratios.

Integers couldn't traditionally be nulls (or Nothing) because they were value types and value types are not references like strings. With the Nullable(Of T) class, you now can declare fields as Nullable; this means an integer field can be declared as Nullable(Of Integer) and assigned null or a valid integer number. This takes some of the work out of populating entity classes from database entities (tables) that have null values.

Note: If you are new to generics—also called template or parameterized types—the most important thing to know is that generic types separate algorithm from type. The parameter T is used to mean that the class provides the behavior and you provide the type T.

The Nullable(Of T) class works by using a couple of methods and overloaded operators, and it does essentially what you or I would do: If the Nullable variable has a value for T, that is returned; otherwise, some equivalent default value is returned. When you write code like (Listing 1)

Listing 1: A simple console application that introduces nullable types.

Module Module1

   Sub Main()

      Dim I As Nullable(Of Integer)
      I = 5
      Console.WriteLine(I)
      Console.ReadLine()

   End Sub

End Module

I is the nullable integer type. That statement assigning 5 to I actually calls Nullable.Operator CType(ByVal value as T) As T?, which is a conversion operator converting T—in this case an Integer—into a Nullable (Of T). Listing 2 shows the disassembled conversion operator (disassembled by Reflector).

Listing 2: Assigning a non-nullable to a nullable invokes a shared conversion operator converting T to T?. T? is C#'s shorthand notation for nullable types.

Public Shared Widening Operator CType(ByVal value As T) As T?
   Return New T?(value)
End Function

You can use the generic Nullable class to define an entity class for the Northwind Employees table. You don't need Nullable for nvarchars (basically strings), but you can use them for value types such as Integers and DateTime values. Listing 3 demonstrates an abridged entity class for the Employees table, called Employee.

Listing 3: The Employee class using Nullable types.

Public Class Employee

   Private FEmployeeID As Nullable(Of Integer)
   Public Property EmployeeID() As Nullable(Of Integer)
      Get
         Return FEmployeeID
      End Get
      Set(ByVal Value As Nullable(Of Integer))
         If (Value.HasValue = False) Then
            Console.WriteLine("Employee ID is null")
         End If
         FEmployeeID = Value
      End Set
   End Property

   Private FLastName As String
   Public Property lastName() As String
      Get
         Return FLastName
      End Get
      Set(ByVal Value As String)
         FLastName = Value
      End Set
   End Property

   Private FFirstName As String
   Public Property FirstName() As String
      Get
         Return FFirstName
      End Get
      Set(ByVal Value As String)
         FFirstName = Value
      End Set
   End Property

   Private FBirthDate As Nullable(Of DateTime)
   Public Property BirthDate() As Nullable(Of DateTime)
      Get
         Return FBirthDate
      End Get
      Set(ByVal Value As Nullable(Of DateTime))
         FBirthDate = Value
      End Set
   End Property

   Private FPhoto As Byte()
   Public Property Photo() As Byte()
      Get
         Return FPhoto
      End Get
      Set(ByVal Value As Byte())
         FPhoto = Value
      End Set
   End Property


   Public Overrides Function ToString() As String
      Return String.Format("Employee: {0} {1}, {2} is in {3}", _
         FEmployeeID, FLastName, FFirstName, FRegion)
   End Function

   Private FRegion As String
   Public Property Region() As String
      Get
         Return FRegion
      End Get
      Set(ByVal Value As String)
         FRegion = Value
      End Set
   End Property

End Class

In the listing, EmployeeID and BirthDate are nullable types. All of the other types are strings or the photo which is a byte array, both of which can already be assigned to null (Nothing). (In real life a primary key will likely only be null when you create a new entity and employee records generally have birth dates, but the listing demonstrates how to apply the Nullable class.)

Now, if you happen to read a null value for the BirthDate or EmployeeID, you can safely assign it to the field without all of that flabberlanche code, checking for Nothing. Unfortunately, Nullable(Of Date) can be assigned Nothing but Nullable(Of Date) cannot be assigned System.DBNull.Value. So, you have solved one problem, but you need a solution for DBNull too.