CODEWITHSUNDEEP
linq-to-sql
Supremestar
Supremestar

Reputation: 190

LINQ to SQL DuplicateKeyException problem

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

Upvotes: 1

Views: 4828

Answers (3)

Daniel Bammer
Daniel Bammer

Reputation: 83

I had the problem when adding an entity i got from a different DataContext.
(I bound the FooDb-Property of a BarDb-Entity to the SelectedValue from a DropDownList, which had a Table from a short-living DataContext as DataSource)
Let's look at the Linq2Sql internal Attach(entity):

private void attach_FooDb(FooDb entity){
    this.SendPropertyChanging();
    entity.BarDb = this;
}

And the BarDb Property set:

set{
    BarDb previousValue = this._BarDb.Entity;
    if (((previousValue != value) || (this._BarDb.HasLoadedOrAssignedValue == false))){
        this.SendPropertyChanging();
        if ((previousValue != null)){
            this._BarDb.Entity = null;
            previousValue.FooDb.Remove(this);
        }

        this._BarDb.Entity = value;

        if ((value != null)){
            value.FooDb.Add(this);
            this._FK_Bar = value.ID;
        }else{
            this._FK_Bar = default(System.Guid);
        }
        this.SendPropertyChanged("BarDb");
    }
}

So when attaching to a FooDb, the corresponding DataContext will recognize the BarDb Entity as newly created and added, even though it already existed in the Database.

You can solve it by:

  • only using a single DataContext or
  • creating a BarDb_safe Property in the submitting DataContext, which will first request the "equal" entity from the database, applies all properties and then attaches it the usual way.

Upvotes: 0

wloescher
wloescher

Reputation: 4593

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.

Upvotes: 0

Stephen Wrighton
Stephen Wrighton

Reputation: 37819

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

Upvotes: 2

Related Questions