Sitecore ContentSearch - Filtering by null and empty strings

Sitecore ContentSearch - Filtering by null and empty strings

By default Sitecore will not save null and empty strings in your indexes as it wastes space.

However, you might need to do something like this:

using (var context = ContentSearchManager.GetIndex("MyIndex").CreateSearchContext())
{
    var query = context.GetQueryable<SearchResultItem>().Where(x => x.Name != null);
    var results = query.GetResults();
}

But that will result in an exception:

System.NotSupportedException: Comparison of null values is not supported.

Comparing with an empty string will not throw an exception, but it won’t return the expected results either, as the query will be translated into -MatchNoDocsQuery[] +*:* (Lucence).

Solution

Luckily there is a solution. It is possible to overwrite this functionality on a per field basis.

You can add a nullValue and emptyString parameter to the needed fields in your index configuration under the <fieldNames hint="raw:AddFieldByFieldName"> section:

<field 
    fieldName="name" storageType="NO" indexType="TOKENIZED" 
    vectorType="NO" boost="1f" type="System.String" 
    nullValue="NULL" emptyString="EMPTY"
    settingType="Sitecore.ContentSearch.LuceneProvider.LuceneSearchFieldConfiguration,                    
                 Sitecore.ContentSearch.LuceneProvider" />

This will map nulls and empty strings to the string values "NULL" and "EMPTY" respectively. You will now be able to do queries on nulls and empty strings without getting exceptions and also get the expected results.

Considerations

You should be aware that this will increase the size of your index slightly. In most cases I don’t think it will have any noticable impact.

You can read a little more about this subject in Sitecore’s own post about it.