Need advice on complex insert scenario using LinqToSql with repository pattern

Question

I have a User table and a ClubMember table in my database. There is a one-to-one mapping between users and club members, so every time I insert a ClubMember, I need to insert a User first. This is implemented with a foreign key on ClubMember (UserId REFERENCES User (Id)).

Over in my ASP.NET MVC app, I'm using LinqToSql and the Repository Pattern to handle my persistence logic. The way I currently have this implemented, my User and ClubMember transactions are handled by separate repository classes, each of which uses its own DataContext instance.

This works fine if there are no database errors, but I'm concerned that I'll be left with orphaned User records if any ClubMember insertions fail.

To solve this, I'm considering switching to a single DataContext, which I could load up with both inserts then call DataContext.SubmitChanges() only once. The problem with this, however, is that the Id for User is not assigned until the User is inserted into the database, and I can't insert a ClubMember until I know the UserId.

Questions:

1. Is it possible to insert the User into the database, obtain the Id, then insert the ClubMember, all as a single transaction (which can be rolled back if anything goes wrong with any part of the transaction)? If yes, how?

2. If not, is my only recourse to manually delete any orphaned User records that get created? Or is there a better way?

Answer 1

Yes, you can do it in a single transaction. Use the TransactionScope object to begin and commit the transaction (and rollback if there is an error of course)

Answer 2

You can use System.Transactions.TransactionScope to perform this all in an atomic transaction, but if you are using different DataContext instances, it will result in a distributed transaction, which is probably not what you really want.

By the sounds of it, you're not really implementing the repository pattern correctly. A repository should not create its own DataContext (or connection object, or anything else) - these dependencies should be passed in via a constructor or public property. If you do this, you'll have no problem sharing the DataContext:

public class UserRepository
{
    private MyDataContext context;

    public UserRepository(MyDataContext context)
    {
        if (context == null)
            throw new ArgumentNullException("context");
        this.context = context;
    }

    public void Save(User user) { ... }
}

Use the same pattern for ClubMemberRepository (or whatever you call it), and this becomes trivial:

using (MyDataContext context = new MyDataContext())
{
    UserRepository userRep = new UserRepository(context);
    userRep.Save(user);
    ClubMemberRepository memberRep = new ClubMemberRepository(context);
    memberRep.Save(member);
    context.SubmitChanges();
}

Of course, even this is a little bit iffy. If you have a foreign key in your database, then you shouldn't even need two repositories, because Linq to SQL manages the relationship. The code to create should simply look like this:

using (MyDataContext context = new MyDataContext())
{
    User user = new User();
    user.Name = "Bob";
    user.ClubMember = new ClubMember();
    user.ClubMember.Club = "Studio 54";

    UserRepository userRep = new UserRepository(context);
    userRep.Save(user);
    context.SubmitChanges();
}

Don't fiddle with multiple repositories - let Linq to SQL handle the relationship for you, that's what ORMs are for.