Issue referencing a foreign key 'Id' from 'StoreShippingMethod' table in VirtoCommerce using EntityFramework

support
1

I’m encountering an issue while trying to reference a foreign key ‘Id’ from the ‘StoreShippingMethod’ table in my ‘CustomShipping’ table. When I attempt to run the migrations, a new ‘StoreShippingMethodEntity’ table is created, and it does not reference the existing ‘StoreShippingMethod’ table from Shipping Module.

Here is my DbContext file:

using EntityFrameworkCore.Triggers;
using Microsoft.EntityFrameworkCore;
using VirtoCommerce.CatalogModule.Data.Model;
using VirtoCommerce.CustomShipping.Data.Models;
using VirtoCommerce.ShippingModule.Data.Model;
using VirtoCommerce.ShippingModule.Data.Repositories;

namespace VirtoCommerce.CustomShipping.Data.Repositories;

public class CustomShippingDbContext : DbContextWithTriggers
{
    public CustomShippingDbContext(DbContextOptions<CustomShippingDbContext> options)
        : base(options)
    {
    }

    protected CustomShippingDbContext(DbContextOptions options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<CustomShippingEntity>().ToTable("CustomShipping").HasKey(x => x.Id);
      
        modelBuilder.Entity<CustomShippingEntity>().Property(x => x.Id).HasMaxLength(128).ValueGeneratedOnAdd();

        modelBuilder.Entity<CustomShippingEntity>().HasOne(x => x.StoreShippingMethod)
             .WithMany().HasForeignKey(x => x.ShippingMethodId).IsRequired().OnDelete(DeleteBehavior.Restrict);


    }
}

This is my Entity file :

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using VirtoCommerce.CatalogModule.Core.Model;
using VirtoCommerce.CatalogModule.Data.Model;
using VirtoCommerce.CustomShipping.Core.Models;
using VirtoCommerce.Platform.Core.Common;
using VirtoCommerce.Platform.Core.Domain;
using VirtoCommerce.ShippingModule.Core.Model;
using VirtoCommerce.ShippingModule.Data.Model;

namespace VirtoCommerce.CustomShipping.Data.Models
{
    public class CustomShippingEntity : AuditableEntity, IDataEntity<CustomShippingEntity, CustomShippingRates>
    {
        [Required]
        public string CategoryId { get; set; }
        [Required]public string OptionName { get; set; }
        public string OptionDescription { get; set; }
        public decimal Rate { get; set; }
        public decimal RateWithTax { get; set; }
        public string Currency { get; set; }
        public decimal DiscountAmount { get; set; }
        public decimal DiscountAmountWithTax { get; set; }

        
        [Required, StringLength(128)]
        public string ShippingMethodId { get; set; }

        public string ShippingRateId { get; set; }

        public virtual StoreShippingMethodEntity StoreShippingMethod { get; set; }

        public virtual CustomShippingRates ToModel(CustomShippingRates customShippingRates)
        {
            if(customShippingRates == null)
            {
                throw new ArgumentNullException(nameof(customShippingRates));
            }

            customShippingRates.Id = Id;
            customShippingRates.CreatedBy = CreatedBy;
            customShippingRates.CreatedDate = CreatedDate;
            customShippingRates.ModifiedBy = ModifiedBy;
            customShippingRates.ModifiedDate = ModifiedDate;

            customShippingRates.CategoryId = CategoryId;
            customShippingRates.ShippingRateId = ShippingRateId;
            customShippingRates.OptionName = OptionName;
            customShippingRates.OptionDescription = OptionDescription;
            customShippingRates.Rate = Rate;
            customShippingRates.RateWithTax = RateWithTax;
            customShippingRates.Currency = Currency;
            customShippingRates.DiscountAmount = DiscountAmount;
            customShippingRates.DiscountAmountWithTax = DiscountAmountWithTax;
            customShippingRates.ShippingMethodId = ShippingMethodId;
            if (StoreShippingMethod!=null) {
                customShippingRates.ShippingMethod = (ShippingMethod)StoreShippingMethod?.ToModel(AbstractTypeFactory<ShippingMethod>.TryCreateInstance());
            }
            return customShippingRates;

        }

