### SQL Query Example

Source: https://github.com/jacentino/dbfun/wiki/Basic-concepts

An example of a SQL query to select blog information.

```sql
select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
from Blog 
where id = @id
```

--------------------------------

### Defining Data Access Modules

Source: https://github.com/jacentino/dbfun/wiki/Defining-queries

Example of using query.Sql to define functions for retrieving blog and post data.

```fsharp
module Blogging = 
        
    let getBlog = query.Sql<int, Blog>(
        "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
         from Blog 
         where id = @id", 
        "id")
        
    let getAllBlogs = query.Sql<unit, Blog list>(
        "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
         from Blog") 

    let getPosts = query.Sql<int, Post list)>( 
        "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status 
         from post 
         where blogId = @blogId",
        "blogId")
```

--------------------------------

### F# Expand Function Examples

Source: https://github.com/jacentino/dbfun/wiki/Templating

Demonstrates the use of `Templating.expand` to define common SQL clauses like WHERE, JOIN, and ORDER BY, specifying initial values and separators.

```fsharp
let where = Templating.expand "WHERE-CLAUSE" " where " " and "
let join = Templating.expand "JOIN-CLAUSES" " " " "
let orderBy = Templating.expand "ORDER-BY-CLAUSE" " order by " ", "
```

--------------------------------

### Initialize BulkImportBuilder

Source: https://github.com/jacentino/dbfun/wiki/PostgreSQL-binary-import

Instantiate the BulkImportBuilder to prepare for bulk import operations. This is the starting point for configuring bulk inserts.

```fsharp
let bulkImport = BulkImportBuilder()
```

--------------------------------

### Joining Master-Detail Collections with Results.Join

Source: https://github.com/jacentino/dbfun/wiki/Defining-queries

Example demonstrating how to use Results.Join to combine collections of master and detail records, linking them by primary and foreign keys.

```fsharp
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)
```

--------------------------------

### Initialize BatchCommandBuilder

Source: https://github.com/jacentino/dbfun/wiki/Firebird-batch-commands

Instantiate the BatchCommandBuilder to start creating batch commands. Ensure the DbFun.Firebird package is configured.

```fsharp
let batch = BatchCommandBuilder()
```

--------------------------------

### Configure Database Query Runner

Source: https://github.com/jacentino/dbfun/wiki/Working-with-Dependency-Injection-frameworks

Configure the database query runner, including connection creation and query builder setup. The `run` function must be a generic method of an object.

```F#
module DB = 
    let createConnection () = new SqlConnection(connectionString) :> IDbConnection

    let query = 
        let config = QueryConfig.Default createConnection
        <Some configuration code>
        QueryBuilder(config)

    type IRunner = 
        abstract member Run: DbCall<'T> -> Async<'T>

    let runner = 
        { new IRunner with
            member __.Run f = DbCall.Run(createConnection, f)
        }
```

--------------------------------

### Inserting Tag with Parameter Builders

Source: https://github.com/jacentino/dbfun/wiki/Defining-queries

Example of using query.Sql with parameter builders to insert a tag, specifying parameter names for a tuple.

```fsharp
let insertTag = query.Sql(    
    "insert into tag (postId, name) values (@postId, @name)",
    Params.Tuple<int, string>("postId", "name"),
    Results.Unit)
```

--------------------------------

### Define and Register IBloggingService for DI

Source: https://context7.com/jacentino/dbfun/llms.txt

Define an interface for database operations and register it with a DI container. This example shows how to expose `QueryBuilder` and `IRunner` as singletons for use in ASP.NET Core controllers.

```fsharp
open DbFun.Core.Builders
open DbFun.Core
open Microsoft.AspNetCore.Mvc

type IBloggingService =
    abstract GetBlog  : int -> DbCall<Blog>
    abstract GetPosts : int -> DbCall<Post list>

type BloggingService(query: QueryBuilder) = 
    interface IBloggingService with
        member val GetBlog  = query.Sql<int, Blog>(
            "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
             from Blog where id = @id", "id")
        member val GetPosts = query.Sql<int, Post list>(
            "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
             from post where blogId = @blogId", "blogId")

module DB = 
    type IRunner = 
        abstract Run: DbCall<'T> -> Async<'T>
    let runner = 
        { new IRunner with 
            member _.Run f = DbCall.Run(createConnection, f) }

// Startup registration
builder.Services.AddSingleton<IBloggingService, BloggingService>() |> ignore
builder.Services.AddSingleton(DB.query)  |> ignore
builder.Services.AddSingleton(DB.runner) |> ignore

// Controller usage
[<ApiController; Route("[controller]")>]
type BlogController(svc: IBloggingService, runner: DB.IRunner) = 
    inherit ControllerBase()

    [<HttpGet("{id}")>]
    member this.Get(id: int) = 
        svc.GetBlog(id)
        |> DbCall.Map this.Ok
        |> runner.Run
```

--------------------------------

### Composing Queries Manually

Source: https://github.com/jacentino/dbfun/wiki/Basic-concepts

An example of manually composing two queries using a lambda function, which can be less elegant.

```fsharp
(
    let postId = insertPost post con
    insertTags postId tags con)
|> run
```

--------------------------------

### Basic SQL Query for Posts and Comments

