Unit of Work Pattern with Foreign Keys - ASP.NET Web API

Question

Update: I think maybe I've asked the wrong question on this thread. Please see my newer post on the topic: ASP.NET Web API - Service Between Controller and Repository?

I'm trying to implement the Repository/Unit of Work patterns, as described in https://codewithmukesh.com/blog/repository-pattern-in-aspnet-core/.

The idea is for my controller to be able to write to multiple entities via individual repositories, but to call context.SaveChanges() only once at the end (via a method in the UnitOfWork class). The advantage here is that there will be only one database transaction. If one write fails, they all fail.

Here's an example:

public class Customer
{
    public int CustomerID { get; set; }
    public string Name  { get; set; }
}

public class Order 
{
    public int OrderId  { get; set; }
    public int CustomerId  { get; set; } // foreign key to Customer
    ...
}

The example in the link above (and others I've seen) are writing to totally unrelated tables, but a real-world example is when you have to write to 2 tables, but the second table needs a foreign key to the first.

My example above is not the best metaphor, because typically a Customer would already exist before they do any ordering. But please don't let that distract you. My scenario involves writing a graph of data involving several types.

Obviously I want to write them all in the same transaction, but the problem is that you don't know the ID of the Customer you just created (so you can write it into the Order table as its foreign key) until you call context.SaveChanges(), which breaks the single transaction requirement.

Has anybody been in this situation? If so, how did you solve it? Thanks.

Answer 1

You can wrap multiple calls to SaveChanges() in a single Transaction, so long as you're using a single DbContext instance.

So start a transaction, DbSet.Add() the the Customer, call DbContext.SaveChanges() which will assign the CustomerId, and use that to save the Orders and then Commit the transaction. eg

using var tran = db.Database.BeginTransaction();

var c = new Customer();
db.Set<Customer>().Add(c);
db.SaveChanges();

var o = new Order();
o.CustomerId = c.CustomerID;
db.Set<Order>().Add(o);
db.SaveChanges();

tran.Commit(); //commit both the Customer and Order insert

Any exception will cause the Transaction to be Disposed and rolled back.