Asp.Net Dynamic Data update/insert 2 tables in single transaction

Question

I'm currenlty using Asp.Net Dynamic Data, with Linq to Sql, to create an simple site that only has CRUD actions associated with it.

Unfortunately I'm not able to change the database schema at all and have run into an issue which I'm having trouble resolving gracefully.

The database has a central users table and an additional table related to access rights where there are 3 rows per user with a access type and bit flag indicating if they have the right e.g. UserId, Access Type, Has Access.

The requirement is that checkboxes are provided in the users details & edit screen to allow selection of the access rights.

The biggest problem that I can see is how to implement insert/update/delete actions to ensure both tables are altered within the one transaction.

So far the only solution I can see is to hijack the insert/update/delete event and do it all manually but if thats the case I would probably be better off writing the pages in normal Asp.Net webforms rather than trying to use Dynamic Data.

Does anybody have any ideas of any other ways I could do it?

Answer 1

This is an unfortunate database design. You are right, you must intercept the SubmitChanges() of your data context and do the specialized updating there. Another option would be to use database triggers.

public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
{
    ChangeSet changeset = GetChangeSet();

    foreach (var u in changeset.Deletes.OfType<Users>())
    {
        // do something to details
    }
}