EF Core failed to execute database update command "fail: Microsoft.EntityFrameworkCore.Database.Command[20102]"

Question

I'm trying to run PM> database update . But print out the error message

I used the entity (code-first) to create the database. But I manually added table in the sql server , then I added class.cs in the project , I did Update-database Then I made the error as above. Below is my code. books.cs

namespace library_pou.Models
{
    public class books
    {
        public int id { get; set; }
        public string? nameBook { get; set; }
        public string? describe { get; set; }
        public string? author { get; set; }  
        public string? img { get; set; }
        public DateTime? publishDate { get; set; }
        public string? Slug { get; set; }
        public int userid { get; set; }
        public virtual User User { get; set; }
        public List<brrowBook>? brrowBooks { get; set; }
        public List<genreslnBooks>? genreslnBooks { get; set; }
    }
}

User.cs

using library_pou.Utilties.Enum;
using Microsoft.AspNetCore.Identity;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

namespace library_pou.Models
{
    public class User
    {
        public int Id { get; set; }
        public string? FirtName { get; set; }
        public string? LastName { get; set; }
        [NotMapped]
        public string FullName { get => FirtName +" "+LastName; set => FullName= value; }
        public string? Email { get; set; }
        public string? Password { get; set; }
        public string? Avatar { get; set; }
        public string? Adress { get; set; }
        public string? Intro { get; set; }
        public EUserStatus Status { get; set; } = EUserStatus.InActive;
        public EUserRole Role { get; set; } = EUserRole.Member;
        public DateTime Created_At { get; set; } = DateTime.Now;
        public DateTime Updated_At { get; set; } = DateTime.Now;
        public List<Comment>? Comments { get; set; }
        public List<Post>? Posts { get; set; }
        public List<books> books { get; set; }
        public List<brrowBook>? brrowBooks { get; set; }

    }
}

Configurationsbooks.cs

using library_pou.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
namespace library_pou.Data.Configurations
{
    public class Configurationsbooks : IEntityTypeConfiguration<books>
    {
        public void Configure(EntityTypeBuilder<books> builder)
        {
            builder.HasKey(x => x.id);
            builder.Property(x => x.nameBook).IsRequired().IsUnicode().HasMaxLength(250);
            builder.Property(x => x.author).IsUnicode().IsRequired();
            builder.Property(x => x.describe).IsUnicode().IsRequired();
            builder.Property(x => x.publishDate).IsUnicode().IsRequired();
            builder.Property(x => x.Slug).IsUnicode().IsRequired();
            builder.Property(x => x.img).IsUnicode().IsRequired();
            builder.HasOne(x => x.User).WithMany(x => x.books).HasForeignKey(x => x.userid);
        }
    }
}

ConfigurationsUser.cs

using library_pou.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;

namespace library_pou.Data.Configurations
{
    public class ConfigurationsUser : IEntityTypeConfiguration<User>
    {
        public void Configure(EntityTypeBuilder<User> builder)
        {
            builder.Property(x => x.FirtName).IsRequired().IsUnicode().HasMaxLength(50);
            builder.Property(x => x.LastName).IsRequired().IsUnicode().HasMaxLength(50);
            builder.Property(x => x.Email).IsRequired().IsUnicode().HasMaxLength(150);
            builder.Property(x => x.Adress).IsUnicode().HasMaxLength(150);
            builder.Property(x => x.Avatar).IsUnicode().HasMaxLength(150);
            builder.Property(x => x.Created_At).HasDefaultValue(DateTime.Now);
            builder.Property(x => x.Updated_At).HasDefaultValue(DateTime.Now);

        }
    }
}

OnModelCreating in classDbContext.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new ConfigurationsPost());
            modelBuilder.ApplyConfiguration(new ConfigurationsPostInCategory());
            modelBuilder.ApplyConfiguration(new ConfigurationsCategory());
            modelBuilder.ApplyConfiguration(new ConfigurationsComment());
            modelBuilder.ApplyConfiguration(new ConfigurationsTagInPost());
            modelBuilder.ApplyConfiguration(new ConfigurationsTag());
            modelBuilder.ApplyConfiguration(new ConfigurationsUser());
            modelBuilder.ApplyConfiguration(new Configurationsbooks());
            modelBuilder.ApplyConfiguration(new ConfigurationsborrwBook());
            modelBuilder.ApplyConfiguration(new ConfigurationsgenreslnBooks());
            modelBuilder.ApplyConfiguration(new Configurationsgenres());
            base.OnModelCreating(modelBuilder);
        }

migration

migrationBuilder.DropForeignKey(
                name: "FK_Books_Users_UserId",
                table: "Books");
migrationBuilder.AddForeignKey(
                name: "FK_books_Users_userid",
                table: "books",
                column: "userid",
                principalTable: "Users",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);

Please i need help !

why you dont add this builder.HasKey(x => x.id) config on userEntityConfiguration ? add that and builder.Property(x => x.id).IsRequired(); — thisisnabi, May 07 '22 at 18:04
If you are using the Code First approach then you should not manually add the table to the database. adding things manually may create issues as you have mentioned above. I would suggest you revert the changes you made in your DB and in your project. Create the class in your project and run the migration command. — Deepak-MSFT, May 09 '22 at 01:35

score 1 · Answer 1 · answered May 07 '22 at 20:16

Initial migration that you created probably generated constraint "FK_Books_Users_UserId". Since you haven't executed it, but added table(s) manually (as you said), this constraint was never created in the database.

Now when you added configuration for your class(es), you have property "userid" in class books, which will generate foreign key called "FK_books_Users_userid" (notice lowercase u in userId instead of uppercase U in UserId).

When your new migration tries to drop "FK_Books_Users_UserId" in the database it fails since it was never created in the first place.

One solution would be to simply delete this peace of code from your new migration:

migrationBuilder.DropForeignKey(
                name: "FK_Books_Users_UserId",
                table: "Books");

which would leave just this part:

migrationBuilder.AddForeignKey(
                name: "FK_books_Users_userid",
                table: "books",
                column: "userid",
                principalTable: "Users",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);

which I would not recommend, but it will work for that one database.

Second solution would be to clear your database, remove these two created migrations and just create new one and execute it. This would give you clean starting point without future errors.

EF Core failed to execute database update command "fail: Microsoft.EntityFrameworkCore.Database.Command[20102]"

1 Answers1