Self referencing Entity, PostgreSQL database

[kejdajar]

Hello,
I have an issue creating a database table based on a self-referencing class, using:

• Microsoft.EntityFrameworkCore v3.1.1
• Microsoft.EntityFrameworkCore.Design v3.1.1
• Npgsql.EntityFrameworkCore.PostgreSQL v3.1.1.2

Steps to reproduce

I am trying to create a database table based on the following model:

public class User
    {
        public User(string fullName)
        {
            FullName = fullName;             
        }
        
        [Key]
        public int Id { get; set; }

        // common property
        public string FullName { get; set; }
       
        // foreign key & navigation property
        public int FriendId { get; set; }
        public User Friend { get; set; } // this property is of the same type - User
    }

DatabaseContext is set up this way:

 public class SelfReferenceIssueDbContext : DbContext
    {
        public DbSet<User> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql("User ID=postgres;Password=root;Server=localhost;Port=5432;Database=issue;");
        }
    }

my business logic is as follows:

using (var context = new SelfReferenceIssueDbContext())
            {
                var johnDoe = new User("John Doe");
                johnDoe.Friend = johnDoe; // here I'am assigning John Doe as a friend of himself
                context.Users.Add(johnDoe);
                context.SaveChanges();
            }

I am using The EF Core tools version 3.1.1 to create an initial migration and update the database.
The database migration and update executes without any errors. Unfortunately, when I try to run my application, I get the following error. Project reproducing the error is attached.

Exception
PostgresException: 23503: insert or update on table "Users" violates foreign key constraint "FK_Users_Users_FriendId"

Full error details:

Microsoft.EntityFrameworkCore.DbUpdateException
  HResult=0x80131500
  Message=An error occurred while updating the entries. See the inner exception for details.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at SelfReferenceIssue.Program.Main(String[] args) in C:\Users\kejda\Desktop\SelfReferenceIssue\SelfReferenceIssue\Program.cs:line 15

  This exception was originally thrown at this call stack:
	Npgsql.NpgsqlConnector.DoReadMessage.__ReadMessageLong|0(Npgsql.DataRowLoadingMode, bool, bool)
	System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
	Npgsql.NpgsqlConnector.DoReadMessage.__ReadMessageLong|0(Npgsql.DataRowLoadingMode, bool, bool)
	System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
	System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(System.Threading.Tasks.Task)
	Npgsql.NpgsqlDataReader.NextResult(bool, bool)
	System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
	System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(System.Threading.Tasks.Task)
	System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(System.Threading.Tasks.Task)
    ...
    [Call Stack Truncated]
Inner Exception 1:
PostgresException: 23503: insert or update on table "Users" violates foreign key constraint "FK_Users_Users_FriendId"

Further technical details

Microsoft.EntityFrameworkCore v3.1.1
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL v3.1.1.2
Target framework: NET Core 3.1
Operating system: Windows 10 Pro
IDE: Visual Studio 2019 16.4.5
SelfReferenceIssue.zip

[ajcvickers]

@kejdajar The problem here is that the key value for a new User is generated when the row is inserted into the database. But this means that the value to put into the FK is not known until after the insert has completed. This general case is covered by #15319 and #1699.

The way to make this work is to either:

• Use explicit key values instead of generated values
• Or save the entity first, and then set the self-referencing relationship. For example:

var johnDoe = new User("John Doe");
context.Users.Add(johnDoe);

context.SaveChanges();

johnDoe.Friend = johnDoe;
context.SaveChanges();

However, this requires an optional (nullable) FK, since it needs to be set to something value for the first save to complete. So, for your example, FriendId needs to be an int?.