Construct Query to database dynamically

Question

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 ?

Just use a list [asutpid; orgId1; orgId2; orgId3] instead a tuple (asutpid, orgId1, orgId2, orgId3) so it is flexible in size. — Functional_S
– Functional_S, Commented Jan 9, 2016 at 10:05
Use it where? In pattern mathing expression? match [asutpid, orgId1, orgId2, orgId3] with ? But it doesn't solve my problem. In the case of 10 parameters I need to have 2^10 queries, types and pattern matching cases. — ceth
– ceth, Commented Jan 9, 2016 at 10:07
Then why not parse the filter sequence "1-0-34-42" ? Why do you append the filter(-codes) in the path/route? — Functional_S
– Functional_S, Commented Jan 9, 2016 at 10:12
I can parse the sequence 1-0-34-42. It is not the problem. But in the result I need to execure SQL query. If I will parse sequence (or anything else), my SQL Query will be constructed dinamically. Right? But SqlCommandProvider get the query as literal (and only literal). — ceth
– ceth, Commented Jan 9, 2016 at 10:15
Have you seen fsprojects.github.io/FSharp.Data.SqlClient/… — Functional_S
– Functional_S, Commented Jan 9, 2016 at 10:28

kvb · Accepted Answer · 2016-01-11 16:00:44Z

1

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)
...

answered Jan 11, 2016 at 16:00

kvb

55.2k2 gold badges94 silver badges133 bronze badges

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

Collectives™ on Stack Overflow

Construct Query to database dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related