SqlProvider recursively composing queries

Question

Using the SqlProvider type provider, I'm trying to do something whereby I recursively fold up a list of 'query criterions',

type Criterion = {
    Column : string
    Operator : string
    Value : string
}

such that the expression tree gets only gets compiled to SQL once, and I don't hit the database multiple times. I've tried a few approaches, the most successful of which is something like this

let rec eval (acc : IQueryable<SourceEntity> option) (qrys : Criterion list) =
match qrys with
|[] -> acc
|x :: xs -> let acc' = let op,valu = translateOpnValu x
                       match acc with
                       |Some acc' -> query {
                                            for elem in acc' do
                                            where (elem.GetColumn x.Column op valu)
                                            select elem 
                                     } |> Some
                       |None     -> query {
                                            for elem in ctx.Dbo.Source do
                                            where (elem.GetColumn x.Column op valu)
                                            select elem 
                                     } |> Some
            eval acc' xs

Where the function translateOpnValu is

let translateOpnValu (c:Criterion) =
     match c.Operator with
     |"%=%" -> (=%), sprintf "%%%s%%" c.Value
     |_     -> (=), c.Value

I am getting this excpetion

System.Exception: Unsupported expression. Ensure all server-side objects appear on the left hand side of predicates.  The In and Not In operators only support the inline array syntax. InvokeFast(elem.GetColumn("Source Code"), value(FSI_0006+acc'@38-2), "%BEN%")
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@1735-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr`1 )
at FSI_0006.evaluate(FSharpOption`1 acc, FSharpList`1 qrys) in F:\code_root\vs2015\F\CAMS\CAMS\scratch.fsx:line 47
at <StartupCode$FSI_0007>.$FSI_0007.main@() in F:\code_root\vs2015\F\CAMS\CAMS\scratch.fsx:line 60

If I replace the 'op' returned from translateOpnValu with an implicit operator (= / =%), it works fine.

I have a feeling it is to do with the fact the type of the operator returned is getting constrained to (string -> string -> bool), whereas the implicit operators are more generic. How could I get the translateOpnValu function to return more generic operators ? Or perhaps that's not the problem at all ...

Answer 1

@Fyodor is right -- for the SQL provider to pick up your function properly, you need to wrap it in a quotation and splice it into the query expression. Something like this should work:

let translateOpnValu (c:Criterion) =
     match c.Operator with
     |"%=%" -> <@ (=%) @>, sprintf "%%%s%%" c.Value
     |_     -> <@ (=) @>, c.Value

// ...

query {
    for elem in acc' do
    where ((%op) (elem.GetColumn x.Column) valu)
    select elem 
}