Entity framework core: Challenge Modeling Product Variants Database Design with Many to Many

Question

I am trying to model a product variant database design with Entity Framework Core

Issue/blocker faced with design:

I am getting the following error on running dotnet ef migrations add InitialCreate command:

Introducing FOREIGN KEY constraint 'FK_ProductSKUValues_ProductSKUs_ProductId_SkuId' on table 'ProductSKUValues' 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.

Db Design:

Note: This design was modeled based on this link: Modeling Product Variants

ApplicationDbContext.cs with Fluent API (pay attention to ProductSKU & ProductSKUValue relationship):

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using TikkyBoxWebAPI.Models.Account;
using TikkyBoxWebAPI.Models;
using TikkyBoxWebAPI.Models.Core;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using System.Linq;

namespace TikkyBoxWebAPI.Data
{
    public class TikkyBoxDbContext : DbContext
    {

        public TikkyBoxDbContext(DbContextOptions<TikkyBoxDbContext> options)
            : base(options)
        {
            Database.Migrate();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            modelBuilder
                .Entity<ProductSKU>()
                .HasKey(p => new { p.ProductId, p.SkuId });

            modelBuilder
            .Entity<ProductSKU>()
            .HasOne(p => p.Product)
            .WithMany(ps => ps.ProductSKUs)
            .HasForeignKey(x => x.ProductId);

            modelBuilder
                .Entity<ProductSKU>()
                .HasIndex(p => p.Sku);

            modelBuilder
                .Entity<ProductSKU>()
                .Property(p => p.SkuId).ValueGeneratedOnAdd();

            modelBuilder
            .Entity<ProductSKUValue>()
            .HasOne<ProductSKU>()
            .WithMany( p => p.ProductSKUValues)
            .IsRequired(false)
            .OnDelete(DeleteBehavior.Restrict);

            modelBuilder
                .Entity<ProductSKUValue>()
                .HasKey(p => new { p.ProductId, p.SkuId, p.OptionId});

            modelBuilder
            .Entity<ProductSKUValue>()
            .HasOne(p => p.ProductOptionValue)
            .WithMany(ps => ps.ProductSKUValues)
            .HasForeignKey(x => new { x.ProductId, x.OptionId, x.ValueId })
            .OnDelete(DeleteBehavior.Restrict);

            modelBuilder
            .Entity<ProductSKUValue>()
            .HasOne(p => p.ProductOption)
            .WithMany(ps => ps.ProductSKUValues)
            .HasForeignKey(x => new { x.ProductId, x.OptionId })
            .OnDelete(DeleteBehavior.Restrict);

            modelBuilder
        .Entity<ProductOptionValue>()
        .HasKey(p => new { p.ProductId, p.OptionId, p.ValueId });

            modelBuilder
        .Entity<ProductOptionValue>()
        .HasOne(p => p.ProductOption)
        .WithMany(ps => ps.ProductOptionValues)
        .HasForeignKey(x => new { x.ProductId, x.OptionId });
            //    .OnDelete(DeleteBehavior.Restrict);

            modelBuilder
                .Entity<ProductOptionValue>()
                .Property(p => p.ValueId).ValueGeneratedOnAdd();


            modelBuilder
        .Entity<ProductOption>()
        .HasKey(p => new { p.ProductId, p.OptionId });

            modelBuilder
        .Entity<ProductOption>()
        .HasOne(p => p.Product)
        .WithMany(po => po.ProductOptions)
        .HasForeignKey(x => new { x.ProductId })
        .OnDelete(DeleteBehavior.Restrict);


            modelBuilder
            .Entity<ProductOption>()
            .Property(p => p.OptionId).ValueGeneratedOnAdd();

            // base.OnModelCreating(modelBuilder);

        }
        public DbSet<Product> Products { get; set; }
        public DbSet<ProductOption> ProductOptions { get; set; }
        public DbSet<ProductOptionValue> ProductOptionValues { get; set; }
        public DbSet<ProductSKU> ProductSKUs { get; set; }
        public DbSet<ProductSKUValue> ProductSKUValues { get; set; }
    }
}

