Working With Transactions in Entity Framework Core and Entity Developer

Entity Framework Core, a lightweight cross-platform version of the Entity Framework, gives you a standard method to retrieve data from various data sources using the Entity Framework. It supports working with transactions as well, so you can create and manage transactions elegantly.

This article presents a discussion on how we can work with transactions using Entity Framework Core and Entity Developer for data access.

Prerequisites

To be able to work with the code examples demonstrated in this article, you should have the following installed in your system:

• Visual Studio 2019 Community Edition or higher
• SQL Server 2019 Developer Edition or higher
• Entity Developer from Devart

You can download Visual Studio 2019 from here.

You can download SQL Server 2019 Developer Edition from here.

You can download a copy of Entity Developer (trial version) from here.

Creating the Database

Let’s first create the database. We’ll use a database with a few tables. Create a database named Test and then run the following script to create tables in this database:

CREATE TABLE [dbo].[Customer](

      [Id] [bigint] IDENTITY(1,1) NOT NULL,
      [CustomerName] [nvarchar](max) NOT NULL,
      [CustomerEmail] [nvarchar](max) NOT NULL,
      [CustomerPhone] [nvarchar](max) NOT NULL,
  
      CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
      (
            [Id] ASC
      )

) ON [PRIMARY]

GO

 
CREATE TABLE [dbo].[Product](
      [Id] [bigint] IDENTITY(1,1) NOT NULL,
      [ProductName] [varchar](max) NULL,
      [Description] [nvarchar](max) NULL,
      [Quantity] [bigint] NULL,
      [Price] [decimal](18, 2) NULL,

      CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
      (
            [Id] ASC
      )
)

CREATE TABLE [dbo].[Order](

      [Id] [bigint] IDENTITY(1,1) NOT NULL,
      [OrderNumber] [nvarchar](max) NULL,
      [OrderDate] [datetime] NULL,
      [OrderQuantity] [int] NULL,
      [CustomerId] [bigint] NULL,
      [ProductId] [bigint] NULL,

      CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
      (
            [Id] ASC
      )
)

GO

Next, you can run the following script to add foreign key constraints to the Order table.

ALTER TABLE [dbo].[Order]  WITH CHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerId])

REFERENCES [dbo].[Customer] ([Id])

GO

ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Orders_Customers]

GO

ALTER TABLE [dbo].[Order]  WITH CHECK ADD CONSTRAINT [FK_Orders_Product] FOREIGN KEY([ProductId])

REFERENCES [dbo].[Product] ([Id])

GO

ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Orders_Product]

GO

When you execute these scripts, the three database tables Customer, Product and Order will be created together with the relationships. Figure 1 below shows the table design:

Follow the steps mentioned in an earlier article “Working With Queries Using Entity Framework Core and Entity Developer” to create a new ASP.NET Core 5.0 project in Visual Studio. Create a new model by following the steps mentioned there as well.

Once you’ve created a new ASP.NET Core 5.0 project and an Entity Data Model using Entity Developer, here’s how your Entity Data Model would look like in the design view.

Why Do We Need Transactions?

Transactions allow for the atomic execution of multiple database operations in a single batch. The statements as part of the transaction are applied successfully to the database when the transaction is committed. Consequently, if any of the updates fail, the transaction is rolled back, and none of those modifications are committed to the database. As an example, when you place an order for an item say having an order quantity 100, the same quantity should be deducted from the Product table and the current stock quantity should be updated.

Working With Transactions

This section talks about how transactions can be used to execute interdependent transactions in a batch. Assuming that you've a DataContext instance named dbContext, you can use the following code to retrieve a connection instance from the data context instance:

var connection = dbContext.Database.GetDbConnection();

You can then check if the connection state is open and open the connection if it is not already open as shown in the code snippet given below:

if (connection.State != System.Data.ConnectionState.Open) 

{ 

 dbContext.Database.GetDbConnection().Open(); 

}

