Parametrize F# queries based on properties

Question

I'm looking to factor out some common queries over several tables. In a very simple example all tables have a DataDate column, so I have queries like this:

let dtexp1 = query { for x in table1 do maxBy x.Datadate }
let dtexp2 = query { for x in table2 do maxBy x.Datadate }

Based on a previous question I can do the following:

let mkQuery t q  = 
        query { for rows in t do maxBy ((%q) rows) }

let getMaxDt1 = mkQuery table1 (<@ fun q -> q.Datadate @>)   
let getMaxDt2 = mkQuery table2 (<@ fun q -> q.Datadate @>)

I would be interested if there are any other solutions not using quotations. The reason being is that for more complicated queries the quotations and the splicing become difficult to read.

This for example won't work, obviously, as we don't know that x has property DataDate.

let getMaxDt t = query { for x in t do maxBy x.Datadate }

Unless I can abstract over the type of table1, table2, etc. which are generated by SqlProvider.

score 3 · Accepted Answer · answered Jul 23 '17 at 13:30

The answer very much depends on what kind of queries you need to construct and how static or dynamic they are. Generally speaking:

LINQ is great if they are mostly static and if you can easily list all the templates for all queries you'll need - the main nice thing is that it statically type checks the queries
LINQ is not so great when your query structure is very dynamic, because then you end up composing lots of quotations and the type checking sometimes gets into the way.

If your queries are very dynamic (including selecting the source dynamically), but are not too complex (e.g. no fancy groupings no fancy joins), then it might be easier to write code to generate SQL query from an F# domain model.

For your simple example, the query is really just a table name and aggregation:

type Column = string
type Table = string

type QueryAggregate =
  | MaxBy of Column

type Query = 
  { Table : Table
    Aggregate : QueryAggregate }

You can then create your two queries using:

let q1 = { Table = "table1"; Aggregate = MaxBy "Datadate" }
let q2 = { Table = "table2"; Aggregate = MaxBy "Datadate" }

Translating those queries to SQL is quite simple:

let translateAgg = function
  | MaxBy col -> sprintf "MAX(%s)" col

let translateQuery q =
  sprintf "SELECT %s FROM %s" (translateAgg q.Aggregate) q.Table

Depending on how rich your queries can be, the translation can get very complicated, but if the structure is fairly simple then this might just be an easier alternative than constructing the query using LINQ. As I said, it's hard to say what will be better without knowing the exact use case!

thx. that's an interesting approach. Will think it over. Yes, the queries are somewhat dynamic and I can handle them with quotations. But was curious if there is anything to make it more idiomatic/easier, with generics or interfaces, or (gasp) SRTP as a last resort. — s952163, Jul 23 '17 at 14:45
If you go down the 'string concat' road: make sure you don't put your application into SQL injection danger zone. — CaringDev, Jul 23 '17 at 22:46
@CaringDev Good point. When using `SqlCommand` to run the resulting query, the best option is to add parameters via `SqlParameter` (see https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types). That way, user input will never be concatenated with the query string and you should be fine! — Tomas Petricek, Jul 24 '17 at 01:38

Parametrize F# queries based on properties

1 Answers1