Rollback transaction throws "There is already an open DataReader associated with this Command..." error

I'm using Entity Framework Core 2.0 on .NET Core 2.0. To recreate this issue, I made a simple console app.

// Program.cs
static void Main(string[] args)
{
  using (var dbContext = new MyDbContext())
  {
    using (var transaction = dbContext.Database.BeginTransaction())
    {
      try
      {
        var blogs = dbContext.Blogs
          .ToList(); // throws error because of schema mismatch in my Blog class

        // other stuff that may or may not make db changes

        dbContext.SaveChanges();

        transaction.Commit();
      }

      catch (Exception ex)
      {
        transaction.Rollback(); // throws second error that hides the initial error: "There is already an open DataReader..."
      }
    }
  }
}

I have intentionally made a mistake in mapping my Blog class to the database schema, so that when I do the .ToList() on dbContext.Blogs Entity Framework throws an Invalid Operation Error, something like An exception occurred while reading a database value for Blog.Name because Name is nvarchar(max) in the database, but int in my Blog class.

So now my catch statement attempts to rollback the transaction when any error occurs during the transaction, but that rollback causes another error, which is the one that ultimately gets logged, hiding the initial error.

System.InvalidOperationException occurred
HResult=0x80131509
Message=There is already an open DataReader associated with this Command which must be closed first.
Source=<Cannot evaluate the exception source>
StackTrace:
at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlInternalTransaction.Rollback()
at System.Data.SqlClient.SqlTransaction.Rollback()
at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Rollback()
at ConsoleApp.Program.Main(String[] args) in C:\Users\mitch\OneDrive\Dev\EFCore\ConsoleApp\ConsoleApp\Program.cs:line 28

Now this is a very oversimplified example, and I realize there is nothing to commit here. In reality I'm running an ASP.NET core application where I have a global transaction wrapper, so some requests may just be reads but others may be reads and updates.

Am I doing something wrong here? From what I've read this is pretty standard, yet I've been googling for two days without finding anyone having the same problem.

It seems like EF opens a DataReader when querying the database for Blogs, then an exception occurs in the middle of executing the query, so the DataReader stays open, so when doing anything to the connection after that, I get the open DataReader error. If that's the case, how am I supposed to handle errors that happen during queries? I need to make sure any updates that may have occurred are rolled back, and I need to dispose of the transaction and connection.

This is (IMO) a bug in System.Data.SqlClient.SqlTransaction and it repros on .NET Framework too. It's also a bug in EF Core as this doesn't repro on EF6. I'm not sure if this is a known bug yet, and it may be triaged differently for EF Core and SqlTransaction. Eventually your Using block will rollback the transaction.

Here's a minimal ADO.NET repro:

using System;
using System.Data.SqlClient;

namespace ConsoleApp8
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var con = new SqlConnection("Server=localhost;database=tempdb;Integrated Security=true;MultipleActiveResultsets=false"))
            {
                con.Open();
                using (var tran = con.BeginTransaction())
                {
                    var cmd = new SqlCommand("select * from sys.objects", con, tran);
                    var rdr = cmd.ExecuteReader();
                    rdr.Read();

                    tran.Rollback();
                }
            }
        }
    }
}

And a minimal EF Core repro:

using Microsoft.EntityFrameworkCore;
using System;
using System.Data.SqlClient;
using System.Linq;

namespace ConsoleApp8
{

    public class Foo
    {
        public int Id { get; set; }
    }
    public class Db : DbContext
    {
        public DbSet<Foo> Foos { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=localhost;database=EfCoreTest;Integrated Security=true;MultipleActiveResultsets=false");
            base.OnConfiguring(optionsBuilder);
        }

    }
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new Db())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();
                db.Foos.Add(new Foo());
                db.SaveChanges();
            }

            using (var db = new Db())
            { 
                var tran = db.Database.BeginTransaction();
                foreach (var foo in db.Foos)
                {
                    tran.Rollback();
                }

            }

        }
    }
}

Interestingly this error does not repro on EF6 because in EF6 DbContext.Database.BeginTransaction uses an EntityTransaction, where EF core uses a lightweight wrapper over SqlClient's SqlTransation, which has this unfortunate behavior.

To work around either add MultipleActiveResultsets=true to your connection string (which is otherwise useful in EF as it allows you to run additional queries while reading results). Or roll back your transaction with TSQL like this:

db.Database.ExecuteSqlCommand("if @@trancount > 0 rollback;");

I opened a GitHub issue to track this: https://github.com/aspnet/EntityFrameworkCore/issues/9658