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

Question

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

asp.net-core asp.net-core-mvc c# entity-framework entity-framework-core

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

Hue

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 8/12/2017 1:15:55 AM

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);

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related