Adding another foreign key to a table setup in ef core

Question

I'm working at a .net core project with ef. I have two tables:

    public class Asset
    {
        [Key]
        public Guid Id { get; set; }
        
        public string Name { get; set; }
        public string Description { get; set; }
        
        // Relationships
        public ICollection<AssetMixRecord> AssetMixRecords { get; set; }
    }

    public class AssetMixRecord
    {        
        public decimal Percentage { get; set; }        
        public Guid AssetId { get; set; }

        // Relationships
        public Guid ParentAssetId { get; set; }
    }

The context looks like this:

            modelBuilder.Entity<Asset>()
                .HasMany(a => a.AssetMixRecords)
                .WithOne()
                .OnDelete(DeleteBehavior.Cascade);

            modelBuilder.Entity<AssetMixRecord>()
                .HasKey(c => new { c.ParentAssetId, c.AssetId })
                .IsClustered();

The migration code for this looks like:

            migrationBuilder.CreateTable(
                name: "AssetMixRecords",
                columns: table => new
                {
                    AssetId = table.Column<Guid>(nullable: false),
                    ParentAssetId = table.Column<Guid>(nullable: false),
                    Percentage = table.Column<decimal>(type: "decimal(8,7)", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AssetMixRecords", x => new { x.ParentAssetId, x.AssetId })
                        .Annotation("SqlServer:Clustered", true);
                    table.ForeignKey(
                        name: "FK_AssetMixRecords_Assets_AssetId",
                        column: x => x.AssetId,
                        principalTable: "Assets",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

This is all nice and right. But 'ParentAssetId' also is a foreign key to 'Assets.Id'.

Our use case looks like this:

var asset1 = new Asset();
            var asset2 = new Asset();

            dbContext.Add(asset1);
            dbContext.Add(asset2);
            dbContext.SaveChanges();

            var asset3 = new Asset();

            asset3.AssetMixRecords.Add(new AssetMixRecord()
            {
                AssetId = asset1.Id,
                ParentAssetId = asset3.Id
            });

            asset3.AssetMixRecords.Add(new AssetMixRecord()
            {
                AssetId = asset2.Id,
                ParentAssetId = asset3.Id
            });

            dbContext.Add(asset3);
            dbContext.SaveChanges();

I'm not able to to get this 2nd foreign key into the migration code. Should I just add it manually?

Thanks and regards

S.

Answer 1

Please check out my solution with complete demo, it will generate following migration. I am not sure if it is possible to have DeleteBehavior.Cascade with such schema, EF migration tool was complaining about circular dependency.

migrationBuilder.DropForeignKey(
    name: "FK_AssetMixRecords_Assets_AssetId",
    table: "AssetMixRecords");

migrationBuilder.AddForeignKey(
    name: "FK_AssetMixRecords_Assets_AssetId",
    table: "AssetMixRecords",
    column: "AssetId",
    principalTable: "Assets",
    principalColumn: "Id");

migrationBuilder.AddForeignKey(
    name: "FK_AssetMixRecords_Assets_ParentAssetId",
    table: "AssetMixRecords",
    column: "ParentAssetId",
    principalTable: "Assets",
    principalColumn: "Id");

Demo itself is below. Please notice that there are 2 navigation properties inside Asset. EF will load there child entities based on their location (AssetId or ParentAssetId)

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace ConsoleApp9
{
    public class Asset
    {
        [Key]
        public Guid Id { get; set; }

        public string Name { get; set; }
        public string Description { get; set; }

        // items where AssetMixRecord.AssetId == Id
        public ICollection<AssetMixRecord> AssetMixRecords { get; set; }

        // items where AssetMixRecord.ParentAssetId == Id
        public ICollection<AssetMixRecord> ParentAssetMixRecords { get; set; }
    }

    public class AssetMixRecord
    {
        public decimal Percentage { get; set; }
        public Guid AssetId { get; set; }
        public virtual Asset Asset { get; set; }
        public Guid ParentAssetId { get; set; }
        public virtual Asset ParentAsset { get; set; }
    }

    public class ApplicationDbContext : DbContext
    {
        public DbSet<Asset> Assets { get; set; }
        public DbSet<AssetMixRecord> AssetMixRecords { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=DESKTOP-1111111;Database=testef11db;Integrated Security=true;");

            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Asset>()
                .HasMany(a => a.AssetMixRecords)
                .WithOne(x => x.Asset)
                .HasForeignKey(x => x.AssetId)
                .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<Asset>()
                .HasMany(x => x.ParentAssetMixRecords)
                .WithOne(x => x.ParentAsset)
                .HasForeignKey(x => x.ParentAssetId)
                .OnDelete(DeleteBehavior.NoAction);

            modelBuilder.Entity<AssetMixRecord>()
                .HasKey(c => new { c.ParentAssetId, c.AssetId })
                .IsClustered();

            base.OnModelCreating(modelBuilder);
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            var dbContext = new ApplicationDbContext();

            var asset1 = new Asset();
            var asset2 = new Asset();

            dbContext.Add(asset1);
            dbContext.Add(asset2);
            dbContext.SaveChanges();

            var assetDataMixRecord = new AssetMixRecord()
            {
                AssetId = asset1.Id,
                ParentAssetId = asset2.Id
            };

            dbContext.Add(assetDataMixRecord);
            dbContext.SaveChanges();

            var assets = dbContext.Assets
                .Include(x => x.AssetMixRecords)
                .Include(x => x.ParentAssetMixRecords)
                .ToList();
        }
    }
}

If you do not want to ParentAssetId be equal to ChildAssetId simply add code below to solution above

modelBuilder.Entity<AssetMixRecord>()
    .HasCheckConstraint("PK_Check_ChildAssetId_And_ParentAssetId", "ParentAssetId != ChildAssetId");

Your use case will work fine if you change AssetMixRecords to ParentAssetMixRecords for asset3

var dbContext = new ApplicationDbContext();
var asset1 = new Asset();
var asset2 = new Asset();

dbContext.Add(asset1);
dbContext.Add(asset2);
dbContext.SaveChanges();

var asset3 = new Asset();

asset3.ParentAssetMixRecords.Add(new AssetMixRecord()
{
    AssetId = asset1.Id,
    ParentAssetId = asset3.Id
});

asset3.ParentAssetMixRecords.Add(new AssetMixRecord()
{
    AssetId = asset2.Id,
    ParentAssetId = asset3.Id
});

dbContext.Add(asset3);
dbContext.SaveChanges();