Query: Support for String.CompareTo

[Villason]

Might be related to #1767.

I have some classes that returns expressions that afterwards are compiled into a final expression. This code does not translates to SQL:

        public override Expression<Func<Comment, bool>> GetAfterExpression(Comment after)
        {
            if (IsDescending)
            {
                return m => m.Path.CompareTo(after.Path) > 0;
            }
            else
            {
                return m => m.Path.CompareTo(after.Path) < 0;
            }
        }

The warning is:

warn: Microsoft.Data.Entity.Query.Internal.SqlServerQueryCompilationContextFactory[0]
      The LINQ expression '([f].Path.CompareTo(__after_Path_1) < 0)' could not be translated and will be evaluated locally.

Is this the expected behavior?

Update:

If I switch to this:

        public override Expression<Func<Comment, bool>> GetAfterExpression(Comment after)
        {
            if (IsDescending)
            {
                return m => string.Compare(m.Path, after.Path) > 0;
            }
            else
            {
                return m => string.Compare(m.Path, after.Path) < 0;
            }
        }

I get an exception with this bug: #3464

fail: Microsoft.AspNet.Diagnostics.DeveloperExceptionPageMiddleware[0]
      An unhandled exception has occurred while executing the request
      System.InvalidOperationException: Extension node must override the property Expression.Type.
         at System.Linq.Expressions.Expression.get_Type()
         at System.Linq.Expressions.Expression.AndAlso(Expression left, Expression right, MethodInfo method)
         at Microsoft.Data.Entity.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
         at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
         at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)
         at Microsoft.Data.Entity.Query.ExpressionVisitors.SqlTranslatingExpressionVisitor.Visit(Expression expression)
         at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
         at Remotion.Linq.Clauses.WhereClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
         at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
         at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
         at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
         at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
         at Microsoft.Data.Entity.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
         at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
         at Microsoft.Data.Entity.Storage.Database.CompileAsyncQuery[TResult](QueryModel queryModel)
         at Microsoft.Data.Entity.Query.Internal.QueryCompiler.<>c__DisplayClass19_0`1.<CompileAsyncQuery>b__0()
         at Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddAsyncQuery[TResult](Object cacheKey, Func`1 compiler)
         at Microsoft.Data.Entity.Query.Internal.QueryCompiler.CompileAsyncQuery[TResult](Expression query)
         at Microsoft.Data.Entity.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query)
         at Microsoft.Data.Entity.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression)
         at Microsoft.Data.Entity.Query.Internal.EntityQueryable`1.System.Collections.Generic.IAsyncEnumerable<TResult>.GetEnumerator()
         at System.Linq.AsyncEnumerable.Aggregate[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken)
         at System.Linq.AsyncEnumerable.Aggregate[TSource,TAccumulate](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, CancellationToken cancellationToken)
         at System.Linq.AsyncEnumerable.ToList[TSource](IAsyncEnumerable`1 source, CancellationToken cancellationToken)
         at Microsoft.Data.Entity.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at Chincheta.Data.EntityFramework.ReadOnlyModelRepository`1.<GetAsync>d__11`1.MoveNext() in E:\Repositories\Chincheta\src\Chincheta.Data.EntityFramework\ReadOnlyModelRepository.cs:line 74
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
         at Chincheta.Data.EntityFramework.ReadOnlyModelRepository`1.<GetListingAsync>d__22.MoveNext() in E:\Repositories\Chincheta\src\Chincheta.Data.EntityFramework\ReadOnlyModelRepository.cs:line 233
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
         at Chincheta.Managers.ModelManagerBase`1.<GetListingAsync>d__20.MoveNext() in E:\Repositories\Chincheta\src\Chincheta.Core\Managers\ModelManagerBase.cs:line 79
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
         at Chincheta.Managers.CommentManager.<GetDescendantsAsync>d__11.MoveNext() in E:\Repositories\Chincheta\src\Chincheta.Core\Managers\CommentManager.cs:line 152
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
         at Chincheta.Web.Areas.Posts.Controllers.CommentsController.<Index>d__3.MoveNext() in E:\Repositories\Chincheta\src\Chincheta.Web\Areas\Posts\Controllers\CommentsController.cs:line 57
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Mvc.Controllers.ControllerActionExecutor.<CastToObject>d__8`1.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Mvc.Controllers.ControllerActionInvoker.<InvokeActionAsync>d__6.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
         at Microsoft.AspNet.Mvc.Controllers.FilterActionInvoker.<InvokeActionFilterAsync>d__53.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at Microsoft.AspNet.Mvc.Controllers.FilterActionInvoker.<InvokeAsync>d__44.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Mvc.Infrastructure.MvcRouteHandler.<RouteAsync>d__6.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Mvc.Routing.InnerAttributeRoute.<RouteAsync>d__10.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Routing.RouteCollection.<RouteAsync>d__9.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Builder.RouterMiddleware.<Invoke>d__4.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Session.SessionMiddleware.<Invoke>d__8.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at Microsoft.AspNet.Session.SessionMiddleware.<Invoke>d__8.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at Microsoft.AspNet.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at Microsoft.AspNet.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.IISPlatformHandler.IISPlatformHandlerMiddleware.<Invoke>d__8.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Diagnostics.StatusCodePagesMiddleware.<Invoke>d__3.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Diagnostics.Entity.MigrationsEndPointMiddleware.<Invoke>d__5.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Diagnostics.Entity.DatabaseErrorPageMiddleware.<Invoke>d__6.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at Microsoft.AspNet.Diagnostics.Entity.DatabaseErrorPageMiddleware.<Invoke>d__6.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.AspNet.Diagnostics.DeveloperExceptionPageMiddleware.<Invoke>d__7.MoveNext()

To answer the questions: Path is a string.

[ErikEJ]

What would be the SQL translation?

[gdoron]

@ErikEJ I guess it depends on what type is Path.
For numeric values for instance, it's very easy to translate to SQL.

[Villason]

@ErikEJ a simple WHERE Path > 'something'.

[gdoron]

@Villason in SQL (well at lest on MSSQL), strings comparison is simply a length comparison
Anyway it's translatable, but probably not supported.
Did you check if it was supported in EF 6 or not?

[Villason]

@gdoron that is not correct. > and < operands compare the strings "alphabetically".

[gdoron]

@Villason ohh my bad, this was what I remembered...
Anyway I think I have never had to use string comparison in SQL.
(Except obviously from sorting)

[Villason]

@gdoron well, this is why I am using it. The code is part of a "Load more" pattern implementation.

[smitpatel]

This should be working in 1.0.0 already. @Villason - Can you verify?