NULL Value Handling Specifics

• Nov 21, 2018
• 3 minutes to read

This topic describes the specifics of working with ‘null’ values, and how you should treat them when writing criteria.

Consider the following example. The Employee persistent class exposes two properties - Name and Manager. Name is a string property specifying an employee name. Manager is reference property that references an Employee object if the current object does not represent a manager. If the current employee is a manager, the property contains a null reference.

C#

public class Employee : XPObject {
    public string Name;
    public Employee Manager;
}

VB.NET

Public Class Employee
    Inherits XPObject
    Public Name As String
    Public Manager As Employee
End Class

Suppose, a database contains six Employee objects.

You are presented with a task to retrieve all employees who do not work under a specified manager’s direction (including the manager). Suppose this manager is Mike. You could try using criteria like this to solve your task.

C#

XPCollection<Employee> team = new XPCollection<Employee>();
Employee manager = 
    Session.DefaultSession.FindObject<Employee>(CriteriaOperator.Parse("[Name] = 'Mike'"));
team.Criteria = CriteriaOperator.Parse("Manager.Oid <> ? And Oid <> ?", manager.Oid, manager.Oid);
int count = team.Count; // Returns 2 (Nathan and Bob)

VB.NET

Dim team As New XPCollection(Of Employee)()
Dim manager As Employee = _
Session.DefaultSession.FindObject(Of Employee)(CriteriaOperator.Parse("[Name] = 'Mike'"))
team.Criteria = CriteriaOperator.Parse("Manager.Oid <> ? And Oid <> ?", manager.Oid, manager.Oid)
Dim count As Integer = team.Count ' Returns 2 (Nathan and Bob)

This code is supposed to return all employees who do not work under Mike’s supervision, including Mike himself. In our example, this should be John, Nathan and Bob. However, as you see the resulting collection contains only Nathan and Bob. This is because John does not have a manager assigned. In SQL, you cannot compare a value against ‘null’. Such a comparison does not yield a Boolean value, it yields ‘unknown’, which results in an empty result set. Since John does not have a manager, the corresponding database column holds ‘null’. The criteria demonstrated above tries to compare ‘null’ to Mike’s identifier. Such a comparison cannot be evaluated correctly and thus, John is omitted from the resulting collection.

When writing a criteria like this, you need to explicitly check that a reference property value is not ‘null’. What is more, you cannot do this by writing “Manager == NULL” as this will also be a comparison to ‘null’, which cannot be evaluated correctly. To check that a value is ‘null’, you must use the SQL ‘is null’ predicate. In XPO context, this translates to using the IsNull function operator. The following code snippet demonstrates the rewritten criteria that functions as you would expect.

C#

XPCollection<Employee> team = new XPCollection<Employee>();
Employee manager = 
    Session.DefaultSession.FindObject<Employee>(CriteriaOperator.Parse("[Name] = 'Mike'"));
team.Criteria = CriteriaOperator.Parse("Iif(IsNull(Manager), Oid, Manager.Oid) <> ?", manager.Oid);
int count = team.Count; // Returns 3 (John, Nathan and Bob)

VB.NET

Dim team As New XPCollection(Of Employee)()
Dim manager As Employee = _
Session.DefaultSession.FindObject(Of Employee)(CriteriaOperator.Parse("[Name] = 'Mike'"))
team.Criteria = CriteriaOperator.Parse("Iif(IsNull(Manager), Oid, Manager.Oid) <> ?", manager.Oid)
Dim count As Integer = team.Count ' Returns 3 (John, Nathan and Bob)

The demonstrated criteria operator checks whether the Manager property value is set, which means that the currently processed employee references a manager. If it is, the operator ensures that the referenced manager is not Mike. Otherwise, the operator checks to make sure that the currently processed employee is not Mike himself.