How to solve Entity Framework: There is already an open DataReader, multiple context

Question

I am building a complex .Net Core application with entity framework core. I have multiple db operations in each transaction. I am running an issue with "DataReader already open, and it does not make sense at all as I am using multiple contexts. Here is dummy representations of respective classes

public class MyDbContext : DbContext
{
    private readonly PjSqlConnectionStringBuilder pjSqlConnectionStringBuilder;

    public MyDbContext(PjSqlConnectionStringBuilder pjSqlConnectionStringBuilder):base()
    {
        this.pjSqlConnectionStringBuilder= pjSqlConnectionStringBuilder;
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(this.pjSqlConnectionStringBuilder.ConnectionString);
    }

    public Transaction FetchByTranId(Decimal TranId)
    {
        string query = "TransactionById @TranId;"; // calling a store procedure
        var pId = new SqlParameter("TranId", TranId);
        return this.Transaction.FromSql(query, pId).First();
    }

    public DbSet<Transaction> Transactions { get; set; }
    public DbSet<Sales> DealNos { get; set; }
    public DbSet<DealAuditTrail> DealAuditTrails { get; set; }
    public DbSet<Deal> Deals { get; set; }
    public DbSet<Audit> Audits { get; set; }
}

Then I have two classes that use this contest as follows:

public class TransactionRepository 
{
    public Decimal dealNo;
    private Decimal transactionId;
    public Decimal TransactionId
    {
        get { return this.transactionId; }
        set
        {
            this.transactionId = value;
            Sales d;
            using (var dbContext = new MyDbContext(new PjSqlConnectionStringBuilder()))
            {
                d = dbContext.DealNos.Where(fd => fd.TransactionId == value).First();
            }
            this.dealNo = d.DealNo;
        }
    }

    public Transaction Fetch()
    {
        Transaction t;
        using (var dbContext = new MyDbContext(new PjSqlConnectionStringBuilder()))
        {
            t = dbContext.FetchByTranId(this.transactionId);
        }
        return t;
    }
}

public class AuditRepository
{

    public Task<int> LogRequest(decimal TransactionId, string json)
    {
        var obj = new Audit(TransactionId, "Request", json);
        return this.logObj(obj);
    }

    public Task<int> LogResponse(decimal TransactionId, string json)
    {
        var obj = new Audit(TransactionId, "Response", json);
        return this.logObj(obj);
    }

    private Task<int> logObj(Audit obj)
    {
        using (var dbContext = new MyDbContext(new PjSqlConnectionStringBuilder()))
        {
            dbContext.Audits.Add(obj);
            return dbContext.SaveChangesAsync();
        }

    }
}

The following is the order of executions that causing the error "“There is already an open DataReader associated with this Command which must be closed first.”.

TransactionRepository tr = new TransactionRepository();
tr.TransactionId = 1234;
Transaction  T = tr.Fetch()
.....
.....
AuditRepository ar = new AuditRepository()
var lr1 = ar.LogRequest(tr.TransactionId, T.ToString()) // Exception thrown
....
....

In my understanding, each of the DbContext is separate and not related to each other. Therefore I should not be seeing that error. Any help will be appreciated.

Answer 1

I have a hunch pointing here - this could lead to very odd things:

    private Task<int> logObj(Audit obj)
    {
        using (var dbContext = new MyDbContext(new PjSqlConnectionStringBuilder()))
        {
            dbContext.Audits.Add(obj);
            return dbContext.SaveChangesAsync();
        }
    }

In particular, note that you're disposing a context while an operation is in flow. What you need is to await the pending operation:

    private async Task<int> logObj(Audit obj)
    {
        using (var dbContext = new MyDbContext(new PjSqlConnectionStringBuilder()))
        {
            dbContext.Audits.Add(obj);
            return await dbContext.SaveChangesAsync().ConfigureAwait(false);
        }
    }

Adding the await here ensures that we don't dispose the dbContext until after the save has actually reported completion. The ConfigureAwait is largely optional; there's no need for this code to jump back to the sync-context, so it might as well not bother.

Note that you do not need to do this in LogRequest / LogResponse; they're fine as-written (although I'd probably add the Async suffix onto all 3 methods here). However, your calling code probably should await:

var lr1 = await ar.LogRequest(tr.TransactionId, T.ToString());

and since we're back at the app-tier here, we should let sync-context have a say in what happens (i.e. don't add ConfigureAwait(false) here)