Constructing computation expression programmatically

Question

I'm want to create a method which would construct an equivalent of query { ... } computation expression (using FSharp.Data.TypeProviders with either LINQ2SQL or LINQ2Entities) from provided data structure, i.e.:

Collection("customers", [ 
    Field "customerId"; 
    Collection("orders", [ 
        Field "orderId" ]) ])

For that I need to understand how the query is translated into code on the first place. Given following query as an example:

query {
    for c in db.Customers do
    select (c.CustomerID, query {
        for o in c.Orders do
        select o.OrderID
    } |> Seq.toList)
}

What would it look like without computation expression syntax?

A great introduction into computation expressions is https://fsharpforfunandprofit.com/series/computation-expressions.html. What every keyword corresponds to is well documented in MSDN - for example, `for ... in` translates into a call to the `For` function on the builder type. To get the exact code emitted, you can always use a decompiler. — Luaan, Aug 17 '16 at 12:37
Thanks Luaan. Unfortunately, I've failed when trying to make a straightforward translation using `query` expression. I've also tried to read decompiled code. This however query's body turns out to be a mixing of typeof's and byte numbers written directly into an array and called using single method - probably part of that query body is in fact a F# quotation. Nor particulary readable for humans thou. — Bartosz Sypytkowski, Aug 17 '16 at 12:45
There are many type providers for [SQL Access](http://fsharp.org/guides/data-access/#sql-data-access). F# already provides type providers [over EF](https://msdn.microsoft.com/en-us/visualfsharpdocs/conceptual/walkthrough-accessing-a-sql-database-by-using-type-providers-and-entities-%5bfsharp%5d) and [L2S](https://msdn.microsoft.com/en-us/visualfsharpdocs/conceptual/walkthrough-generating-fsharp-types-from-a-dbml-file-%5bfsharp%5d) since version 3. But .. — Panagiotis Kanavos, Aug 17 '16 at 12:48
With [FSharp.Data.SqlClient's](http://fsprojects.github.io/FSharp.Data.SqlClient/) SqlCommandProvider though you don't need the mappings at all. The provider exposes the tables as types. Think of it as the type-safe, LINQ enabled equivalent of Dapper — Panagiotis Kanavos, Aug 17 '16 at 12:50
What is the original data source? Perhaps there is already a provider for it, eg the JSON provider — Panagiotis Kanavos, Aug 17 '16 at 12:51
@Panagiotis Kanavos - all of your questions are already answered, if your read my question carefully: 1. I've mentioned the specific NuGet package I'm using (`FSharp.Data.TypeProviders` with LinqToSql in the example). 2. Also I need to return entities with nested one-to-many relationships (also presented in the example). So Dapper-like provider won't help me. Also erasing type providers will probably fail here. 3. I clearly mentioned that I need to deconstruct a `query` (all type providers exposing `query` expression work on SQL databases). — Bartosz Sypytkowski, Aug 17 '16 at 12:58
@Horusiath the most important question is "why"? It almost looks like you are trying to create a type provider from a model definition (in the MDD sense of the word). This question is more about type providers than computation expressions. Once you have the type provider, `query` will work with it — Panagiotis Kanavos, Aug 17 '16 at 13:03
@Panagiotis Kanavos - again, as I've written: I'm getting a data type in a specific shape, that I need to convert into executable query. The exact structure of incoming data is not known at compile time and may change with each request (so no, it's not problem to be solved with type provider). — Bartosz Sypytkowski, Aug 17 '16 at 13:13

score 4 · Accepted Answer · answered Aug 17 '16 at 14:44

I would not recommend generating F# queries programmatically.

The main purpose of queries is to make it possible to write nicely looking F# source code. When they are executed, the code you write is first translated to LINQ-style expression tree, which is then translated to SQL query. If you managed to translate your query specification into F# query, you'd have something like:

 +------------+    +----------+    +----------------------+    +-----+
 | Your query | -> | F# query | -> | LINQ expression tree | -> | SQL |
 +------------+    +----------+    +----------------------+    +-----+

There is nothing wrong with multiple transformations, but there is a number of things that make the path via F# query more complicated:

In your query format, things seem to be represented as strings. So, you'd need to go from strings to .NET MethodInfo just to go back to strings.
With F# queries/LINQ, you get back typed objects and that's one of the main benefits, but if you build query dynamically, you'll just get back a squence of obj values - and will have to use reflection or something like that to access them.

TL;DR - I think it will be much easier to just take your query representation and generate SQL directly. The structure of the transformation is similar to generating F# query, but you won't have to mess around with quotations and reflection.

I was thinking about direct transformation to SQL too, but there's a catch - I would have to deal with one-to-many and many-to-many relationships and detecting correct foreign keys. From LINQ perspective, both columns and junctions are just properties. — Bartosz Sypytkowski, Aug 17 '16 at 16:35
Another alternative is to compose the query from pre-defined blocks of F# code that are then just put together (see http://stackoverflow.com/questions/10158512/dynamic-sql-queries-with-f-3-0). If your vocabulary is fairly restricted, then that might work better. My thoughts were based on the fact that you had strings in your sample - which sounds very general-purpose. — Tomas Petricek, Aug 17 '16 at 21:18

score 4 · Answer 2 · answered Aug 17 '16 at 15:00

If you want to see how something desugars, just put it into a quotation. If you do this with your example, you'll see that

<@ 
    query {
        for c in db.Customers do
        select (c.CustomerID, query {
            for o in c.Orders do
            select o.OrderID
        } |> Seq.toList)
    } 
@>

is roughly equivalent to

query.Run 
    <@ query.Select(query.For(query.Source db.Customers, 
                              fun c -> query.Yield c), 
                    fun c -> 
                        c.CustomerID, 
                        query.Run <@ 
                                    query.Select(query.For(query.Source c.Orders, 
                                                           fun o -> query.Yield o), 
                                                 fun o -> o.OrderID) @> 
                             |> Seq.toList) @>

(where I've cleaned up a few extraneous variables). I agree with Tomas that there are probably better ways to achieve what you want than trying to create such an expression directly.

Neat! But where/how can you see what the quotation generates? — Dax Fohl, Aug 17 '16 at 20:32
@DaxFohl - I just used F# Interactive. You need to make sure all the identifiers are defined appropriately beforehand, and the output isn't super readable (e.g. you'll see stuff like `PropertyGet (Some (o), OrderID, [])` instead of `o.OrderID`), but otherwise it's completely straightforward. — kvb, Aug 17 '16 at 21:20

Constructing computation expression programmatically

2 Answers2