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
– UriK, Commented Sep 22, 2019 at 9: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: stackoverflow.com/questions/36540777/parametric-linq-query And of course you can use dynamic/expandoobject as well. — s952163
– s952163, Commented Sep 22, 2019 at 15:14

glennsl · Accepted Answer · 2019-09-22 13:34:34Z

4

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
    }

edited Sep 22, 2019 at 13:34

answered Sep 22, 2019 at 9:57

glennsl

29.2k12 gold badges60 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

glennsl Over a year ago

Updated the answer. This was trickier than I thought, with lots of edge cases, but turned out fairly readable I think.

Collectives™ on Stack Overflow

SqlDataProvider - Compose a where clause dynamically and execute the command

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related