Source: https://github.com/jacentino/dbfun/wiki/Result-transformations

This snippet shows a basic SQL query that selects posts and their associated comments separately. It's used as a starting point before applying DbFun transformations.

```fsharp
let p = any<Post>
let getManyPostsWithComments = query.Sql<int, Post seq * Comment seq>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status\n     from post\n     where blogId = @blogId;\n     select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt\n     from comment c join post p on c.postId = p.id\n     where p.blogId = @blogId",
    "blogId", 
    Results.Multiple<Post seq, Comment seq>()
)
```

--------------------------------

### Implicit Row Builder for List Result

Source: https://github.com/jacentino/dbfun/wiki/Defining-queries

Example of using an implicit row builder with Results.List to fetch a collection of Blog objects.

```fsharp
let getBlogsBefore = query.Sql(
    "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
     from Blog 
     where createdAt <= @createdTo", 
    Params.Auto<DateTime> "createdTo", 
    Results.List<Blog>())
```

--------------------------------

### Group Posts with Tags using Parent Object Key

Source: https://github.com/jacentino/dbfun/wiki/Result-transformations

Groups query results where parent-child pairs are collected using a parent object as a key. Corresponding children become items of a specified field of the parent. This example uses Results.Group with a field name.

```fsharp
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)
```

--------------------------------

### Explicit Row Builder for Tuple Result

Source: https://github.com/jacentino/dbfun/wiki/Defining-queries

Example using an explicit row builder with Results.List and Rows.Tuple to define a result set with specific column names and types.

```fsharp
let getTagUsage = query.Sql(
    "select name, count(postId) as cnt from tag group by name",
    Params.Unit,
    Results.List(Rows.Tuple<string, int>("name", "cnt"))
```

--------------------------------

### Group Posts with Tags using a Function

Source: https://github.com/jacentino/dbfun/wiki/Result-transformations

An alternate version for grouping results that uses a simple function to update the parent record. This allows for custom logic when assigning children to their parent. This example uses Results.Group with a lambda function.

```fsharp
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 }))
```

--------------------------------

### Helper 'run' Function Implementation

Source: https://github.com/jacentino/dbfun/wiki/Basic-concepts

A sample implementation of the 'run' function that manages database connection opening, execution, and closing for a query function.

```fsharp
let run (f: IConnector -> Async<'Result>): Async<'Result> = 
    async {
        use connection = createConnection()
        connection.Open()
        let connector = Connector(connection)
        return! f(connector)
    }
```

--------------------------------

### Configure DbFun Connection and QueryBuilder

Source: https://github.com/jacentino/dbfun/blob/master/README.md

Set up the database connection factory and the default query configuration. Then, initialize the QueryBuilder with the configuration.

```fsharp
let createConnection () = new SqlConnection(<your database connection string>)
let defaultConfig = QueryConfig.Default(createConnection)
let query = QueryBuilder(defaultConfig)
```

--------------------------------

### Initialize BulkCopyBuilder for Oracle

Source: https://github.com/jacentino/dbfun/wiki/Oracle-bulk-copy

Instantiate the BulkCopyBuilder to begin configuring bulk copy operations. Ensure the DbFun.OracleManaged package is used for configuration.

```fsharp
let bulkCopy = BulkCopyBuilder()
```

--------------------------------

### Sql Method with Parameter Builders

Source: https://github.com/jacentino/dbfun/wiki/Defining-queries

Demonstrates using parameter builders with the Sql method to specify parameter details, such as naming tuple items.

```fsharp
member this.Sql (
        paramSpecifier1: ParamSpecifier<'Params1>,
        paramSpecifier2: ParamSpecifier<'Params2>,
        ...
        paramSpecifierN: ParamSpecifier<'ParamsN>,
        resultSpecifier: ResultSpecifier<'Result>)
    : string -> 'Params1 -> 'Params2 -> ... -> 'ParamsN -> DbCall<'Result>
```

--------------------------------

### Integrate FastExpressionCompiler

Source: https://context7.com/jacentino/dbfun/llms.txt

Replace the default `LambdaExpression.Compile()` with FastExpressionCompiler by calling `UseFastExpressionCompiler()` on `QueryConfig`. This can significantly improve application startup time.

```fsharp
open DbFun.FastExpressionCompiler.Builders  // provides UseFastExpressionCompiler()

let config =
    QueryConfig.Default(createConnection)
        .UseFastExpressionCompiler()

let query = QueryBuilder(config)
// All subsequent query.Sql / query.Proc calls use the faster compiler
```

--------------------------------

### Running a Query Function with IConnector

Source: https://github.com/jacentino/dbfun/wiki/Basic-concepts

Shows how to execute a query function by providing an IConnector, typically managed by a helper function like 'run'.

```fsharp
let blog = getBlog id |> run
```

--------------------------------

### Configure DbFun Connection and QueryBuilder

Source: https://context7.com/jacentino/dbfun/llms.txt

Sets up the minimal configuration for DbFun by providing a connection factory, a QueryBuilder, and a run helper. The QueryBuilder is stateless and safe to store as a module-level value. The run helper opens and closes a connection around a single DbCall.

