none
DuplicateKeyException when I attach linq entities generated from business objects RRS feed

  • Question

  • In my application I have business objects which are saved using the same DataContext.
    Sometimes two instances of one and the same Business object needs to generate and attach linq entity because they are child of another entities.

    Here is a sample code so you can reproduce the error:

    1            using (DB.DefaultDataContext db = new DB.DefaultDataContext(ConnectionString)) 
    2            { 
    3                DB.Employee emp1 = new DB.Employee() { EmployeeID = 1 }; 
    4                db.Employees.Attach(emp1, false); 
    5 
    6                DB.Employee emp2 = new DB.Employee() { EmployeeID = 1 }; 
    7                db.Employees.Attach(emp2, false); //throws DuplicateKeyException 
    8 
    9                db.SubmitChanges(); 
    10            } 


    How to avoid the second attach if my business object does not know for the first attach?
    How should I attach linq entities from business objects?


    Thursday, March 12, 2009 8:53 AM

All replies

  • In this case, is it possible for you to use the same object instance as the child of those other entities? i.e. Instead of having two separate Employees with the same ID attached to the DataContext, you would just have one.
    Thursday, March 12, 2009 1:04 PM
    Answerer
  • Yes it will be nice to have one instance but I cannot get that entity instance that I first attached.
    I do not know if it even exists.
    Thursday, March 12, 2009 1:31 PM
  • I don't quite understand. You know what the first entity instance that you attach is because you have to pass it as a parameter to the Attach method.

    Or (as I'm sure is the case) am I missing something here?

    Sunday, March 15, 2009 4:56 PM
    Answerer
  • The sample code is just to reproduce the error.
    In my real code I have a business object which has a child business object Employee, and one more child Business object,
    which has Employee.

    Something like this:

    Master business object
    -------- has Employee business object (emp1)
    -------- has child business object
    ------------- child has Employee business object (emp2)

    When I save master BO(business object) it calls save to the child BOs. Sometimes emp1 and emp2 are the same BO.
    The save operation is done in one DataContext.
    I save emp1 and attach its corresponding entity then I save emp2 and when I attach its entity I get the error.

    I hope I explain it right.

    Do I have to redesign my business objects or I can do something with linq to avoid that exception?
    Monday, March 16, 2009 7:10 AM
  • Can you post code that more accurately reflects the scenario you want to achieve? Although I understand more based on your last post, I still find it vague because I don't know which objects you're trying to insert, which ones you're trying to update, and which ones you only want to reference from the database.

    Monday, March 16, 2009 3:37 PM
    Answerer
  • I will try to explain it in more detail.

    Master business object Order
    -------- has Employee business object
    -------- has child collection of business object OrderItems
    ------------- one OrderItem has a child business object Employee

    I create a new Order with an existing Employee fetched form the DB.
    Order BO has a new OrderItems collection and each new OrderItem in the collection has an existing Employee fetched from DB.
    Sometimes Order.Employee is the same as OrderItem.Employee.

    If Order.Employee is diffrent from OrderItem.Employee I do not have any problems.

    I call method Save on Order BO which attaches Employee and submit changes to DB.
    Then I call save on OrderItems which again attaches the same Employee, and because I reuse the DataContext to save Order and its children I get the error on the second try to attach Employee.
    Tuesday, March 17, 2009 7:29 AM
  • Hi tchimev, sorry for the delay. Is this similar to what you are trying to achieve?

    static void Main(string[] args)  
    {  
        // We will attach these later.  
        Employee employee1 = new Employee { employeeId = 1 };  
        Employee employee2 = new Employee { employeeId = 2 };  
     
        // Setup objects  
        Order order = new Order();  
        order.OrderItems.Add(new OrderItem { Employee = employee1 });  
        order.OrderItems.Add(new OrderItem { Employee = employee2 });  
        order.Employee = employee1;  
     
        SaveOrder(order);  
    }  
     
    static void SaveOrder(Order order)  
    {  
        using (ReproDataContext db = new ReproDataContext())  
        {  
            foreach (OrderItem oi in order.OrderItems)  
            {  
                db.Employees.Attach(oi.Employee);  
                db.OrderItems.InsertOnSubmit(oi);  
            }  
     
            // This will throw.  
            db.Employees.Attach(order.Employee);  
     
            db.Orders.InsertOnSubmit(order);  
            db.SubmitChanges();  
        }  


    If so you can set the employeeId properties on the Order and OrderItem objects instead of setting the Employee objects directly. This way you will avoid the need to attach the Employees. This greatly simplifies the code:

    static void Main(string[] args)  
    {  
        // Setup objects  
        Order order = new Order();  
        order.OrderItems.Add(new OrderItem { employeeId = 1 });  
        order.OrderItems.Add(new OrderItem { employeeId = 2 });  
        order.employeeId = 1;  
     
        SaveOrder(order);  
    }  
     
    static void SaveOrder(Order order)  
    {  
        using (ReproDataContext db = new ReproDataContext())  
        {  
            db.Orders.InsertOnSubmit(order);  
            db.SubmitChanges();  
        }  


    If you cannot use the IDs in your solution there are other alternatives. For example, you can attach all of the Employees on the Order Items and keep track of their IDs yourself, and then ensure that the Order's Employee is not the same as any of the ones that were previously attached.

    You can also use the Enumerable.Distinct and/or the Enumerable.Union extension methods to get rid of duplicates and then use db.Employees.AttachAll to attach the result IEnumerable to the context.

    Thursday, March 19, 2009 6:12 PM
    Answerer
  • Hi David, thank you for your help.

    In my solution the logic is little different.

    First I save the Order which attaches the Order.Employee and inserts the Order to DB.
    Then I save the OrderItems collection which attaches OrderItem.Employee and inserts OrderItem to DB.
    This is done in the same DataContext.

    My data access logic uses entities not entity ids so I cannot use employeeId property on the Order and OrderItmes.

    Before I attach an entity I call GetOriginalEntityState(entity) method on the entity table so I can check if it is already attached.
    I think the problem comes from this method because when I attach Order.Employee and insert Order I call SubmitChanges() method,
    which I guess clears the states of the Employee in this DataContext. Correct me if I'm wrong.
    And then I call GetOriginalEntityState(OrderItem.Employee) method on OrderItem.Employee table which returns null so I attach OrderItem.Employee and get that error.

    I hope this clears what I am doing in my data access layer.

    Friday, March 20, 2009 12:03 PM
  • Why not just use one call to SubmitChanges? Certainly you wouldn't want an order to be inserted without its order items. This should be all you need.


    static void SaveOrder(Order order)  
    {  
        using (ReproDataContext db = new ReproDataContext())  
        {  
            db.Employees.Attach(order.Employee);  
     
            db.Orders.InsertOnSubmit(order);  
            db.OrderItems.InsertAllOnSubmit(order.OrderItems);  
     
            db.SubmitChanges();   
        }  

    When you call Attach, LINQ to SQL traverses the entire object graph and attaches everything associated with that Employee instance. In this case, if you used code generated by the designer or SQLMetal, the Employee instance will have a reference to an Order collection, and each Order instance in that collection will reference a collection of OrderItem instances. Each of those OrderItems also has its own Employee reference.

    So, when you call Attach on the order.Employee object, everything, including the related Orders, its OrderItems, and each of those OrderItem's Employees, are attached.

    Then you call InsertOnSubmit and InsertAllOnSubmit to explicitly tell LINQ to SQL, "I want to insert these objects, not attach them."

    Calling SubmitChanges will insert the Order and OrderItems, while referencing existing Employees in the database. The output from the LINQ to SQL log follows. Notice that only Orders and OrderItems are inserted, as expected.

    INSERT INTO [dbo].[Order]([employeeId])  
    VALUES (@p0)  
     
    SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]  
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]  
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1  
     
    INSERT INTO [dbo].[OrderItem]([orderId], [employeeId])  
    VALUES (@p0, @p1)  
     
    SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]  
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [14]  
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]  
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1  
     
    INSERT INTO [dbo].[OrderItem]([orderId], [employeeId])  
    VALUES (@p0, @p1)  
     
    SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]  
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [14]  
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]  
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 
    Friday, March 20, 2009 3:20 PM
    Answerer