SQLite Provider using Microsoft.Data.Sqlite library does not perform updates transactionally

[FedericoBinda]

Describe the bug
Updates from SQLProvider should be transactional. In the example below, the row in the Parent table is created even though the creation of the Child row fails because of the foreign key constraint (assuming no row with ID=100 exists in the Parent table).
Notice that SQLProvider behaves correctly when using System.Data.SQLite instead of Microsoft.Data.Sqlite as library.

To Reproduce
Database definitions

CREATE TABLE Parent(
	ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	Name TEXT
);
CREATE TABLE Child(
	ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	ParentId INTEGER NOT NULL,
	Name Text,
	FOREIGN KEY(ParentId) REFERENCES Parent(ID)
);

Program.fs

namespace BugRepro

open FSharp.Data.Sql

module Program =

    let [<Literal>] private ConnectionString = "Data Source=" + __SOURCE_DIRECTORY__ + "/BugRepro.db;foreign keys=true"
    let [<Literal>] private ResolutionPath = @"libs/win-x64"

    type sqLite = SqlDataProvider<
        Common.DatabaseProviderTypes.SQLITE,
        //SQLiteLibrary = Common.SQLiteLibrary.SystemDataSQLite,
        SQLiteLibrary = Common.SQLiteLibrary.MicrosoftDataSqlite,
        ConnectionString = ConnectionString, 
        CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL,
        ResolutionPath = ResolutionPath,
        UseOptionTypes=Common.NullableColumnType.OPTION>

    let ctx = sqLite.GetDataContext()

    [<EntryPoint>]
    let main _ = 
        let parent = ctx.Main.Parent.Create()
        parent.Name <- Some "Hello"
        ctx.Main.Child.``Create(ParentId)``(100) |> ignore
        ctx.SubmitUpdates()
        0

Project file

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <Compile Include="Program.fs" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.Sqlite" Version="8.0.2" />
    <PackageReference Include="SQLitePCLRaw.core" Version="2.1.6" />
    <PackageReference Include="SQLitePCLRaw.provider.sqlite3" Version="2.1.6" />
    <PackageReference Include="SQLProvider" Version="1.3.27" />
  </ItemGroup>

</Project>

Expected behavior
Assuming a row with ID=100 does not exist in the Parent table, the transaction should rollback and no new rows should be created in the Database.

System Info

• Windows 11
• .NET SDK 8.0.200

[Thorium]

That is weird. What if you do new transaction and wrap the datacontext inside it, something like this:

    open System.Transactions
    [<EntryPoint>]
    let main _ = 
        let scope = new Transactions.TransactionScope(Transactions.TransactionScopeAsyncFlowOption.Enabled)
        let ctx = sqLite.GetDataContext()
        let parent = ctx.Main.Parent.Create()
        parent.Name <- Some "Hello"
        ctx.Main.Child.``Create(ParentId)``(100) |> ignore
        ctx.SubmitUpdates()
        scope.Complete()
        0

[FedericoBinda]

I tried that but the parent row still gets committed to the database.

[FedericoBinda]

I think it might be a problem with Microsoft.Data.Sqlite itself. Somehow it does not seem to adhere to the System.Transaction scope. I get the same behavior in the example below: when using the System.Data.SQLite it works as expected, when using Microsoft.Data.Sqlite it creates the Parent row. Should this be a bug on Microsoft.Data.SQLite instead?

namespace TransTest

open Microsoft.Data.Sqlite
open System.Data.SQLite
open System.Transactions

module Program = 

    let [<Literal>] ConnectionString = "Data Source=" + __SOURCE_DIRECTORY__ + "/BugRepro.db;foreign keys=true"

    [<EntryPoint>]
    let main _ = 
        use trans = new TransactionScope()
        
        // Using Microsoft.Data.Sqlite
        
        use conn = new SqliteConnection(ConnectionString)
        use cmd1 = new SqliteCommand("INSERT INTO Parent(Name) VALUES ('MyName')",conn)
        use cmd2 = new SqliteCommand("INSERT INTO Child(ParentId) VALUES (1000)",conn)
        
        // Using System.Data.Sqlite
        
        // use conn = new SQLiteConnection(ConnectionString)
        // use cmd1 = new SQLiteCommand("INSERT INTO Parent(Name) VALUES ('MyName')",conn)
        // use cmd2 = new SQLiteCommand("INSERT INTO Child(ParentId) VALUES (1000)",conn)
        
        conn.Open()
        try
            cmd1.ExecuteNonQuery() |> ignore 
            cmd2.ExecuteNonQuery() |> ignore 
            trans.Complete()
        with 
        | ex ->
            printfn "%s" ex.Message 
        conn.Close()
        0

[Thorium]

Definitely it's issue in Microsoft.Data.Sql, but I'm not sure if they fix it or just close it "as designed":

I'd expect the problem is that they don't support System.Transactions as every other .NET database driver, but they have some custom way of doing their transactions.

But you never know, it could maybe easy for them to add a wrapper to their library to implement proper transactions with their current implementation.

The benefits of using System.Transactions is clear: what if you want a transaction over multiple data sources, without them you'd have to write lot of non-happy-path code that is hard to test.

[FedericoBinda]

@Thorium based on what you say, do you think it would be a lot of work to adapt SQLProvider to use Microsof.Data.Sql BeginTransaction and EndTransaction methods? From a quick look at the source, it seems relatively simple to add a condition for the transaction scope in the ProcessUpdatesAsync and ProcessUpdates methods of the SQLiteProvider class. I can also do it and send a pull request. What do you think?

[Thorium]

Sure, the main concern is will that break System.Data.SQLite.

[FedericoBinda]

Well, I was thinking on a condition that selects which transaction scope to use based on the selected SQLite library. There are already similar match expressions in the SQLite provider implementation (see example below). Using something similar it should be possible to support Microsoft.Data.Sqlite transactions without breaking System.Data.SQLite.

https://github.com/fsprojects/SQLProvider/blob/7c75b433f4274acf2e98d559360f4fe58663f542/src/SQLProvider.Runtime/Providers.SQLite.fs#L209C1-L219C70

    let getSchema name conn =
        match sqliteLibrary with
        | SQLiteLibrary.MicrosoftDataSqlite -> customGetSchema name conn
        | SQLiteLibrary.AutoSelect ->
#if NETSTANDARD
            customGetSchema name conn
#else
            getSchemaMethod.Value.Invoke(conn,[|name|]) :?> DataTable
#endif
        | _ ->
            getSchemaMethod.Value.Invoke(conn,[|name|]) :?> DataTable