Answered by:
Change index values, get "Cannot insert duplicate key"

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