How to update a multi-table DataSet in ADO.NET with CASCADE UPDATE

Question

Question

If I understand correctly, ADO.NET is geared for the following workflow:

1. Query multiple tables from the database into a local model.
2. Make edits to the local model.
3. Push edits back to the database using optimistic concurrency.

What is the standard way for (3) to work on multiple tables when CASCADE UPDATE is present?

Minimal Example

Consider the following tables Product and Price. There are two products "Apples" and "Oranges", with a price of $3.50 for "Oranges". The column Price.ProductName references Product.Name, with ON UPDATE CASCADE.

Let's say we update both tables locally, from "Oranges" to "Orange Is", and from $3.50 to $9.99. Then we push edits back to the database.

The problem is, no matter which table we push first, the operation fails.

Case 1: Push Product first, then Price. First, Product is updated, from "Oranges" to "Orange Is". The change also propagates automatically to Price.ProductName, due to the CASCADE. Across the board, the name is "Orange Is". But then the update from $3.50 to $9.99 fails with zero rows affected because the optimistic concurrency is still trying to update rows in Price with "Oranges", but that ProductName no longer exists.

Case 2: Push Price first, then Product. The attempt to update Price.ProductName from "Oranges" to "Orange Is" fails immediately because no Product.Name exist yet with the name "Orange Is"; it still has the old value "Oranges". The foreign key constraint prevents the update.

Sample Code

SQL

CREATE TABLE Product(
    Name VARCHAR(100) NOT NULL PRIMARY KEY
);

CREATE TABLE Price(
    ProductName VARCHAR(100) NOT NULL,
    Amount DECIMAL(18,2),
    CONSTRAINT FK_Price_Product
        FOREIGN KEY (ProductName)
        REFERENCES Product(Name)
        ON UPDATE CASCADE
);

INSERT INTO Product
VALUES ('Apples'), ('Oranges');
INSERT INTO Price
VALUES ('Oranges', 3.50);

C#

using System.Data;
using System.Data.Common;

void DoEdit(DbConnection conn)
{
    using (var model = new DataSet())
    using (var productAdapter = CreateDataAdapter(conn, "Product", "SELECT * FROM [Product]"))
    using (var priceAdapter = CreateDataAdapter(conn, "Price", "SELECT * FROM [Price]"))
    {
        QueryFromDatabase(conn, model, productAdapter, priceAdapter);
        EditLocalModel(model);
        PushEditsToDatabase(conn, model, productAdapter, priceAdapter);
    }
}

void QueryFromDatabase(DbConnection conn, DataSet model, DataAdapter productAdapter, DataAdapter priceAdapter)
{
    conn.Open();
    productAdapter.Fill(model);
    priceAdapter.Fill(model);
    conn.Close();

    model.Relations.Add(new DataRelation("Price References Product",
        model.Tables["Product"].Columns["Name"],
        model.Tables["Price"].Columns["ProductName"]
    ));
}

void EditLocalModel(DataSet model)
{
    // Edit both tables, foreign key cascades.
    model.Tables["Product"].Rows.Find("Oranges")["Name"] = "Orange Is";
    model.Tables["Price"].Rows[0]["Amount"] = 9.99M;
}

void PushEditsToDatabase(DbConnection conn, DataSet model, DataAdapter productAdapter, DataAdapter priceAdapter)
{
    conn.Open();

    productAdapter.Update(model);
    priceAdapter.Update(model);

    //// Or, reverse order
    // priceAdapter.Update(model);
    // productAdapter.Update(model);

    conn.Close();
}

DbDataAdapter CreateDataAdapter(DbConnection conn, string tableName, string selectSql)
{
    var adapter = DbProviderFactories.GetFactory(conn).CreateDataAdapter();
    adapter.SelectCommand = conn.CreateCommand();
    adapter.SelectCommand.CommandText = selectSql;
    adapter.TableMappings.Add("Table", tableName);
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    DbProviderFactories.GetFactory(conn).CreateCommandBuilder().DataAdapter = adapter;
    return adapter;
}