2

I'm using Entity Framework (Code first, or rather code only, database first) as my DAL. Among other things it's used in WCF service that inserts some new data to database. For most cases the performance is ok, but some of the endpoints in the service are responsilble for importing larger volumes of data. For such cases i'm using StoredProcedures with SQL Table Type parameter, and this works ok as well. The code ends up being something like this:

    public void Import(ImportItem item)
    {
        using(var tran = new TransactionScope())
        using (var dbContext = new ItemContext())
        {
            var entity = new Entity(); //Fill some values from item
            dbContext.Items.Add(entity);
            dbContext.SaveChanges();
            dbContext.ImportItemRows(item.Rows); //This executes the stored procedure and insert few thousands of rows
            tran.Complete();
        }
    }

What troubles me about this, is that I always need to remember to wrap this in transaction. I'm thinking about something like this: when I call the ImportItemRows, i could just add the call to some list, and execute it later, when I actually call SaveChanges()

public class ItemContext : DbContext
{
    public ItemContext()
    {
        storedProcedures = new List<StoredProcedure>();
    }
    public virtual IDbSet<object> Items { get; set; }

    private IList<StoredProcedure> storedProcedures { get; set; }

    public void ImportItemRows(IEnumerable<object> items)
    {
        storedProcedures.Add(new ImportItemRowsStoredProcedure(items));
    }

    public override int SaveChanges()
    {
        using (var transaction = new TransactionScope())
        {
            var saveChangesResult =  base.SaveChanges();
            foreach (var sp in storedProcedures)
            {
                sp.ExecuteNonQuery(this);
            }
            transaction.Complete();
            return saveChangesResult;

        }

    }
}

Then I could just use it as follows:

    public void Import(ImportItem item)
    {
        using (var dbContext = new ItemContext())
        {
            var entity = new Entity(); //Fill some values from item
            dbContext.Items.Add(entity);
            dbContext.ImportItemRows(item.Rows); 
            dbContext.SaveChanges();

        }
    }

Is there something wrong with this approach? Is it worth the trouble?

  • 1
    There is some implicit transaction capability in EF6, but it's strictly for CRUD operations. The problem here is that there's no way for EF to know whether you want a transaction or not without you telling it explicitly that you do want one, which is precisely what the TransactionScope is there for. It's just two additional function calls; there is such a thing as too much reduction. – Robert Harvey Apr 5 '15 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.