Do I need to close the DbConnection manually when using ambient transactions with EF Core 2.1?

c# entity-framework-core transactionscope

Question

EF Core 2.1 introduced support for ambient transactions. The sample creates a new SqlConnection, manually opens it and passes it to the DbContext:

using (var scope = new TransactionScope(
    TransactionScopeOption.Required, 
    new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
    var connection = new SqlConnection(connectionString);
    connection.Open();

    try
    {
        // Run raw ADO.NET command in the transaction
        var command = connection.CreateCommand();
        command.CommandText = "DELETE FROM dbo.Blogs";
        command.ExecuteNonQuery();

        // Run an EF Core command in the transaction
        var options = new DbContextOptionsBuilder<BloggingContext>()
            .UseSqlServer(connection)
            .Options;

        using (var context = new BloggingContext(options))
        {
            context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });
            context.SaveChanges();
        }

        // Commit transaction if all commands succeed, transaction will auto-rollback
        // when disposed if either commands fails
        scope.Complete();
    }
    catch (System.Exception)
    {
        // TODO: Handle failure
    }
}

There is no call to connection.Close() though.

Is this part just missing in the sample or is the connection closed automatically somehow when the TransactionScope or the DbContext are disposed?

Edit: The call to Close/Dispose was missing. I filed a pull request and the docs are updated now.

1
3
7/11/2018 12:10:52 PM

Accepted Answer

The behavior seems to be unrelated to ambient transactions, but the answer of the question who owns the DbConnection passed to a DbContext.

EF6 DbContext constructor accepting DbConnection has bool contextOwnsConnection parameter for explicitly specifying that.

But how about EF Core? There is no such parameter on UseXyz methods accepting DbConnection.

The rule seems to be the as follows, taken from UseSqlServer method connection parameter documentation:

If the connection is in the open state then EF will not open or close the connection. If the connection is in the closed state then EF will open and close the connection as needed.

Which I read "If the passed connection is not opened, EF Core will take the ownership, otherwise the ownership is left to the caller".

Since the example calls connection.Open(); before UseSqlServer(connection), I would assume you are responsible for closing/disposing it, so I would consider the example being incorrect.

8
7/9/2018 7:32:03 PM

Popular Answer

For safeside use using so that the connection is disposed.

using(var connection = new SqlConnection(connectionString)) {
   ....
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow