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

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

Rate This
  • Comments 0

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

}

Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post