Edit

Share via

Null Comparisons

  • Article

A null value in the data source indicates that the value is unknown. In LINQ to Entities queries, you can check for null values so that certain calculations or comparisons are only performed on rows that have valid, or non-null, data. CLR null semantics, however, may differ from the null semantics of the data source. Most databases use a version of three-valued logic to handle null comparisons. That is, a comparison against a null value does not evaluate to true or false, it evaluates to unknown. Often this is an implementation of ANSI nulls, but this is not always the case.

By default in SQL Server, the null-equals-null comparison returns a null value. In the following example, the rows where ShipDate is null are excluded from the result set, and the Transact-SQL statement would return 0 rows.

-- Find order details and orders with no ship date.  
SELECT h.SalesOrderID  
FROM Sales.SalesOrderHeader h  
JOIN Sales.SalesOrderDetail o ON o.SalesOrderID = h.SalesOrderID  
WHERE h.ShipDate IS Null

This is very different from the CLR null semantics, where the null-equals-null comparison returns true.

The following LINQ query is expressed in the CLR, but it is executed in the data source. Because there is no guarantee that CLR semantics will be honored at the data source, the expected behavior is indeterminate.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
    ObjectSet<SalesOrderDetail> details = context.SalesOrderDetails;

    var query =
        from order in orders
        join detail in details
        on order.SalesOrderID
        equals detail.SalesOrderID
        where order.ShipDate == null
        select order.SalesOrderID;

    foreach (var OrderID in query)
    {
        Console.WriteLine("OrderID : {0}", OrderID);
    }
}

Using context As New AdventureWorksEntities()

    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
    Dim details As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails

    Dim query = _
        From order In orders _
        Join detail In details _
        On order.SalesOrderID _
        Equals detail.SalesOrderID _
        Where order.ShipDate = Nothing
        Select order.SalesOrderID


    For Each orderID In query
        Console.WriteLine("OrderID: {0} ", orderID)
    Next
End Using

Key Selectors

A key selector is a function used in the standard query operators to extract a key from an element. In the key selector function, an expression can be compared with a constant. CLR null semantics are exhibited if an expression is compared to a null constant or if two null constants are compared. Store null semantics are exhibited if two columns with null values in the data source are compared. Key selectors are found in many of the grouping and ordering standard query operators, such as GroupBy, and are used to select keys by which to order or group the query results.

Null Property on a Null Object

In the Entity Framework, the properties of a null object are null. When you attempt to reference a property of a null object in the CLR, you will receive a NullReferenceException. When a LINQ query involves a property of a null object, this can result in inconsistent behavior.

For example, in the following query, the cast to NewProduct is done in the command tree layer, which might result in the Introduced property being null. If the database defined null comparisons such that the DateTime comparison evaluates to true, the row will be included.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{

    DateTime dt = new DateTime();
    var query = context.Products
        .Where(p => (p as NewProduct).Introduced > dt)
        .Select(x => x);
}

Using context As New AdventureWorksEntities()
    Dim dt As DateTime = New DateTime()
    Dim query = context.Products _
        .Where(Function(p) _
            ((DirectCast(p, NewProduct)).Introduced > dt)) _
        .Select(Function(x) x)
End Using

Passing Null Collections to Aggregate Functions

In LINQ to Entities, when you pass a collection that supports IQueryable to an aggregate function, aggregate operations are performed at the database. There might be differences in the results of a query that was performed in-memory and a query that was performed at the database. With an in-memory query, if there are no matches, the query returns zero. At the database, the same query returns null. If a null value is passed to a LINQ aggregate function, an exception will be thrown. To accept possible null values, cast the types and the properties of the types that receive query results to nullable value types.

See also