Entity Framework: Atomic Transaction (Database context)

Question

i am dealing with Entity framework 4 as the application is already been built and i have to make some up gradation in it.

Scenario: Implemented a DBTransaction(inserts data in database) in my code and once a transaction aborts in the mid way and roll back executes then on next time when same transaction executes with correct/validated data still the transaction abort by giving the previous exception. It is quite difficult to understand as i presume that the RollBack should remove the validation messages and data from the Database Context as it is SQL. Note: I am using a static DatabaseContext all through.

public class TestClass
{
    static SampleDataBaseEntities ctx = new SampleDataBaseEntities();

    public void SqlTransaction()
    {
        ctx.Connection.Open();
        using (DbTransaction transaction = ctx.Connection.BeginTransaction())
        {
            try
            {
                Student std = new Student();
                std.first_name = "first";
                //std.last_name = "last"; (This is responsible for generating the exception)
                AddTeacher();
                ctx.AcceptAllChanges();
                transaction.Commit();
            }
            catch (Exception e)
            {
                transaction.Rollback();
            }
            finally
            {
                ctx.Connection.Close();
            }
        }
    }

    public void SqlTransaction2()
    {
        ctx.Connection.Open();
        using (DbTransaction transaction = ctx.Connection.BeginTransaction())
        {
            try
            {
                Student std = new Student();
                std.first_name = "first";
                std.last_name = "last";
                AddTeacher();
                ctx.Students.AddObject(std);
                ctx.SaveChanges(false);
                transaction.Commit();
                ctx.AcceptAllChanges();
            }
            catch (Exception e)
            {
                transaction.Rollback();
                transaction.Dispose();
                ctx.Connection.Close();
            }
        }
    }

    public void AddTeacher()
    {
        Teacher t = new Teacher();
        t.first_name = "teacher_first";
        t.last_name = "teacher_last";
        t.school_name = "PUCIT";
        ctx.Teachers.AddObject(t);
        ctx.SaveChanges(false);
    }
}

class Program
{
    static void Main(string[] args)
    {
        TestClass test = new TestClass();
        test.SqlTransaction();
        test.SqlTransaction2();
    }
}

Solutions(Which i have tried): Using the SaveChanges(false). Using SaveChanges(false) and ctx.AcceptAllChanges().

Workaround: The workaround which i got is to re instantiate the DatabaseContext object.

As i have complexity issues while re instantiating the context that's why looking for a more appropriate solution. Thanks in advance.

Answer 1

"The workaround which i got is to re instantiate the DatabaseContext object."

yes this is correct for doing the transaction again. Since you are using the static data context, so for the next time you do transaction the same data context is used that cause you problem re entering-data and validation errors.

Solution: Try never to use static dataContext since you are doing transactions very sooner. So you need updated datacontext for each transaction. so always try to instantiate a new dataContext and destroy it as soon as your transaction completes. Hope it will work!

Answer 2

All problems come from not creating new instances of the context. Simplify your code to this and it should work.

using (var ctx = new SampleDataBaseEntities()) {
    Student std = new Student();
    std.first_name = "first";
    std.last_name = "last";
    ctx.Student.Add(std);
    ctx.SaveChanges();
}