How do I run a stored procedure once per class hierarchy update in LINQ?

Question

I have two related tables in my database: Page and Tag. One Page can be related to many Tags.

Whenever either of these two tables is modified, a stored procedure called BeforePageHierarchyUpdate needs to be executed (in my case this stored procedure performs some logging and versioning on the Page hierarchy).

What's giving me problems are these two requirements:

1. The SP must be run if either a Page instance or a Tag instance are updated. But if BOTH a Page AND one of its related Tags are updated, the SP should only be called once.

2. The stored procedure must be contained within the same transaction as the other LINQ statements. If the LINQ statements fail, the stored procedure needs to be rolled back. If the stored procedure fails, the LINQ statements must not be executed.

Does anyone have any ideas as to how to implement something like this?

Answer 1

only update these tables using the following procedure:

create procedure UpdatePageAndOrTag
(
    @mode              char(1)  --"P"=page only, "T"=tag only, "B"=both
    ,@pageParam1 ...
    ,@pageParam2 ....
    ....
    ,@TagParam1.....
    ,@TagParam2....
    ....
)

as

EXEC BeforePageHierarchyUpdate

if @Mode="B" or @Mode="P"
Begin
    update Page....
END

IF @Mode="B" or @Mode="T"
Begin
    update tag...
END

return 0
go

Answer 2

After digging through some code, here is another alternative. I'm not completely comfortable that the connection/transaction code is correct (it was mostly reverse engineered from the base SubmitChanges implementation).

public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode) {
    if (System.Transactions.Transaction.Current == null && this.Transaction == null) {
        bool connectionOpened = false;
        DbTransaction transaction = null;
        try {
            if (this.Connection.State == ConnectionState.Closed) {
                this.Connection.Open();
                connectionOpened = true;
            }
            transaction = this.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
            this.Transaction = transaction;

            BeforeSubmitChanges();
            base.SubmitChanges(failureMode);

            transaction.Commit();
        }
        catch {
            if (transaction != null) {
                try {
                    transaction.Rollback();
                }
                catch {
                }
                throw;
            }
        }
        finally {
            this.Transaction = null;
            if (connectionOpened) {
                this.Connection.Close();
            }
        }
    }
    else {
        BeforeSubmitChanges();
        base.SubmitChanges(failureMode);
    }
}

private void BeforeSubmitChanges() {
    ChangeSet changes = this.GetChangeSet();
    HashSet<int> modifiedPages = new HashSet<int>();

    foreach (Page page in changes.Updates.OfType<Page>()) {
        modifiedPages.Add(page.PageId);
    }

    foreach(PageTag tag in changes.Updates.OfType<PageTag>()) {
        modifiedPages.Add(tag.PageId);
    }

    foreach (PageTag tag in changes.Inserts.OfType<PageTag>()) {
        //If the parent is being inserted, don't run the Update SP.
        if (!changes.Inserts.Contains(tag.Page)) {
            modifiedPages.Add(tag.PageId);
        }
    }

    foreach (PageTag tag in changes.Deletes.OfType<PageTag>()) {
        //If the parent is being deleted, don't run the Update SP.
        if (!changes.Deletes.Contains(tag.Page)) {
            modifiedPages.Add(tag.PageId);
        }
    }

    foreach (int pageId in modifiedPages) {
        this.BeforePageHierarchyUpdate(pageId);
    }
}

Answer 3

A third potential solution is to put it in the repository class (or other wrapping implementation). This simplifies the transaction code quite a bit, but the functionality feels more appropriate int the DataContext layer.

public class PageRepository : IPageRepository {
    public void Save() {
        using(TransactionScope trans = new TransactionScope()) {
            BeforeSubmitChanges();
            mDataContext.SubmitChanges();
            trans.Complete();
        }
    }

    private void BeforeSubmitChanges() {
        ChangeSet changes = this.GetChangeSet();
        HashSet<int> modifiedPages = new HashSet<int>();

        foreach (Page page in changes.Updates.OfType<Page>()) {
            modifiedPages.Add(page.PageId);
        }

        foreach(PageTag tag in changes.Updates.OfType<PageTag>()) {
            modifiedPages.Add(tag.PageId);
        }

        foreach (PageTag tag in changes.Inserts.OfType<PageTag>()) {
            //If the parent is being inserted, don't run the Update SP.
            if (!changes.Inserts.Contains(tag.Page)) {
                modifiedPages.Add(tag.PageId);
            }
        }

        foreach (PageTag tag in changes.Deletes.OfType<PageTag>()) {
            //If the parent is being deleted, don't run the Update SP.
            if (!changes.Deletes.Contains(tag.Page)) {
                modifiedPages.Add(tag.PageId);
            }
        }

        foreach (int pageId in modifiedPages) {
            this.BeforePageHierarchyUpdate(pageId);
        }
    }
}