Wriju's BLOG

.NET, Cloud and everything

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

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
            }
        }
    }

}