none
Change index values, get "Cannot insert duplicate key" RRS feed

  • Question

  • I am changing only the values of an index field and I see no duplicates yet when I SaveChanges I get:

    System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.OrderItems' with unique index 'IX_ListIndex'. The duplicate key value is (2).

    I see only one item with a ListIndex of 2.

    The following is the OrderItem definition.

    public class OrderItem
    {
    	public OrderItem()
    	{
    		this.ListIndex = 0;
    		this.Name = string.Empty;
    	}
    	public OrderItem(int ListIndex, string Name)
    	{
    		this.ListIndex = ListIndex;
    		this.Name = Name;
    	}
    	public int Id { get; set; }
    	[Required, Index(IsUnique = true)]
    	public int ListIndex { get; set; }
    	[Required]
    	public string Name { get; set; }
    }

    The purpose of ListIndex is to determine how the items are shown; it is the index used to determine where the item is shown in the list. So if the user wants to reorder the sequence of the items in the list then I change the ListIndex accordingly. The lowest actual value of ListIndex should be 1. I am doing the moving (re-sequencing) as in the following.

    private void MoveItem(int Afterx)
    {
    	int MovingId = (int)ViewState["MovingId"];
    	ViewState["MovingId"] = NotMoving;
    	try
    	{
    		OrderItem ItemMoving = db.OrderItems.Find(MovingId);
    		IEnumerable<OrderItem> items =
    			from s in db.OrderItems
    			where s.ListIndex > Afterx
    			orderby s.ListIndex
    			select s;
    		int newx = 0;
    		if (Afterx == -1)
    			newx = 1;
    		else
    			newx = items.First<OrderItem>().ListIndex;
    		ItemMoving.ListIndex = newx;
    		foreach (OrderItem s in items)
    		{
    			if (s.Id != ItemMoving.Id)
    				s.ListIndex = ++newx;
    		}
                    foreach (DbEntityEntry e in TrackEntries)
                    {
                        OrderItem s;
                        if ((s = e.Entity as OrderItem) is OrderItem)
                            System.Diagnostics.Debug.WriteLine($"\t{s.Id}\t{s.ListIndex} {e.State} {s.Name}");
                        else
                            System.Diagnostics.Debug.WriteLine($"Type {e.GetType().Name} is not OrderItem");
                    }
    		db.SaveChanges();
    	}
    	catch (Exception ex)
    	{
    		WalkExceptions(ex);
    	}
    }

    If the item is to be moved to the top then I call MoveItem with Afterx=-1 otherwise Afterx is the index of the item to be moved after. For now I am calling MoveItem with Afterx=-1 but the problem is the same if I try to move after any item.

    As you see, I dump the items before the SaveChanges and the data is perfect. The ListIndex starts at 1 and increments by 1 up to the number of items, so I do not understand why EF has a problem. Do I need to do something as in Attaching and Detaching Objects?

    For what it is worth, I do not want to use StackOverflow. I prefer that Microsoft fix the documentation and whatever the problem is with these forums. I did look at StackOverflow and I don't see an answer there.



    Sam Hobbs
    SimpleSamples.Info



    • Edited by Simple Samples Sunday, March 24, 2019 8:38 PM fixed the code so it works except for the specified problem
    Saturday, March 23, 2019 9:44 PM

Answers

