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

Question

Guys I'm trying to run dotnet ef database update command but during the executing of it, it trows me an exception:

Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [ProductSubCategory] (
    [ProductId] int NOT NULL,
    [SubCategoryId] int NOT NULL,
    CONSTRAINT [PK_ProductSubCategory] PRIMARY KEY ([SubCategoryId], [ProductId]),
    CONSTRAINT [FK_ProductSubCategory_Product_ProductId] FOREIGN KEY ([ProductId]) REFERENCES [Product] ([ProductId]) ON DELETE CASCADE,
    CONSTRAINT [FK_ProductSubCategory_SubCategory_SubCategoryId] FOREIGN KEY ([SubCategoryId]) REFERENCES [SubCategory] ([SubCategoryId]) ON DELETE CASCADE
);
Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_ProductSubCategory_SubCategory_SubCategoryId' on table 'ProductSubCategory' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.
Introducing FOREIGN KEY constraint 'FK_ProductSubCategory_SubCategory_SubCategoryId' on table 'ProductSubCategory' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

I'm using Sql Server as my database provider and Entity framework core 3.19 in my ASP.NET CORE MVC application. To be fair I'm not sure what is causing it, since i didn't have that troubles before, they appeared once I added foreign key properties such as public int CategoryId { get; set; } on all dependent entities (on one to many relationships) such as Product because i forgot to add them before, and now i will use them in order to update the Product Entities.I'm showing the entities and fluent api code
Product.cs

public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public double Price { get; set; }
        public Category Category { get; set; }
        public int CategoryId { get; set; } //It's one of the foreign keys properties added
        public Brand Brand { get; set; }
        public int BrandId { get; set; }    //It's one of the foreign keys properties added
        public string ImageName { get; set; }
        public IEnumerable<ProductSubCategory> SubCategories { get; set; }
}

ProductSubCategory.cs

public class ProductSubCategory
{
    public int ProductId { get; set; }
    public Product Product { get; set; }
    public int SubCategoryId { get; set; }
    public SubCategory SubCategory { get; set; }
}

SubCategory.cs

public class SubCategory
{
    public int SubCategoryId { get; set; }
    public string Name { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
    [JsonIgnore]
    public IEnumerable<ProductSubCategory> ProductSubCategories { get; set; }
}

Category

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public IEnumerable<Product> Products { get; set; }
    public IEnumerable<SubCategory> SubCategories { get; set; }
}

DbContext.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
modelBuilder.Entity<ProductSubCategory>()
                .HasKey(psc => new {psc.SubCategoryId, psc.ProductId});
            
            modelBuilder.Entity<ProductSubCategory>()
                .HasOne(psc => psc.Product)
                .WithMany(p => p.SubCategories)
                .HasForeignKey(psc => psc.ProductId);

            modelBuilder.Entity<ProductSubCategory>()
                .HasOne(psc => psc.SubCategory)
                .WithMany(sc => sc.ProductSubCategories)
                .HasForeignKey(psc => psc.SubCategoryId);
}

I'm pretty sure i configure the relationship correctly.
Things i have tried:

Delete the migration folder and created a new one.
Update Ef Core nuget package to the latest version
Dropping the database and then create a new one.

PD: Please i need help, i'm out of time, if you want the source code of the entities project with it's respective Dbcontext, i can give it to you, but as i said, it has 19 entities classes.
This is the source code: https://github.com/ToastedGuy2/Food-Town-issue
EDIT: The real question was How to remove On Cascade On Delete in every foreign key? It doesn't matter if it is One to Many or Many to many relationship.

The error is telling you the problem: "Introducing FOREIGN KEY constraint 'FK_ProductSubCategory_SubCategory_SubCategoryId' on table 'ProductSubCategory' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints." — Thom A
– Thom A ♦, Commented Nov 4, 2020 at 16:54
Which part? The error is quite clear, and explicit on the solution. — Thom A
– Thom A ♦, Commented Nov 4, 2020 at 17:00
Not for me, I have forgotten lots of things about database, this is the first time, I'm facing it. Please can you provide me a solution to it? — Manguera v
– Manguera v, Commented Nov 4, 2020 at 17:02
OK, let's start off, what doesn't make sense for the statement "may cause cycles or multiple cascade paths"? Then, after that, what part of "Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints." don't you understand? Which specific parts are confusing you? — Thom A
– Thom A ♦, Commented Nov 4, 2020 at 17:06

Thom A · Accepted Answer · 2020-11-04 17:51:03Z

