LINQ to SQL: Read entity while calling "SubmitChanges" causes DuplicateKeyException

Question

I'm experiencing a strange behavior that I really don't know how to work around it. I'm trying to read an entity just after it has been inserted (right after the ExecuteDynamicInsert) and when the partial method returns, I always get a System.Data.Linq.DuplicateKeyException "The database generated a key that is already in use.".

Here is what I'm trying to achieve with a very simple example:

DataContext file MyDataContext.cs:

public partial class MyDataContext
{
    public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
    {
        //using (TransactionScope ts = new TransactionScope())
        //{
            base.SubmitChanges(failureMode);
        //}
    }

    partial void InsertCountry(Country instance)
    {
        this.ExecuteDynamicInsert(instance);

        Country country = this.Countries.Where(c => c.CountryID == instance.CountryID).Single();
    }  //Exception occurs when this method returns...
}

Program file Program.cs:

class Program
{
    static void Main(string[] args)
    {
        using (MyDataContext dataContext = new MyDataContext())
        {
            Country c = new Country()
            {
                Code = "C",
                CreatedBy = "Me",
                CreatedDate = DateTime.Now,
                ModifiedBy = "Me",
                ModifiedDate = DateTime.Now
            };

            dataContext.Countries.InsertOnSubmit(c);

            dataContext.SubmitChanges();
        }
    }
}

If I don't read the country after it has been inserted, the code works fine, but I need to read it for whatever reason and I don't want to use the ChangeSet.

Is there a way to achieve this or to work around this behavior?

Thanks in advance.

Answer 1

I had something like this happen before. Try deleting the Country entity from the DataContext designer and re-adding it.

Answer 2

I had this same exception. In my instance I was using instance.ExecuteDynamicUpdate instead of ExecuteDynamicInsert but I don't think it matters.

What does seem to matter is that if you make a change to an object in the object hierarchy within the inset/update you create this error. To me it seems more to do with optimistic concurrency than duplicate keys, but hey, I don't work at Microsoft...

I solved my issue by creating a new method in my base business object that called SubmitChanges on the object's data context and then made whatever changes were required.

In your case you might try adding a method called Commit to your Country object as follows:

public sub Commit(dc as MyDataContext)
 dc.SubmitChanges()
 Dim country as Country = dc.Countries.Where(function (c) c.CountryID = Me.CountryID).Single()
end sub

Then call c.Commit instead of datacontext.SubmitChanges in Main.

Answer 3

When you override the default behavior by using ExecuteDynamic[Insert|Update|Delete], you should not call SubmitChanges. ExecuteDynamic[...] implements the change immediately external to the DataContext. You're getting the exception because you are trying to apply the changes again when you call SubmitChanges.

Answer 4

I had a similar problem. In my case, I had overridden the default INSERT with a stored procedure but forgotten to set the primary key. So, any time I inserted objects, I would get a duplicate key error since they were all coming back with a primary key of zero.