How to use transactions with Entity Framework?

Question

Let's say I have two tables, A and B, whereA has a foreign key pointing to B.

Here's how the database might look:

A:                         |   B:
                           |
id (int) | b (foreign B)   |   id (int)
---------+--------------   |   --------
1        | 2               |   2
2        | 5               |   5
3        | 2               |

I now wish to insert both a new object of type A and a new object of type B into the database, with the new A referencing the new B. Also, they must both be inserted within the same transaction, as I cannot allow e.g. that only the B gets inserted, but A fails.

However, I don't know how to insert A without first separately inserting B, as I only get to know B.id after B is inserted: B.id gets assigned by the database as an incrementing primary key.

This is what I'd like to write, but cannot:

b = new B { };
a = new A { b = b };
db.Add(b);
db.Add(a);
db.SaveChanges()

How can I accomplish this using Entity Framework?

Thanks!

Answer 1

It sounds like your models do not have navigation properties, and instead only have the Foreign Key property.

In Entity Framework, you are able to leverage navigation properties to allow EF to perform much of the background work of relationship management for you. For example:

public class A {
  public int Id {get;set;}
  public int BId {get;set;}
  public virtual B B {get;set;}
}

public class B{
  public int Id {get;set;}
  public virtual ICollection<A> AColl {get;set;}
}

The following code would perform a single SQL transaction to insert both a and a.B into the database, and when successful, populate a.Id, a.BId, and b.Id automatically.

//Add A and B to the database

A a = new A();
a.B = new B();
db.Add(a);
db.SaveChanges();

Note that having navigation properties also allows you to perform other useful tasks, like fetching all the A items along with a B, like so:

var b = db.B.Find(someId).Include(b => b.AColl);
// we now have all the A records that have B FK
foreach (A a in b.AColl){
  //do something
}

Answer 2

As stated by @CodeCaster, EF using transactions by default. If you want more granular control, wrap it in a using (TransactionScope). See https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx. EDIT: As to why your code posted won't work, you need to have a navigation property from A to B, then you would just assign the value of said property to B when you instantiate an object of type A.

Answer 3

Maybe a stored procedure call from your code , a procedure can be something like ......

CREATE PROCEDURE usp_InsertRow
AS
BEGIN
  SET NOCOUNT ON;
    DECLARE @New_ID INT;
 BEGIN TRY
    BEGIN TRANSACTION;

        INSERT INTO TableB DEFAULT VALUES;
        SET @New_ID = SCOPE_IDENTITY();

        INSERT INTO TableA (B)
        VALUES (@New_ID)

    COMMIT TRANSACTION;
 END TRY
 BEGIN CATCH
   IF @@TRANCOUNT <> 0
     ROLLBACK TRANSACTION;

 END CATCH
END

Answer 4

From EF 6 onwards, Microsoft recommends to use dbContext.Database.BeginTransaction over TransactionScope (https://msdn.microsoft.com/en-us/data/dn456843.aspx)

Basically you have to wrap the transaction in a using block like this:

using (var dbContextTransaction = db.Database.BeginTransaction()) 
{
    b = new B { };
    a = new A { b = b };
    db.Add(b);
    db.Add(a);
    db.SaveChanges()

    dbContextTransaction.Commit(); 
}

Then I recommend you to read about Unit of Work pattern because it is gonna help you managing transactions (http://www.asp.net/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application)

Answer 5

As @Ciaes mention in his comment you don't need to create a transaction to achieve what you need. Supposing you have a model like this:

public class A 
{
   public int Id{get;set;}

   [ForeignKey("B")]
   public int BId{get;set;}

   public virtual B B{get;set;}
}
public class B 
{
   public int Id{get;set;}

   public virtual ICollection<A> As{get;set;}
}

The only you need to do to add two new entities is this:

var a = new A(){B=new B()};
db.SaveChanges();

Both entities will be inserted in a single transaction.

From this msdn page:

If the entity being added has references to other entities that are not yet tracked then these new entities will also be added to the context and will be inserted into the database the next time that SaveChanges is called

Answer 6

I tried and it's working: CREATE PROCEDURE usp_InsertRow AS BEGIN SET NOCOUNT ON; DECLARE @New_ID INT; BEGIN TRY BEGIN TRANSACTION;

    INSERT INTO TableB DEFAULT VALUES;
    SET @New_ID = SCOPE_IDENTITY();

    INSERT INTO TableA (B)
    VALUES (@New_ID)

COMMIT TRANSACTION;

END TRY BEGIN CATCH IF @@TRANCOUNT <> 0 ROLLBACK TRANSACTION;