DeleteOnSubmit LINQ exception "Cannot add an entity with a key that is already in use"

Question

edit: in case anyone is wondering, the actionhandler invokes code that creates and disposes the same kind of datacontext, in case that might have anything to do with this behaviour. the code doesn't touch the MatchUpdateQueue table, but i figure i should mention it just in case.

double edit: everyone who answered was correct! i gave the answer to the respondent who suffered most of my questioning. fixing the problem allowed another problem (hidden within the handler) to pop up, which happened to throw exactly the same exception. whoops!

I'm having some issues with deleting items in LINQ. The DeleteOnSubmit call in the code below causes a LINQ Exception with the message "Cannot add an entity with a key that is already in use." I'm not sure what I'm doing wrong here, it is starting to drive me up the wall. The primary key is just an integer autoincrement column and I have no other problems until I try to remove an item from the database queue. Hopefully I'm doing something painfully retarded here that is easy to spot for anyone who isn't me!

static void Pacman()
{
    Queue<MatchUpdateQueue> waiting = new Queue<MatchUpdateQueue>();

    events.WriteEntry("matchqueue worker thread started");

    while (!stop)
    {
        if (waiting.Count == 0)
        {
            /* grab any new items available */
            aDataContext db = new aDataContext();
            List<MatchUpdateQueue> freshitems = db.MatchUpdateQueues.OrderBy(item => item.id).ToList();

            foreach (MatchUpdateQueue item in freshitems)
                waiting.Enqueue(item);
            db.Dispose();
        }
        else
        {
            /* grab & dispatch waiting item */
            MatchUpdateQueue item = waiting.Peek();
            try
            {
                int result = ActionHandler.Handle(item);
                if (result == -1)
                    events.WriteEntry("unknown command consumed : " + item.actiontype.ToString(), EventLogEntryType.Error);

                /* remove item from queue */
                waiting.Dequeue();

                /* remove item from database */
                aDataContext db = new aDataContext();
                db.MatchUpdateQueues.DeleteOnSubmit(db.MatchUpdateQueues.Single(i => i == item));
                db.SubmitChanges();
                db.Dispose();
            }
            catch (Exception ex)
            {
                events.WriteEntry("exception while handling item : " + ex.Message, EventLogEntryType.Error);
                stop = true;
            }
        }

        /* to avoid hammering database when there's nothing to do */
        if (waiting.Count == 0)
            Thread.Sleep(TimeSpan.FromSeconds(10));
    }

    events.WriteEntry("matchqueue worker thread halted");
}

Answer 1

Use:

aDataContext db = new aDataContext();
item = new MatchUpdateQueue { id=item.id }; // <- updated
db.MatchUpdateQueues.Attach(item);
db.MatchUpdateQueues.DeleteOnSubmit(item);
db.SubmitChanges();

Since you are using a new datacontext it doesn't know that the object is already in the db.

Answer 2

You could do something to the effect of

db.MatchUpdateQueues.DeleteOnSubmit(db.MatchUpdateQueues.Single(theItem => theItem == item));

Just a note as other answers hinted towards Attach.. you will not be able to use attach on a context other then the original context the item was received on unless the entity has been serialized.

Answer 3

Try wrapping the entire inside of the while loop in a using statement for a single data context:

Queue<MatchUpdateQueue> waiting = new Queue<MatchUpdateQueue>();
events.WriteEntry("matchqueue worker thread started");
while (!stop)
{
    using (var db = new aDataContext())
    {
      if (waiting.Count == 0)
      {
        /* grab any new items available */
           List<MatchUpdateQueue> freshitems = db.MatchUpdateQueues
                                                 .OrderBy(item => item.id)
                                                 .ToList();
           foreach (MatchUpdateQueue item in freshitems)
                waiting.Enqueue(item);
      }
      ...
   }
}

Answer 4

Remove the first db.Dispose() dispose. It can be the problem code because the entities keep a reference to their data context, so you don't want to dispose it while you are still be working with the instances. This won't affect connections, as they are open/closed only when doing operations that need them.

Also don't dispose the second data context like that, since an exception won't call that dispose code anyway. Use the using keyword, which will make sure to call dispose whether or not an exception occurs. Also grab the item from the db to delete it (to avoid having to serialize/deserialize/attach).

using (aDataContext db = new aDataContext())
{
   var dbItem = db.MatchUpdateQueues.Single(i => i.Id == item.Id);
   db.MatchUpdateQueues.DeleteOnSubmit(dbItem);
   db.SubmitChanges();
}

Answer 5

Hi everybody, try this if your TEntity's (here Area) Primary Key is of type Identity column; Just it, without any change in your SP or Model:

    public void InitForm()
    {
        'bnsEntity is a BindingSource and cachedAreas is a List<Area> created from dataContext.Areas.ToList()
        bnsEntity.DataSource = cachedAreas;
        'A nominal ID
        newID = cachedAreas.LastOrDefault().areaID + 1;
        'grdEntity is a GridView
        grdEntity.DataSource = bnsEntity;
    }

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

'Bardaan