Include Null Properties in NHibernate QBE

To include properties with null values in NHibernate’s Query By Example (QBE), simply call ExcludeNone() on your created example.

For example, a class called ProductSize has 5 properties called Id, Dimension1, Dimension2, Dimension3, and Dimension4. All properties except Dimension1 are nullable. The combination of all dimension fields are unique for each record.

If I pass in an Example object with only Dimension1 set to 1, I want a search criteria like:

where Dimension1 = 1 and Dimension2 is null and Dimension3 is null and Dimension4 is null

NHibernate’s defaul behavior is to exclude all properties with null values when you create an Example object. So, this code would not work:

var productSize = new ProductSize(1, 2, null, null);
var sampleSize = Example.Create(productSize);
var result = session.CreateCriteria<ProductSize>()
    .Add(sampleSize)
    .UniqueResult();

The generated SQL expression is

where Dimension1 = 1 and Dimension2 = 2

As a result, more than one record may be returned because the query does not include Dimension3 and Dimension4, and you’ll get an IncorrectResultSizeDataAccessException.

Here’s the code that works:

var productSize = new ProductSize(1, 2, null, null);
var sampleSize = Example.Create(productSize).ExcludeNone();  // Include all properties.
var result = session.CreateCriteria<ProductSize>()
    .Add(sampleSize)
    .UniqueResult();

Using ExcludeNone() tells NHibernate to generate SQL expression based on all properties, including those will null values.

ป้ายกำกับ:nhibernate