linq2sql: Cannot add an entity with a key that is already in use

Question

I have a linq2sql setup where objects are sent from client side (flex via flourinefx) and attach them to a new datacontext a seen below:

I also have a "global" datacontext that is used throughout the session.

    public static void Update(Enquiry enquiry)
    {
        OffertaDataContext db = new OffertaDataContext();


        db.Enquiries.Attach(enquiry);
        db.Refresh(RefreshMode.KeepCurrentValues, enquiry);

        db.SubmitChanges();
    }

This approach usually works fine, but after a while I get the error "Cannot add an entity with a key that is already in use".

Answer 1

Are you trying to add multiple new objects in one hit where the LinqEntities are being created with a key of 0?

I had this issue in the past when I was trying to add items into a table on my page and then when I tried to delete or update these items, multiples would have the key 0. So obviously it didn't know what to do with my requests...

Answer 2

This is what I've been doing to get around this error. Basically you find where this row is in the database based on a primary key. If it doesn't exist then you insert it. Otherwise you get the version from the database and update all of the necessary fields.

public static void Update(Enquiry enquiry)
{
    JobsDataContext db = new JobsDataContext();

    var enquiries = from e in db.Enquiries
                    where e.PKID == enquiry.PKID
                    select e;

    if (enquiries.Count() < 1)
    {
        db.Enquiries.InsertOnSubmit(enquiry);
    }
    else
    {
        Enquiry updateEnquiry = enquiries.Single();

        updateEnquiry.LengthMm = enquiry.LengthMm;
        updateEnquiry.ShippedQty = enquiry.ShippedQty;
        updateEnquiry.StatusCode = enquiry.StatusCode;
    }

    db.SubmitChanges();
}

This can become tedious if you make updates to your database schema all the time, as you'll have to come back here to update the code.

Answer 3

I think this error happens if you Attach an entity to a DataContext that was already loaded.

The code that causes the error is exactly like you show here? After creating the new OffertaDataContext do you query anything before the Attach?

Answer 4

Hi everybody, try this even if your TEntity's (here Area) ID is an Identifier column; Just it, without any change in your SP or Model:

    public void InitForm()
    {
        bnsEntity.DataSource = CacheManagement.cachedAreas;
        newID = CacheManagement.cachedAreas.LastOrDefault().areaID + 1;
        grdEntity.DataSource = bnsEntity;
    }

''' private void tsbNew_Click(object sender, EventArgs e) { var newArea = new Area(); newArea.areaID = (byte)newID++; dataContext.GetTable().InsertOnSubmit(newArea); bnsEntity.Add(newArea); grdEntity.MoveToNewRecord(); }

Bardaan

Answer 5

I have the same problem

Answer 6

I have the same problem !

Answer 7

I have a similar aproach to Noah's, but I use a stored procedure to verify if a record with that PK exists or not, this way the Entity is not loaded in the context and the update code only involves two lines of code and will not require changes in the future when you add/remove fields from the table, it will require to change the SP only if the PK of the table changes:

bool existe = Convert.ToBoolean(dbc.spSConfigReportesPeriodicos(configReportesPeriodicos.CodigoCliente));

if (existe)
{
    dbc.ConfigReportesPeriodicos.Attach(configReportesPeriodicos);
    dbc.Refresh(RefreshMode.KeepCurrentValues, configReportesPeriodicos);
}
else
{
    dbc.ConfigReportesPeriodicos.InsertOnSubmit(configReportesPeriodicos);
}
dbc.SubmitChanges();

And here is the stored procedure:

ALTER PROCEDURE dbo.spSConfigReportesPeriodicos
(
    @codigoCliente int
)
AS

IF EXISTS(SELECT 1 FROM dbo.ConfigReportesPeriodicos WHERE CodigoCliente = @codigoCliente)
    RETURN 1
ELSE
    RETURN 0

RETURN