       public virtual CustomShippingEntity FromModel(CustomShippingRates customShippingRates, PrimaryKeyResolvingMap pkMap)
       {
            if (customShippingRates == null)
            {
                throw new ArgumentNullException(nameof(customShippingRates));
            }

            pkMap.AddPair((IEntity)customShippingRates, this);

            Id = customShippingRates.Id;
            CreatedBy = customShippingRates.CreatedBy;
            CreatedDate = customShippingRates.CreatedDate;
            ModifiedBy = customShippingRates.ModifiedBy;
            ModifiedDate = customShippingRates.ModifiedDate;

            CategoryId = customShippingRates.CategoryId;
            ShippingRateId = customShippingRates.ShippingRateId;
            OptionName = customShippingRates.OptionName;
            OptionDescription = customShippingRates.OptionDescription;
            Rate = customShippingRates.Rate;
            RateWithTax = customShippingRates.RateWithTax;
            Currency = customShippingRates.Currency;
            DiscountAmount = customShippingRates.DiscountAmount;
            DiscountAmountWithTax = customShippingRates.DiscountAmountWithTax;
            ShippingMethodId = customShippingRates.ShippingMethodId;

            return this;
        }

        public void Patch(CustomShippingEntity target)
        {
            
        }
    }
}

When I try to run migrations it creates a new table StoreShippingMethodEntity though it should have referenced to the existing StoreShippingMethod table.

Here is my migration file :-

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace VirtoCommerce.CustomShipping.Data.Migrations
{
    public partial class Init : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "StoreShippingMethodEntity",
                columns: table => new
                {
                    Id = table.Column<string>(type: "nvarchar(450)", nullable: false),
                    Code = table.Column<string>(type: "nvarchar(128)", maxLength: 128, nullable: false),
                    Priority = table.Column<int>(type: "int", nullable: false),
                    LogoUrl = table.Column<string>(type: "nvarchar(2048)", maxLength: 2048, nullable: true),
                    TaxType = table.Column<string>(type: "nvarchar(64)", maxLength: 64, nullable: true),
                    IsActive = table.Column<bool>(type: "bit", nullable: false),
                    TypeName = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    StoreId = table.Column<string>(type: "nvarchar(max)", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_StoreShippingMethodEntity", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "CustomShipping",
                columns: table => new
                {
                    Id = table.Column<string>(type: "nvarchar(128)", maxLength: 128, nullable: false),
                    CategoryId = table.Column<string>(type: "nvarchar(max)", nullable: false),
                    OptionName = table.Column<string>(type: "nvarchar(max)", nullable: false),
                    OptionDescription = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    Rate = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
                    RateWithTax = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
                    Currency = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    DiscountAmount = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
                    DiscountAmountWithTax = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
                    ShippingMethodId = table.Column<string>(type: "nvarchar(128)", maxLength: 128, nullable: false),
                    ShippingRateId = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    CreatedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
                    ModifiedDate = table.Column<DateTime>(type: "datetime2", nullable: true),
                    CreatedBy = table.Column<string>(type: "nvarchar(64)", maxLength: 64, nullable: true),
                    ModifiedBy = table.Column<string>(type: "nvarchar(64)", maxLength: 64, nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_CustomShipping", x => x.Id);
                    table.ForeignKey(
                        name: "FK_CustomShipping_StoreShippingMethodEntity_ShippingMethodId",
                        column: x => x.ShippingMethodId,
                        principalTable: "StoreShippingMethodEntity",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateIndex(
                name: "IX_CustomShipping_ShippingMethodId",
                table: "CustomShipping",
                column: "ShippingMethodId");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "CustomShipping");

            migrationBuilder.DropTable(
                name: "StoreShippingMethodEntity");
        }
    }
}

I am using this command to add the migrations :-

EntityFrameworkCore\Add-Migration Init -Context VirtoCommerce.CustomShipping.Data.Repositories.CustomShippingDbContext -Verbose -OutputDir Migrations -Project VirtoCommerce.CustomShipping.Data -StartupProject VirtoCommerce.CustomShipping.Data -Debug

2

It’s not possible to build relations between different DB Context.

By Virto Commerce principles, no relation on DB level between different modules, because every module can be stored in own database.

I recommend to glue the data on Service or XAPI level.

3

Okay I get it. I also came up with a temporary solution where I ran migrations for ShippingDbContext and removed the migration commands which were generating the new StoreShippingMethod table. So it is a good practice or not?

4

Best practice is join data between in XAPI (Services).