Pitfalls when indexing numbers – “Input string was not in a correct format” – Sitecore 7 Content Search

Reason

A System.FormatException is thrown when you run a Linq-to-Sitecore query. The source of the exception is System.Number.ParseInt32 or the likes.
I’ve come across this issue in the following cases:

  1. A Sitecore field which doesn’t contain valid numerical data is mapped to a numeric property (e.g. the field value “I have a horse” is mapped to MyClass.MyPropertyOfTypeDouble).
  2. A Sitecore Number field is being mapped to an integer property (e.g. the value 12.34 is mapped to MyClass.MyPropertyOfTypeLong).
  3. A Standard Values token used in a Sitecore Number or Integer field has been stored in the index (e.g. $MyStandardValuesToken is mapped to MyClass.MyPropertyOfTypeInt).

The following is a brief sum-up of how I went about solving the issues outlined above; if simpler methods exist, please let me know!

Examples are based on Sitecore CMS 7.1 Update-1 (rev. 140130).

Code

1. Invalid data

Well … fix your data.
Yes, really.

2. Floating point format ≠ integer format

In short, Sitecore Number fields should be mapped to floating point properties like float, double and decimal whereas Sitecore Integer fields should be mapped to properties like int and long.

Sitecore uses standard .NET System.ComponentModel.TypeConverters when converting to and from values stored in Content Search indexes. This can cause problems as e.g. the System.ComponentModel.Int32Converter doesn’t know how to handle numbers stored by System.ComponentModel.DoubleConverter. The difference in output format can be seen in the screenshots below, taken from Luke:

Sitecore field value indexed using the NumericFieldReader

Sitecore field value indexed using the NumericFieldReader

Sitecore field value indexed using the PrecisionNumericFieldReader

Sitecore field value indexed using the PrecisionNumericFieldReader

To solve this issue, either change the Sitecore field type from Number to Integer or change the property it’s mapped to from int/long to float/double/decimal.

Alternatively, it’s possible to explicitly mark properties with a TypeConverterAttribute, but doing so throughout a solution feels like treating symptoms instead of curing the disease.

3. Standard Values tokens in Integer and Number fields

Neither the Sitecore.ContentSearch.FieldReaders.NumericFieldReader or the Sitecore.ContentSearch.FieldReaders.PrecisionNumericFieldReader are configured as the default for Sitecore fields of type Integer and Number, respectively.
This means that e.g. Standard Values tokens like $GeneratedSerialNo will be added to indices as text and cause a System.FormatException as soon as Sitecore tries to convert said text to a number (e.g. when using Linq-to-Sitecore queries).
Both the NumericFieldReader and the PrecisionNumericFieldReader return the value 0 when encountering field values which can’t be converted to numbers, hence avoiding invalid numerical data in Content Search indexes.

Use the configuration described in a previous post to solve this issue for Sitecore fields of type Number.

To solve this issue for fields of type Integer, insert the following into a .config-file and place it in a subfolder of “/App_Config/Include”, e.g. “/App_Config/Include/MyCompany/ContentSearch.IntegerFields.config”.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <contentSearch>
      <configuration>
        <defaultIndexConfiguration>
          <fieldReaders>
            <mapFieldByTypeName>
              <fieldReader fieldTypeName="integer" fieldNameFormat="{0}" fieldReaderType="Sitecore.ContentSearch.FieldReaders.NumericFieldReader, Sitecore.ContentSearch" />
            </mapFieldByTypeName>
          </fieldReaders>
        </defaultIndexConfiguration>
      </configuration>
    </contentSearch>
  </sitecore>
</configuration>

Example

Yellow Screen Of Death, why does thou haunt me so?
Yellow Screen Of Death caused by an attempt to convert a value stored in floating point format to an integer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s