```F#
open System.Data.SqlClient
open DbFun.Core.Builders

module DB = 
    let createConnection () = new SqlConnection("Server=.;Database=MyDb;Integrated Security=True") :> IDbConnection

    // QueryConfig.Default wires up converters and the connection factory
    let config = QueryConfig.Default(createConnection)
    let query  = QueryBuilder(config)

    // run opens/closes a connection around a single DbCall
    let run f = DbCall.Run(createConnection, f)
```

--------------------------------

### DbFun Query Builder for getBlog Function

Source: https://github.com/jacentino/dbfun/wiki/Basic-concepts

Demonstrates using DbFun's QueryBuilder to generate a function for retrieving blog data.

```fsharp
let qb = QueryBuilder(config)
let getBlog =  qb.Sql<int, Blog>(
    "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
     from Blog 
     where id = @id", 
    "id")
```

--------------------------------

### Handling Multiple Results with Explicit Builders

Source: https://github.com/jacentino/dbfun/wiki/Defining-queries

Demonstrates fetching multiple distinct result sets (Post, Comment list, string list) from a single SQL command and mapping them into a tuple.

```fsharp
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<Post, Comment list, string list>())
```

--------------------------------

### Map F# POCOs with DbFun

Source: https://context7.com/jacentino/dbfun/llms.txt

Demonstrates mapping F# classes for parameters and results using DbFun. Includes both config-level registration and inline specifiers for query definitions.

```fsharp
open DbFun.Core.Builders

type User(userId: int, name: string, email: string, created: DateTime) =
    member _.UserId  = userId
    member _.Name    = name
    member _.Email   = email
    member _.Created = created

// Option 1: config-level registration
let config =
    QueryConfig.Default(createConnection)
        .AddParamPropertyMapper<User>()
        .AddRowClassMapper<User>()

// Option 2: inline specifiers
let insertUser = DB.query.Sql(
    "insert into [User] (userId, name, email, created) values (@userId, @name, @email, @created)",
    Params.Properties<User>(),
    Results.Unit)

let getUsers = DB.query.Sql(
    "select userId, name, email, created from [User] where created >= @since",
    Params.Auto<DateTime> "since",
    Results.List(Rows.Class<User>()))

async {
    let user = User(1, "Alice", "alice@example.com", DateTime.UtcNow)
    do! insertUser user |> DB.run
    let! users = getUsers (DateTime.UtcNow.AddDays(-7.0)) |> DB.run
    printfn "Found %d users" (List.length users)
}
```

--------------------------------

### Chained Joins for Posts, Comments, and Tags

Source: https://github.com/jacentino/dbfun/wiki/Result-transformations

Illustrates chaining multiple `Results.Join` operations to retrieve posts and then join them with both comments and tags.

```fsharp
let p = any<Post>
let getManyPostsWithTagsAndComments = query.Sql<int, Post seq>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status\n     from post\n     where blogId = @blogId;\n     select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt\n     from comment c join post p on c.postId = p.id\n     where p.blogId = @blogId;\n     select t.postId, t.name\n     from tag t join post p on t.postId = p.id\n     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)
```

--------------------------------

### Define SQL Queries for Blog and Posts

Source: https://github.com/jacentino/dbfun/blob/master/README.md

Define SQL queries for retrieving blog and post data. Queries are type-checked on first access and generated at runtime.

```fsharp
module Blogging =    

    let getBlog = query.Sql<int, Blog>(
        "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy\n         from Blog\n         where id = @id",
        "id")
            
    let getPosts = query.Sql<int, Post list>(
        "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status \n         from post \n         where blogId = @blogId",
        "blogId") 
```

--------------------------------

### Generate Insert Command with Explicit Parameter Specification

Source: https://github.com/jacentino/dbfun/wiki/Firebird-batch-commands

Create a batch insert command for a Blog type by providing the SQL statement and explicitly defining parameters using BatchParams.Record<Blog>().

```fsharp
let insertBlogs = BatchCommandBuilder().Command(
    "insert into blog (id, name, title, description, owner, createdAt, modifiedAt, modifiedBy) 
     values (@id, @name, @title, @description, @owner, @createdAt, @modifiedAt, @modifiedBy)",
    BatchParams.Record<Blog>())
```

--------------------------------

### Define IBloggingService with Property-Based Queries

Source: https://github.com/jacentino/dbfun/wiki/Working-with-Dependency-Injection-frameworks

Define an interface for a blogging service where query functions are properties. This approach is concise due to the `member val` construct.

```F#
type IBloggingService = 
    abstract member GetBlog: (int -> DbCall<Blog>)
    abstract member GetPosts: (int -> DbCall<List<Post>>)
    abstract member GetComments: (int -> DbCall<List<Comment>>)

type BloggingService(query: QueryBuilder) = 

    interface IBloggingService with         
        member val GetBlog = query.Sql<int, Blog>(...)
        member val GetPosts = query.Sql<int, Post list>(...)
        member val getComments = query.Sql<int, Comment list>(...)
```

--------------------------------

### F# Module for Blogging Functions

Source: https://github.com/jacentino/dbfun/wiki/Basic-concepts

Organizes multiple DbFun generated query functions within a module for better structure.

```fsharp
module Blogging =     
    let getBlog = qb.Sql<int, Blog>(...)
    let getPosts = qb.Sql<int, Post list>(...)
    let getComments = qb.Sql<int, Comment list>(...)
```