All replies

  • Hi,

        s.ListIndex = ++newx;

    After you modified the ListIndex of an item, it may be the same as the ListIndex of the next item, which resulting in a conflict.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 25, 2019 9:08 AM
  •     s.ListIndex = ++newx;

    After you modified the ListIndex of an item, it may be the same as the ListIndex of the next item

    The ++ operator increments the value then returns the new value. This ensures that each ListIndex has a unique value. And it does; see my original post. I look at the data and the values do begin with 1 and go to the number of items. You say may and that word is ambiguous. Do you mean might? Are you guessing? Because I know that the ++ is working correctly.



    Sam Hobbs
    SimpleSamples.Info

    Monday, March 25, 2019 5:03 PM
  • Hi,

    Yes, you are right. I viewed your code, but didn't see any code for the "Entity" operation, you just modified the ListIndex of the items in "items". Have you tried "System.Data.Entity.EntityState.Modified"?

    Or, could you provide a simple project that can reproduce the issue via OneDrive?

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 27, 2019 9:55 AM
  • Yes, that is the point of what I am saying. I only modified the ListIndex.

    I do not understand how to use Modified for the EntityState. I did modify the code slightly in the original post. Note the following loop before the SaveChanges.

    foreach (DbEntityEntry e in TrackEntries)
    {
    	OrderItem s;
    	if ((s = e.Entity as OrderItem) is OrderItem)
    		System.Diagnostics.Debug.WriteLine($"\t{s.Id}\t{s.ListIndex} {e.State} {s.Name}");
    	else
    		System.Diagnostics.Debug.WriteLine($"Type {e.GetType().Name} is not OrderItem");
    }

    In my original post the loop did not show the DbEntityEntry.Entity but now it does. Is that what you mean by EntityState? Because the DbEntityEntry.Entity is Modified for all the rows that were modified (all the entries in TrackEntries).

    Yes, I realized that I need to provide a complete sample. The application is a Web App (or Web Site, I get them confused) but I will try to make a console program. If I can recreate it in a console program then I know that will help.



    Sam Hobbs
    SimpleSamples.Info


    • Edited by Simple Samples Wednesday, March 27, 2019 6:17 PM I forgot the code
    Wednesday, March 27, 2019 6:16 PM
  • I created Repos/Forums/EFDuplicateProblem at master · SimpleSamples/Repos in GitHub. It is a sample console program that shows the problem. See SampleOutput.txt for a sample output.

    The OrderItem class is the same as in the original post. The following is the important part of the code; the rest is just overhead, such as constructing OrdersContext.

    private static void MoveAnItem(OrdersContext db)
    {
        if (db.OrderItems.Count<OrderItem>() <= 2)
        {
            Console.WriteLine("No data");
            return;
        }
        try
        {
            OrderItem ItemMoving = db.OrderItems.OrderByDescending(p => p.ListIndex).FirstOrDefault();
            Console.WriteLine($"Moving Id: {ItemMoving.Id}, ListIndex: {ItemMoving.ListIndex}, Name: {ItemMoving.Name}");
            IEnumerable<OrderItem> items =
                from s in db.OrderItems
                orderby s.ListIndex
                select s;
            Console.WriteLine("\tData before resequence");
            foreach (OrderItem item in items)
                Console.WriteLine($"{item.Id} {item.ListIndex} {item.Name}");
            int newx = 1;
            ItemMoving.ListIndex = newx;
            foreach (OrderItem s in items)
            {
                if (s.Id != ItemMoving.Id)
                    s.ListIndex = ++newx;
            }
            Console.WriteLine("\tData before SaveChanges");
            IEnumerable<DbEntityEntry> TrackEntries = db.ChangeTracker.Entries();
            foreach (DbEntityEntry e in TrackEntries)
            {
                OrderItem s;
                if ((s = e.Entity as OrderItem) is OrderItem)
                    Console.WriteLine($"{s.Id} {s.ListIndex} {e.State} {s.Name}");
                else
                    Console.WriteLine($"Type {e.GetType().Name} is not OrderItem");
            }
            db.SaveChanges();
        }
        catch (Exception ex)
        {
            WalkExceptions(ex);
        }
    }
    

    The IEnumerable<OrderItem> items might seem unnecessary here but the actual code needs to support moving (re-sequencing) of arbitrary items to arbitrary new locations.



    Sam Hobbs
    SimpleSamples.Info

    Thursday, March 28, 2019 1:20 AM
  • See Change index values, get "Cannot insert duplicate key" | The ASP.NET Forums. The answer is that the existing records in the database are duplicates, eventhough there would be no duplicates after all the records are updated. My guess is that there is a more direct way (there should be) but at least there are possible solutions.


    Sam Hobbs
    SimpleSamples.Info

    Tuesday, April 9, 2019 3:57 PM
  • Hi,

        s.ListIndex = ++newx;

    After you modified the ListIndex of an item, it may be the same as the ListIndex of the next item, which resulting in a conflict.


    So that could be considered as an explanation except we just need to explain that the duplicates would be the data in the database.


    Sam Hobbs
    SimpleSamples.Info

    Tuesday, April 9, 2019 3:59 PM