ADO.NET Entity Framework : Executing TSQL and DbContext under same Transaction

  • Article

There might be a requirement that we need to run a RAW SQL statement and DbContext update under same connection and would like to have the Transaction in place. Let’s see how we can achieve this easily

SQL Table

 

CREATE TABLE [dbo].[Emp](

   [Id] [int] IDENTITY(1,1) NOT NULL,

   [FirstName] [varchar](50) NOT NULL,

    [LastName] [varchar](50) NULL,

 CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED 

(

   [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

C# Code

 private static void TransactionSample()

{

    int id = 2299;

    var trOptions = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted };

    using (var tran = new TransactionScope(TransactionScopeOption.Required, trOptions))

    {

        using (var ctx = new TestDBEntities())

        {

            try

            {

                ctx.Database.Connection.Open();

                var sqlCommand = ctx.Database.Connection.CreateCommand();

                //TODO: You may add some just char to produce bad query

                sqlCommand.CommandText = "UPDATE Emp SET FirstName ='FirstName1' WHERE Id=" + id.ToString();

                sqlCommand.ExecuteNonQuery();

                //TODO: you may add some wrong id so that it will fail

                var empUpdate = ctx.Emps.Where(p => p.Id == id).First();

                empUpdate.LastName = "LastName1";

                ctx.SaveChanges();

                        

                tran.Complete();

            }

            catch

            {

                Console.WriteLine("Some error occured");

                //Fail the Transaction

            }

        }

    }

}