I am trying to model a product variant database design with Entity Framework Core
Issue/blocker faced with design:
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
Any assistance would really be appreciated. I have been searching the web for a solution for 2 days
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);