duplicate values in the index, primary key, or relationship

Question

Using JetEntityFrameworkProvider and an MS Access/Jet File I am updating values of an Entity, and I am receiving the following error when I save changes.

OleDbException: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Here is my code

var pagesList = projectDB.Pages.OrderBy(x => x.PageNumber).ToList();

for(int i = 0; i < pagesList.Count(); i++)
{
    pagesList[i].BookNumber = i+30;
}

foreach(var blah2 in pagesList.OrderBy(x => x.Id))
{
    System.Console.WriteLine($" {{ID:{blah2.Id}, BN:{blah2.BookNumber}, PN:{blah2.PageNumber}}}");

}

projectDB.SaveChanges();

This is my code output, as you can see there is no duplicate index, or duplicate BookNumber (which is indexed and is unique)

 {ID:2, BN:58, PN:5}
 {ID:3, BN:59, PN:6}
 {ID:6, BN:62, PN:11}
 {ID:7, BN:65, PN:21}
 {ID:20, BN:64, PN:13}
 {ID:21, BN:66, PN:22}
 {ID:25, BN:67, PN:23}
 {ID:29, BN:68, PN:24}
 {ID:78, BN:35, PN:1}
 {ID:79, BN:45, PN:2}
 {ID:108, BN:34, PN:1}
 {ID:132, BN:73, PN:41}
 {ID:177, BN:33, PN:1}
 {ID:291, BN:74, PN:42}
 {ID:318, BN:32, PN:1}
 {ID:319, BN:42, PN:2}
 {ID:320, BN:48, PN:3}
 {ID:340, BN:31, PN:1}
 {ID:341, BN:41, PN:2}
 {ID:342, BN:50, PN:3}
 {ID:343, BN:53, PN:4}
 {ID:344, BN:55, PN:5}
 {ID:345, BN:60, PN:6}
 {ID:346, BN:61, PN:7}
 {ID:452, BN:71, PN:32}
 {ID:469, BN:63, PN:12}
 {ID:510, BN:72, PN:39}
 {ID:520, BN:43, PN:2}
 {ID:524, BN:75, PN:43}
 {ID:533, BN:70, PN:31}
 {ID:539, BN:69, PN:25}
 {ID:610, BN:30, PN:1}
 {ID:611, BN:36, PN:1}
 {ID:612, BN:46, PN:2}
 {ID:613, BN:37, PN:1}
 {ID:614, BN:38, PN:1}
 {ID:615, BN:44, PN:2}
 {ID:616, BN:51, PN:3}
 {ID:617, BN:52, PN:4}
 {ID:618, BN:56, PN:5}
 {ID:619, BN:40, PN:1}
 {ID:620, BN:39, PN:1}
 {ID:621, BN:47, PN:2}
 {ID:622, BN:49, PN:3}
 {ID:623, BN:54, PN:4}
 {ID:624, BN:57, PN:5}

ETA:

Model

[Table("Content")]
public class Page
{
    //** Primary Key
    [Column("Counter")]
    public int Id { get; set; }

    public int ProjectCounter { get; set; }

    public short Version { get; set; }
    public short Revision { get; set; }
    public bool Locked { get; set; }

    public int DrawingType { get; set; }
    //** Forign Key?
    public int DeviceLocationCounter { get; set; }
    //** Forign Key?
    public int FolderID { get; set; }

    [Column("Page")]
    public int PageNumber { get; set; }
    //** Indexed, Unique 
    public int BookNumber { get; set; }

    public string PageIndex { get; set; }

    //** Product 
    //** DrawingObject is not here

    public bool Update { get; set; }
    public short Flag { get; set; }        
}

score 2 · Accepted Answer · answered Jun 29 '17 at 12:55

Without the model is hard to understand the problem but I think you have defined a unique index on BookNumber.

EF runs an UPDATE statement for each update (for each page).
At a certain point, on the DB (not in memory), there are 2 entities with the same BookNumber.
Also inside a transaction you could have the same problem so the solution is to update the books in two different steps

var pagesList = projectDB.Pages.OrderBy(x => x.PageNumber).ToList();

for (int i = 0; i < pagesList.Count; i++)
    pagesList[i].BookNumber = null;

projectDB.SaveChanges();

for (int i = 0; i < pagesList.Count; i++)
    pagesList[i].BookNumber = i + 30;

foreach (var blah2 in pagesList.OrderBy(x => x.Id))
    Console.WriteLine("{{ID:{0}, BN:{1}, PN:{2}}}", blah2.Id, blah2.BookNumber, blah2.PageNumber);

projectDB.SaveChanges();

duplicate values in the index, primary key, or relationship

1 Answers1

Linked