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:

  1. 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:


enter image description here

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:

  1. Configuring Many to Many in Entity Framework Core
  2. Docs: Entity Framework Core Relationships
  3. EF One-To-Many - may cause cycles or multiple cascade paths Ef 4 Solution with nullable primary key (which I have tried)

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

1
2
8/12/2017 12:08:10 AM

Accepted Answer

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);
2
8/12/2017 1:15:55 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow