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 participate in functional composition. There is no need to prepare any complex, reflection-based mechanisms, only standard >> and |> operators are needed.

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<Post>.Left
        |> 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>.Left     
        |> 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:

    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>.Left
        |> 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"
        >> Results
            .Transform(Join<Tag>.Left) 
            .Transform(Join<Comment>.Left)
            .Combine
        |> curry 

The Results.Transform builds an object containing function, that performs first join. The next call to Transform extends this function to transform next tuple result. The function can be further extended to process even bigger tuples. The Combine method returns final result transformation function.

Combination of transformations can be specified in more hipster way:

    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"
        >> Results()
           >- Join<Tag>.Left 
           >- Join<Comment>.Left
           |> Results.Combine
        |> curry 

It's 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