Transactions commit and rollback with Entity Framework, Mysql and LINQ to SQL in C#

c# entity-framework linq-to-sql mysql transactions

Question

My problem is that the transaction is not working properly it should not save the data for one table if an exception occurs during the trascation

When all the table is correct then only save data.

Consider the following:

databaseEntites objEntites = null;  
using (objEntites = new databaseEntites())  
{
     objEntites.Connection.Open();  
     using (System.Data.Common.DbTransaction transaction = 
         objEntites.Connection.BeginTransaction())
     {
         try
         {  
             customer objcust=new customer();  
             objcust.id=id;  
             objcust.name="test1";  
             objcust.email="test@gmail.com";  
             objEntites.customer.AddObject(objcust);  

             order objorder=new order();  
             objorder.custid=objcust.id;  
             objorder.amount=500;  
             objEntites.order.AddObject(objorder);  

             objEntites.SaveChanges();  
             transaction.Commit();  
         }  
         catch()  
         {  
             transaction.Rollback();  
         }  
     }  
}

In this my second table column name is not correct and on SaveChanges() giving the exception.

When i see the database and found that it saving the data for customer table which is wrong i want data will go in the customer table when all table is correct and this savechanges either save for all table or not save for any.

For this i have also try the TransactionScope

using (TransactionScope tscope = 
    new TransactionScope(TransactionScopeOption.RequiresNew))
{
......all the code here....
    objEntites.SaveChanges(false);

    tscope.Complete();
    objEntites.AcceptAllChanges();
}

But its giving the same issue as described above.

Thanks in advance.

1
3
6/28/2016 1:31:01 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

You can use database transaction or EF TransactionScope. For using database transaction it is enough to do as below:

using (var dbContextTransaction = context.Database.BeginTransaction()) 
{
  try
  {
    //Some stuff
    dbContextTransaction.Commit(); 
  } 
  catch (Exception) 
  { 
    dbContextTransaction.Rollback(); 
  }
}

And for using second way that EF TransactionScope just use easily as below:

using (var scope = new TransactionScope(TransactionScopeOption.Required)) 
    {  
      try
      {
        //Some stuff
        scope.Complete(); 
      } 
      catch (Exception) 
      { 
         //Don't need call any rollback like method
      }
    }

The point is no Rollback() method exist in the TransactionScope (against the normal ADO.NET Transaction) and Unless you call the Complete() method, the transaction do not complete and all the changes are rolled back automatically. You can see MSDN for better understand: http://msdn.microsoft.com/en-us/data/dn456843.aspx

Hope this help

3
12/17/2014 8:58:20 AM

Related Questions

Entity Framework: Is it possible to use transactions and rollbacks?

From ...MSDN...;..."SaveChanges operates within a transaction. SaveChanges will roll back that transaction and throw an exception if any of the dirty ObjectStateEntry objects cannot be persisted. "...So it seems that there is no need to to explicitly add your own transaction handling through ...TransactionScope....
entity-framework transactions
asked by Jeroen Huinink

In Entity Framework, how do you rollback a transaction?

OK...I created a sample a application like the example from the the question and afterwords I checked in the DB and no users were added....Conclusion: ObjectContext.SaveChange it's automatically a transaction....Note:... I believe transactions will be needed if executing sprocs etc.
c# entity-framework rollback savechanges transactions
asked by Shimmy

Linq to Entities vs. Linq to SQL vs. Entity Framework

There are two ways of writing queries with Entity Framework:...Linq to Entities ( ...http://msdn.microsoft.com/en-us/library/bb386964.aspx... )...Entity SQL ( ...http://msdn.microsoft.com/en-us/library/bb387145.aspx... )...L2E is not a separate technology, but rather a part of EF....Have a look at this article for a comparison: ...http://thedatafar...
ado.net entity-framework linq-to-entities linq-to-sql
asked by Yakimych

The LINQ expression node type 'Invoke' is not supported in the entity framework's LINQ to Entities.

This problem can be solved using the AsExpandable() method present in LINQKIT by Joe Albahari. He's the same creator of ...PredicateBuilder... that I see you're using....If querying with Entity Framework, change the last line to this:...return objectContext.Products.AsExpandable().Where(predicate);...You can grab LINQKIT DLL ...here... or install i...
c# entity-framework expression linq predicate
asked by Leniel Maccaferri

Connection closed when doing Transaction rollback on Mysql with Entity Framework 6 and Devart provider

I downloaded the new version 8.1.45 of Devart DotConnect and this has solved the problem completely. Now my transactions get a rollback if any query in them gets an error. And I get the correct exception in my EF6 Executionstrategy....So problem solved. Thx Devart. :)
c# devart entity-framework-6 mysql transactions
asked by Steven Engels

Enable Entity Framework 6 for MySql (C#) in WinForms of Microsoft Visual Studio 2013

I followed the instructions given by V-Shy and was having the same problem as LaRae White with the wizard shutting down on me. I'm running VS2015 and had just used the MySql installer to update to MySQL for Visual Studio v1.2.6 and Connector/NET v6.9.8....What I eventually did to get it to work was this:...Uninstall all the packages I had previousl...
c# entity-framework-6 mysql visual-studio-2013 winforms
asked by ih303

Entity Framework 6 transaction rollback

As long as you will always be using SQL Server with EF, there is no need to explicitly use the catch to call the Rollback method. Allowing the using block to automatically rollback on any exceptions will always work....However, when you think about it from the Entity Framework point of view, you can see why all examples use the explicit call to Ro...
c# entity-framework entity-framework-6 sql-server transactions
asked by Rwb

Logging the SQL generated by LINQ to SQL in Entity Framework in .net

According to ...Entity Framework Logging...:...The DbContext.Database.Log property can be set to a delegate for any method that takes a string.... Most commonly it is used with any TextWriter by setting it to the “Write” method of that TextWriter. All SQL generated by the current context will be logged to that writer. For example, the following...
entity-framework-6 logging sql sql-server
asked by Markus Safar

transaction is not getting commit in entity framework

I found an alternate way....Rather then calling Stored Procedure from dbcontext directly I am calling it like ...dbContext.Database.DbRawSqlQuery.... Now I am doing all the above thing in following order...Declaring and initializing dbcontext....Creating transaction....Executing procedure using context initialized in step 1....Saving record in data...
c# entity-framework-6
asked by Varinder

Rollback additions / removals to DBSets of the DbContext using a transaction in Entity Framework

Option #1 from ...https://code.msdn.microsoft.com/How-to-undo-the-changes-in-00aed3c4... is typically enough for most implementations where you want to roll-back changes in the DbContext without dropping and re-instantiating a Context. Generally the request would have failed and the call would have ended so the DbContext scope should end until a re...
c# dbcontext entity-framework entity-framework-6
asked by Steve Py

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow