Entity framework code first modelling issues

I when I try to migrate/update my DB I always get messages, like that this and this column of this table causes multiple cascade delete paths etc. I understand what cascade delete paths are, but dont know why I get the errors in this case, because I did everything right in my option.

Since there are several models, that are connected with each other, I am giving you the link to my github repos (Folder Backend/Messanger-Backend).

https://github.com/niklasxulls/ASP-Angular-Messanger

Thank you for your help

EDIT: The error (I have also tried to comment the cause of the first error out, but there are also other errors, that appear, when the first error isnt there anymore. Thats why I have posted the repos, so you can try by yourself)

Error Message in German sry:

PM> Add-Migration "Initial mig"
Build started...
Build succeeded.
To undo this action, use Remove-Migration.
PM> Update-Database
Build started...
Build succeeded.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [GroupMessageStatuses] (
[GroupMessageStatusID] int NOT NULL IDENTITY,
[GroupMessageID] int NOT NULL,
[UserID] int NOT NULL,
[MessageStatusID] int NOT NULL,
[CreatedAt] datetime2 NULL,
[UpdatedAt] datetime2 NULL,
CONSTRAINT [PK_GroupMessageStatuses] PRIMARY KEY ([GroupMessageStatusID]),
CONSTRAINT [FK_GroupMessageStatuses_GroupMessages_GroupMessageID] FOREIGN KEY ([GroupMessageID]) REFERENCES [GroupMessages] ([GroupMessageID]) ON DELETE CASCADE,
CONSTRAINT [FK_GroupMessageStatuses_MessageStatuses_MessageStatusID] FOREIGN KEY ([MessageStatusID]) REFERENCES [MessageStatuses] ([MessageStatusID]) ON DELETE CASCADE,
CONSTRAINT [FK_GroupMessageStatuses_Users_UserID] FOREIGN KEY ([UserID]) REFERENCES [Users] ([UserID]) ON DELETE CASCADE
);
Failed executing DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [GroupMessageStatuses] (
[GroupMessageStatusID] int NOT NULL IDENTITY,
[GroupMessageID] int NOT NULL,
[UserID] int NOT NULL,
[MessageStatusID] int NOT NULL,
[CreatedAt] datetime2 NULL,
[UpdatedAt] datetime2 NULL,
CONSTRAINT [PK_GroupMessageStatuses] PRIMARY KEY ([GroupMessageStatusID]),
CONSTRAINT [FK_GroupMessageStatuses_GroupMessages_GroupMessageID] FOREIGN KEY ([GroupMessageID]) REFERENCES [GroupMessages] ([GroupMessageID]) ON DELETE CASCADE,
CONSTRAINT [FK_GroupMessageStatuses_MessageStatuses_MessageStatusID] FOREIGN KEY ([MessageStatusID]) REFERENCES [MessageStatuses] ([MessageStatusID]) ON DELETE CASCADE,
CONSTRAINT [FK_GroupMessageStatuses_Users_UserID] FOREIGN KEY ([UserID]) REFERENCES [Users] ([UserID]) ON DELETE CASCADE
);
Microsoft.Data.SqlClient.SqlException (0x80131904): Das Einführen der FOREIGN KEY-Einschränkung "FK_GroupMessageStatuses_Users_UserID" für die GroupMessageStatuses-Tabelle kann Schleifen oder mehrere Kaskadepfade verursachen. Geben Sie ON DELETE NO ACTION oder ON UPDATE NO ACTION an, oder ändern Sie andere FOREIGN KEY-Einschränkungen.
Die Einschränkung oder der Index konnte nicht erstellt werden. Siehe vorherige Fehler.
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:86f8c011-ee35-44cd-a526-a5cfe9d579be
Error Number:1785,State:0,Class:16
Das Einführen der FOREIGN KEY-Einschränkung "FK_GroupMessageStatuses_Users_UserID" für die GroupMessageStatuses-Tabelle kann Schleifen oder mehrere Kaskadepfade verursachen. Geben Sie ON DELETE NO ACTION oder ON UPDATE NO ACTION an, oder ändern Sie andere FOREIGN KEY-Einschränkungen.
Die Einschränkung oder der Index konnte nicht erstellt werden. Siehe vorherige Fehler.

 protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "AttachmentType",
                columns: table => new
                {
                    AttachmentTypeID = table.Column(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    AttachmentTypeName = table.Column(type: "nvarchar(max)", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AttachmentType", x => x.AttachmentTypeID);
                });

            migrationBuilder.CreateTable(
                name: "MessageStatuses",
                columns: table => new
                {
                    MessageStatusID = table.Column(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    MessageStatusText = table.Column(type: "nvarchar(max)", nullable: false),
                    MessageStatusID1 = table.Column(type: "int", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MessageStatuses", x => x.MessageStatusID);
                    table.ForeignKey(
                        name: "FK_MessageStatuses_MessageStatuses_MessageStatusID1",
                        column: x => x.MessageStatusID1,
                        principalTable: "MessageStatuses",
                        principalColumn: "MessageStatusID",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateTable(
                name: "AttachmentGroupMessage",
                columns: table => new
                {
                    AttachmentsAttachmentID = table.Column(type: "int", nullable: false),
                    GroupMessagesGroupMessageID = table.Column(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AttachmentGroupMessage", x => new { x.AttachmentsAttachmentID, x.GroupMessagesGroupMessageID });
                });

            migrationBuilder.CreateTable(
                name: "AttachmentUserMessage",
                columns: table => new
                {
                    AttachmentsAttachmentID = table.Column(type: "int", nullable: false),
                    UserMessagesUserMessageID = table.Column(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AttachmentUserMessage", x => new { x.AttachmentsAttachmentID, x.UserMessagesUserMessageID });
                });

            migrationBuilder.CreateTable(
                name: "Groups",
                columns: table => new
                {
                    GroupID = table.Column(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    GroupName = table.Column(type: "nvarchar(50)", nullable: false),
                    GroupDescription = table.Column(type: "nvarchar(250)", nullable: true),
                    AttachmentID = table.Column(type: "int", nullable: false),
                    CreatedAt = table.Column(type: "datetime2", nullable: true),
                    UpdatedAt = table.Column(type: "datetime2", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Groups", x => x.GroupID);
                });

            migrationBuilder.CreateTable(
                name: "Attachments",
                columns: table => new
                {
                    AttachmentID = table.Column(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    AttachmentPath = table.Column(type: "nvarchar(100)", nullable: false),
                    AttachmentTypeID = table.Column(type: "int", nullable: false),
                    GroupID = table.Column(type: "int", nullable: true),
                    CreatedAt = table.Column(type: "datetime2", nullable: true),
                    UpdatedAt = table.Column(type: "datetime2", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Attachments", x => x.AttachmentID);
                    table.ForeignKey(
                        name: "FK_Attachments_AttachmentType_AttachmentTypeID",
                        column: x => x.AttachmentTypeID,
                        principalTable: "AttachmentType",
                        principalColumn: "AttachmentTypeID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_Attachments_Groups_GroupID",
                        column: x => x.GroupID,
                        principalTable: "Groups",
                        principalColumn: "GroupID",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateTable(
                name: "Users",
                columns: table => new
                {
                    UserID = table.Column(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    UserFirstname = table.Column(type: "nvarchar(50)", nullable: true),
                    UserLastname = table.Column(type: "nvarchar(50)", nullable: true),
                    UserPhoneNumber = table.Column(type: "nvarchar(20)", nullable: false),
                    UserStatus = table.Column(type: "nvarchar(250)", nullable: true),
                    AttachmentID = table.Column(type: "int", nullable: false),
                    CreatedAt = table.Column(type: "datetime2", nullable: true),
                    UpdatedAt = table.Column(type: "datetime2", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Users", x => x.UserID);
                    table.ForeignKey(
                        name: "FK_Users_Attachments_AttachmentID",
                        column: x => x.AttachmentID,
                        principalTable: "Attachments",
                        principalColumn: "AttachmentID",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateTable(
                name: "GroupMessages",
                columns: table => new
                {
                    GroupMessageID = table.Column(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    GroupMessageSenderID = table.Column(type: "int", nullable: false),
                    GroupID = table.Column(type: "int", nullable: false),
                    GroupMessageText = table.Column(type: "nvarchar(500)", nullable: false),
                    CreatedAt = table.Column(type: "datetime2", nullable: true),
                    UpdatedAt = table.Column(type: "datetime2", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_GroupMessages", x => x.GroupMessageID);
                    table.ForeignKey(
                        name: "FK_GroupMessages_Groups_GroupID",
                        column: x => x.GroupID,
                        principalTable: "Groups",
                        principalColumn: "GroupID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_GroupMessages_Users_GroupMessageSenderID",
                        column: x => x.GroupMessageSenderID,
                        principalTable: "Users",
                        principalColumn: "UserID",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateTable(
                name: "GroupUser",
                columns: table => new
                {
                    GroupsGroupID = table.Column(type: "int", nullable: false),
                    UsersUserID = table.Column(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_GroupUser", x => new { x.GroupsGroupID, x.UsersUserID });
                    table.ForeignKey(
                        name: "FK_GroupUser_Groups_GroupsGroupID",
                        column: x => x.GroupsGroupID,
                        principalTable: "Groups",
                        principalColumn: "GroupID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_GroupUser_Users_UsersUserID",
                        column: x => x.UsersUserID,
                        principalTable: "Users",
                        principalColumn: "UserID",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateTable(
                name: "UserMessages",
                columns: table => new
                {
                    UserMessageID = table.Column(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    UserMessageSenderID = table.Column(type: "int", nullable: true),
                    UserMessageReceiverID = table.Column(type: "int", nullable: true),
                    UserMessageText = table.Column(type: "nvarchar(500)", nullable: true),
                    UserMessageReceived = table.Column(type: "DateTime", nullable: false),
                    UserMessageRead = table.Column(type: "DateTime", nullable: false),
                    CreatedAt = table.Column(type: "datetime2", nullable: true),
                    UpdatedAt = table.Column(type: "datetime2", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_UserMessages", x => x.UserMessageID);
                    table.ForeignKey(
                        name: "FK_UserMessages_Users_UserMessageReceiverID",
                        column: x => x.UserMessageReceiverID,
                        principalTable: "Users",
                        principalColumn: "UserID");
                    table.ForeignKey(
                        name: "FK_UserMessages_Users_UserMessageSenderID",
                        column: x => x.UserMessageSenderID,
                        principalTable: "Users",
                        principalColumn: "UserID");
                });

            migrationBuilder.CreateTable(
                name: "GroupMessageStatuses",
                columns: table => new
                {
                    GroupMessageStatusID = table.Column(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    GroupMessageID = table.Column(type: "int", nullable: false),
                    UserID = table.Column(type: "int", nullable: false),
                    MessageStatusID = table.Column(type: "int", nullable: false),
                    CreatedAt = table.Column(type: "datetime2", nullable: true),
                    UpdatedAt = table.Column(type: "datetime2", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_GroupMessageStatuses", x => x.GroupMessageStatusID);
                    table.ForeignKey(
                        name: "FK_GroupMessageStatuses_GroupMessages_GroupMessageID",
                        column: x => x.GroupMessageID,
                        principalTable: "GroupMessages",
                        principalColumn: "GroupMessageID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_GroupMessageStatuses_MessageStatuses_MessageStatusID",
                        column: x => x.MessageStatusID,
                        principalTable: "MessageStatuses",
                        principalColumn: "MessageStatusID",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_GroupMessageStatuses_Users_UserID",
                        column: x => x.UserID,
                        principalTable: "Users",
                        principalColumn: "UserID",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_AttachmentGroupMessage_GroupMessagesGroupMessageID",
                table: "AttachmentGroupMessage",
                column: "GroupMessagesGroupMessageID");

            migrationBuilder.CreateIndex(
                name: "IX_Attachments_AttachmentTypeID",
                table: "Attachments",
                column: "AttachmentTypeID");

            migrationBuilder.CreateIndex(
                name: "IX_Attachments_GroupID",
                table: "Attachments",
                column: "GroupID");

            migrationBuilder.CreateIndex(
                name: "IX_AttachmentUserMessage_UserMessagesUserMessageID",
                table: "AttachmentUserMessage",
                column: "UserMessagesUserMessageID");

            migrationBuilder.CreateIndex(
                name: "IX_GroupMessages_GroupID",
                table: "GroupMessages",
                column: "GroupID");

            migrationBuilder.CreateIndex(
                name: "IX_GroupMessages_GroupMessageSenderID",
                table: "GroupMessages",
                column: "GroupMessageSenderID");

            migrationBuilder.CreateIndex(
                name: "IX_GroupMessageStatuses_GroupMessageID",
                table: "GroupMessageStatuses",
                column: "GroupMessageID");

            migrationBuilder.CreateIndex(
                name: "IX_GroupMessageStatuses_MessageStatusID",
                table: "GroupMessageStatuses",
                column: "MessageStatusID");

            migrationBuilder.CreateIndex(
                name: "IX_GroupMessageStatuses_UserID",
                table: "GroupMessageStatuses",
                column: "UserID");

            migrationBuilder.CreateIndex(
                name: "IX_Groups_AttachmentID",
                table: "Groups",
                column: "AttachmentID");

            migrationBuilder.CreateIndex(
                name: "IX_GroupUser_UsersUserID",
                table: "GroupUser",
                column: "UsersUserID");

            migrationBuilder.CreateIndex(
                name: "IX_MessageStatuses_MessageStatusID1",
                table: "MessageStatuses",
                column: "MessageStatusID1");

            migrationBuilder.CreateIndex(
                name: "IX_UserMessages_UserMessageReceiverID",
                table: "UserMessages",
                column: "UserMessageReceiverID");

            migrationBuilder.CreateIndex(
                name: "IX_UserMessages_UserMessageSenderID",
                table: "UserMessages",
                column: "UserMessageSenderID");

            migrationBuilder.CreateIndex(
                name: "IX_Users_AttachmentID",
                table: "Users",
                column: "AttachmentID");

            migrationBuilder.CreateIndex(
                name: "IX_Users_UserPhoneNumber",
                table: "Users",
                column: "UserPhoneNumber",
                unique: true);

            migrationBuilder.AddForeignKey(
                name: "FK_AttachmentGroupMessage_Attachments_AttachmentsAttachmentID",
                table: "AttachmentGroupMessage",
                column: "AttachmentsAttachmentID",
                principalTable: "Attachments",
                principalColumn: "AttachmentID",
                onDelete: ReferentialAction.Cascade);

            migrationBuilder.AddForeignKey(
                name: "FK_AttachmentGroupMessage_GroupMessages_GroupMessagesGroupMessageID",
                table: "AttachmentGroupMessage",
                column: "GroupMessagesGroupMessageID",
                principalTable: "GroupMessages",
                principalColumn: "GroupMessageID",
                onDelete: ReferentialAction.Cascade);

            migrationBuilder.AddForeignKey(
                name: "FK_AttachmentUserMessage_Attachments_AttachmentsAttachmentID",
                table: "AttachmentUserMessage",
                column: "AttachmentsAttachmentID",
                principalTable: "Attachments",
                principalColumn: "AttachmentID",
                onDelete: ReferentialAction.Cascade);

            migrationBuilder.AddForeignKey(
                name: "FK_AttachmentUserMessage_UserMessages_UserMessagesUserMessageID",
                table: "AttachmentUserMessage",
                column: "UserMessagesUserMessageID",
                principalTable: "UserMessages",
                principalColumn: "UserMessageID",
                onDelete: ReferentialAction.Cascade);

            migrationBuilder.AddForeignKey(
                name: "FK_Groups_Attachments_AttachmentID",
                table: "Groups",
                column: "AttachmentID",
                principalTable: "Attachments",
                principalColumn: "AttachmentID",
                onDelete: ReferentialAction.Cascade);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(
                name: "FK_Groups_Attachments_AttachmentID",
                table: "Groups");

            migrationBuilder.DropTable(
                name: "AttachmentGroupMessage");

            migrationBuilder.DropTable(
                name: "AttachmentUserMessage");

            migrationBuilder.DropTable(
                name: "GroupMessageStatuses");

            migrationBuilder.DropTable(
                name: "GroupUser");

            migrationBuilder.DropTable(
                name: "UserMessages");

            migrationBuilder.DropTable(
                name: "GroupMessages");

            migrationBuilder.DropTable(
                name: "MessageStatuses");

            migrationBuilder.DropTable(
                name: "Users");

            migrationBuilder.DropTable(
                name: "Attachments");

            migrationBuilder.DropTable(
                name: "AttachmentType");

            migrationBuilder.DropTable(
                name: "Groups");
        }

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Niklas Salkin5y ago

Sachin Singh5y ago
always work on small migrations, small changes, add a migration, and updateDatabase.
also always read the UP() method of migration and make sure everything is OK.
please share your UP() method of migration and let's guess what is wrong.