--------------------------------

### Configuring DbCall.Run for Custom Connections

Source: https://github.com/jacentino/dbfun/wiki/Working-with-multiple-connections

Adapt the `run` function to use the custom `createConnection` function, enabling `DbCall.Run` to manage connections for multiple databases.

```fsharp
let run dbCall = DbCall.Run(createConnection, dbCall)
```

--------------------------------

### Joining Posts with Comments using DbFun

Source: https://github.com/jacentino/dbfun/wiki/Result-transformations

Demonstrates joining posts with their comments using `Results.PKeyed`, `Results.Join`, and `Results.FKeyed`. The `Results.Unkeyed` function is used to drop the join key from the final result.

```fsharp
let p = any<Post>
let getManyPostsWithComments = query.Sql<int, Post seq>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status\n     from post\n     where blogId = @blogId;\n     select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt\n     from comment c join post p on c.postId = p.id\n     where p.blogId = @blogId",
    "blogId", 
    Results.PKeyed<int, Post> "id"
    |> Results.Join p.comments (Results.FKeyed<int, Comment> "postId")
    |> Results.Unkeyed)
```

--------------------------------

### Dynamic Query Construction with Templating

Source: https://context7.com/jacentino/dbfun/llms.txt

Employ Templating.define to create query templates that conditionally expand SQL clauses at execution time. Use composition operators like '>>' to combine expansion functions for clauses like WHERE, JOIN, and ORDER BY based on input criteria.

```fsharp
open DbFun.Core.Builders
open DbFun.Core

type SortField = Name | Title | Author | CreatedAt | Status
type SortDirection = Asc | Desc
type SortOrder = { field: SortField; direction: SortDirection }

type Criteria = {
    name        : string option
    title       : string option
    author      : string option
    createdFrom : DateTime option
    createdTo   : DateTime option
    statuses    : PostStatus list
    tags        : string list
    sortOrder   : SortOrder
}

let where   = Templating.expand "WHERE-CLAUSE"    " where " " and "
let join    = Templating.expand "JOIN-CLAUSES"    " "       " "
let orderBy = Templating.expand "ORDER-BY-CLAUSE" " order by " ", "

let findPosts = DB.query.Sql(
    Templating.define
        "select p.id, p.blogId, p.name, p.title, p.content, p.author,
                p.createdAt, p.modifiedAt, p.modifiedBy, p.status
         from post p {{JOIN-CLAUSES}} {{WHERE-CLAUSE}} {{ORDER-BY-CLAUSE}}"
        (Templating.applyWhen (fun c -> c.name.IsSome)
            (where "p.name like '%' + @name + '%'")
        >> Templating.applyWhen (fun c -> c.title.IsSome)
            (where "p.title like '%' + @title + '%'")
        >> Templating.applyWhen (fun c -> c.author.IsSome)
            (where "p.author like '%' + @author + '%'")
        >> Templating.applyWhen (fun c -> c.createdFrom.IsSome)
            (where "p.createdAt >= @createdFrom")
        >> Templating.applyWhen (fun c -> c.createdTo.IsSome)
            (where "p.createdAt <= @createdTo")
        >> Templating.applyWhen (fun c -> not c.statuses.IsEmpty)
            (where "p.status in (@statuses)")
        >> Templating.applyWhen (fun c -> not c.tags.IsEmpty)
            (join "join Tag t on t.postId = p.id" >> where "t.name in (@tags)")
        >> Templating.applyWith
            (fun c -> sprintf "%A %A" c.sortOrder.field c.sortOrder.direction)
            "p.createdAt asc"
            orderBy),
    Params.Record<Criteria>(),
    Results.Seq<Post>())

// Usage
async {
    let criteria = {
        name = Some "fsharp"; title = None; author = None
        createdFrom = None; createdTo = None
        statuses = [Published]; tags = []
        sortOrder = { field = CreatedAt; direction = Desc }
    }
    let! results = findPosts criteria |> DB.run
    for p in results do printfn "%s" p.title
```

--------------------------------

### Simple Bulk Insert with Type Mapping

Source: https://github.com/jacentino/dbfun/wiki/PostgreSQL-binary-import

Define a bulk import function that maps directly to a Blog type using WriteToServer. Suitable for straightforward object-to-table mappings.

```fsharp
let bulkInsertBlogs = bulkImport.WriteToServer<Blog>()
```

--------------------------------

### Define IBloggingService with Method-Based Queries

Source: https://github.com/jacentino/dbfun/wiki/Working-with-Dependency-Injection-frameworks

Define an interface for a blogging service using method-based queries. This approach requires additional fields to hold the query functions.

```F#
type IBloggingService = 
    abstract member GetBlog: int -> DbCall<Blog>
    ...

type BloggingService(query: QueryBuilder) = 

    let getBlog = query.Sql<int, Blog>(...)
    ...

    interface IBloggingService with         
        member __.GetBlog id = getBlog id
        ...
```

--------------------------------

### Bulk Insert with Custom Parameters and Table Name

Source: https://github.com/jacentino/dbfun/wiki/PostgreSQL-binary-import

Configure a bulk import for user profiles using specific column names and types, and explicitly define the target table name. This allows for more control over the import process.

