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

0

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:

  1. Delete the migration folder and created a new one.
  2. Update Ef Core nuget package to the latest version
  3. 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.

c#
sql-server
database
entity-framework-core
dbcontext
asked on Stack Overflow Nov 4, 2020 by Manguera v • edited Nov 4, 2020 by Manguera v

3 Answers

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.

answered on Stack Overflow Nov 4, 2020 by Larnu • edited Nov 4, 2020 by Larnu
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 on Stack Overflow Nov 4, 2020 by Sergey
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);
answered on Stack Overflow Nov 4, 2020 by Dave Cousineau • edited Nov 4, 2020 by Dave Cousineau

User contributions licensed under CC BY-SA 3.0