how to use transactions in DapperFSharp?

[filippo]

Hello, I'm using Dapper.FSharp to develop an assembly that's going to be used from C# The C# code passes to the assembly a DbConnection and DbTransaction (just to give some context)

When I use the DbConnection in fsharp after opening the BeginTransaction I get the following error:

Unhandled exception. System.AggregateException: One or more errors occurred. (BeginExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.)
 ---> System.InvalidOperationException: BeginExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
  ...

A simple example to reproduce the problem:

let dbConn = new SqlConnection(connString)
dbConn.Open()
let tr = dbConn.BeginTransaction()
// get user by id
let userById (conn : IDbConnection) (UserId userId) =
    let user = table'<UserDto.UserDBRecord> "vw_UserRoleEMS"

    task {
        let! items = 
            select {
                for u in user do
                where (u.Id = userId)
            } |> conn.SelectAsync<UserDto.UserDBRecord>
        match items |> Seq.toList with
        | [] -> return None
        | item::_ -> return Some (UserDto.toDomain item)
    } |> Async.AwaitTask |> Async.RunSynchronously

userById dbConn (UserId (Guid "11111111-2222-3333-4444-555555555555")) |> printfn "User by id: \n%A"

If I remove the call to BeginTransaction() everything works. What am I doing wrong? How do I perform queries in a transaction?

[filippo]

I found the solution, but if someone has any suggestion they are welcome. I'm a newbie in FSharp. I changed the select like this passing the transaction as an argument to SelectAsync:

            let q = select {
                for u in user do
                where (u.Id = userId)
            }
            conn.SelectAsync<UserDto.UserDBRecord>(q, trans=tr)

[Dzoukr]

Hi Filippo (and welcome to the best community ever! 😄), yup, that's the right way of doing it. All methods accept transaction as second parameter, so you can use it.

[filippo]

Thanks :-) I close the issue then