CODEWITHSUNDEEP
f#
ceth
ceth

Reputation: 45295

Construct Query to database dynamically

I am currently using FSharp.Data.SqlClient, but I am interested in any solution of my task.

I have a web client and the backend written in F#. In the web client user can set 10-20 filters and make the GET request to my F# backend. The URL looks like:

http://mybackend.com/get/1/0/34/ ....

Any number in this URL is a filter. There is no filter on the corresponding field if the value of corresponding filter is zero.

Now I need to construct the SQL query in my backend. While the number of possible filters was 3-4 I was using pattern matching:

type GetEObyOrg1Org2AsutpParent = SqlCommandProvider<"SELECT * FROM ITEM WHERE ORGID1 = @org1 AND ORGID2 = @org2 AND ASUTPID = @asutp AND PARENTAUTOINCID = @parent", "name=MobileConnectionProvider", ResultType.Tuples>
type GetEObyOrg1Org2Org3AsutpParent = SqlCommandProvider<"SELECT * FROM ITEM WHERE ORGID1 = @org1 AND ORGID2 = @org2 AND ORGID3 = @org3 AND ASUTPID = @asutp AND PARENTAUTOINCID = @parent", "name=MobileConnectionProvider", ResultType.Tuples>

match (asutpid, orgId1, orgId2, orgId3) with

    | "0", _, "0", "0" -> 
        let cmd = new GetEObyOrg1Org2AsutpParent()
        cmd.Execute(org1 = orgId1, parent = parentAid)

    | "0", _, _ , "0" ->
        let cmd = new GetEObyOrg1Org2Org3AsutpParent()
        cmd.Execute(org1 = orgId1, org2 = orgId2, parent = parentAid)

But when the number of filters is greater then 3-4 it is very difficult to write pattern matching for all combinations of the parameters.

I think I need to construct the SQL query dynamically. Each non-zero parameter in the URL must add AND Field = Value expression to SQL statement. But I can not do it:

type GetEObyOrg1AsutpParent = SqlCommandProvider<Query, "name=MobileConnectionProvider", ResultType.Tuples>

Query paramter is this expression must be Literal, and I can not to construct literal expression dinamically:

[<Literal>]
let q1 = "SELECT * FROM ITEM WHERE ORGID1 = @org1 AND ASUTPID = @asutp"
[<Literal>]
let q2 = " AND PARENTAUTOINCID = @parent"

let f a = 
    match a with 
    | 1 -> q1 + q2
    | _ -> q1

[<Literal>]
let q3 = f()

What is the best way of doing it in my case ?

Upvotes: 2

Views: 190

Answers (1)

kvb
kvb

Reputation: 55185

I think in a case like this the core library's SqlDataConnection type provider might be a better fit. Then you should be able to use standard IQueryable operations to dynamically add additional WHERE clauses, something like:

type MyDb = SqlDataConnection<...>
let items = MyDb.GetContext().Item
let q1 = if asutpid = 0 then items else items.Where(fun i -> i.Asutpid = asutpid)
let q2 = if orgId1 = 0 then q1 else q1.Where(fun i -> i.OrgId1 = orgid1)
...

Upvotes: 1

Related Questions