LINQ to SQL concurrency conflict - Looks like a clean attach with proper row versioning

Question

I am trying to get LINQ to SQL to persist changes to an attached object wherein the backing table has a DateTime column that I think should function for row versioning, as described here.

The table looks like this:

CREATE TABLE [dbo].[client](
[client_id] [int] IDENTITY(1,1) NOT NULL,
[client_address1] varchar(100) NULL,
/* snip */
[modified_date] datetime NOT NULL,
CONSTRAINT [PK_CLIENT] PRIMARY KEY CLUSTERED ([client_id] ASC) )

The relevant attributes on the modified_date property are set as so in the DBML designer:

Auto Generated Value: True
Auto-Sync: Always
Nullable: False
Primary Key: False
Read Only: False
Server Data Type: DateTime
Source: modified_date
Time Stamp: True
Update Check: Never

And the resulting attributes on the declaration of the modified_date property look right as far as I can tell:

[Column(Storage="_modified_date", AutoSync=AutoSync.Always, 
DbType="DateTime", IsDbGenerated=true, IsVersion=true, 
UpdateCheck=UpdateCheck.Never)]

The process of attempting to save changes to a client goes something like this:

var c = new client { client_id = idOfClientToSave };

c.client_address1 = uxAddress1.Text;

// The DataContext is initialized in the constructor
// of ClientDataAccess
using (var ClientData = new ClientDataAccess())
{
    ClientData.SaveClient(c);
}

And lastly, the method that attempts to submit the changes looks like this:

public int SaveClient(client c)
{
    c.modified_date = DateTime.Now.ToUniversalTime();
    
    if (c.client_id == 0)
    {
        _db.GetTable<client>().InsertOnSubmit(c);
    }
    else
    {
        _db.GetTable<client>().Attach(c, true);
    }
    try
    {
        _db.SubmitChanges(ConflictMode.ContinueOnConflict);
    }
    catch (ChangeConflictException)
    {
        foreach (var con in _db.ChangeConflicts)
        {
            con.Resolve(RefreshMode.OverwriteCurrentValues);
        }
        throw;
    }
}

But _db.SubmitChanges(ConflictMode.ContinueOnConflict) still throws a ChangeConflict exception (as does _db.SubmitChanges()). I can't see how the attached object might could be originating from the current DataContext, as it's instanciated on its own and not retrieved from the DataContext before attaching.

Any help would be greatly appreciated.

Answer 1

Try loading the original object in behind the new version.

else   
{
    _db.GetTable<client>().Attach(c, true);
    _db.Refresh(RefreshMode.KeepCurrentValues, c);
}

I've learned this technique from this article, in the section: concurrency patterns for Update (and Delete) operations.