Dynamic SQL queries with F# 3.0?

f#, sql

Solution

In F# 3.0, the query is quoted automatically and so you cannot use quotation splicing (the `<@ foo %bar @>` syntax) that makes composing queries possible. Most of the things that you could write by composing queries using splicing can still be done in the "usual LINQ way" by creating a new query from the previous source and adding i.e. filtering:

// Initial query that simply selects products
let q1 = 
  query { for p in ctx.Products do 
          select p }

// Create a new query that specifies only expensive products
let q2 = 
  query { for p in q1 do
          where (p.UnitPrice.Value > 100.0M) }

This way, you can dynamically add conditions, dynamically specify projection (using `select`) and do a couple of other query compositions. However, you don't get the full flexibility of composing queries as with explicit quotations. I guess this is the price that F# 3.0 has to pay for a simpler syntax similar to what exists in C#.

In principle, you should be able to write query explicitly using the `query.Select` (etc.) operators. This would be written using explicit quotations and so you should be able to use splicing. However, I don't exactly know how the translation works, so I can't give you a working sample. Something like this should work (but the syntax is very ugly, so it is probably better to just use strings or some other techniques):

<@ query.Select(Linq.QuerySource<_, _>(ctx.Products), fun prod -> 
     // You could use splicing here, for example, if 'projection' is
     // a quotation that specifies the projection, you could write:
     //   %projection
     prod.ProductName) @>
|> query.Run

The queries in F# 3.0 are based on `IQueryable`, so it might be possible to use the same trick as the one that I implemented for C#. However, I guess that some details would be different, so I wouldn't expect that to work straight away. The best implementation of that idea is in LINQKit, but I think it won't directly work in F#.

So, in general, I think the only case that works well is the first example - where you just apply additional query operators to the query by writing multiple queries.

Problem

Original source

Related problems

• How do you compose query expressions in F#?