CODEWITHSUNDEEP
linqf#type-providersquotations
s952163
s952163

Reputation: 6324

Parametrize F# queries based on properties

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.

Upvotes: 1

Views: 156

Answers (1)

Tomas Petricek
Tomas Petricek

Reputation: 243061

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!

Upvotes: 3

Related Questions