How to get visibility to inserted records where DbContext is not saved yet

Question

I am implementing a service layer where I need to make sure that certain number of operations across multiple tables happen in a transaction. Here is the work flow.

1. I get an instance of HistoricalData object that need to be stored in HistoricalData table. This is accomplished in AddHistoricalData method.
2. I need to retrieve all records from HistoricalData table that include what’s inserted in #1 but can have more records. This is done in ProcessAllData method.
3. After processing all those records, the results are stored in two other tables, ProcessStatus and ProcessResults. If anything goes wrong, I need to rollback transaction include what’s inserted in operation #1.

This is how its implemented.

public class HistoricalDataService : IHistoricalDataService
    {
        private MyDbContext dbContext;
        public HistoricalDataService(MyDbContext context)
        {
            this.dbContext = context;
        }

        void AddHistoricalData(HistoricalData hData)
        {
            // insert into HistoricalData table
        }

        void ProcessAllData()
        {
            // Here we process all records from HistoricalData table insert porcessing results into two other tables
        }

        void SaveData()
        {
            this.dbContext.SaveChanges();
        }
    }

Here is how this class methods are been called.

HistoricalDataService service = new HistoricalDataService (dbcontext);
service.AddHistoricalData(HistoricalData instance);
service.ProcessAllData();
service.SaveData();

Problem with this approach is that whatever is inserted in HistoricalData table during call to AddHistoricalData method is not visible in the ProcessAllData call, because dbContext.SaveChanges is called only at the end. I am thinking that I need to somehow bring transaction scope in here but not sure how to expose a function where that transaction scope should be started?

Answer 1

There are different ways you can do this. Try this (untested, but POC)

public class HistoricalDataService : IHistoricalDataService
{
    private DbContext dbContext;
    private DbContextTransaction dbContextTransaction;

    public HistoricalDataService(DbContext context)
    {
        this.dbContext = context;
    }

    void AddHistoricalData(HistoricalData hData)
    {
        if (dbContext.Database.CurrentTransaction == null)
        {
            dbContextTransaction = dbContext.Database.BeginTransaction();
        }

        // insert into HistoricalData table
        dbContext.SaveChanges();
    }

    void ProcessAllData()
    {
        // Here we process all records from HistoricalData table insert porcessing results into two other tables
    }

    void Rollback()
    {
        if (dbContextTransaction != null)
        {
            this.dbContextTransaction.Rollback();
        }
    }

    void SaveData()
    {
        this.dbContextTransaction.Commit();
        this.dbContextTransaction.Dispose();
    }
}

Use as such:

HistoricalDataService service = new HistoricalDataService (dbcontext);

try 
{
     service.AddHistoricalData(HistoricalData instance);
     service.ProcessAllData();
     service.SaveData();
}
catch (Exception ex)
{
     service.Rollback();
}