FSharp Dapper with FSharp.Interop.Dynamic

mysql.fsx

open MySql.Data.MySqlClient
open FSharp.Interop.Dynamic
open Dapper
open System
open System.Dynamic

type GList<'a> = System.Collections.Generic.List<'a>

type Ask() =
    member val AskId = 0 with get,set
    member val UserId = 0 with get,set
    member val Title = "" with get,set
    override self.ToString() = (sprintf "AskId: %d UserId: %d Title: %s" self.AskId self.UserId self.Title)

type User() =
    member val UserId = 0 with get,set
    member val UserName = "" with get,set
    member val Asks = new GList<Ask>() with get,set
    override self.ToString() = (sprintf "UserId: %d UserName: %s Ask: %A" self.UserId self.UserName self.Asks)

let getConnection (addr, user, pass, db, port) =
    let builder = new MySqlConnectionStringBuilder()
    builder.Server <- addr
    builder.UserID <- user
    builder.Password <- pass
    builder.Database <- db
    builder.Port <- port
    let conn = new MySqlConnection(builder.ConnectionString)
    conn.Open()
    conn

let conn = getConnection("","","","",3306)

// simple query
let query =  ExpandoObject()
query?userId <- 1
let obj = conn.QueryFirst("select * from users where userid = @userId", query)
let objs = conn.Query("select * from users limit 10")
printfn "%A" obj?username
objs
|> Seq.iter (fun e -> printfn "%s" e?username)

// one to many
let dic = new Dictionary<int, User>()
let sql =
    """
    SELECT
        u.userid,
        u.username,
        a.askid,
        a.title,
        u.userid
    FROM users AS u
    LEFT JOIN asks AS a ON u.userid = a.userid AND a.delete=0
    WHERE u.userid = 1
    LIMIT 100
    """
let users =
    conn.Query<User ,Ask, User>(sql,
        (fun u a ->
            match dic.TryGetValue(u.UserId) with
            | (true, us) -> us.Asks.Add(a);us
            | (false, _) ->  u.Asks.Add(a);dic.Add(u.UserId, u);u
        ),
        splitOn = "askid")
printfn "%A" (dic.[1].Asks |> Seq.toList)

conn.Close()