```fsharp
let bulkInsertUsers = bulkImport.WriteToServer(
    BulkImportParams.Tuple<string, string, string, byte array>("id", "name", "email", "avatar"), 
    "userprofile")
```

--------------------------------

### Define Function for Running DbFun Queries

Source: https://github.com/jacentino/dbfun/blob/master/README.md

Create a helper function to execute DbFun queries using the provided connection factory.

```fsharp
let run f = DbCall.Run(createConnection, f)
```

--------------------------------

### Execute a Single Query

Source: https://github.com/jacentino/dbfun/blob/master/README.md

Execute a single SQL query using the `run` function, which manages database connection opening and closing.

```fsharp
async {
    let! blog = Blogging.getBlog 1 |> run
}
```

--------------------------------

### Generate Insert Command with Type Inference

Source: https://github.com/jacentino/dbfun/wiki/Firebird-batch-commands

Create a batch insert command for a Blog type by providing the SQL statement. The builder infers parameter types from the Blog type.

```fsharp
let insertBlogs = BatchCommandBuilder().Command<Blog>(
    "insert into blog (id, name, title, description, owner, createdAt, modifiedAt, modifiedBy) 
     values (@id, @name, @title, @description, @owner, @createdAt, @modifiedAt, @modifiedBy)")
```

--------------------------------

### F# Dynamic Query Template with DbFun

Source: https://github.com/jacentino/dbfun/wiki/Templating

Constructs a dynamic SQL query for finding posts using DbFun's templating. It conditionally applies WHERE clauses based on criteria, joins tables for tags, and applies sorting.

```fsharp
let findPosts = query.Sql (
    Templating.define 
        "select p.id, p.blogId, p.name, p.title, p.content, p.author, 
                p.createdAt, p.modifiedAt, p.modifiedBy, p.status 
         from post p
         {{JOIN-CLAUSES}} {{WHERE-CLAUSE}} {{ORDER-BY-CLAUSE}}"
        (Templating.applyWhen (fun c -> c.name.IsSome) 
            (Templating.where "p.name like '%' + @name + '%'"
        >> Templating.applyWhen (fun c -> c.title.IsSome) 
            (Templating.where "p.title like '%' + @title + '%'"
        >> Templating.applyWhen (fun c -> c.content.IsSome) 
            (Templating.where "p.content like '%' + @content + '%'"
        >> Templating.applyWhen (fun c -> c.author.IsSome) 
            (Templating.where "p.author like '%' + @author + '%'"
        >> Templating.applyWhen (fun c -> c.createdFrom.IsSome) 
            (Templating.where "p.createdAt >= @createdFrom"
        >> Templating.applyWhen (fun c -> c.createdTo.IsSome) 
            (Templating.where "p.createdAt <= @createdTo"
        >> Templating.applyWhen (fun c -> c.modifiedFrom.IsSome) 
            (Templating.where "p.modifiedAt >= @modifiedFrom"
        >> Templating.applyWhen (fun c -> c.modifiedTo.IsSome) 
            (Templating.where "p.modifiedAt <= @modifiedTo"
        >> Templating.applyWhen (fun c -> not c.statuses.IsEmpty) 
            (Templating.where "p.status in (@statuses)"
        >> Templating.applyWhen (fun c -> not c.tags.IsEmpty) 
            (Templating.join "join Tag t on t.postId = p.id" >> Templating.where "t.name in (@tags)"
        >> Templating.applyWith 
            (fun c -> sprintf "%A %A" c.sortOrder.field c.sortOrder.direction) "p.createdAt asc" 
            Templating.orderBy),
    Params.Record<Criteria>(), 
    Results.Seq<Post>()) 
```

--------------------------------

### Minimal DbFun Configuration

Source: https://github.com/jacentino/dbfun/wiki/Configuration

Defines the essential components for DbFun: a database connection function, a query builder, and a query execution function. The query builder is stateless and can be a module-level variable.

```fsharp
module DB = 
    let createConnection () = new SqlConnection(connectionString)
    let query = QueryBuilder(createConnection)
    let run f = DbCall.Run(createConnection, f)
```

--------------------------------

### Register Services in DI Container

Source: https://github.com/jacentino/dbfun/wiki/Working-with-Dependency-Injection-frameworks

Register services, the query builder, and the runner as singletons in the dependency injection container. This is suitable for stateless objects.

```F#
    builder.Services.AddSingleton<IBloggingService, BloggingService>()
    builder.Services.AddSingleton(DB.query)
    builder.Services.AddSingleton(DB.runner)
```

--------------------------------

### Use PostgreSQL Array Parameters

Source: https://context7.com/jacentino/dbfun/llms.txt

Enable native PostgreSQL array parameters by configuring the `QueryConfig` with `UsePostgresArrays()`. This allows passing F# arrays directly to PostgreSQL queries.

```fsharp
open DbFun.Npgsql.Builders

let config = QueryConfig.Default(createConnection).UsePostgresArrays()
let query  = QueryBuilder(config)

let getPostsByIds = query.Sql<int array, Post list>(
    "select p.id, p.blogId, p.name, p.title, p.content, p.author,
            p.createdAt, p.modifiedAt, p.modifiedBy, p.status
     from post p join unnest(@ids) ids on p.id = ids",
    "ids")

// Usage
async {
    let! posts = getPostsByIds [| 1; 2; 3; 5; 8 |] |> DB.run
    printfn "Found %d posts" (List.length posts)
}
```

