String concatenation within the query doesn't work

[Thorium]

I try to do this:

let query = 
    query {
        for cust in dc.Main.Customers do
        select (cust.CompanyName + " " + cust.CompanyName)
    }

It worked on the NuGet version but doesn't work with the GitHub version.
GitHub version fails with:

The binary operator Add is not defined for the types 'System.String' and 'System.String'.","T":"   
   at System.Linq.Expressions.Expression.GetUserDefinedBinaryOperatorOrThrow(ExpressionType binaryType, String name, Expression left, Expression right, Boolean liftToNull)
   at System.Linq.Expressions.Expression.Add(Expression left, Expression right, MethodInfo method)
   at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.transform@49(List`1 tupleIndex, ParameterExpression resultParam, FSharpMap`2 aliasEntityDict, Dictionary`2 projectionMap, FSharpOption`1 en, Expression e) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.QueryExpression.fs:line 64
   at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.transform@49(List`1 tupleIndex, ParameterExpression resultParam, FSharpMap`2 aliasEntityDict, Dictionary`2 projectionMap, FSharpOption`1 en, Expression e) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.QueryExpression.fs:line 64
   at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.transform@49(List`1 tupleIndex, ParameterExpression resultParam, FSharpMap`2 aliasEntityDict, Dictionary`2 projectionMap, FSharpOption`1 en, Expression e) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.QueryExpression.fs:line 64
   at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.transform@49(List`1 tupleIndex, ParameterExpression resultParam, FSharpMap`2 aliasEntityDict, Dictionary`2 projectionMap, FSharpOption`1 en, Expression e) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.QueryExpression.fs:line 64
   at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.transform@118-8.Invoke(Expression a) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.QueryExpression.fs:line 118
   at Microsoft.FSharp.Collections.IEnumerator.map@107.DoMoveNext(b& )
   at Microsoft.FSharp.Collections.IEnumerator.MapEnumerator`1.System-Collections-IEnumerator-MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Dynamic.Utils.CollectionExtensions.ToReadOnly[T](IEnumerable`1 enumerable)
   at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, IEnumerable`1 arguments)
   at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.transform@49(List`1 tupleIndex, ParameterExpression resultParam, FSharpMap`2 aliasEntityDict, Dictionary`2 projectionMap, FSharpOption`1 en, Expression e) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.QueryExpression.fs:line 118
   at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.transform@49(List`1 tupleIndex, ParameterExpression resultParam, FSharpMap`2 aliasEntityDict, Dictionary`2 projectionMap, FSharpOption`1 en, Expression e) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.QueryExpression.fs:line 123
(...cutted some loop here, expression tree recursion I suppose...)
FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.transform[a](Expression projection, List`1 tupleIndex, ParameterExpression resultParam, a baseTableAlias, FSharpMap`2 aliasEntityDict) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.QueryExpression.fs:line 146
   at FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.convertExpression(SqlExp exp, List`1 entityIndex, IDbConnection con, ISqlProvider provider) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.QueryExpression.fs:line 169
   at FSharp.Data.Sql.Runtime.QueryImplementation.executeQuery(ISqlDataContext dc, ISqlProvider provider, SqlExp sqlExp, List`1 ti) in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.Linq.fs:line 36
   at FSharp.Data.Sql.Runtime.QueryImplementation.SqlQueryable`1.System-Collections-Generic-IEnumerable`1-GetEnumerator() in C:\\git\\SQLProvider\\src\\SQLProvider\\SqlRuntime.Linq.fs:line 77
   at Microsoft.FSharp.Collections.SeqModule.ToArray[T](IEnumerable`1 source)
(...and this is the query evaluation...)

But to be able to really investigate this it would be nice if the unit tests would work!

[Thorium]

I somehow like this SQLProvider project: If I feel too confident and think I'm a good F# developer then I just spend half an hour with this source code and these kind of problems. It brings you back to earth again.

[colinbull]

👍

[pezipink]

Ha! It is certainly not for the faint of heart!

On this problem, if this used to work but doesn't anymore, what I suspect is that before when it was working, it was doing the concatenation client side, but since I re-wrote the projection transformation tree from using the LINQ mutable transforming visitor thing to the immutable version, I have messed something up.

Really this kind of thing could be done server side as well, but it should not be too hard to get it working client-side again, the tree transforming thing is probably either doing something wrong, or going too deep when it should just stop. (just a theory, might be totally wrong !)

[Thorium]

Serverside is of course better but there is a catch in case of string: http://stackoverflow.com/questions/7027384/the-binary-operator-add-is-not-defined-for-the-types-system-string-and-syste

[pezipink]

yes but we are in control so we can identify this case and do whatever we like with it ourselves, the bigger problem is we don't currently do any pre-analysis on the projection part of the tree in order to do something sensible server side, everything is currently assumed to be client side for that portion.

[pezipink]

I guess the first question to answer with this one though, from a client side perspective, is how did it work before?? :)

[pezipink]

@Thorium looking at this, you know you can just use String.Concat(...) instead of the operator and it will work right?

To detect the operator case is a little tricky since Add is a binary expression tree which would need traversing to see if anything it is calling returns a string, in which case each non-Add leaf of the tree can be folded up into a String.Concat MethodCall tree and replace the original with it- so that is one for a rainy day :)

[Thorium]

Right, e.g. this works:

let employeesFirstName = 
    query {
        for emp in ctx.Hr.Employees do
        select (String.Concat [|emp.FirstName; " "; emp.LastName|])
    } |> Seq.toList

[pezipink]

yes exactly, going to close this for now.

[Thorium]

Ok, I have similar issue with the current NuGet package (btw thanks for the release! 👍 ) but this time with DateTimes:

  // dateTimeAfter      = 1/1/1970 12:00:00 AM
  // c.DatabaseDateTime = 4/4/1975 12:00:00 AM
  // dateTimeBefore     = 1/11/2016 4:35:17 PM

  for c in dbContext.MyDatabase.Items do
  where (
      c.DatabaseDateTime < dateTimeBefore // Not working!
      c.DatabaseDateTime > dateTimeAfter  // Working!
  )
  select (c.Name) // 0 items hit.

[pezipink]

What SQL does it generate?

[Thorium]

Hmm, to me this looks quite good:

 SELECT `c`.`Id` as `Id`,`c`.`Name` as `Name`,`c`.`DatabaseDateTime` as `DatabaseDateTime`
 FROM MyDatabase.Items as `c` 
 WHERE ((`c`.`DatabaseDateTime`< @param1 AND `c`.`DatabaseDateTime`> @param2))

[pezipink]

Well I'm not really sure what to say :)

[colinbull]

Any chance this could be some weird coalescing thing with DateTimes and the MySQL driver? What is the actual value of that parameter?

Sent from my iPhone

On 11 Jan 2016, at 02:10, Ross McKinlay notifications@github.com wrote:

Well I'm not really sure what to say :)

—
Reply to this email directly or view it on GitHub.