I have an event listing API, which takes as input start and end dates and returns a list of events. The requirement for sort, is to show closest to farthest events by start date in the past, followed by closest to farthest event by end date in the future.

Essentially the following logic (which was done on a list previously).

    List<EventModel> newList = listEvent.Where(e => e.startDT <= DateTime.Today).OrderByDescending(e => e.startDT).ToList();
    newList.AddRange(listEvent.Where(e => e.startDT > DateTime.Today).OrderBy(e => e.endDT));
    listEvent = newList;

How can I achieve this on an IQueryable to execute in my SOLR search?

I see that concat isn't supported with Linq to Sitecore https://doc.sitecore.com/developers/90/sitecore-experience-management/en/linq-to-sitecore.html

Any other way to do this?


Consider researching Solr functions. My experience has used a hard-coded field name with solr field extension. Perhaps you can figure out how to make this more dynamic.

First you can start by adding a new field to your CustomSearchResultItem class.

public string _val_ { get; set; }

Second you can write a query using the new field.

var query = PredicateBuilder.True();
query = query.And(x => x._val_.Equals("recip(abs(ms(NOW/HOUR,startdate_tdt)),3.16e-11,4,.4)"));

What this query should do is rank the results higher when the date is close to today, and then rank lower the further away it is.

Here is a simple demonstration while using Sitecore PowerShell Extensions against Solr.

$criteria = @(
    @{Filter = "Equals"; Field = "_val_"; Value = "recip(abs(ms(NOW/HOUR,__smallcreateddate_tdt)),3.16e-11,4,.4)"}
$props = @{
    Index = "sitecore_master_index"
    Criteria = $criteria

Find-Item @props | Select-Object Name,CreatedDate

I used CreatedDate and so I don't expect any future dates.

Example results

  • Thanks Michael, perhaps I am doing something wrong in the code, but this is what I added: Expression<Func<IndexedEventItem, bool>> dateRanking = PredicateBuilder.True<IndexedEventItem>(); dateRanking = dateRanking.And(x => x._val_.Equals("recip(abs(ms(NOW/HOUR,startdate_tdt)),3.16e-11,4,.4)")); queryable = queryable.Where(dateRanking);
    – Durga Bose
    Aug 6 '19 at 6:57
  • And I see this coming through in SOLR as ((((((_language:(en) AND _path:(f2c81b01775f4789ab045e519c5172ad)) AND _latestversion:(True)) AND (startdate_tdt:[* TO 2020-08-06T04:17:18.948Z] AND enddate_tdt:[2018-08-06T04:17:18.948Z TO *])) AND _template:(504cb359dc9e4df991b662b7151692eb)) AND featured_b:(True)) AND _val__t:("recip abs ms NOW HOUR,startdate_tdt ,3.16e 11,4,.4")) and no results are being returned, any ideas?
    – Durga Bose
    Aug 6 '19 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.