Entity Framework duplicate primary key

Question

I'm using Entity Framework 6 and sqlite to store and retrieve some data. When the following code block runs, an exception is thrown on db.SaveChanges().

using (var db = new RepoDatabase(DBPath))
{
    var folder1 = new Folder
    {
        Name = "name1",
        Subtitle = "subtitle",
        ParentFolder = null,
        IsHidden = false,
        IsSimple = true,
        QueryOrFilePath = "query"
    };

    var folder2 = new Folder
    {
        Name = "name2",
        Subtitle = "subtitle",
        ParentFolder = folder1,
        IsHidden = false,
        IsSimple = true,
        QueryOrFilePath = "query"
    };
    db.Folders.Add(folder1);
    db.Folders.Add(folder2);
    db.SaveChanges();
}

Exception:

'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll

Additional information: Unable to determine the principal end of the 'RhythmRepository.Folder_ParentFolder' relationship. Multiple added entities may have the same primary key.

From my understanding, this problem often occurs when the ID is used directly for foreign keys, but that doesn't seem to be the case here, as the type of "ParentFolder" is "Folder".

The folder type is set to auto-increment in the database, and has the attribute:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)] 

The underlying entity structure:

class Folder
{
    #region database fields
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int FolderID { get; set; }
    public string Name { get; set; }
    public string Subtitle { get; set; }
    [ForeignKey("FolderID")]
    public Folder ParentFolder { get; set; }
    public bool IsHidden { get; set; }
    public bool IsSimple { get; set; }
    public string QueryOrFilePath { get; set; }
    #endregion
}

And the SQL query to create the table:

CREATE TABLE IF NOT EXISTS Folders 
(
    FolderID INTEGER PRIMARY KEY AUTOINCREMENT,
    Name varchar(255) NOT NULL,
    Subtitle varchar(255) NULL,
    ParentFolderID INT NULL,
    IsHidden BIT NOT NULL,
    IsSimple BIT NOT NULL,
    QueryOrFilePath varchar(255) NOT NULL,
    FOREIGN KEY (ParentFolderID) REFERENCES Folders(FolderID)
);

Answer 1

The error is in the part

[ForeignKey("FolderID")]
public Folder ParentFolder { get; set; }

This makes EF think that FolderID is the foreign key to the parent Folder. In reality, it's ParentFolderID. So change your class definition and mapping to

class Folder
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int FolderID { get; set; }

    public int? ParentFolderID { get; set; }
    [ForeignKey("ParentFolderID")]
    public Folder ParentFolder { get; set; }

    ...
}

If FolderID is the foreign key, EF concludes that there is a 1:1 association between a folder and its parent. Normally, a 1:1 association is implemented by a primary key that's also the foreign key to the parent. I.e the child's primary key copies its parent's primary key. When parent and child are the same entity class, two records of the same table would have to have the same primary key --impossible.