--------------------------------

### Use DI Services in an API Controller

Source: https://github.com/jacentino/dbfun/wiki/Working-with-Dependency-Injection-frameworks

Inject and use the configured services, such as `IBloggingService` and `DB.IRunner`, within an API controller to execute database queries.

```F#
[<ApiController>]
[<Route("[controller]")>] 
type BlogController (bloggingService: IBloggingService, runner: DB.IRunner) = 
    inherit ControllerBase()

    [<HttpGet>]
    member this.Get(id: int) = 
        bloggingService.GetBlog(id)
        |> DbCall.Map this.Ok
        |> runner.Run
```

--------------------------------

### Joining with Custom Assignment Functions

Source: https://github.com/jacentino/dbfun/wiki/Result-transformations

Shows how to use custom lambda functions with `Results.Join` for more complex assignments of child collections to parent objects, such as posts with comments and tags.

```fsharp
let getManyPostsWithTagsAndComments = query.Sql<int, Post seq>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status\n     from post\n     where blogId = @blogId;\n     select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt\n     from comment c join post p on c.postId = p.id\n     where p.blogId = @blogId\n     select t.postId, t.name\n     from tag t join post p on t.postId = p.id\n     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)
```

--------------------------------

### DbFun Configuration with Custom Type Conversions

Source: https://github.com/jacentino/dbfun/wiki/Configuration

Extends the basic DbFun configuration to include custom type conversions by providing a QueryConfig object. This allows for more specialized data handling.

```fsharp
module DB = 
    let createConnection () = new SqlConnection(connectionString)
    let defaultConfig = QueryConfig.Default(createConnection)
    let actualConfig = <some-code-changing-default-config>
    let query = QueryBuilder(actualConfig)
    let run f = DbCall.Run(createConnection, f)
```

--------------------------------

### Custom Connection Creation Function

Source: https://github.com/jacentino/dbfun/wiki/Working-with-multiple-connections

Implement a function that creates `SqlConnection` instances based on the provided database discriminator. This function is used by `DbCall.Run` to obtain the correct connection.

```fsharp
let createConnection = function 
    | Main    -> new SqlConnection(<main-db-connection-string>)
    | Archive -> new SqlConnection(<archive-db-connection-string>)
```

--------------------------------

### Configure DbFun for PostgreSQL Arrays

Source: https://github.com/jacentino/dbfun/wiki/PostgreSQL-array-parameters

Use the `DbFun.Npgsql.Builders` package and call `UsePostgresArrays()` on the `QueryConfig` to enable array parameter support for PostgreSQL.

```fsharp
let config = QueryConfig.Default(createConnection).UsePostgresArrays()
```

--------------------------------

### Join Multiple Query Results by Key

Source: https://github.com/jacentino/dbfun/blob/master/README.md

Define a query to fetch multiple posts and their associated comments and tags, joining them based on keys. This uses `Results.PKeyed`, `Results.Join`, and `Results.Unkeyed` for complex result set composition.

```fsharp
let p = any<Post>
let getManyPostsWithTagsAndComments = query.Sql<int, Post seq>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status\n     from post\n     where blogId = @blogId;\n     select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt\n     from comment c join post p on c.postId = p.id\n     where p.blogId = @blogId\n     select t.postId, t.name\n     from tag t join post p on t.postId = p.id\n     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)
```

--------------------------------

### Executing Data Access with Multiple Databases

Source: https://github.com/jacentino/dbfun/wiki/Working-with-multiple-connections

Run a `dbsession` block that interacts with multiple databases. The `run` function ensures that data access functions use the correct connections based on their configured discriminators.

```fsharp
dbsession {
    let! archivedPostIds = Blogging.getArchivedPostIds()
    for ids in archivedPostIds |> List.chunkBySize batchSize do
        let! batch = Blogging.getPostsToBeArchived(ids) 
        do! Archiving.saveBatch(batch) 
        do! Blogging.removeArchivedPosts(ids)
} |> run
```

--------------------------------

### Fetch User by ID Using Class Mapping

Source: https://github.com/jacentino/dbfun/wiki/Mapping-classes

Defines an SQL query to retrieve a User by their ID, mapping the result row directly into a User class instance. This simplifies result handling.

```fsharp
let getUser = query.Sql(
    "select userId, name, email, created from User where userId = @userId", 
    Params.Int("userId"), 
    Results.List(Rows.Class<User>()))
```

--------------------------------

### Configure DbFun to Use FastExpressionCompiler

Source: https://github.com/jacentino/dbfun/wiki/Generating-code-with-FastExpressionCompiler

Replace the default compiler with FastExpressionCompiler by calling the UseFastExpressionCompiler() method on the QueryConfig. This requires adding the DbFun.FastExpressionCompiler NuGet package.

```fsharp
let config = QueryConfig.Default(createConnection).UseFastExpressionCompiler()
```

--------------------------------

### Combine and Join Multi-Result Queries

Source: https://context7.com/jacentino/dbfun/llms.txt

