Queries for null or empty strings

Last updated Thursday, October 22, 2015 in Sitecore Experience Platform for Developer

The nature of analytics data means that you sometimes want to run queries for null or empty string values.

Sitecore supports this in the LINQ layer and in the indexing layer by storing physical values of EMPTYVALUE and NULLVALUE. Sitecore translates this in the LINQ layer, so all you need to do is write LINQ queries as usual.

You must specify which fields you want to have an empty string and null support for in the FieldMap. You should not store empty strings and nulls in your index because they waste space.

However, there are some cases where you need to do this. For example, when you want users to be able to search for all customers that have not specified a gender and to contact these customers to determine this. To make this possible, you must set up the FieldMap to store empty strings in the Gender field.

For Lucene, you do this in the App_Config\Include\Sitecore.ContentSearch.Lucene.DefaultIndexConfiguration.config file in the <fieldNames hint="raw:AddFieldByFieldName"> section.

For example:

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

For Solr, you do this in the App_Config\Include\Sitecore.ContentSearch.Solr.Indexes.config file in the <fieldNames hint="raw:AddFieldByFieldName"> section.

For example:

<fieldType fieldName="title" returnType="text" nullValue="NULLVALUE" emptyString="EMPTYVALUE"/>
<fieldType fieldName="title_t" returnType="text" nullValue="NULLVALUE" emptyString="EMPTYVALUE"/>

When you run the following query, it maps "" to EMPTYVALUE and null to NULLVALUE:

return context.GetQueryable<Contact>().Where(i => i.Gender == “”).Take(10).ToList();