实体框架核心：挑战建模产品变体数据库设计有多对多-Java 学习之路

我想用 Entity Framework Core 模拟 a product variant database design

Issue/blocker faced with design:

我在运行 dotnet ef migrations add InitialCreate 命令时收到 following error ：

在'ProductSKUValues'表上引入FOREIGN KEY约束'FK_ProductSKUValues_ProductSKUs_ProductId_SkuId'可能会导致循环或多个级联路径 . 指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束 . 无法创建约束或索引 .

Db Design:

enter image description here

Note: 此设计基于以下链接建模：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:

我在用

Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Design
Microsoft.AspNetCore.Identity.EntityFrameworkCore版本1.1.2

真的很感激任何帮助 . 我一直在网上搜索解决方案2天

1 回答

除了以下流畅的配置外，一切都很好

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

这导致了几个问题 .

首先，无参数 .HasOne<ProductSKU>() 将 ProductSKUValue 类的 ProductSKU 导航属性保留为未映射，因此按照惯例，EF会尝试创建另一个一对多关系 .

其次， .IsRequired(false) 不允许使用现有的 {ProductId, SkuId} 字段作为外键，因为它们是必需的（不允许 null 值），因此EF为其创建了另外两个可为空的字段 .

以下是上述配置的结果表：

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

请注意附加列和两个FK约束到 ProductSKUs .

要解决此问题，只需使用正确的配置（类似于您对其他关系所做的操作）：

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

回复于 2024-04-29T20:39:12+08:00

实体框架核心：挑战建模产品变体数据库设计有多对多

1 回答

相关问题