Add support for lt, gt, le, ge oData operators on strings

[afioletov]

OData allows to use operators like >, <, <= and >= with strings, e.g.
$filter=TerritoryDescription ge 'South'

But attempt to use them with nHibernate cause System.NotSupportedException.

Is it possible to add support for these operators and stings?

{
   "error": {
   	"code": "",
   	"message": "An error has occurred.",
   	"innererror": {
   		"message": "The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; odata.metadata=minimal'.",
   		"type": "System.InvalidOperationException",
   		"stacktrace": "",
   		"internalexception": {
   			"message": "Int32 Compare(System.String, System.String, System.StringComparison)",
   			"type": "System.NotSupportedException",
   			"stacktrace": "   at NHibernate.Linq.Visitors.HqlGeneratorExpressionVisitor.VisitMethodCallExpression(MethodCallExpression expression)\r\n   at NHibernate.Linq.Visitors.HqlGeneratorExpressionVisitor.VisitExpression(Expression expression)\r\n   at NHibernate.Linq.Visitors.HqlGeneratorExpressionVisitor.VisitBinaryExpression(BinaryExpression expression)\r\n   at NHibernate.Linq.Visitors.HqlGeneratorExpressionVisitor.VisitExpression(Expression expression)\r\n   at NHibernate.Linq.Visitors.QueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)\r\n   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)\r\n   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)\r\n   at NHibernate.Linq.Visitors.QueryModelVisitor.Visit()\r\n   at NHibernate.Linq.Visitors.QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root, Nullable`1 rootReturnType)\r\n   at NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter)\r\n   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)\r\n   at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)\r\n   at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)\r\n   at NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression)\r\n   at NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query)\r\n   at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)\r\n   at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression)\r\n   at Remotion.Linq.QueryableBase`1.System.Collections.IEnumerable.GetEnumerator()\r\n   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)\r\n   at Microsoft.AspNet.OData.Formatter.ODataOutputFormatterHelper.WriteToStream(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, IWebApiUrlHelper internaUrlHelper, IWebApiRequestMessage internalRequest, IWebApiHeaders internalRequestHeaders, Func`2 getODataMessageWrapper, Func`2 getEdmTypeSerializer, Func`2 getODataPayloadSerializer, Func`1 getODataSerializerContext)\r\n   at Microsoft.AspNet.OData.Formatter.ODataMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__1b.MoveNext()"
   		}
   	}
   }
}

[bahusoid]

OData allows to use operators like >, <, <= and >= with strings, e.g.
$filter=TerritoryDescription ge 'South'

OData doesn't really know/care string it or not. So I guess it allows any nonsense operators on any types.
It looks like "Not An Issue/Won't Fix" issue to me. What's expected equivalent in .NET/SQL?

[afioletov]

There is no equivalent in .NET. In SQL comparison operations work with strings:

SELECT *
FROM [NORTHWND].[dbo].[Categories]
WHERE [CategoryName] > 'M'

Odata sample: https://services.odata.org/Northwind/Northwind.svc/Categories?$filter=CategoryName ge 'M'

In .net you can use Compare to get the same results:

using System;
using System.Linq;			
public class Program
{
	public static void Main()
	{
		var strs=new[]{"a", "b","c", "d", "dd", "e", "g"};
		
		foreach(var str in strs.Where(x => string.Compare(x,"d")>0))
		Console.WriteLine(str);
	}
}

[bahusoid]

Indeed. string.Compare substitution makes sense (and it's actually used but seems not supported by hql)

[bahusoid]

So the problem seems to be that we don't register string.Compare overload taking StringComparison argument:

nhibernate-core/src/NHibernate/Linq/Functions/CompareGenerator.cs

Line 17 in ae0f1f7

ReflectHelper.FastGetMethod(string.Compare, default(string), default(string)),

Same applies for StartsWith and EndsWith methods too. Is it by intention or just overlook?

[fredericDelaporte]

Overloads taking a StringComparison argument have not been registered because it seemed not to make sense. How do you translate the StringComparison argument to SQL? There is no SQL standard about that as far as I know.
With SQL-Server, we may use collation, but which one? We may have to somehow transcode the current culture to its corresponding collation. Other databases may not have any suitable mechanism.
But I do not see how it is relevant to the issue here. Do you mean OData string comparison expressions are translated to Linq expressions using these kind of overloads?

[bahusoid]

But I do not see how it is relevant to the issue here. Do you mean OData string comparison expressions are translated to Linq expressions using these kind of overloads

Have you checked exception message. Relevant part:

"message": "Int32 Compare(System.String, System.String, System.StringComparison)",
"type": "System.NotSupportedException",

With SQL-Server, we may use collation,

I don't think it should be considered or somehow used - just ignored. string.Compare(string,string) makes current culture sensitive compare by default and DB can do whatever comparisons it's been configured to do (in example case insensitive default collation in SQL Server). And we don't do anything about it.

[fredericDelaporte]

I think there has been a discussion on the subject, and it was decided to not support these overloads for not letting people use an argument which would have been ignored. We were not seeing the point of supporting these overloads, as it is better to not let users think it seems supported, while it is actually ignored.

Of course this reasoning was not taking into account the possibility for some external framework to generate expressions using these overloads, maybe without the user being able to instruct such framework to instead use the overloads without the StringComparison argument.
Especially if there is no way to configure OData on this point, this decision could be reconsidered.

But also, can someone test if registering those overloads is enough to solve this issue?