Sorting on date field with Sitecore 7 ContentSearch

Question

I'm trying to add a field sort to a date field in a ContentSearch query. I'm able to filter on the index field properly so I'm assuming the field is getting populated with values properly, however, results are not being sorted properly. Any thoughts? Here's the code I'm using to do query:

public static IEnumerable<Episode> GetPastEpisodes(Show show, bool includeMostRecent = false, int count = 0)
{
    IEnumerable<Episode> pastEpisodes;
    using (var context = _index.CreateSearchContext())
    {
        // querying against lucene index
        pastEpisodes = context.GetQueryable<Episode>().Where(GetPastAirDatePredicate(show));

        if(!includeMostRecent)
        {
            pastEpisodes = pastEpisodes.Where(item => item.Id != GetMostRecentEpisode(show).Id);
        }

        pastEpisodes = pastEpisodes.OrderByDescending(ep => ep.Latest_Air_Date);

        if (count > 0)
        {
            pastEpisodes = pastEpisodes.Take(count);
        }

        pastEpisodes = pastEpisodes.ToList();

        // map the lucene documents to Sitecore items using the database
        foreach (var episode in pastEpisodes)
        {
            _database.Map(episode);
        }

    }
    return pastEpisodes;
}

private static Expression<Func<Episode,bool>> GetPastAirDatePredicate(Show show)
{
    var templatePredicate = PredicateBuilder.Create<Episode>(item => item.TemplateId == IEpisodeConstants.TemplateId);
    var showPathPredicate = PredicateBuilder.Create<Episode>(item => item.FullPath.StartsWith(show.FullPath));
    var airDatePredicate = PredicateBuilder.Create<Episode>(item => item.Latest_Air_Date < DateTime.Now.Date.AddDays(1));

    var fullPredicate = PredicateBuilder.Create<Episode>(templatePredicate).And(showPathPredicate).And(airDatePredicate);

    return fullPredicate;
}

The field is stored and untokenized and using the LowerCaseKeywordAnalyzer.

<field fieldName="latest_air_date" storageType="YES" indexType="UN_TOKENIZED" vectorType="NO" boost="1f" type="System.DateTime" settingType="Sitecore.ContentSearch.LuceneProvider.LuceneSearchFieldConfiguration, Sitecore.ContentSearch.LuceneProvider">
    <analyzer type="Sitecore.ContentSearch.LuceneProvider.Analyzers.LowerCaseKeywordAnalyzer, Sitecore.ContentSearch.LuceneProvider"/>
</field>

Episode class has IndexField attribute set:

[IndexField("latest_air_date")]
public virtual DateTime Latest_Air_Date  {get; set; }

Could you change type="System.DateTime" to type="System.String" and then rebuild your index and try again — Ahmed Okour
Kyle, could you confirm which rev of Sitecore you are testing this on? — Matt Gartman
just noticed that index type='UN_TOKENIZED', i think it should be indexType="untokenized". — Ahmed Okour
I have exactly the same issue. Kyle, did you find a solution yet? — Pascal Mathys

Matt Gartman Matt Gartman · Accepted Answer · 2014-06-27T01:21:59

Kyle,

As far as I can tell everything looks correct with your configuration and code. I mocked out something very similar in a vanilla Sitecore 7.2 instance and Dates sorted without issue.

One thing to note though, and this might be what is giving you some issues, is that Sitecore's FieldReader for DateTime only store the date portion of the DateTime. If you are expecting to be able to sort by true DateTime you will need to add a custom FieldReader or some computed fields.

See this blog that discusses the issue and explains the process to swap out a custom field reader: http://reasoncodeexample.com/2014/01/30/indexing-datetime-fields-sitecore-7-content-search/

I would also suggest looking at the index with Luke to verify what data is actually in the index. https://code.google.com/p/luke/

And finally you may want to enable Search debugging in Sitecore to see exactly how Sitecore is executing the query in Lucene.

Sitecore.ContentSearch.config:

<setting name="ContentSearch.EnableSearchDebug" value="true" />

Sorting on date field with Sitecore 7 ContentSearch

1 Answers