Handle multiple SQL result sets from a single command. Results.Combine merges a fixed set of results using the applicative operator '<*>'. Results.Join performs in-memory joins across parent-child collections.

```fsharp
open DbFun.Core.Builders

// Combine: single master + two child collections
let getPostWithDetails = DB.query.Sql<int, Post>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
     from post where id = @postId;\n     select c.id, c.postId, c.parentId, c.content, c.author, c.createdAt
     from comment c where c.postId = @postId;\n     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")

// Join: multiple masters each with their own child collections
let p = any<Post>
let getBlogPostsWithDetails = DB.query.Sql<int, Post seq>(
    "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
     from post where blogId = @blogId;\n     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;\n     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)

// Usage
async {
    let! post = getPostWithDetails 5 |> DB.run
    printfn "Post '%s' has %d comments" post.title (List.length post.comments)
    let! posts = getBlogPostsWithDetails 1 |> DB.run
    for p in posts do printfn "  %s — %d tags" p.title (List.length p.tags)
}
```

--------------------------------

### Function with IConnector Parameter

Source: https://github.com/jacentino/dbfun/wiki/Basic-concepts

Illustrates a generated function that takes an IConnector as its last parameter, allowing for effective connection management.

```fsharp
let call = getBlog id
```

--------------------------------

### Define SQL Query with Tuple Parameters

Source: https://github.com/jacentino/dbfun/blob/master/README.md

Define an SQL insert query that accepts parameters defined as a tuple, specifying parameter names explicitly. This is useful for simple, ordered parameters.

```fsharp
let insertTag = query.Sql(
    "insert into tag (postId, name) values (@postId, @name)",
    Params.Tuple<int, string>("postId", "name"),
    Results.Unit)
```

--------------------------------

### Execute a Single Query

Source: https://github.com/jacentino/dbfun/wiki/Executing-queries

Execute a single DbFun query using the DB.run function. This is suitable for simple, standalone queries.

```fsharp
let blog = getBlog id |> DB.run
```

--------------------------------

### Combining Multiple Results with Applicative Functor Style

Source: https://github.com/jacentino/dbfun/wiki/Defining-queries

Uses an applicative functor-like construct with Results.Combine and <*> to define how multiple result sets are combined into a single Post record.

```fsharp
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")
```

--------------------------------

### Define a Streaming Query with AsyncSeq

Source: https://github.com/jacentino/dbfun/wiki/Processing-large-results

Define a query function that returns an `AsyncSeq` to enable streaming results. This avoids loading the entire result set into memory.

```fsharp
let getAllComments = query.Sql<int, AsyncSeq<Comment>>(
        "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",
        "id")
```

--------------------------------

### Define Query with Array Parameter

Source: https://github.com/jacentino/dbfun/wiki/PostgreSQL-array-parameters

Define a query function that accepts an array of integers and uses `unnest` in the SQL to expand the array for joining.

```fsharp
let getPostsByIds = query.Sql<int array, Post list>(
    "select p.postid, .blogId, p.name, p.title, p.content, 
            p.author, p.createdAt, p.modifiedAt, p.modifiedBy, p.status
     from post p join unnest(@ids) ids on p.postid = ids",
    "ids")
```

--------------------------------

### Define SQL Query with Explicit Parameter and Result Types

Source: https://github.com/jacentino/dbfun/blob/master/README.md

Define an SQL insert query with explicit parameter and result type definitions using `Params.Record` and `Results.Int`. This method offers more flexibility.

```fsharp
let insertPost = query.Sql(
    "insert into post \n            (blogId, name, title, content, author, createdAt, status)\n     values (@blogId, @name, @title, @content, @author, @createdAt, @status);\n     select scope_identity()",
    Params.Record<Post>(),
    Results.Int "")
```

--------------------------------

### F# Enable Collection Parameters for Batch Operations

Source: https://github.com/jacentino/dbfun/wiki/Templating

Enables DbFun's feature to handle collection parameters by automatically creating parameters for subsequent rows, useful for batch inserts and updates.

```fsharp
let insertComments = 
    qb.HandleCollectionParams().Sql(
       Templating.define
```

--------------------------------

### Lift AsyncSeq to DbCall for dbsession Return

Source: https://github.com/jacentino/dbfun/wiki/Processing-large-results

When returning an `AsyncSeq`-based calculation from `dbsession`, lift it to `DbCall` using `DbCall.FromAsync`. This ensures proper handling within the database session context. Note that the sequence can only be iterated once.

```fsharp
dbsession {
    let! comments = getAllComments blogId
    return! comments |> AsyncSeq.toListAsync |> DbCall.FromAsync
} |> run
```

--------------------------------

### DbFun Simplified Query Composition

Source: https://github.com/jacentino/dbfun/wiki/Differences-between-DbFun-and-SqlFun

DbFun allows for simplified query composition with type inference for parameters and results.

```fsharp
let getBlog = queryBuilder.Sql<int, Blog>("select * from Blog where id = @id", "id")
```

--------------------------------

### Convert Parameter to Database Value

Source: https://github.com/jacentino/dbfun/wiki/Custom-type-conversions

Use `QueryConfig.AddParameterConverter` to convert custom types to database-compatible values when passing them as parameters.

