Gravatar for debabrata.biswas@nttdata.com

Question by debubiswas, Jun 19, 2014 9:30 AM

Linq predicate to compare my custom field value with datetime

Sitecore is 7.1 (130926)

Please review below the sample code I have where if I remove the "Where" for DateTime checks then the results are returned fine.

result = context.GetQueryable<SC.ContentSearch.SearchTypes.SearchResultItem>()
         .Where(predicate)
         .Where(i => (i[sortField] != null && (DateTime.Parse(i[sortField]) <= DateTime.Now.AddHours(48))
         && (DateTime.Parse(i[sortField]) >= DateTime.Now)))
         .Where(predicate2).ToList();

But if I include the "Where" above, where "sortField" is mapped to "@fXlasttime84229", which shows a valid datetime value of type string (is this is the problem??) in the indexed document, then I get the exception as below:

{"Invalid Method Call Argument Type: Field - FieldNode - Field: @fXlasttime84229 - System.String. Only constant arguments is supported."}

The stack trace says:

at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.ValidateMethodCallArguments(IEnumerable`1 arguments)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.EvaluateMethodCall(MethodCallExpression methodCall)\r\n   at Coveo.SearchProvider.Linq.CoveoExpressionParser.VisitMethodCall(MethodCallExpression p_MethodCall)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.Visit(Expression expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.VisitBinary(BinaryExpression expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.Visit(Expression expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.VisitBinary(BinaryExpression expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.Visit(Expression expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.VisitBinary(BinaryExpression expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.Visit(Expression expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.VisitWhereMethod(MethodCallExpression methodCall)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.VisitQueryableMethod(MethodCallExpression methodCall)\r\n   at Coveo.SearchProvider.Linq.CoveoExpressionParser.VisitMethodCall(MethodCallExpression p_MethodCall)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.Visit(Expression expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.VisitWhereMethod(MethodCallExpression methodCall)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.VisitQueryableMethod(MethodCallExpression methodCall)\r\n   at Coveo.SearchProvider.Linq.CoveoExpressionParser.VisitMethodCall(MethodCallExpression p_MethodCall)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.Visit(Expression expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.ExpressionParser.Parse(Expression expression)\r\n   at Coveo.SearchProvider.CoveoQueryable`2.GetQuery(Expression p_Expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.GenericQueryable`2.GetEnumerator()\r\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   at X.Web.Data.Coveo.DoCoveoSearchWhatsNew(Int32 pageindex, Int32 pageCount, Expression`1 predicate, Boolean isDescending, String sortField, Type sortFieldType, List`1 currentResults, String[] collections, Boolean RestrictFromToday) in c:\\Debu\\X-DEV\\X.Web.Data\\Coveo.cs:line 2218

The field is defined as:

<fields hint="raw:AddComputedIndexField">
    <field fieldName="Xlasttime">X.Sitecore.Mapping_Fields.Lasttime, X.Sitecore</field>
    ...
</fields>

The field is defined as sortable by:

<fieldNames hint="raw:AddFieldByFieldName">
    <fieldType fieldName="Xlasttime" isSortable="true" settingType='Coveo.Framework.Configuration.FieldConfiguration, Coveo.Framework' returnType='System.DateTime' />
    ...
</fieldNames>

UPDATE FOLLOWING SUGGESTION

Code now looks like:

using (var context = searchIndex.CreateSearchContext(SC.ContentSearch.Security.SearchSecurityOptions.DisableSecurityCheck)) {
    predicate2 = SC.ContentSearch.Linq.Utilities.PredicateBuilder.True<sc.contentsearch.searchtypes.searchresultitem>(); 
    result = context.GetQueryable<sc.contentsearch.searchtypes.searchresultitem>() 
        .Where(predicate) 
        .Where(i => (i[sortField] != null && i.GetDateFieldValue(sortField)!=null && (i.GetDateFieldValue(sortField) <= DateTime.Now.AddHours(48)) && (i.GetDateFieldValue(sortField) >= DateTime.Now))) 
        .Where(predicate2).ToList(); 
} 

But I now get

{"Exception has been thrown by the target of an invocation."}
StackTrace  "   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)\r\n   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)\r\n   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)\r\n   at Coveo.SearchProvider.Linq.CoveoQueryMapper.HandleGenericNode(QueryNode p_CurrentNode, CoveoCompositeQuery p_QueryParams)\r\n   at Coveo.SearchProvider.Linq.CoveoQueryMapper.MapQuery(IndexQuery p_SitecoreQuery)\r\n   at Coveo.SearchProvider.CoveoQueryable`2.GetQuery(Expression p_Expression)\r\n   at Sitecore.ContentSearch.Linq.Parsing.GenericQueryable`2.GetEnumerator()\r\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   at X.Web.Data.Coveo.DoCoveoSearchWhatsNew(Int32 pageindex, Int32 pageCount, Expression`1 predicate, Boolean isDescending, String sortField, Type sortFieldType, List`1 currentResults, String[] collections, Boolean RestrictFromToday) in c:\\Debu\\X-DEV\\X.Web.Data\\Coveo.cs:line 2218" string
Gravatar for lbergeron@coveo.com

Comment by Luc Bergeron, Jun 20, 2014 11:00 AM

You don't need to perform null checking because if the field has no value, whatever the condition, it will evaluate to false. Try with this expression instead:

.Where(i => i.GetDateFieldValue(sortField) <= DateTime.Now.AddHours(48) && i.GetDateFieldValue(sortField) >= DateTime.Now)

Hope this helps

Gravatar for debabrata.biswas@nttdata.com

Comment by debubiswas, Jun 20, 2014 11:24 AM

In fact i had to resort to those null checks thinking that might fix this error. In other words i had to code as you suggested but am still getting that target invocation exception.

1 Reply
Gravatar for lbergeron@coveo.com

Answer by Luc Bergeron, Jun 19, 2014 10:18 AM

Dates and numerical field comparisons are a bit tricky with Linq. The C# code is not executed like it would be on a IEnumerable for instance.

So, instead of doing:

DateTime.Parse(i[sortField])

try this:

i.GetDateFieldValue(sortField)

You will need to add this using directive to use see extension method.

using Coveo.SearchProvider.Linq;

Also, you can use the GetNumericalFieldValue method for comparisons with integer and floating point values.

Hope this helps

Gravatar for debabrata.biswas@nttdata.com

Comment by debubiswas, Jun 19, 2014 10:22 AM

cool. Trying it now. But also about the field @fXlasttime84229" itself, even though the class behind returns a datetime value, when i see the result set in coveo indexed documents, I see this as "string" type. Shouldn't that be showing Date/Time like it does for created/updated system fields? Just curious if this would matter. Please let me know.

Gravatar for lbergeron@coveo.com

Comment by Luc Bergeron, Jun 20, 2014 10:55 AM

I've done some checks and the field must appear as 'Date/time' on indexed documents otherwise, the Linq expression won't give the expected results.

I guess your field type (in Sitecore) is 'Single-Line Text', so you need to change it to 'Datetime'. After changing the field type in Sitecore, you will need to restart the Coveo Enterprise Search service. Then you must reindex the Sitecore items that contains the modified field.

After that, the field should appear as 'Date/time' on indexed documents.

Gravatar for debabrata.biswas@nttdata.com

Comment by debubiswas, Jun 20, 2014 11:24 AM

Nope, the sitecore field is of type datetime and also my computed field class returns that as datetime. But still I see the field as "string" in the result set.

Gravatar for lbergeron@coveo.com

Comment by Luc Bergeron, Jun 20, 2014 1:15 PM

I created a computed field using this article and it worked fine for me.

https://developers.coveo.com/display/SC201406/Computed+Date+Fields

As it is explained in the article, the computed field must return the date as a formatted string. The expected format is "yyyy/MM/dd@HH:mm:ss".

Gravatar for vseguin@coveo.com

Comment by Vincent Séguin, Jun 22, 2014 9:36 AM

Did you follow exactly this line ? : To create a computed date field, the ReturnType property must be datetime and the ComputeFieldValue method must return the date as a yyyy/MM/dd@HH:mm:ss formatted string.

You must set the returnType attribute in the configuration (Coveo.SearchProvider.config) as 'datetime' on the computed field.

Ask a question