However, none of the above statements are required in EntityFrameworkCore (unless you need to write some custom code to retrieve the connection properties, etc) since a connection instance is created and opened automatically when you create an instance of your data context.

You can write the following code to execute a couple of statements as a batch in a transactional way:

using var dbContext = new TestModel();            

using var transaction = dbContext.Database.BeginTransaction();

try
{

     Order order = new Order();
     order.OrderNumber = "Ord-2021-003";
     order.ProductId = 1;
     order.CustomerId = 2;
     order.OrderDate = DateTime.Today;
     order.OrderQuantity = 100;
     dbContext.Add(order);
  
     dbContext.SaveChanges(); 

     Product product = dbContext.Products.Where(p => p.Id ==
     order.ProductId).First();
     product.Quantity -= 100;

     dbContext.SaveChanges();
     transaction.Commit();
}

 catch (Exception ex)
{
    transaction.Rollback();
    _logger.LogError(ex.Message);
    throw;
}

finally
{
     transaction.Dispose();
}

If the database supports transactions, all changes you’ve made in the entities and then called the SaveChanges method are applied to the underlying database. If an error occurs, SaveChanges will guarantee that the operation will either entirely succeed or that the database will be completely unaffected by the error.

When you call the SaveChanges method, and a transaction is already in progress, Entity Framework Core automatically creates a savepoint before any data is saved onto the database. The savepoint is automatically created by EF when SaveChanges is called, and a transaction is currently in process on the context. Savepoints are points in a database transaction to which a transaction may be rolled back if an error occurs or for any cause other than the transaction itself. The transaction is immediately rolled back to the savepoint if SaveChanges experiences an error; otherwise, it is left in the same condition as if the transaction had never begun in the first place.

The BeginTransaction method creates and starts a new transaction object. It also returns this newly created transaction instance. You can take advantage of the DbContext.Database.UseTransaction() method to use an existing transaction instance that has been created out of scope of the context object.

Working With TransactionScope

When dealing with transactions, you can take advantage of the TransactionScope class. This class provides an elegant way to mark a code snippet as taking part in a transaction without you having to interact with the transaction.

TransactionScope is adept at managing ambient transactions automatically. A transaction scope can pick and handle the ambient transactions automatically. If you are building a transaction application, it is strongly advised that you use the TransactionScope class because of its simplicity and efficiency.

When using TransactionScope, you can use transactions with multiple databases or a single database with several connection strings. When you're using TransactionScope, you can handle local as well as distributed transactions seamlessly.

The following code snippet illustrates a sample structure that you should follow when working with Transaction Scope in your applications:

try
{
    using (TransactionScope scope = new TransactionScope())
    {

        //Perform first database operation
        //Perform second database operation
        //...
         scope.Complete();
    }
}

catch (Exception ex)
{
    //Write your code here to handle exception
}

The following code listing uses the above structure and then illustrates how you can work with transaction scope:

try
{
      using (TransactionScope transactionScope = new TransactionScope())
      {
             using (var dbContext = new TestModel())
                    {

                        Order order = new Order();
                        order.OrderNumber = "Ord-2021-003";
                        order.ProductId = 1;
                        order.CustomerId = 2;

                        order.OrderDate = DateTime.Today;
                        order.OrderQuantity = 100;
                        dbContext.Add(order);

                        dbContext.SaveChanges(); 

                        Product product = dbContext.Products.Where
                        (p => p.Id ==
                        order.ProductId).First();
                        product.Quantity -= 100;
               
                        dbContext.SaveChanges();
             }
          transactionScope.Complete();
       }
}

catch (Exception ex)
{
     _logger.LogError(ex.Message);
     throw;
}

Here I’ve shown you how to work with TransactionScope using a single database.

Summary

It is a good practice not to run transactions for a long time, i.e., you should avoid using transactions if the statements to be executed by the transaction are long-running. Transactions that are dependent on user input to proceed can degrade the performance of your application.