SqlDataProvider - Compose a where clause dynamically and execute the command

Question

I have the following requirement:

Query a SQL table with a dynamically generated where clause that should be composed from a list at run time.

[<Literal>]
let connectionString = "Data Source=..."

type sql = SqlDataProvider< 
                  ConnectionString = connectionString,
                  DatabaseVendor = Common.DatabaseProviderTypes.MSSQLSERVER,
                  UseOptionTypes = true>
let ctx = sql.GetDataContext()

type Key = {k:string;v:string}

let findCustomersByKeys (keys:Key list) =
    query{
         for c in ctx.Dbo.Customers do
         where (keys.Any(fun k -> c.k = k.k && c.v = k.v))//this is what i wish i could do
         select c
    }

Is there a way to do it in F# with SqlDataProvider? Any other technique?

I mean that only in run-time the size of the list will be known and the where clause could be compiled. — UriK, Sep 22 '19 at 09:30
when you hear dynamically generated, start entertaining an ORM, like Dapper. The other way is to generate your queries using quotations as it is suggested in the answer but let's face it it, everybody will hate you for it. I did it once too: https://stackoverflow.com/questions/36540777/parametric-linq-query And of course you can use dynamic/expandoobject as well. — s952163, Sep 22 '19 at 15:14

glennsl · Accepted Answer · 2019-09-22T13:34:34.293

You can use quotations to construct the predicate dynamically, and splice that directly into the query expressions since query expressions are actually compiled into quotations themselves.

The predicate is built by folding over the keys, recursively splicing or-conditions onto an initial condition of false. But because we can't close over c here, we also need to wrap each condition in a function and thread the argument through the predicate chain.

open Microsoft.FSharp.Quotations

type Key = {k:string;v:string}

let findCustomersByKeys (keys:Key list) =
    let predicate = 
        keys
            |> List.fold
                (fun (acc: Expr<Key -> bool>) k ->
                    <@ fun c -> (%acc) c || c.k = k.k && c.v = k.v @>)
                <@ fun c -> false @>

    query {
         for c in ctx.Dbo.Customers do
         where ((%predicate) c)
         select c
    }

Updated the answer. This was trickier than I thought, with lots of edge cases, but turned out fairly readable I think. — glennsl, Sep 22 '19 at 13:14

SqlDataProvider - Compose a where clause dynamically and execute the command

1 Answers1