I can't insert a row in a sqlserver table. I get a "Enlisting in Ambiant transactions is not supported yet"

[tforkmann]

Description

I can't insert a row in a sql table on ASP.NetCore 2.0

Repro steps

1. Get a data context
2. Opening a TransactionScope
3. Submit changes

Like this:

let saveReportdefinitiion (report:Reportdefinition) =
try
let dc = sql.GetDataContext()
use ts = new TransactionScope()
let newReport = dc.Dbo.Berichtsdefinitionen.Create ()
newReport.BerichtsId <- report.Berichtsid
newReport.Berichtsname <- Some report.Berichtsname
...

    dc.SubmitUpdates()
    ts.Dispose()
finally
    GC.Collect()
report

Expected behavior

I expect the framework to insert a row in the database.

Actual behavior

I get this error:

An unhandled exception has occurred while executing the request. System.NotSupportedException: Enlisting in Ambient transactions is not supported.
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at FSharp.Data.Sql.Providers.MSSqlServerProvider.FSharp-Data-Sql-Common-ISqlProvider-GetColumns(IDbConnection con, Table table) at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-CreateEntity(String tableName) at EnergyDashboard.BackEnd.Common.Crudsql.createReport(Reportdefinition report, Object dc)

Related information

• Used database: sql server 2008 rs2
• Operating system: Windows 10
• SQL Provider 1.1.23
• ASP.NetCore 2.0 using Giraffe

[Thorium]

Ok, sounds like a missing feature of .NET Core.
Luckily SQLProvider lets you control the transacitons:

let ctx = 
    db.GetDataContext(
         { Timeout = TimeSpan.MaxValue;
            IsolationLevel = Transactions.IsolationLevel.DontCreateTransaction
         }:FSharp.Data.Sql.Transactions.TransactionOptions)

edit: added type annotation

[tforkmann]

I tried that before.
I get that error message:

[Thorium]

open FSharp.Data.Sql.Transactions

[tforkmann]

Ok, that works.

Last question how do i parse in the runtimeconnection string then?

If I do:
let getCDB =
SqlInsert.GetDataContext(
{ Timeout = TimeSpan.MaxValue;
IsolationLevel = IsolationLevel.DontCreateTransaction
})

and then
let getCrudDB = SqlInsert.GetDataContext runtimeconnectionString

I will basically open an second datacontext and run into the same error

[Thorium]

There is an override with 2 params:

let ctx = 
    db.GetDataContext(
         runtimeconnectionString,
         { Timeout = TimeSpan.MaxValue;
            IsolationLevel = Transactions.IsolationLevel.DontCreateTransaction
         })

[tforkmann]

Awesome. Thank you!

I could add that to the docs if you want?

[Thorium]

That would be nice.