1

Introducing FOREIGN KEY constraint 'FK_ProductSubCategory_SubCategory_SubCategoryId' on table 'ProductSubCategory' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

The error is literally telling you the problem here. I am not sure what bit is unclear, as the OP states they understand none of it (which I must admit implies a language barrier and so I suggest changing the language of your LOGIN to get the error in a language you are more familiar with), however, I'm going to overly "dumb" this down:

Introducing FOREIGN KEY constraint 'FK_ProductSubCategory_SubCategory_SubCategoryId'

This is talking about the FOREIGN KEY you are trying to create called FK_ProductSubCategory_SubCategory_SubCategoryId

on table 'ProductSubCategory'

This is telling you that the aforementioned FOREIGN KEY is trying to be created on the table ProductSubCategory

may cause cycles or multiple cascade paths.

This means that the aforementioned FOREIGN KEY, that is trying to be created on the aforementioned TABLE, will cause cycles or multiple cascade paths.

A Cycle would mean that the DELETE will cause further deletes on the same table, trigging more deletes on the same table, trigging more deletes on the same table, ... trigging more deletes on the same table, ... (You get the idea)
Multiple Cascade Paths means that there will be multiple other tables that will have rows deleted from, which is now allowed in SQL Server; especially if they converge on the same table later on.

Specify ON DELETE NO ACTION or ON UPDATE NO ACTION

I don't really know how to clarify this further. Instead of ON CASCADE use NO ACTION. AKA, handle the cascading yourself.

or modify other FOREIGN KEY constraints

Again, this is very much telling you what to do. Modify the other CONSTRAINTs on your table so that they won't cause a cycle or multiple paths; must likely by changing that key to NO ACTION and handing the cascading yourself.

edited Nov 4, 2020 at 17:51

answered Nov 4, 2020 at 17:40

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manguera v Over a year ago

Thank you for taking your time. How do i Specify ON Delete NO Action? Because I have noticed the whole project have them, and to be fair, it should not have them, so i basically have to tell ef core, to remove it, but how? Just to provide more context, in my project i don't delete records, i just disable them, with a isItActive boolean property.

Manguera v Over a year ago

Question: How does EF Core know if a foreign key is ON DELETE CASCADE OR NO ACTION. Because on many to many relationships, it added by default, i just followed the convention as other pages have said. When it comes to relationship one to many, i followed this convention #4 of this page: entityframeworktutorial.net/efcore/… . Basically it says : Defining the relationship fully at both ends with the foreign key property in the dependent entity creates a one-to-many relationship.

Serge · Accepted Answer · 2020-11-04 18:26:58Z

1

Try to change

 modelBuilder.Entity<ProductSubCategory>()
                .HasOne(psc => psc.Product)
                .WithMany(p => p.SubCategories)
                .HasForeignKey(psc => psc.ProductId);

To

 modelBuilder.Entity<ProductSubCategory>()
                .HasOne(psc => psc.Product)
                .WithMany(p => p.ProductSubCategories)
                .HasForeignKey(psc => psc.ProductId);

And I usually use .OnDelete(DeleteBehavior.ClientSetNull)

answered Nov 4, 2020 at 18:26

Serge

44.4k4 gold badges26 silver badges53 bronze badges

4 Comments

Manguera v Over a year ago

Serge, SubCategories property basically have a list of ProductSubCategories. So i don't understand where do you get ProductSubCategories. I guess i should rename the properties again, so it's more clear.

Serge Over a year ago

The next record in you code modelBuilder.Entity<ProductSubCategory>() .HasOne(psc => psc.SubCategory) .WithMany(sc => sc.ProductSubCategories) .HasForeignKey(psc => psc.SubCategoryId);

Manguera v Over a year ago

I mean the relationship is well configure, i just need to remove all ON DELETE CASCADE on my foreign keys.

Manguera v Over a year ago

Ok i'm going to try it.

Dave Cousineau · Accepted Answer · 2020-11-04 21:29:48Z

1

EF Core defaults to cascading deletes. This starts to cause problems once you have too many of them, and you don't want them all over the place anyway.

When defining your relationships, you should try to default to no cascade yourself, except in situations where you really do want a cascade.

entityBuilder
.HasMany(one => one.Many)
.WithOne(many => many.One)
.OnDelete(DeleteBehavior.NoAction);

edited Nov 4, 2020 at 21:29

answered Nov 4, 2020 at 19:06

Dave Cousineau

13.4k8 gold badges70 silver badges83 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related