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

3

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

f#
.net-core
type-providers
csqldataprovider
asked on Stack Overflow Feb 4, 2018 by Mr D

2 Answers

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)
answered on Stack Overflow Feb 5, 2018 by DevNewb
0

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.

answered on Stack Overflow Feb 6, 2018 by Mr D

User contributions licensed under CC BY-SA 3.0