Unit Of Work Pattern with Database Transactions

Question

I've been tried to get my head around this for a couple of days and there are lots of tutorials around unit of work and around TransactionScope but I can't find anything that talks about the two together. Any help much appreciated!

I am using Entity Framework with the Unit Of Work pattern and a repository per type. As per the simple code below I have a Member and MembershipDefinition entity. I want to create a Membership entity that links the two but when I create the Membership object I want to query the DB for a max value based on some business logic. Therefore, I need to use some kind of DB transaction to prevent another thread from incrementing the value in the db before my thread has written the Membership object back to the DB.

If I was using stored procs, this would be pretty straightforward but I can't figure out how to do it using pure c#...

The code below creates 100 Membership entities in the database with duplicated MembershipNumbers. I need to get this use to use transactions to ensure that all the Membership Numbers generated in the c# code are unique.

class Program
{
    static void Main(string[] args)
    {
        var p = new Program();
        p.Go();;
    }

    public void Go()
    {
        long memberId;
        long membershipDefId;

        using(var unitOfWork = new UnitOfWork())
        {
            // Setup - create test club and member entities

            var testUsername = ("TestUserName" + Guid.NewGuid()).Substring(0, 29);
            var member = new Member()
                             {
                                 UserName = testUsername
                             };

            var testmemebrshpDefName = ("TestMembershipDef" + Guid.NewGuid()).Substring(0, 29);
            var membershipDefinition = new ClubMembershipDefinition()
            {
                ClubId = 1,
                Name = testmemebrshpDefName
            };

            unitOfWork.MemberRepository.Add(member);
            unitOfWork.MembershipDefinitionRepository.Add(membershipDefinition);

            unitOfWork.Save();

            memberId = member.Id;
            membershipDefId = membershipDefinition.Id;
        }

        Task[] tasks = new Task[100];

        // Now try to add a membership to the Member object, linking it to the test Club's single Club Definition
        for (int i = 0; i < 100; i++)
        {
            var task = new Task(() => CreateMembership(memberId, membershipDefId));
            tasks[i] = task;
            task.Start();
        }
        Task.WaitAll(tasks);
    }

    private void CreateMembership(long memberId, long membershipDefId)
    {
        using (var unitOfWork = new UnitOfWork())
        {
            var member = unitOfWork.MemberRepository.GetById(memberId);
            var membershipDef = unitOfWork.MembershipDefinitionRepository.GetById(membershipDefId);

            var membership = new ClubMembership()
                                    {
                                        ClubMembershipDefinition = membershipDef
                                    };

            membership.MembershipNumber = (unitOfWork.MembershipRepository.GetMaxMembershipNumberForClub(membershipDef.ClubId) ?? 0) + 1;

            member.ClubMemberships.Add(membership);
            unitOfWork.Save();
        }
    }

}

public class UnitOfWork : IUnitOfWork, IDisposable
{
    internal ClubSpotEntities _dbContext = new ClubSpotEntities();
    internal MemberRepository _memberRepository;
    internal MembershipRepository _membershipRepository;
    internal MembershipDefinitionRepository _membershiDefinitionpRepository;

    public MemberRepository MemberRepository
    {
        get
        {
            if (_memberRepository == null)
                _memberRepository = new MemberRepository(_dbContext);

            return _memberRepository; ;
        }
    }

    public MembershipRepository MembershipRepository
    {
        get
        {
            if (_membershipRepository == null)
                _membershipRepository = new MembershipRepository(_dbContext);

            return _membershipRepository; ;
        }
    }

    public MembershipDefinitionRepository MembershipDefinitionRepository
    {
        get
        {
            if (_membershiDefinitionpRepository == null)
                _membershiDefinitionpRepository = new MembershipDefinitionRepository(_dbContext);

            return _membershiDefinitionpRepository; ;
        }
    }

    public virtual int Save()
    {
        return _dbContext.SaveChanges();

    }

    private bool _disposed = false;

    protected virtual void Dispose(bool disposing)
    {
        if (!this._disposed)
        {
            if (disposing)
            {
                _dbContext.Dispose();
            }
        }
        this._disposed = true;
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
}

public class MembershipRepository
{
    ClubSpotEntities _dbContext = new ClubSpotEntities();

    public MembershipRepository(){}

    public MembershipRepository(ClubSpotEntities dbContext)
    {
        _dbContext = dbContext;
    }

    public IEnumerable<ClubMembership> GetAll()
    {
        return _dbContext.Set<ClubMembership>().ToList<ClubMembership>();
    }

    public ClubMembership GetById(long id)
    {
        return _dbContext.ClubMemberships.First(x => x.Id == id);
    }

    public long? GetMaxMembershipNumberForClub(long clubId)
    {
        return _dbContext.ClubMemberships.Where(x => x.ClubMembershipDefinition.ClubId == clubId).Max(x => x.MembershipNumber);
    }

    public ClubMembership Add(ClubMembership entity)
    {
        return _dbContext.Set<ClubMembership>().Add(entity);
    }

    public void Delete(ClubMembership membership)
    {
        _dbContext.Set<ClubMembership>().Remove(membership);
    }

    public void Save()
    {
        _dbContext.SaveChanges();
    }
}


public partial class ClubMembership
{
    public long Id { get; set; }
    public long MembershipDefId { get; set; }
    public Nullable<long> MemberId { get; set; }
    public Nullable<long> MembershipNumber { get; set; }

    public virtual ClubMembershipDefinition ClubMembershipDefinition { get; set; }
    public virtual Member Member { get; set; }
}

public partial class ClubMembershipDefinition
{
    public ClubMembershipDefinition()
    {
        this.ClubMemberships = new HashSet<ClubMembership>();
    }

    public long Id { get; set; }
    public long ClubId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<ClubMembership> ClubMemberships { get; set; }
}

public partial class Member
{
    public Member()
    {
        this.ClubMemberships = new HashSet<ClubMembership>();
    }

    public long Id { get; set; }
    public string UserName { get; set; }

    public virtual ICollection<ClubMembership> ClubMemberships { get; set; }
}

Solution

You can create transaction scope when instantiate new UnitOfWork, and commit it on completion. This not full exmaple:

class UnitOfWork
{
     ClubSpotEntities _dbContext;
     TransactionScope _transaction;

     public UnitOfWork()
     {
         _dbContext = new ClubSpotEntities();
         _transaction = new TransactionScope();
     }

     public void Complete()
     {
         _dbContext.SaveChanges();
         _transaction.Complete();
     }

     ...
}

UPD: As Steven said this is not solution of you problem. And UnitOfWork can't help you, TransactionScope alose not a solution in this case. EF does not support pessimistic locks which you want use, but you can try this solution.