```fsharp
let config = config.AddParameterConverter (fun (EmailAddress s) -> s)
```

--------------------------------

### DbFun Query Composition with Params and Results

Source: https://github.com/jacentino/dbfun/wiki/Differences-between-DbFun-and-SqlFun

DbFun composes query functions using parameter and result specification objects.

```fsharp
let getBlog = queryBuilder.Sql("select * from Blog where id = @id", Params.Int "id", Results.Single<Blog>())
```

--------------------------------

### Mapping Multiple Results to a Record

Source: https://github.com/jacentino/dbfun/wiki/Defining-queries

Shows how to use DbCall.Map to transform a tuple of results (post, comments, tags) into a single Post record with populated fields.

```fsharp
    >> DbCall.Map (fun (post, comments, tags) -> { post with comments = comments; tags = tags })
```

--------------------------------

### Insert User Using Mapped Properties

Source: https://github.com/jacentino/dbfun/wiki/Mapping-classes

Defines an SQL insert query that uses mapped properties from the User class for its parameters. Ensures type safety for insert operations.

```fsharp
let insertUser = query.Sql(
    "insert into User (userId, name, email, created) values (@userId, @name, @email, @created)",
    Params.Properties<User>(),
    Results.Unit)
```

--------------------------------

### Custom Type Conversions with QueryConfig

Source: https://context7.com/jacentino/dbfun/llms.txt

Extend QueryConfig with AddParameterConverter and AddRowConverter to handle domain types not directly mappable to ADO.NET primitives. Parameter converters serialize F# types to DB values, while row converters deserialize them back.

```fsharp
open DbFun.Core.Builders

// Domain wrapper type
type EmailAddress = EmailAddress of string
type UserId = UserId of int

let config =
    QueryConfig.Default(DB.createConnection)
        // Unwrap to primitive for INSERT/WHERE params
        .AddParameterConverter(fun (EmailAddress s) -> s)
        .AddParameterConverter(fun (UserId i) -> i)
        // Re-wrap when reading from DB result sets
        .AddRowConverter(EmailAddress)
        .AddRowConverter(UserId)

let query = QueryBuilder(config)

type UserRecord = { userId: UserId; name: string; email: EmailAddress }

let getUserByEmail = query.Sql<EmailAddress, UserRecord option>(
    "select userId, name, email from [User] where email = @email",
    "email")

// Usage
async {
    let! user = getUserByEmail (EmailAddress "alice@example.com") |> DB.run
    match user with
    | Some u -> printfn "Found: %A" u.userId
    | None   -> printfn "Not found"
```

--------------------------------

### Batch Archiving with Explicit Connection Management

Source: https://github.com/jacentino/dbfun/wiki/Working-with-multiple-connections

Use this pattern when copying data between databases, requiring frequent opening and closing of connections. The `dbsession` block handles connection lifecycle management.

```fsharp
dbsession {
    let! archivedPostIds = Blogging.getArchivedPostIds()
    for ids in archivedPostIds |> List.chunkBySize batchSize do
        let! batch = Blogging.getPostsToBeArchived(ids) 
        Archiving.saveBatch(batch) |> Archiving.run |> Async.start
        do! Blogging.removeArchivedPosts(ids)
} |> Main.run
```

--------------------------------

### Results.Combine and Results.Join — Multi-Result Queries

Source: https://context7.com/jacentino/dbfun/llms.txt

Multiple SQL result sets from a single command can be combined into rich F# types. `Results.Combine` with the applicative operator `<*>` merges a fixed set of results; `Results.Join` with `Results.PKeyed`/`Results.FKeyed` performs in-memory joins across parent-child collections.

```APIDOC
## Results.Combine and Results.Join — Multi-Result Queries

Multiple SQL result sets from a single command can be combined into rich F# types. `Results.Combine` with the applicative operator `<*>` merges a fixed set of results; `Results.Join` with `Results.PKeyed`/`Results.FKeyed` performs in-memory joins across parent-child collections.

```fsharp
open DbFun.Core.Builders

// Combine: single master + two child collections
let getPostWithDetails = DB.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")

// Join: multiple masters each with their own child collections
let p = any<Post>
let getBlogPostsWithDetails = DB.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)

// Usage
async {
    let! post = getPostWithDetails 5 |> DB.run
    printfn "Post '%s' has %d comments" post.title (List.length post.comments)
    let! posts = getBlogPostsWithDetails 1 |> DB.run
    for p in posts do printfn "  %s — %d tags" p.title (List.length p.tags)
}
```
```

--------------------------------

### Simplified Join with Type Inference

Source: https://github.com/jacentino/dbfun/wiki/Result-transformations

Leverages F# type inference to simplify the `Results.PKeyed` and `Results.FKeyed` definitions when joining posts with comments.

```fsharp
    Results.PKeyed<int, _> "id"
    |> Results.Join p.comments (Results.FKeyed "postId")
    |> Results.Unkeyed
```

--------------------------------

### Map User Properties to Query Parameters

Source: https://github.com/jacentino/dbfun/wiki/Mapping-classes

Configures DbFun to map properties of the User class to query parameters. Use this when inserting or updating records.

```fsharp
let config = defaultConfig.AddParamPropertyMapper<User>()
```