2

While trying to delete a record in a database and recreate it afterwards, using LINQ, I get the error: Cannot add an entity that already exists. Although the record is deleted.

I am initialising my Sales_header object with data I get from a LINQ query in method SelectOrdersByOrderID(OrderID).

If the OrderID of the Salesheader meets a certain condition, I want to delete the record in the database (Delete(Sales_header SalesHeader)), add additional values to the object and insert a new record in the database (Insert(Sales_header SalesHeader)).

I do not want to update the record, but delete it and recreate it.

Sales_header SalesHeader = new Sales_header();
SalesHeader = SalesHeaderClass.SelectOrdersByOrderID(OrderID) as Sales_header;


   if (SalesHeader.OrderID == *certain value*)
      {
       SalesHeaderClass.Delete(SalesHeader);
       SalesHeader.Orderdate = DateTime.Today;
       SalesHeader.Ordertime = DateTime.Now;
       SalesHeaderClass.Insert(SalesHeader);
      }
...

Method in SalesHeaderClass to select the SalesHeader via LINQ

public static object SelectOrdersByOrderID(int OrderID)
{
    var Query = (from p in dc.Sales_headers
                 where p.OrderID.Equals(OrderID)
                 select p).SingleOrDefault();
    return Query;

Method in SalesHeaderClass to insert the SalesHeader via LINQ

public static void Insert(Sales_header SalesHeader)
      {
       dc.Sales_headers.InsertOnSubmit(SalesHeader);
       dc.SubmitChanges();
      }

Method in SalesHeaderClass to delete the SalesHeader via LINQ

public static void Delete(Sales_header SalesHeader)
      {
       var DelOrder = (from p in dc.Sales_headers
                       where p.OrderID == SalesHeader.OrderID
                       select p).Single();
       dc.Sales_headers.DeleteOnSubmit(DelOrder);
       dc.SubmitChanges();
      }

What do I have to do to be able to insert the record? Creating a new object with the same values does not help.

bart_dp
  • 45
  • 2
  • 11
  • Is there a reason you're avoiding record updates? – Grant Thomas Aug 20 '12 at 13:17
  • Because, in further development, I want to reuse this for adding new SalesLines and deleting old ones. Update won't be enough because there might be new SalesLines. – bart_dp Aug 20 '12 at 13:23

2 Answers2

0

Aside from the question why an update is not suitable.

You need two contexts to first delete the record, and then insert the record. EF keeps all inserts/updates/deletes in memory until you do a SubmitChanges(), so you cannot have two entities with the same ID.

But to execute the whole things as a single transaction, you're going to need a TransactionScope.

using (var scope = new TransactionScope()) {
    using (var salesHeader SalesHeader = new Sales_header()) {
        // Delete record
        ...
        salesHeader.SubmitChanges();
    }
    using (var salesHeader SalesHeader = new Sales_header()) {
        // Insert record
        ...
        salesHeader.SubmitChanges();
    }

    // Mark transaction complete
    scope.Complete();
}
Maarten
  • 22,527
  • 3
  • 47
  • 68
  • I already tried two contexts, but then I get the following error: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported. Will try TransactionScope, thanks. – bart_dp Aug 20 '12 at 13:51
  • Since it is indeed an object from another context, you need to Attach it. – Maarten Aug 20 '12 at 14:02
0

To fix this issue, use different datacontext for delete and insert operation.

ЯegDwight
  • 24,821
  • 10
  • 45
  • 52
Kalaivani
  • 1
  • 1
    When I do this I get: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported. – bart_dp Sep 06 '12 at 13:04