LINQ to SQL DuplicateKeyException problem

Question

Guys,

I am using LINQ2SQL and I have a table called Customers with three columns

CustmerID, CustomerCode, CustomerName

CustmerID is Primery Key(and Identity=yes) and CustomerCode is just UniqueKey.

When I am updating this table using LINQ to SQL with duplicate customercode, I expect to see DuplicateKeyException but it is going into the general exception block instead of DuplicateKeyException block. Any ideas?

This is the code

public void Update(Customer cust) { using (LINQDemoDataContext db = new LINQDemoDataContext()) { Customers entity = CustomerMapper.ToEntity(new Customers(), cust);

            try
            {
                db.Customers.Attach(entity, true);
                db.SubmitChanges();
            }

            //Concurrency Exception
            catch (ChangeConflictException)
            {                               
                throw new ChangeConflictException("A concurrency error occurred!");
            }

            //duplicate record
            catch (DuplicateKeyException)
            {
                throw new DuplicateKeyException(entity.CustmerCode);
            }

            //everything else
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

I am using VisualWebDeveloperExpress 2008 and SQL Express 2005.

Thanks & Regards, Supremestar

Answer 1

If memory serves, and I may be wrong here, the DuplicateKeyException only fires for the primary key of the table.

Answer 2

I ran into this same problem, where the duplicate key objects were being retained in-memory even though they were not getting inserted into the database. I came up with the following work around:

    MyDataContext _myDataContext = new MyDataContext(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);

    public void AddObject(object myObject)
    {
        try
        {
            _myDataContext.Objects.InsertOnSubmit(myObject);
            _myDataContext.SubmitChanges();
        }
        catch (System.Data.Linq.DuplicateKeyException ex)
        {
            _myDataContext = new MyDataContext(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
            throw ex;

You can then catch and handle (or ignore) the thrown DuplicateKeyException in the caller.

Another work around (albeit inefficient and memory hogging) is to create a new data context inside the AddObject method instead of using the shared data context instantiated as a class member.