Product.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace TikkyBoxWebAPI.Models.Core
{

    public class Product
    {
        public int Id { get; set; }
        [Required]
        public String Name { get; set; }
        // to be used for barcode : remember look at datatype
        [MaxLength(32)]
        public String UniversalProductCode { get; set; }
        public Decimal Height { get; set; }
        public Decimal Weight { get; set; }
        public Decimal NetWeight { get; set; }
        public Decimal Depth { get; set; }

        [MaxLength(128)]
        public String ShortDescription { get; set; }
        [MaxLength(255)]
        public String LongDescription { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime UpdatedOn { get; set; }
        public virtual ICollection<ProductSKU> ProductSKUs { get; set; }
        public virtual ICollection<ProductOption> ProductOptions { get; set; }

    }


}

ProductSKU.cs

     using System;
     using System.Collections.Generic;
     using System.ComponentModel.DataAnnotations;
     using System.ComponentModel.DataAnnotations.Schema;

     namespace TikkyBoxWebAPI.Models.Core
     {
            public class ProductSKU
            {
                public int ProductId { get; set; }
                public int SkuId { get; set; }

                [Required]
                [MaxLength(64)]
                public String Sku { get; set; }

                public  Product Product { get; set; }
                public List<ProductSKUValue> ProductSKUValues { get; set; }

            }
     }

ProductSKUValue.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace TikkyBoxWebAPI.Models.Core
{
    public class ProductSKUValue
    {
        public int ProductId { get; set; }

        public int SkuId { get; set; }

        public int OptionId { get; set; }
        public int ValueId { get; set; }

        public virtual ProductSKU ProductSKU { get; set; }
        public virtual ProductOption ProductOption { get; set; }
        public virtual ProductOptionValue ProductOptionValue { get; set; }

    }
}

ProductOption.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System;

namespace TikkyBoxWebAPI.Models.Core
{
    public class ProductOption
    {
        public int ProductId { get; set; }
        public int OptionId { get; set; }
        [Required]
        [MaxLength(40)]
        public String OptionName { get; set; }
        public virtual Product Product { get; set; }

        public virtual ICollection<ProductSKUValue> ProductSKUValues { get; set; }
        public virtual ICollection<ProductOptionValue> ProductOptionValues { get; set; }

    }
}

ProductOptionValue.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System;

namespace TikkyBoxWebAPI.Models.Core
{
    public class ProductOptionValue
    {
        public int ProductId { get; set; }

        public int ValueId  { get; set; }
        public int OptionId { get; set; }
        [Required]
        [MaxLength(32)]
        public String ValueName { get; set; }

        public virtual  ProductOption ProductOption { get; set; }
        public virtual  ICollection<ProductSKUValue> ProductSKUValues { get; set; }
    }
}

I have already unsuccessfully tried these answers on StackOverflow & the web:

I am using

Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Design
Microsoft.AspNetCore.Identity.EntityFrameworkCore versions 1.1.2

Any assistance would really be appreciated. I have been searching the web for a solution for 2 days

In order to get help, remove the code screenshots and add the actual code for all involved entities (after all, they are only 5) and related fluent configuration. — Ivan Stoev, Aug 11 '17 at 12:18
@IvanStoev Well there are not only 5 entities, way more. But I will remove the Other relationships and models and isolate the bare minimal — Hue, Aug 11 '17 at 13:29
That will be perfect. The shown model definitely contains multiple cascade paths, isolating just these entities will help reproducing the issue and also identifying if it's caused by them or something else. — Ivan Stoev, Aug 11 '17 at 13:52
Mate, please post the **code**, not pictures. It should be easier for you, and also for us to be able to copy/paste it in a test environment. — Ivan Stoev, Aug 11 '17 at 14:04
@IvanStoev, I hadto strip the code down to bare minimal. Let me know if you need anything else — Hue, Aug 12 '17 at 00:11
@IvanStoev I have tried the suggestion and it still results in the same error — Hue, Aug 13 '17 at 00:12

score 2 · Accepted Answer · answered Aug 12 '17 at 01:15

Everything is ok except the following fluent configuration

modelBuilder
    .Entity<ProductSKUValue>()
    .HasOne<ProductSKU>()
    .WithMany(p => p.ProductSKUValues)
    .IsRequired(false)
    .OnDelete(DeleteBehavior.Restrict);

which is causing several issues.

First, the parameterless .HasOne<ProductSKU>() leaves the ProductSKU navigation property of the ProductSKUValue class unmapped, so by convention EF tries to create another one-to-many relationship.

Second, .IsRequired(false) disallows the usage of the existing {ProductId, SkuId} fields as foreign key because they are required (do not allow null values), hence EF creates another two nullable fields for that.

Here is the resulting table from the above configuration:

migrationBuilder.CreateTable(
    name: "ProductSKUValues",
    columns: table => new
    {
        ProductId = table.Column<int>(nullable: false),
        SkuId = table.Column<int>(nullable: false),
        OptionId = table.Column<int>(nullable: false),
        ProductSKUProductId = table.Column<int>(nullable: true),
        ProductSKUProductId1 = table.Column<int>(nullable: true),
        ProductSKUSkuId = table.Column<int>(nullable: true),
        ProductSKUSkuId1 = table.Column<int>(nullable: true),
        ValueId = table.Column<int>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_ProductSKUValues", x => new { x.ProductId, x.SkuId, x.OptionId });
        table.ForeignKey(
            name: "FK_ProductSKUValues_ProductOptions_ProductId_OptionId",
            columns: x => new { x.ProductId, x.OptionId },
            principalTable: "ProductOptions",
            principalColumns: new[] { "ProductId", "OptionId" },
            onDelete: ReferentialAction.Restrict);
        table.ForeignKey(
            name: "FK_ProductSKUValues_ProductSKUs_ProductSKUProductId_ProductSKUSkuId",
            columns: x => new { x.ProductSKUProductId, x.ProductSKUSkuId },
            principalTable: "ProductSKUs",
            principalColumns: new[] { "ProductId", "SkuId" },
            onDelete: ReferentialAction.Restrict);
        table.ForeignKey(
            name: "FK_ProductSKUValues_ProductSKUs_ProductSKUProductId1_ProductSKUSkuId1",
            columns: x => new { x.ProductSKUProductId1, x.ProductSKUSkuId1 },
            principalTable: "ProductSKUs",
            principalColumns: new[] { "ProductId", "SkuId" },
            onDelete: ReferentialAction.Restrict);
        table.ForeignKey(
            name: "FK_ProductSKUValues_ProductOptionValues_ProductId_OptionId_ValueId",
            columns: x => new { x.ProductId, x.OptionId, x.ValueId },
            principalTable: "ProductOptionValues",
            principalColumns: new[] { "ProductId", "OptionId", "ValueId" },
            onDelete: ReferentialAction.Restrict);
    });

Note the additional columns and the two FK constraints to ProductSKUs.

To fix the issue, just use the proper configuration (similar to what you did for other relationships):

modelBuilder
    .Entity<ProductSKUValue>()
    .HasOne(p => p.ProductSKU)
    .WithMany(p => p.ProductSKUValues)
    .HasForeignKey(x => new { x.ProductId, x.SkuId })
    .OnDelete(DeleteBehavior.Restrict);

that did not work. Still getting the same error. I only made the configuration changes you suggested — Hue, Aug 13 '17 at 00:04
Well, then I'm afraid there is nothing more I can do. With the provided model, configuration and the above change I was able to generate (and successfully apply) a migration containing these 5 tables and their relationships. — Ivan Stoev, Aug 13 '17 at 00:26
I removed the 'IsRequired' had to drop my existing database before it worked. Thanks man! — Hue, Aug 13 '17 at 22:02

Entity framework core: Challenge Modeling Product Variants Database Design with Many to Many

1 Answers1