Result transformations

ADO.NET allows to execute many SQL commands at once, potentially retrieving multiple results. Such functionality allows to load data belonging to aribtrary complex in-memory structure (e.g. DDD Aggregate).

Raw results are flat, tabular sets, but DbFun allows to combine them, to reflect parent-child or other relationships.

Basic transformations

Simplest transformation on multiple results is just making a tuple from them:

let getOnePostWithTagsAndComments = query.Sql<int, Post>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
     from post
     where id = @postId;
     select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt
     from comment c
     where c.postId = @postId
     select t.postId, t.name
     from tag t
     where t.postId = @postId",
    "postId",
    Results.Multiple(Results.Single<Post>(), Results.List<Comment>(), Results.List<string> "name")

Usually It's not very useful to implement domain logic, but can be input for another basic transformation, applying some function on the result:

   |> Results.Map(fun (post, comments, tags) -> { post with comments = comments; tags = tags })

There's also applicative functor-like transformation, that allows to apply some function on multiple results in one step:

let getOnePostWithTagsAndComments = query.Sql<int, Post>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
     from post
     where id = @postId;
     select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt
     from comment c
     where c.postId = @postId
     select t.postId, t.name
     from tag t
     where t.postId = @postId",
    "postId",
    Results.Combine(fun post comments tags -> { post with comments = comments; tags = tags })
    <*> Results.Single<Post>()
    <*> Results.List<Comment>()
    <*> Results.List<string> "name")

Joins

Results.Join allows to match items from one collections with items of another by key. Child objects become items of result parent object collection.

The keys are not taken from actual results:

• the parent collection must be a sequence of tuples of primary key specifiers and actual result values; to prepare such collection Results.PKeyed function can be used
• the child collection must be a sequence of tuples of foreign key specifiers and actual result values ; to prepare such collection Results.FKeyed function can be used
• some results can be both parents and children; such collections should be wrapped with Results.Keyed function

Usually, the final collection doesn't need key specifiers. They can be dropped by using Results.Unkeyed function.

let p = any<Post>
let getManyPostsWithTagsAndComments = query.Sql<int, Post seq>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
     from post
     where blogId = @blogId;
     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 = @blogId
     select t.postId, t.name
     from tag t join post p on t.postId = p.id
     where p.blogId = @blogId",
    "blogId", 
    Results.PKeyed<int, Post> "id"
    |> Results.Join p.comments (Results.FKeyed "postId")
    |> Results.Join p.tags (Results.FKeyed("postId", "name"))
    |> Results.Unkeyed)

Typically, target field of child collection is specified using prototype object and reflected definition, but there is also more strightforward way, based on simple functions:

let getManyPostsWithTagsAndComments = query.Sql<int, Post seq>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
     from post
     where blogId = @blogId;
     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 = @blogId
     select t.postId, t.name
     from tag t join post p on t.postId = p.id
     where p.blogId = @blogId",
    "blogId", 
    Results.PKeyed<int, Post> "id"
    |> Results.Join (fun (post, comments) -> { post with comments = comments }) (Results.FKeyed "postId")
    |> Results.Join (fun (post, tags) -> { post with tags = tags }) (Results.FKeyed("postId", "name"))
    |> Results.Unkeyed)

It's more verbose, but can be useful when simple value assignment is not enough.

Grouping

This transformation groups collection of tuples of parent-child pairs using a parent object as a key. Corresponding children become items of specified field of the parent.

let p = any<Post>
let getPostsWithTags = query.Sql(
    "select p.id, p.blogId, p.name, p.title, p.content, p.author, 
            p.createdAt, p.modifiedAt, p.modifiedBy, p.status,
            t.name as tagName
     from post p left join tag t on t.postId = p.id
     where p.id = @id",
    Params.Int "id",
    Results.List<Post, string option>("post", "tagName")
    |> Results.Group p.tags)

There is also alternate version, using simple function to update parent record:

let getPostsWithTags = query.Sql(
    "select p.id, p.blogId, p.name, p.title, p.content, p.author, 
            p.createdAt, p.modifiedAt, p.modifiedBy, p.status,
            t.name as tagName
     from post p left join tag t on t.postId = p.id
     where p.id = @id",
    Params.Int "id",
    Results.List<Post, string option>("post", "tagName")
    |> Results.Group (fun (post, tags) -> { post with tags = tags }))