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

  • Question

  • User-879727125 posted

    I am changing only the values of an index field (ListIndex). There are 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).

    Yet there is only one item with a ListIndex of 2 (or whatever the value is).

    This is for an ASP.Net web forms page using Entity Framework but my sample here is a console program.

    I am allowing items to be shown in whatever order (sequence) the user chooses. ListIndex is the field that determines the sequence. If an item is to be moved elsewhere then I set the ListIndex to the ListIndex+1 of the item it is to be moved after. If it is to be moved to the top then I set the item's ListIndex to 1 and all others with values increasing by 1. The sample only implements moving the last item to the top but the actual code needs to support moving (re-sequencing) of arbitrary items to arbitrary new locations.

    I created Repos/Forums/EFDuplicateProblem at master · SimpleSamples/Reposin GitHub. It is a sample console program that shows the problem and I post the code below. See SampleOutput.txt for a sample output.

    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?

    The following is the model.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.ComponentModel.DataAnnotations;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations.Schema;
    
    namespace EFDuplicateProblem
    {
        class OrdersContext : DbContext
        {
            public OrdersContext() : base(Properties.Settings.Default.ConnectionStringSetting) { }
            public DbSet<OrderItem> OrderItems { get; set; }
        }
    
        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 following is the console sample that moves the last item to the top, just as an example. The actual code needs to support moving (re-sequencing) of arbitrary items to arbitrary new locations.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.SqlClient;
    using System.Linq;
    
    namespace EFDuplicateProblem
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var context = new OrdersContext())
                {
                    try
                    {
                        Database.SetInitializer(new CreateDatabaseIfNotExists<OrdersContext>());
                        context.Database.Initialize(false);
                        MoveAnItem(context);
                    }
                    catch (DataException ex)
                    {
                        if (ex.InnerException == null)
                            Console.WriteLine("DataException: " + ex.Message);
                        else
                            Console.WriteLine(string.Format("DataException: {0}, inner {1}: {2}",
                                ex.Message, ex.InnerException.GetType().FullName,
                                ex.InnerException.Message));
                        return;
                    }
                    catch (SqlException ex)
                    {
                        Console.WriteLine("SqlException: " + ex.Message);
                        return;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.GetType().FullName + ": " + ex.Message);
                        return;
                    }
                }
            }
    
            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);
                }
            }
    
            private static void WalkExceptions(Exception ex)
            {
                Console.WriteLine("\tException tree:");
                Console.WriteLine(ex.GetType().FullName + ": " + ex.Message);
                while (ex.InnerException != null)
                {
                    ex = ex.InnerException;
                    Console.WriteLine(ex.GetType().FullName + ": " + ex.Message);
                }
            }
        }
    }

    Friday, April 5, 2019 7:03 PM

Answers

  • User-893317190 posted

    Hi Sam Hobbs ,

    The problem is with your  Index(IsUnique = true).

    For example,you have three OrderItem

    id 1  name item1 listindex 1
    id 2  name item2 listindex 2
    id 3  name item3 listindex 3

    When you update  item1 whose id is 1, you would set its listindex to 2 which is the same as item2.

    The easiest way is to remove this index.

    If you must have this index, you could try , here I  first update the item whose index is the biggest to a lager index. Then I update the second biggest , third biggest and so on.

    At last , I update the biggest index  back to 1. Please pay attention  every time I update , I save changes to make ef update record as I like.

     private static void MoveAnItem(OrdersContext db)
            {
     
                    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 descending
                        select s).ToList();
                    Console.WriteLine("\tData before resequence");
                    foreach (OrderItem item in items)
                        Console.WriteLine($"{item.Id} {item.ListIndex} {item.Name}");
                    int lastIndex = ItemMoving.ListIndex;
               
                    ItemMoving.ListIndex =ItemMoving.ListIndex+1;
                db.SaveChanges();
                foreach (OrderItem s in items)
                    {
                        if (s.Id != ItemMoving.Id)
                 s.ListIndex = lastIndex--;
                    db.SaveChanges();
    
                }
                
                ItemMoving.ListIndex = 1;
                db.SaveChanges();
           
            }
    

    Although this way works , it updates every time the item's index changes which will affect the performance, which you should consider.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 8, 2019 4:59 AM
  • User-879727125 posted

    Thank you.

    I would not say that the problem is that I set the index to Unique, but I understand what you are saying. The answer to my question is that the duplicate value is from the data that is in the database, right? I did not realize that that would be a problem. I would say that not setting the index to Unique is a possible solution but using unique is not the problem.

    I certainly agree that your suggestion would be a performance problem. I understand that allowing duplicates could allow the data to be stored without causing an error due to a temporary duplicate value.

    Another possibility would be to change ListIndex to values greater than the maximum value of ListIndex then update all the relevant records then re-sequence back to the lower values if I want that.

    So I have multiple possibilities, I think I have it now.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 9, 2019 6:17 AM

All replies

  • User-893317190 posted

    Hi Sam Hobbs ,

    The problem is with your  Index(IsUnique = true).

    For example,you have three OrderItem

    id 1  name item1 listindex 1
    id 2  name item2 listindex 2
    id 3  name item3 listindex 3

    When you update  item1 whose id is 1, you would set its listindex to 2 which is the same as item2.

    The easiest way is to remove this index.

    If you must have this index, you could try , here I  first update the item whose index is the biggest to a lager index. Then I update the second biggest , third biggest and so on.

    At last , I update the biggest index  back to 1. Please pay attention  every time I update , I save changes to make ef update record as I like.

     private static void MoveAnItem(OrdersContext db)
            {
     
                    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 descending
                        select s).ToList();
                    Console.WriteLine("\tData before resequence");
                    foreach (OrderItem item in items)
                        Console.WriteLine($"{item.Id} {item.ListIndex} {item.Name}");
                    int lastIndex = ItemMoving.ListIndex;
               
                    ItemMoving.ListIndex =ItemMoving.ListIndex+1;
                db.SaveChanges();
                foreach (OrderItem s in items)
                    {
                        if (s.Id != ItemMoving.Id)
                 s.ListIndex = lastIndex--;
                    db.SaveChanges();
    
                }
                
                ItemMoving.ListIndex = 1;
                db.SaveChanges();
           
            }
    

    Although this way works , it updates every time the item's index changes which will affect the performance, which you should consider.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 8, 2019 4:59 AM
  • User-879727125 posted

    Thank you.

    I would not say that the problem is that I set the index to Unique, but I understand what you are saying. The answer to my question is that the duplicate value is from the data that is in the database, right? I did not realize that that would be a problem. I would say that not setting the index to Unique is a possible solution but using unique is not the problem.

    I certainly agree that your suggestion would be a performance problem. I understand that allowing duplicates could allow the data to be stored without causing an error due to a temporary duplicate value.

    Another possibility would be to change ListIndex to values greater than the maximum value of ListIndex then update all the relevant records then re-sequence back to the lower values if I want that.

    So I have multiple possibilities, I think I have it now.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 9, 2019 6:17 AM