LINQ, repository pattern and many-to-many relations

Question

Hello,

I've a problem with saving changes to database, I'm using LINQ2SQL mapping. I've implemented M:M relation (User <= UserRole => Role) based on tutorial: http://www.codeproject.com/KB/linq/linqtutorial2.aspx#premain25

Everything works fine when I'm using one class which is inherits from DataContext and is responsible for all of my domain classes for example:

[Database] public class BookCatalog : DataContext { // Create static DataContext for removing M:M Join records private static DataContext contextForRemovedRecords = null;

public BookCatalog() : base("Data Source=KO2\\SQLSERVER;Initial Catalog=Katalog;Integrated Security=True") { }

public Table<User> Users;
public Table<Role> Roles;
public Table<UserRole> UserRoles;

public static void RemoveRecord<T>(T recordToRemove) where T : class
{
    // Use the static contextForRemovedRecords
    if (contextForRemovedRecords == null)
        contextForRemovedRecords = new BookCatalog();

    Table<T> tableData = contextForRemovedRecords.GetTable<T>();
    var deleteRecord = tableData.SingleOrDefault(record => record == recordToRemove);
    if (deleteRecord != null)
    {
        tableData.DeleteOnSubmit(deleteRecord);
    }
}

// NEW method (not part of LINQ to SQL) to cancel changes
public void CancelChanges()
{
    if (contextForRemovedRecords != null)
    {
        contextForRemovedRecords = null;
    }
}

// Override DataContext's SubmitChanges() to handle any removed records
public new void SubmitChanges()
{
    if (contextForRemovedRecords != null)
    {
        contextForRemovedRecords.SubmitChanges();
    }
    base.SubmitChanges();
}

}

Unfortunately for some reasons I need to keep separate repositories in my project. One of them example:

public class SqlRolesRepository : IRolesRepository {

private Table<Role> rolesTable;

public SqlRolesRepository(string connectionString)
{
    rolesTable = (new DataContext(connectionString)).GetTable<Role>();
}

public IQueryable<Role> Roles
{
    get { return rolesTable; }
}

public void SaveRole(Role role)
{
    bool ins = false;

    if (rolesTable.Any(m => m.RoleID == role.RoleID))
    {
        rolesTable.Context.Refresh(RefreshMode.KeepCurrentValues, role);
    }
    else
    {
        try { rolesTable.InsertOnSubmit(role); ins = true; }
        catch (Exception ex)
        {
            throw ex;

        }
    }

    try
    {
        rolesTable.Context.SubmitChanges();
    }
    catch (SqlException ex)
    {
        if (ins) rolesTable.DeleteOnSubmit(role);

        List<ErrorInfo> errors = new List<ErrorInfo>();

        if (ex.Message.Contains("UNQ_RoleName"))
            errors.Add(new ErrorInfo("RoleName", "Rola o takiej nazwie już istnieje", role));
        if (errors.Any()) throw new RulesException(errors);

        throw;
    }
}

public void SaveChanges()
{
    rolesTable.Context.Refresh(RefreshMode.OverwriteCurrentValues);
    rolesTable.Context.SubmitChanges();
}
public void DeleteRole(Role role)
{
    rolesTable.DeleteOnSubmit(role);
    rolesTable.Context.SubmitChanges();
}

public void DeleteRole(string roleName)
{
    rolesTable.DeleteOnSubmit(rolesTable.FirstOrDefault(m => m.Name == roleName));
    rolesTable.Context.SubmitChanges();
}

public Role GetRoleByName(string name)
{
    return rolesTable.Single(m => m.Name == name);
}

public string[] GetAllRoles(){
    return (from rola in rolesTable
           select rola.Name).ToArray();
}

}

The point is that when I'm tying to save changes:

(...)
                foreach (string roleName in roleNames)
                {
                    Role rola = _RolesRepository.GetRoleByName(roleName);
                    if (rola != null)
                    {
                        foreach (string userName in usernames)
                        {
                            User usr = _UsersRepository.GetUserByName(userName);
                            if (usr != null)
                            {
                                if (!rola.Users.Contains(usr))
                                {
                                    rola.Users.Add(usr);               
                                }
                            }

                        }
                        _RolesRepository.SaveChanges();
                   } 
                }
(...)

Instead of saving an instance of association class (UserRole) LINQ is trying to save instance of class User once again, SqlExcepiton error appears (unique key on username column). In debugging mode I've noticed that User instances seems to be doubled somehow...

My LINQ classes are mapped like in tutorial (link listed above), sample project is available here: http://www.codeproject.com/KB/linq/linqtutorial2/linqtutorial2_src.zip

Maybe somebody will have an idea how to use it with repository pattern (multiple repository). I'm reading MSDN article (http://msdn.microsoft.com/en-us/library/bb425822.aspx) and it seems that calling SubmitChanges() from my SqlRolesRepository should update database properly, but it isn't...

Answer 1

You are breaking the unit of work pattern by having each repository encapsulate its own data context instance / table reference. In order for the relationships to be updated correctly you need to use the same data context instance for all operations.

The easiest way to do this is to enable your repository classes to accept an existing data context instance when you create them, so that they perform their work on a shared instance rather than separate instances. You can do this by adding a new constructor to each repository along these lines:

public SqlRoleRepository(DataContext context)
{
    rolesTable = context.GetTable<Role>();
}

Then use those constructors to instantiate your repositories against the same data context when they need to participate in the same unit of work.