Doesn't work with SQLProvider

[ForNeVeR]

trafficstars

Description

Consider this simple database structure (I'm using MS SQL Express 2014):

create table dbo.Users(Id Int primary key, Name NVarChar(max), Role NVarChar(max))
insert into dbo.Users(Id, Name, Role) values (1, N'Peter', N'Admin')
insert into dbo.Users(Id, Name, Role) values (2, N'Peter', N'User')
insert into dbo.Users(Id, Name, Role) values (3, N'Igor', N'Admin')

I've written two very similar programs:

1. Program A using FSharp.Data.TypeProviders: https://gist.github.com/ForNeVeR/c09e21dabc840c9fd4af7600b8f363ae#file-program-fs
2. Program B using FSharp.Data.SqlProvider: https://gist.github.com/ForNeVeR/6658c6faf3719f482694c09cc922299a

Repro steps

Create the database, compile and execute Program A and Program B.

Expected behavior

Both programs should print the same output: https://gist.github.com/ForNeVeR/c09e21dabc840c9fd4af7600b8f363ae#file-output-txt

Actual behavior

Program A works, but Program B throws the following exception:

System.ArgumentException: Type mismatch when building 'args': invalid parameter for a method or indexer property. Expected 'System.Linq.IQueryable`1[FSharp.Data.Sql.Common.SqlEntity]', but received type 'System.Object'.
Parameter name: receivedType
   at Microsoft.FSharp.Quotations.PatternsModule.checkTypesWeakSR[a](Type expectedType, Type receivedType, a name, String threeHoleSR)
   at [email protected](ParameterInfo p, FSharpExpr a)
   at Microsoft.FSharp.Collections.ListModule.loop@183-27[T1,T2](FSharpFunc`3 f, FSharpList`1 list1, FSharpList`1 list2)
   at Microsoft.FSharp.Collections.ListModule.Iterate2[T1,T2](FSharpFunc`2 action, FSharpList`1 list1, FSharpList`1 list2)
   at Microsoft.FSharp.Quotations.PatternsModule.checkArgs(ParameterInfo[] paramInfos, FSharpList`1 args)
   at Microsoft.FSharp.Quotations.PatternsModule.mkInstanceMethodCall(FSharpExpr obj, MethodInfo minfo, FSharpList`1 args)
   at Microsoft.FSharp.Quotations.FSharpExpr.Call(FSharpExpr obj, MethodInfo methodInfo, FSharpList`1 arguments)
   at FSharpComposableQuery.QueryImpl.toExp@386(QueryBuilder this, Exp exp)
   at FSharpComposableQuery.QueryImpl.QueryBuilder.toExpr(Exp exp)
   at FSharpComposableQuery.QueryImpl.QueryBuilder.Norm[T](FSharpExpr`1 expr)
   at FSharpComposableQuery.QueryImpl.QueryBuilder.Run[T](FSharpExpr`1 q)
   at Program.main(String[] argv) в T:\Temp\ConsoleApplication6\ConsoleApplication6\Program.fs:строка 30

Known workarounds

Don't use SQLProvider 😿

Related information

• Operating system: Windows 10
• Branch: see my packages.config below
• .NET Runtime, CoreCLR or Mono Version: .NET 4.6.2, F# 4.4.1.0

packages.config:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="FSharp.Core" version="4.1.17" targetFramework="net462" />
  <package id="FSharp.Data.TypeProviders" version="5.0.0.2" targetFramework="net462" />
  <package id="FSharpComposableQuery" version="1.0.4-beta" targetFramework="net462" />
  <package id="SQLProvider" version="1.1.2" targetFramework="net462" />
  <package id="System.ValueTuple" version="4.3.0" targetFramework="net462" />
</packages>

[jamescheney]

FSharpComposableQuery was intended as a proof of concept, to (hopefully) motivate incorporation of this functionality into existing LINQ-related libraries, where it probably belongs. It has not been tested with SQLProvider, just with the type providers library. I don't think SQLProvider was mature enough for this last time I had the time to work on this (2013-14).

It would be nice to fix this; it looks like the problem may just be differences in the query expression representations/types between SQLProvider and regular LINQ/type providers.

Fortunately, it looks like this functionality may already be integrated into SQLProvider (which I wasn't aware of until just now):

https://fsprojects.github.io/SQLProvider/core/composable.html

Does that help/work for what you want? (i.e. does it "just work" if you comment out the open FSharpComposableQuery line from the SQLProvier version?)

If so I'll update the README to acknowledge this limitation and point to SQLProvider's page about composable queries.

If SQLProvider doesn't support the functionality you want from FSharpComposableQuery then it might be more useful to for us to discuss how to improve its support for query composition with them.

[jamescheney]

OK, it looks like there was some discussion of incorporating ideas from FSharpComposableQuery but it doesn't look like it's been completely supported (at least the test case mentioned by @colinbull in https://github.com/fsprojects/SQLProvider/issues/177 no longer seems to be in the repository).

@pezipink or @colinbull, would it be worthwhile to try to incorporate / adapt FSharpComposableQuery so that it works with SQLProvider? It sounds like there is now demand for this. I really don't have the expertise / time / working set (I haven't been doing F# development for several years now). But if there is interest I may be able to find a summer intern to work on it (or perhaps it could be posted as a summer of code type project).

[ForNeVeR]

@jamescheney thank you for your answer. The code compiles if I remove open FSharpComposableQuery, but it have some problems. I saw the documentation you linked, but I can't see anything about quotation support in the documentation. I assumed it won't work, and that's great that I was wrong.

It seem to work in some of the cases. Namely,

    printfn "\nUsers of role Admin named Peter:"
    query { for u in users do if (%admin) u && (%peter) u then yield u }
    |> Seq.iter printUser

does work (and that's actually great!). Although, the following throws a runtime exception:

    printfn "\nUsers named Peter:"
    query { yield! (%filterUsers) (%peter) }
    |> Seq.iter printUser
(*
System.Exception: Unsupported expression. Ensure all server-side objects won't have any .NET-operators/methods that can't be converted to SQL. The In and Not In operators only support the inline array syntax. value(System.Runtime.CompilerServices.StrongBox`1[Microsoft.FSharp.Core.FSharpFunc`2[FSharp.Data.Sql.Common.SqlEntity,System.Boolean]]).Value.Invoke(_arg1)
   at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e)
   at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence)
   at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedOuter(CanEliminate canElim, FSharpExpr tm)
   at Microsoft.FSharp.Linq.QueryModule.clo@1730-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr`1 )
   at Program.main(String[] argv) в T:\Temp\ConsoleApplication6\ConsoleApplication6\Program.fs:строка 34
*)

Note: I personally find the working sample with for u in users do if (%admin) u && (%peter) u then yield u more practical than sample yield! (%filterUsers) (%peter). I have no use cases of that non-working code sample, so I don't want to even open an issue at SqlProvider. I will probably try to use these composable features in my code with SqlProvider and be happy :)

[jamescheney]

Thanks! I seem to recall that some normalization already happens inside the ordinary F# LINQ library, so it could be that these examples would work anyway - do any of them work using TypeProviders only (again commenting out the open FSharpComposableQuery line)?

It sounds like it would be good to find out what exactly SQLProvider does support in terms of composability and perhaps contribute some test cases/documentation examples, and see whether there is enthusiasm for further transfer of ideas/code. Though, from the error message you copied above it may be that the code that would need to be changed is in Microsoft.FSharp.Linq rather than SQLProvider/TypeProvider.

[ForNeVeR]

do any of them work using TypeProviders only

None of the examples work with FSharp.Data.TypeProviders without FSharpComposableQuery. Here's an exception:

System.NotSupportedException: The method "Boolean Invoke(Users)" doesn't have a supported SQL translation.
   at System.Data.Linq.SqlClient.PostBindDotNetConverter.Visitor.VisitMethodCall(SqlMethodCall mc)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlVisitor.VisitExpression(SqlExpression exp)
   at System.Data.Linq.SqlClient.SqlVisitor.VisitSelectCore(SqlSelect select)
   at System.Data.Linq.SqlClient.PostBindDotNetConverter.Visitor.VisitSelect(SqlSelect select)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.PostBindDotNetConverter.Convert(SqlNode node, SqlFactory sql, ProviderMode providerMode)
   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection`1 parentParameters, SqlNodeAnnotations annotations)
   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc`2 action, IEnumerable`1 source)
   at Program.main(String[] argv) в T:\Temp\ConsoleApplication6\ConsoleApplication6\Program.fs:строка 34

(the error message was localized on my machine, but I've tried to do sensible English translation before posting it)

[Thorium]

SQLProviders System.Exception: Unsupported expression means that we try to convert LINQ-Expression to SQL (where-clause) and we are expecting simple things like a Boolean column, but we have gotten a nested expression tree, System.Runtime.CompilerServices.StrongBox``1[Microsoft.FSharp.Core.FSharpFunc``2...Invoke() and we are not invoking that. Should FSharp.Linq.ComposableQuery get rid of nested expression trees if invoked correctly?

Here is an old issue discussion about SQLProvider with FSharp.Linq.ComposableQuery and I think the end-result was that it's working.

[jamescheney]

Ah, thanks, I had forgotten about that. @ForNeVeR's examples above illustrat that it doesn't seem to work at the moment, but two of the three examples do work with SQLProvider if you don't open FSharpComposableQuery.

If SQLProvider is (intended to) support query normalization as demonstrated by this library, it would be good for this to be tested/documented - that way, we'd know whether it worked correctly in 2014 (when the old issue discussion happened) and stopped work sometime since then, or has never worked. This library has a test suite that could be used to check that.

In any case, I'm classifying this as an enhancement request since support for SQLProvider is not something we set out to do - at this point I think this library is a proof of concept to show how query composition could be supported in other, better-maintained libraries.

[jamescheney]

See also #7, which was waiting on a change to improve cross-compatibility of table names between TypeProvider and SqlProvider.