Using the less than comparison operator for strings in F#

Question

I'm trying to write a F# query as follows

let extract = 
    query {
        for trade in Schema.Trade do
        where (trade.Year + trade.Month <= "202007")
        take 100
        select (trade)
    }

The problem is that both Year and Month are string.

I see an exception coming from Microsoft.FSharp.Linq.QueryModule saying that

LessThanOrEqual is not defined for the types 'System.String' and 'System.String'.

Why not? Is there a different operator?

To give you more context, the query is running inside SQLProvider and, if I write only = instead of <=, the auto-generated sql already translates the + into a correct

CONCAT(`trade`.`year`, `trade`.`month`)

So that part is not an issue on the SQLProvider side.

C# underlying code

Let me rephrase what I think is happening under the cover. Sorry, this is just my fealing about the analysis of the problem, I don't want to confuse things. Anyway, I huess that something like the following is happening (this is C# interactive)

> Expression<Func<String>> ex1 = () => "202006";;
> Expression<Func<String>> ex2 = () => "202005";;
> Expression.GreaterThan(ex1.Body, ex2.Body);;
System.InvalidOperationException: L'operatore binario GreaterThan non è definito per i tipi 'System.String' e 'System.String'.
  + System.Linq.Expressions.Expression.GetUserDefinedBinaryOperatorOrThrow(System.Linq.Expressions.ExpressionType, string, System.Linq.Expressions.Expression, System.Linq.Expressions.Expression, bool)
  + System.Linq.Expressions.Expression.GreaterThan(System.Linq.Expressions.Expression, System.Linq.Expressions.Expression, bool, System.Reflection.MethodInfo)

Stack Trace

This is instead the real, full stack trace from the above said F# program.

</ExceptionType><Message>L'operatore binario LessThanOrEqual non è definito per i tipi 'System.String' e 'System.String'.</Message><StackTrace>   in System.Linq.Expressions.Expression.GetUserDefinedBinaryOperatorOrThrow(ExpressionType binaryType, String name, Expression left, Expression right, Boolean liftToNull)
   in System.Linq.Expressions.Expression.LessThanOrEqual(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext@512-7.Invoke(Tuple`2 tupledArg)
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 512
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 734
   in Microsoft.FSharp.Primitives.Basics.List.mapToFreshConsTail[a,b](FSharpList`1 cons, FSharpFunc`2 f, FSharpList`1 x) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\local.fs:riga 241
   in Microsoft.FSharp.Primitives.Basics.List.map[T,TResult](FSharpFunc`2 mapping, FSharpList`1 x) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\local.fs:riga 252
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprsToLinq(ConvEnv env, FSharpList`1 es) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 820
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 642
   in Microsoft.FSharp.Primitives.Basics.List.map[T,TResult](FSharpFunc`2 mapping, FSharpList`1 x) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\local.fs:riga 250
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprsToLinq(ConvEnv env, FSharpList`1 es) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 820
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 642
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 734
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 844
   in Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Query.fs:riga 1823
   in Microsoft.FSharp.Linq.QueryModule.clo@1926-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr`1 q) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Query.fs:riga 1928
   in Program.main(String[] argv) in C:\FunctionalCmd\Program.fs:riga 23</StackTrace><ExceptionString>System.InvalidOperationException: L'operatore binario LessThanOrEqual non è definito per i tipi 'System.String' e 'System.String'.
   in System.Linq.Expressions.Expression.GetUserDefinedBinaryOperatorOrThrow(ExpressionType binaryType, String name, Expression left, Expression right, Boolean liftToNull)
   in System.Linq.Expressions.Expression.LessThanOrEqual(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext@512-7.Invoke(Tuple`2 tupledArg)
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 512
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 734
   in Microsoft.FSharp.Primitives.Basics.List.mapToFreshConsTail[a,b](FSharpList`1 cons, FSharpFunc`2 f, FSharpList`1 x) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\local.fs:riga 241
   in Microsoft.FSharp.Primitives.Basics.List.map[T,TResult](FSharpFunc`2 mapping, FSharpList`1 x) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\local.fs:riga 252
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprsToLinq(ConvEnv env, FSharpList`1 es) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 820
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 642
   in Microsoft.FSharp.Primitives.Basics.List.map[T,TResult](FSharpFunc`2 mapping, FSharpList`1 x) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\local.fs:riga 250
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprsToLinq(ConvEnv env, FSharpList`1 es) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 820
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 642
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 734
   in Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:riga 844
   in Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Query.fs:riga 1823
   in Microsoft.FSharp.Linq.QueryModule.clo@1926-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr`1 q) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Query.fs:riga 1928
   in Program.main(String[] argv) in C:\FunctionalCmd\Program.fs:riga 23</ExceptionString></Exception></TraceRecord>
Eccezione non gestita di tipo 'System.InvalidOperationException' in FSharp.Core.dll
L'operatore binario LessThanOrEqual non è definito per i tipi 'System.String' e 'System.String'.

I think this is a bug in the query translation, as string implements `IComparable`. You might want to report it. — Abel, Jul 13 '20 at 12:37
Those are part of F#, and so you can report it against the F# repo here: https://github.com/dotnet/fsharp/ — Abel, Jul 14 '20 at 08:53

score 0 · Answer 1 · answered Jul 13 '20 at 12:29

I do not have a database readily available to try this, but I think the issue is that you cannot reasonably compare strings in SQL query. While it is reasonable to concatenate strings and check for equality on strings, I don't think a comparison on strings would do what you want.

I would try re-writing the condition using something like this:

let extract = 
    query {
        for trade in Schema.Trade do
        where (int trade.Year < 2020 || 
          (int trade.Year = 2020 && int trade.Month <= 7))
        take 100
        select (trade)
    }

I assume you want a query that selects trades before July 2020, so I encoded that using a more elaborate condition that takes either trades before 2020 or 2020 trades before or in July.

@Giulio Ah, I did not know that! I guess I do not trust string comparison, so I did not think that would work :-). There should be some way to do this with integers though. Would it work if you replaced `int` with `Int32.Parse`? I will see if I can get a SQL database to try this myself! — Tomas Petricek, Jul 13 '20 at 13:46

score 0 · Answer 2 · answered Jul 13 '20 at 15:22

0

Could you do something like this?

let extract = 
    let limitDate = System.DateTime(2020,08,01)
    query {
        for trade in Schema.Trade do
        where (trade < limitDate)
        take 100
        select (trade)
    }

answered Jul 13 '20 at 15:22

Tuomas Hietanen

4,650
2
35
43

score 0 · Accepted Answer · 2020-07-24T16:31:13.573

This is very similar to the C# equivalent described here.

Indeed if we look at linq2db ExpressionEqualityComparer they point to a CompareBinary

whilst SQLProvider SqlRuntime.Patterns is pointing to a ConditionOperator.GreaterThan

| ExpressionType.GreaterThan,        (:? BinaryExpression as ce) -> Some (ConditionOperator.GreaterThan,  ce.Left,ce.Right)

That could be (see @Abel comment)

a bug in the query translation, as string implements IComparable

I've solved this by switching to C# (that is more robust and prevents the runtime exception with a compile error, coherent with its syntax, as opposite to what happens in F#) and linq2db (that is also more mature and robust than F# SQL type provider)

Using the less than comparison operator for strings in F#

C# underlying code

Stack Trace

3 Answers3