Duplicate values Error on Non-Index, Non-Unique property change

Question

I ask a similar question here, but this is a different situation that results in the same error message.

I am updating an NON-index, NON-unique property, PageNumber.

And I am receiving the following error

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.

public void DoRenumberPages(object blah)
{


    var hiddenPages = projectDB.Pages.AsEnumerable().Where(x => !IsVisibleDrawing(x.DrawingType) && x.DrawingType != 3001).ToList();

    for (int i = 0; i < hiddenPages.Count(); i++)
    {                
        hiddenPages[i].PageNumber = i + 1000;
    }

    var TOCPages = projectDB.Pages.AsEnumerable().Where(x => x.DrawingType == 3001).OrderBy(x => x.BookNumber).ToList();

    for (int i = 0; i < TOCPages.Count(); i++)
    {               
        TOCPages[i].PageNumber = i + 1;
    }

    var visiblePagesNotTOC = projectDB.Pages.AsEnumerable().Where(x => IsVisibleDrawing(x.DrawingType) && x.DrawingType != 3001).OrderBy(x => x.BookNumber).ToList();

    for (int i = 0; i < visiblePagesNotTOC.Count(); i++)
    {                
        visiblePagesNotTOC[i].PageNumber = i + TOCPages.Count() + 1;
    }


    projectDB.SaveChanges();

    RenumberPages.EnableExecute();

}

Page Model Class

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

ETA:

I have change public int BookNumber { get; set; } to public int? BookNumber { get; set; } This doesn't solve the issue.

Answer 1

Ok, I could start the project.
The problem is the one I wrote here duplicate values in the index, primary key, or relationship. If you enable query printing setting JetEntityFrameworkProvider.JetConnection.ShowSqlStatements = true; somewhere (I did it in your Test.Program.Main) you can see the statements that EF runs. The first update query is

update [Content]
set [Page] = @p0
where ([Counter] = @p1)

@p0 = 3
@p1 = 2

If you look at the database, the time you run the query the Page = 3 is already contained in the record with Counter = 3. You can't solve this problem also inside a transaction and also with other (next to all) DBMSs you have the same problem.

The only solution (if you need the unique index on Page) is to update in 2 different SaveChanges. For example:
1. Set Page = null then SaveChanges()
2. Set Page = number then SaveChanges()

Microsoft Access permits duplicated null values in unique indexes. If you will permit to use different databases you could have problems.