Transforming query results

The query result is always tabular. Even though ADO.NET allows to execute many SQL commands at once, their in-memory representation form much more complex, hierarchical structures.

Fortunately, queries are functions and as such, they can be composed with other functions using >> and |> operators. Thus, result transformations are functions too:

• one group is explicit but rather verbose
• another group, based on conventions, uses reflection and code generation to access key fields, etc...

Most transformations fall into four categories.

Join two or more tabular results by some key

Function, returning two results, must return tuple:

    let getPostsWithTags: int -> DataContext -> Post * Tag list = 
        sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
             select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id"

The result can be transformed with the join function:

    let getPostsWithTags: int -> DataContext -> Post list = 
        sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
             select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id"
        >> join (fun p -> p.id) (fun t -> t.postId) (fun p t -> { p with tags = t })
        |> curry  

One drawback of the usage of >> operator is, that it accepts one-arg functions only. Therefore, a call to the curry function is needed to make possible use of two parameters.

The query function may be async. In this case, the transformation looks quite similar:

    let getPostsWithTags: int -> DataContext -> Post list Async = 
        sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
             select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id"
        >> mapAsync (join (fun p -> p.id) (fun t -> t.postId) (fun p t -> { p with tags = t }))
        |> curry 

When following some simple naming conventions, i.e.

• the join key has Id, <Parent>Id or '_id` name in parent record (case insensitive),
• the join key has <Parent>Id or <Parent>_id in child record (case insensitive),
• exactly one proprty of <Child> list type exists in parent record,

the more concise join version can be used:

    let getPostsWithTags: int -> DataContext -> Post list Async = 
        sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
             select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id"
        >> mapAsync join<_, Tag>
        |> curry 

Group rows of one denormalized result by some columns

The result, that is a product, can be represented as a list of tuples:

    let getPostsWithTags: int -> DataContext -> (Post * Tag) list = 
        sql "select p.id, p.blogId, p.name, p.title, p.content, p.author, p.createdAt, p.modifiedAt, p.modifiedBy, p.status,
                    t.postId as item_postId, t.name as item_name
             from post p left join tag t on t.postId = p.id
             where p.id = @id" 

It can be transformed using grouping by first item of a tuple:

    let getPostsWithTags: int -> DataContext -> Post list = 
        sql "select p.id, p.blogId, p.name, p.title, p.content, p.author, p.createdAt, p.modifiedAt, p.modifiedBy, p.status,
                    t.postId as item_postId, t.name as item_name
             from post p left join tag t on t.postId = p.id
             where p.id = @id" 
        >> group (fun p t -> { p with tags = aliasedAsItem t })       
        |> curry

In the example above, there are two 'name' columns. To make it possible to use aliasing, we use aliasedAsItem function, that allows to use item_{column name} aliases for detail data. There are also group3 and group4 functions. There is, of course, convention-based approach, that works when no tranformation of child list is needed and only one child list collection appears in a parent record:

    let getPostsWithTags: int -> DataContext -> Post list = 
        sql "select p.id, p.blogId, p.name, p.title, p.content, p.author, p.createdAt, p.modifiedAt, p.modifiedBy, p.status,
                    t.postId, t.name as tagName
             from post p left join tag t on t.postId = p.id
             where p.id = @id" 
        >> group<_, Tag>    
        |> curry
``
## Consolidate many results to one root object

When reading one object and some detail data, no joining or grouping is needed. In this case, we can use ordinary function:
```fsharp 
    let getBlogWithPosts: int -> DataContext -> Blog = 
        sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
             from Blog 
             where id = @id;
             select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status 
             from post 
             where blogId = @id"
        >> (fun b p -> { b with posts = p })
        |> curry

As with join, when exactly one property of <Child> list exists, more concise way can be used:

    let getBlogWithPosts: int -> DataContext -> Blog = 
        sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
             from Blog 
             where id = @id;
             select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status 
             from post 
             where blogId = @id"
        >> update<_, Post>
        |> curry

Combining many transformations

Sometimes more, than two results should be joined. In this case we can use a mechanism, that combine joins:

    let getPostsWithTagsAndComments: int -> DataContext -> Post list = 
        sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status from post where blogId = @id;
             select t.postId, t.name from tag t join post p on t.postId = p.id where p.blogId = @id;
             select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt from comment c join post p on c.postId = p.id where p.blogId = @id"
        >> (join<_, Tag> >-> join<_, Comment>)
        |> curry 

The >-> operator combines two functions by passing result of the first one as a first argument of the second one:

    let (>->) f g = fun (t1, t2) -> g(f t1, t2)

It allows to build a chain of function, that transform result of the same type.

There is also another operator: >>-, that passes result of the second function as a second argument of the first function:

    let (>>-) f g = fun (t1, t2) -> f(t1, g(t2))

It allows to define deeper hierarchies.

It's also possible to mix joins, groups and updates in one result transformation.

Transforming stored procedure result tuples

There are two functions transforming tuples, returned from stored procedures:

    resultOnly (map: 't -> 'u) (_: int, (), result: 't)

that ignores return code and output parameters, and returns query results with possible mapping function, and:

    outParamsOnly (_: int, outParams, ())

that ignores return code and results, and returns output parameters. They can be called the same way, as other transformations:

    let findPosts: (PostSearchCriteria * SignatureSearchCriteria) -> DataContext -> Post list =
        storedproc "FindPosts"
        >> resultOnly id 
        |> curry

or in asynchronous mode:

    let findPosts: (PostSearchCriteria * SignatureSearchCriteria) -> DataContext -> Post list Async =
        storedproc "FindPosts"
        >> mapAsync (resultOnly id)
        |> curry