Null Comparisons
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 Region is null are excluded from the result set, and the Transact-SQL statement would return 0 rows.
-- Find orders and customers with no regions.
SELECT a.[CustomerID]
FROM [Northwind].[dbo].[Customers] a
JOIN [Northwind].[dbo].[Orders] b ON a.Region = b.ShipRegion
WHERE a.Region 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 NwEntities As New NorthwindEntities()
Dim customers As ObjectQuery(Of Customers) = NwEntities.Customers
Dim orders As ObjectQuery(Of Orders) = NwEntities.Orders
Dim query = _
From c In customers _
Join o In orders On c.Region Equals o.ShipRegion _
Where c.Region = Nothing _
Select c.CustomerID
For Each customerID In query
Console.WriteLine("Customer ID: ", customerID)
Next
End Using
using (NorthwindEntities NwEntities = new NorthwindEntities())
{
ObjectQuery<Customers> customers = NwEntities.Customers;
ObjectQuery<Orders> orders = NwEntities.Orders;
IQueryable<string> query = from c in customers
join o in orders on c.Region equals o.ShipRegion
where c.Region == null
select c.CustomerID;
foreach (string customerID in query)
{
Console.WriteLine("Customer ID: {0}", customerID);
}
}
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 AWEntities As New AdventureWorksEntities()
Dim dt As DateTime = New DateTime()
Dim query = AWEntities.Product _
.Where(Function(p) _
((DirectCast(p, NewProduct)).Introduced > dt)) _
.Select(Function(x) x)
End Using
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
DateTime dt = new DateTime();
var query = AWEntities.Product
.Where(p => (p as NewProduct).Introduced > dt)
.Select(x => x);
}