f# SqlDataProvider .Net Core 2.0 - enlisting in ambient is not supported

Question

By day I'm a C# programmer, but an F# enthusiast.

whist doing some tutorial (suave) I stumbled upon this error

System.NotSupportedException
 HResult=0x80131515
 Message=Enlisting in Ambient transactions is not supported.
 Source=System.Data.SqlClient
 StackTrace:
 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-ProcessUpdates(IDbConnection con, ConcurrentDictionary`2 entities, TransactionOptions transactionOptions, FSharpOption`1 timeout)
 at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.f@1-52(SqlDataContext __, IDbConnection con, Unit unitVar0)
 at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges()
 at Program.main(String[] argv) in C:\Users\M_R_N\source\repos\ConsoleApp2\ConsoleApp2\Program.fs:line 34

yet the code seems so trivial, I cant believe it doesn work, we seem to be able to read data from a (SQL express) database, but not write to it (or at least not delete, I've not tried adding). I don't really know what an ambient transaction is, I'm actually not to concerned about transactional behaviour I simply want to select some data, update it or delete it.

This is all the code....

open System
open FSharp.Data.Sql
[<Literal>]
let ConnectionString = 
 "Data Source=(localdb)\ProjectsV13;Initial Catalog=suavemusicstore;Integrated Security=SSPI;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
type Sql =
 SqlDataProvider<
 ConnectionString = ConnectionString,
 DatabaseVendor = Common.DatabaseProviderTypes.MSSQLSERVER>
type DbContext = Sql.dataContext
type Album = DbContext.``dbo.AlbumsEntity``
type Genre = DbContext.``dbo.GenresEntity``
let getAlbum id (ctx : DbContext) : Album option =
 query {
 for album in ctx.Dbo.Albums do
 where (album.AlbumId = id)
 select album
 } |> Seq.tryHead
[<EntryPoint>]
let main argv =
 let ctx = Sql.GetDataContext()
 match (getAlbum 2 ctx) with
 | Some(album) -> 
 album.Delete()
 ctx.SubmitUpdates() // EXCEPTION thrown here
 0
 | _ -> 0

is there a workaround? its the first time I've used type providers and core, yet it seems that you cannot write a simple CRUD app.

this HAS been reported elsewhere, mostly in C# EF apps, where I think there is more scope to work around the problem (maybe).

Any ideas how to work around it? I've tried upgrading/downgrading various nugget packages, to no avail

• MrD at KookerellaLtd: just to be clear the original tutorial uses postgres, but I decided to use my sql express database instead. I will try to use postgres, though it would seem odd if an MS technology worked in postgress but not in SQL express
• MrD at KookerellaLtd: tried doing the postgres thing....that ends in a different world of pain, with unresolved references, that appear to be an ongoing problem.
• MrD at KookerellaLtd: I'm coming to the conclusion that all is not well here.....if I'm not wresting compile dependencies (back to dll hell?) I wrestling runtime dependencies (back to dll hell?)....feels like C development 20 years ago...
• s952163: Have you tried Dapper or EF Core?

Answer 1

I've had the same issue not that long ago on my toy F# project. I did not find any proper solution and ended up ignoring transactions entirely. This doesn't solve the underlying issue, but for me it was enough (the project is mainly for learning purposes).

let TransactionOptions = {IsolationLevel = IsolationLevel.DontCreateTransaction; Timeout = TimeSpan.FromSeconds(1.0)}
let dbContext = Sql.GetDataContext(TransactionOptions)

Answer 2

I don't know if the above works, but it seems like a reasonable workaround. I DID fix my problem, but by reverting to a framework based console app template rather than the core one.