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);
}
}
}
}
