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

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

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)