EF Core Foreign Key Constraint error that I can't seem to understand-CodePudding

I try to run Update-Database using the Package Manager Console after having run a migration that after looking at it seems fine, no error I can see. When I run the update, here's what I get:

Failed executing DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'InviteId', N'Code', N'CreatedBy', N'CreatedDateTime', N'DateCreated', N'DateUsed', N'PhoneNumber', N'Used', N'UserId') AND [object_id] = OBJECT_ID(N'[Invites]'))
    SET IDENTITY_INSERT [Invites] ON;
INSERT INTO [Invites] ([InviteId], [Code], [CreatedBy], [CreatedDateTime], [DateCreated], [DateUsed], [PhoneNumber], [Used], [UserId])
VALUES ('random-guid', 45564, NULL, 'random-time', 'random-date', NULL, N'', CAST(0 AS bit), 'the-user-guid'),
('random-guid', 45686, NULL, 'random-time', 'random-date', NULL, N'', CAST(0 AS bit), 'the-user-guid'),
('random-guid', 45645, NULL, 'random-time', 'random-date', NULL, N'', CAST(0 AS bit), 'the-user-guid');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'InviteId', N'Code', N'CreatedBy', N'CreatedDateTime', N'DateCreated', N'DateUsed', N'PhoneNumber', N'Used', N'UserId') AND [object_id] = OBJECT_ID(N'[Invites]'))
    SET IDENTITY_INSERT [Invites] OFF;
Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Invites_AspNetUsers_UserId". The conflict occurred in database "LocalDB", table "dbo.AspNetUsers", column 'Id'.
The statement has been terminated.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId: *something*
Error Number:547,State:0,Class:16
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Invites_AspNetUsers_UserId". The conflict occurred in database "LocalDB", table "dbo.AspNetUsers", column 'Id'.
The statement has been terminated.

Here are the Tables and their Configuration using the Fluent API:

User:

public class ApplicationUser : IdentityUser<Guid>
    {
        public ApplicationUser()
        {

        }

        public string FirstName { get; set; }
        public string LastName { get; set; }
        public Guid GenderId { get; set; }
        public virtual Gender Gender { get; set; }
        public virtual IReadOnlyCollection<Invite> Invites { get; set; }
    }

public class ApplicationUserConfiguration : IEntityTypeConfiguration<ApplicationUser>
    {
        public void Configure(EntityTypeBuilder<ApplicationUser> builder)
        {
            builder.Property(p => p.FirstName)
                .IsRequired();

            builder.Property(p => p.LastName)
                .IsRequired();

            builder.Property(p => p.GenderId)
                .IsRequired();

            builder.HasOne<Gender>(d => d.Gender)
                .WithMany(d => d.Users)
                .HasForeignKey(d => d.GenderId);

            builder.HasMany<Invite>(d => d.Invites)
                .WithOne(d => d.User)
                .HasForeignKey(d => d.UserId);
        }
    }

Invites:

public class Invite : AuditableEntities
    {
        public Guid InviteId { get; set; }
        public string PhoneNumber { get; set; }
        public int Code { get; set; }
        public bool Used { get; set; }
        public DateTime DateCreated { get; set; }
        public DateTime? DateUsed { get; set; }
        public Guid UserId { get; set; }
        public virtual ApplicationUser User { get; set; }
    }

public class InviteConfiguration : IEntityTypeConfiguration<Invite>
    {
        public void Configure(EntityTypeBuilder<Invite> builder)
        {
            builder.Property(p => p.Code)
                .IsRequired();

            builder.Property(p => p.Used)
                .IsRequired();

            builder.Property(p => p.UserId)
                .IsRequired();

            builder.HasOne<ApplicationUser>(d => d.User)
                .WithMany(d => d.Invites)
                .HasForeignKey(d => d.UserId)
                .IsRequired();
        }
    }

I honestly don't know what the problem is and it only happens now, what's weird is a couple months ago it worked well so I don't know if it's due to an update in EF Core's system or something.

I know there isn't an Id property on the AppUser table but IdentityUser creates one automatically since I'm inheriting from it.

I’m also adding Seed Data to the migration, I don’t know if that’s the issue but if it is let me know so that I can share it, but there’s nothing I’m seeing that could be wrong there because as you can see in the SQL error statement where it inserts “the-user-id” it is a valid Id.

UPDATE

So I've changed a couple things based on what @wael-moughrbel said, hopefully I understood it, but it still doesn't work.

Changes in the Invite class:

public class Invite : AuditableEntities
    {
        [Key]
        public Guid InviteId { get; set; }
        public string PhoneNumber { get; set; }
        public int Code { get; set; }
        public bool Used { get; set; }
        public DateTime DateCreated { get; set; }
        public DateTime? DateUsed { get; set; }

        // Called the foreign key 'Id' just like IdentityUser<Guid> calls it.
        // Added attributes to make sure there can't
        // be no mistaking from EF Core.

        [ForeignKey("ApplicationUser")]
        public Guid Id { get; set; }
        public virtual ApplicationUser User { get; set; }
    }

But it still gives me this error:

Failed executing DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'InviteId', N'Code', N'CreatedBy', N'CreatedDateTime', N'DateCreated', N'DateUsed', N'Id', N'PhoneNumber', N'Used') AND [object_id] = OBJECT_ID(N'[Invites]'))
    SET IDENTITY_INSERT [Invites] ON;
INSERT INTO [Invites] ([InviteId], [Code], [CreatedBy], [CreatedDateTime], [DateCreated], [DateUsed], [Id], [PhoneNumber], [Used])
VALUES ('Id', 11111, NULL, 'time', 'date', NULL, 'user-id', N'', CAST(0 AS bit)),
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'InviteId', N'Code', N'CreatedBy', N'CreatedDateTime', N'DateCreated', N'DateUsed', N'Id', N'PhoneNumber', N'Used') AND [object_id] = OBJECT_ID(N'[Invites]'))
    SET IDENTITY_INSERT [Invites] OFF;
Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Invites_AspNetUsers_Id". The conflict occurred in database "LocalDB", table "dbo.AspNetUsers", column 'Id'.
The statement has been terminated.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:...
Error Number:547,State:0,Class:16
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Invites_AspNetUsers_Id". The conflict occurred in database "LocalDB", table "dbo.AspNetUsers", column 'Id'.
The statement has been terminated

CodePudding user response：

ForeignKeys cannot have a value that is not also in the primary key column of the referenced table.

Here the error indicates that the foreignKey violation is in UserId column of Invite table. Also according to the error and in the insert statement, there is an insertion of value the-user-guid in the field UserId. Since this value is not also in the referenced table, this foreignKey violation error occurred.

So, the error is related to the values being inserted not the definition of the entities. Presume that User entity has only one entry or record where UserId = 1 When you try to insert new entry for Invite entity with UserId = 3 as foreignKey and since there are no records for UserId = 3 in the referenced entity User, the foreignKey violation error occurs. So check that all insertions for Invite has values of UserId that exists and matches values in referenced table User.