Cannot use a tupled function?

[halcwb]

Is your feature request related to a problem? Please describe. I am trying to convert an FSharp.Data.TypeProviders project to SQLProvider, as the former is no longer maintained.

Describe the solution you'd like One of the things I frequently have in this lib is using composed predicate functions like:

      module Predicate =    

          type Predicate = 
              | All 
              | After of DateTime
              | Before of DateTime
              | Validated of bool
              | Equals of string
              | And of Predicate * Predicate
              | Or of Predicate * Predicate
              | Not of Predicate

          let rec eval = function
              | All -> <@ fun (_, _) -> true @>
              | After dt -> 
                  <@ fun (_, p: sql.dataContext.``dbo.TextSignalsEntity``) -> p.Time > dt @>
              | Before dt -> 
                  <@ fun (_, p: sql.dataContext.``dbo.TextSignalsEntity``) -> p.Time < dt @>
              | Validated b -> 
                  <@ fun (_, p: sql.dataContext.``dbo.TextSignalsEntity``) -> if b then p.ValidationTime.IsSome else true @>
              | Equals s -> 
                  <@ fun (text, _) -> text = s @>
              | And (p1, p2) -> <@ fun p -> (%eval p1) p && (%eval p2) p @>
              | Or (p1, p2) ->  <@ fun p -> (%eval p1) p || (%eval p2) p @>
              | Not p1 -> <@ fun p -> (%eval p1) p |> not @>

      let get (ctx: sql.dataContext) (pats : string list) parPred signPred =
          let parPred' = parPred |> Parameter.Predicate.eval
          let patsqry = pats |> Query.toQuerayable
          let patsPred = pats |> List.isEmpty
          let signPred = signPred |> Predicate.eval

          <@ fun _ ->
              query {
                  for s in ctx.Dbo.TextSignals do
                  join pt in ctx.Dbo.ParametersText 
                      on (s.TextId = pt.TextId)
                  join p in ctx.Dbo.Parameters
                      on (s.ParameterId = p.ParameterId)
                  join h in ctx.Dbo.Patients
                      on (s.PatientId = h.PatientId)
                  where (
                          pt.ParameterId = p.ParameterId &&
                          (patsPred || patsqry.Contains(h.HospitalNumber.Value)) &&
                          (%signPred) (pt.Text, s) && // this line gives trouble
                          ((%parPred') p)
                        )
                  select (h.HospitalNumber, s, p.ParameterName, p.Abbreviation, pt.Text) } @>

However, then I get:

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. (((tupledArg.Item2.GetColumn("ParameterID") == tupledArg.Item3.GetColumn("ParameterID")) AndAlso [].Contains(tupledArg.Item4.GetColumnOption("HospitalNumber").Value)) AndAlso ToFSharpFunc(p => (ToFSharpFunc(tupledArg => (tupledArg.Item1 == "")).Invoke(p) OrElse ToFSharpFunc(tupledArg => (tupledArg.Item1 == "")).Invoke(p))).Invoke(new Tuple2(tupledArg.Item2.GetColumn("Text"), tupledArg.Item1))) at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Linq.fs:line 718 at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Query.fs:line 1815 at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedOuter(CanEliminate canElim, FSharpExpr tm) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\Query.fs:line 1849 at Microsoft.FSharp.Linq.QueryModule.clo@1926-15.Microsoft.FSharp.Linq.ForwardDeclarations.IQueryMethods.Execute[a,b](FSharpExpr1 q) in F:\workspace_work\1\s\src\fsharp\FSharp.Core\Query.fs:line 1928 at <StartupCode$FSI_0049>.$FSI_0049.main@() Stopped due to error

Commenting out ((%signPred) (pt.Text, s), resolves the problem.

Describe alternatives you've considered That will be a tough one, have to rewrite a lot of code, I fear.

[Thorium]

I would guess the problem is ToFSharpFunc(p => (ToFSharpFunc... what SQL is this trying to generate?

[halcwb]

@Thorium. I have to admit, I really don't know. But this is the code I used with FSharp.Data.TypeProviders and which ran really well and was also very easy to develop. Only I am unable to get that code running again as it depends on the net45 sdk which I cannot even download anymore. So, I am a bit stuck.

[halcwb]

@Thorium After some thinking, should I do the %signpred 'post SQL' as a sort of a System.Linq expression?

[Thorium]

SQLProvider should support this kind of quotations: https://fsprojects.github.io/SQLProvider/core/composable.html#Generate-composable-queries-from-quotations

However, I'm not familiar with that part of this project. If there is a feature missing, I will accept PRs, however I must warn you that SQLProvider source code is not the easiest one.

[halcwb]

@Thorium The example shows a one argument quotation function, and this indeed works. However, it seems to fail with multiple argument functions as with %signPred.

[Thorium]

Few notes: 1) you have one more brackets in the working part. 2) It does serialize the tuple as tupledarg.Item1.GetColumn(...), sound good, but the other...are you selecting in where the whole entity at Item2? 3) Would a 2 parameter function work better than a tuple?

[halcwb]

Ad 1. Indeed copy and past mistake Ad 2. Indeed selecting the whole item, in the end this will resolved to a specific attribute. Ad 3. You mean something that would look like: ((%signPred) arg1 arg2) ?

Thanks for looking into this!

[halcwb]

Yep, it works as a multiple arg function! Great:

    module Text =

        module Predicate =    

            type Predicate = 
                | All 
                | After of DateTime
                | Before of DateTime
                | Validated of bool
                | Equals of string
                | And of Predicate * Predicate
                | Or of Predicate * Predicate
                | Not of Predicate

            let rec eval = function
                | All -> <@ fun _ _ -> true @>
                | After dt -> 
                    <@ fun _ (p: sql.dataContext.``dbo.TextSignalsEntity``) -> p.Time > dt @>
                | Before dt -> 
                    <@ fun _ (p: sql.dataContext.``dbo.TextSignalsEntity``) -> p.Time < dt @>
                | Validated b -> 
                    <@ fun _ (p: sql.dataContext.``dbo.TextSignalsEntity``) -> if b then p.ValidationTime.IsSome else true @>
                | Equals s -> 
                    <@ fun text _ -> text = s @>
                | And (p1, p2) -> <@ fun x1 x2 -> (%eval p1) x1 x2 && (%eval p2) x1 x2 @>
                | Or (p1, p2) ->  <@ fun x1 x2 -> (%eval p1) x1 x2 || (%eval p2) x1 x2 @>
                | Not p1 -> <@ fun x1 x2 -> (%eval p1) x1 x2 |> not @>

        let get (ctx: sql.dataContext) (pats : string list) parPred signPred =
            let parPred' = parPred |> Parameter.Predicate.eval
            let patsqry = pats |> Query.toQuerayable
            let patsPred = pats |> List.isEmpty
            let signPred = signPred |> Predicate.eval

            <@ fun _ ->
                query {
                    for s in ctx.Dbo.TextSignals do
                    join pt in ctx.Dbo.ParametersText 
                        on (s.TextId = pt.TextId)
                    join p in ctx.Dbo.Parameters
                        on (s.ParameterId = p.ParameterId)
                    join h in ctx.Dbo.Patients
                        on (s.PatientId = h.PatientId)
                    where (
                            pt.ParameterId = p.ParameterId &&
                            (patsPred || patsqry.Contains(h.HospitalNumber.Value)) &&
                            ((%signPred) pt.Text s) &&
                            ((%parPred') p)
                          )
                    select (h.HospitalNumber, s, p.ParameterName, p.Abbreviation, pt.Text) } @>

        let orderByTime  = <@ fun (_, s: sql.dataContext.``dbo.TextSignalsEntity``, _, _, _) -> s.Time @>
        let orderByValue = <@ fun (_, _, _, _, text) -> text @>

        let getSortedByTime (ctx: sql.dataContext) pats parPred signPred ord =
            let get = Query.sort (get ctx pats parPred signPred) orderByTime ord

            query { yield! (%get) () }

        let getSortedByValue (ctx: sql.dataContext) pats parPred signPred ord =
            let get = Query.sort (get ctx pats parPred signPred) orderByValue ord

            query { yield! (%get) () }

        let test hospNum abbr v1 v2 =
            let ctx = getDataContextWithLog ()

            let pats = [ hospNum ]
            let parPred = [ abbr ] |> Parameter.Predicate.ContainsAbbreviation 
            let signPred = Predicate.Or((v1 |> Predicate.Equals), (v2 |> Predicate.Equals)) 

            let f = <@ fun (_, _, _, _, text) -> text @>
            let get = Query.sort (get ctx pats parPred signPred) f Query.Sort.Ascending

            query { for _ in (%get) () do count }

        let test2 hospNum pName v1 v2 =
            let ctx = getDataContextWithLog ()

            let pats = [ hospNum ]
            let parPred =  [ pName ] |> Parameter.Predicate.ContainsName 
            let signPred = Predicate.Or((v1 |> Predicate.Equals), (v2 |> Predicate.Equals)) 

            let f = <@ fun (_, s: sql.dataContext.``dbo.TextSignalsEntity``, _, _, _) -> s.Time @>
            let get = Query.sort (get ctx pats parPred signPred) f Query.Sort.Ascending

            query { for _ in (%get) () do count }