DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Working With Lazy Loading and Eager Loading in Entity Framework Core and Entity Developer
  • Strategies for Improving the Performance of Applications Using EF Core
  • From Naked Objects to Naked Functions
  • Top Entity Framework Core Tools and Extensions

Trending

  • How to Format Articles for DZone
  • How AI Agents Are Transforming Enterprise Automation Architecture
  • 5 Subtle Indicators Your Development Environment Is Under Siege
  • Build Your First AI Model in Python: A Beginner's Guide (1 of 3)
  1. DZone
  2. Coding
  3. Frameworks
  4. Working With Transactions in Entity Framework Core and Entity Developer

Working With Transactions in Entity Framework Core and Entity Developer

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

By 
Joydip Kanjilal user avatar
Joydip Kanjilal
DZone Core CORE ·
Jul. 28, 21 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
19.8K Views

Join the DZone community and get the full member experience.

Join For Free

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:

MS SQL
 
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.

MS SQL
 
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:

 

Figure 1


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.


Figure 2



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:

C#
 
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:

C#
 
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:

C#
 
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.

Entity Framework Database dev Framework

Opinions expressed by DZone contributors are their own.

Related

  • Working With Lazy Loading and Eager Loading in Entity Framework Core and Entity Developer
  • Strategies for Improving the Performance of Applications Using EF Core
  • From Naked Objects to Naked Functions
  • Top Entity Framework Core Tools and Extensions

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!