How to implement transaction in Entity Framework 6 with Web Api in Asp.Net?

Question

Is there any way to implement a transaction in Entity Framework 6 with Web API in Asp.Net?

I am inserting 10 tables separately using Web API calls in a single asp.net web form. I am seeking ideas or technical feasibility suggestions using Entity framework and Web API.

This is not how SO works. You need to add code which you tried. — Rakesh Burbure, Jul 25 '18 at 07:14
Thanks guys for quick reply. I have 10 tables in a ASPX form in which the values getting inserted in a sequence with separate API call to each table. I need a approach or a technical feasibility in Entity Framework/ WebApi to achieve this. — Nanda kumar, Jul 25 '18 at 07:19
@nandhakumar the answer is "Yes, lots". You can find what you want in any EF6 tutorial. — Panagiotis Kanavos, Jul 25 '18 at 07:26
I'm sorry, I'd have to say No Way with 10 separate calls. A single call with 10 entities being updated, Yes. Since you clarified in a comment that you want to use 10 calls, the answer would be No. — John White, Jul 26 '18 at 20:56
That said, there is always a way, but in this case you would be bypassing the "stateless" nature of web applications. — John White, Jul 26 '18 at 20:58

score 2 · Answer 1 · answered Jul 25 '18 at 07:24

2

Keep in mind:

In Entity Framework, the SaveChanges() method internally creates a transaction and wraps all INSERT, UPDATE and DELETE operations under it. Multiple SaveChanges() calls, create separate transactions, perform CRUD operations and then commit each transaction.

If you really want to perform transaction its really easy:

using (var context = new SomeDbContext())
{
 using (DbContextTransaction transaction = context.Database.BeginTransaction()) {
   //do stuff
   context.SaveChanges();
   // multiple saves
   context.SaveChanges();
   transaction.Commit(); // this is one transaction
 }
}

answered Jul 25 '18 at 07:24

Bola

718
1
6
20

Thanks for the help.Would like to know whether there is any way to use the generated primary key of the first table can be used in all other subsequent tables. – Nanda kumar Jul 26 '18 at 09:45
once transaction is completed primary keys are generated in database and inserted. In EF you just need to assign that Id field of that table to wherever you need it. Eg. User user = new User(); ... Book book = new Book(). book.UserId = user.Id; When transaction is performed ID will be automatically generated by EF and inserted appropriately. (I am assuming you are talking about ForeignKeys) – Bola Jul 26 '18 at 11:34
@Nandakumar if this answer helped you please mark it as answered. Appreciate it – Bola Jul 27 '18 at 06:26
..Thanks a lot for your guidance. In addition the below gives a bit more clarityand thus helped me to achieve my requirement. Thanks again. URL --> https://stackoverflow.com/questions/17523568/entity-framework-retrieve-id-before-savechanges-inside-a-transaction – Nanda kumar Jul 27 '18 at 13:56

score 2 · Accepted Answer · answered Jul 27 '18 at 14:17

Below is the sample code snippet would give a clarity on the multiple tables Create or Update transaction. The first table column ID is the foreign key for other child tables. So if there is an exception on child table insertion, the parent table record will also get rolled back. And thus the whole table which is included in transaction will get rolled back successfully.

    public bool CreateOrUpdateEmployee(Common common)
    {
        bool IstransactionComplete= false;
        EmployeeEntities DbContext = new EmployeeEntities();

        using (var transaction = DbContext.Database.BeginTransaction())
        {
            try
            {
                if (common.Mode == Modes.CREATE) //Modes - User defined Enum
                {
                    DbContext = CreateFinanceEmployees(common, DbContext); //DbContext.savechanges() inside this method.

                    DbContext = CreateManufacturingEmployee(common, DbContext); //DbContext.savechanges() inside this method.

                    DbContext = CreateLogisticsEmployee(common, DbContext);  //DbContext.savechanges() inside this method.
                }
                else
                {
                    DbContext = UpdateFinanceEmployees(common, DbContext);  //DbContext.savechanges() inside this method.

                    DbContext = UpdateManufacturingEmployee(common, DbContext);  //DbContext.savechanges() inside this method.

                    DbContext = UpdateLogisticsEmployee(common, DbContext);  //DbContext.savechanges() inside this method.
                }

                **transaction.Commit();**

                IstransactionComplete=true;
            }
            catch (Exception ex)
            {
                **transaction.Rollback();**

                IstransactionComplete=false;
            }
            finally
            {
                transaction.Dispose();
            }
        }
        return IstransactionComplete;
    }

score 1 · Answer 3 · answered Jul 25 '18 at 07:26

Yes.

You can use the context provided by EF to create a transaction scope. At the end of the scope you can commit or rollback.

You can do something like that:

class WebController
{
    public Response restMethod()
    {
        var context = getYourDBCOntext();
        using(var dbContextTransaction = context.Database.BeginTransaction())
        {
            try {
                // do something with the DB
                context.Database.ExecuteSqlCommand( /* sql command */ );

                // save changes
                context.SaveChanges();
                // commit transaction
                dbContextTransaction.Commit();
            catch(Exception)
            {
                // Rollback in case of an error
                dbContextTransaction.Rollback();
            }
        }
    }
}

How to implement transaction in Entity Framework 6 with Web Api in Asp.Net?

3 Answers3