search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.72k stars 3.17k forks source link

Table Per Type Causes Check Constrained to be Duplicated onto the base table when Creating a migration. #24219

Closed koimad closed 2 years ago

koimad commented 3 years ago

I've found that when generating a Table Per type set of classes. (I only have a base Class and one Inherited Class at the moment) As Below. It Duplicates the Check Contraint CK_SalesCategory_StatusId twice once on the DomainBase Table and once on the SalesCategory Table. 

using System;
using System.ComponentModel.DataAnnotations.Schema;

namespace Domain.Entities
{
    public abstract class DomainBase
    {
        #region Properties

        public Int64 Id { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime CreatedDate { get; set; }

        public String CreatedBy { get; set; }

        public String LastModifiedBy { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime LastModifiedDate { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public Byte[] RowVersion { get; set; }

        #endregion
    }
}

using System;

using Domain.Enums;

namespace Domain.Entities
{
    public class SalesCategory : DomainBase
    {
        public String Name { get; set; }        

        public String Code { get; set; }

        public EntityStatus Status { get; set; }

    }
}

using System;

namespace Domain.Enums
{
    public enum EntityStatus : Int64
    {
        New = 0,
        Draft = 1,
        Approved = 2
    }

    public class EntityStatusEntity
    {
        public EntityStatus Id { get; set; }
        public String Name { get; set; }
    }
}

namespace Infrastructure.Domain.Persistence.Contexts
{
    public class PolicyContext : DbContext, IPolicyContext
    {
        #region Members

        private readonly ILoggerFactory _loggerFactory;

        #endregion

        #region Properties

        public DbSet<DomainBase> BaseEntities { get; set; }

        public DbSet<SalesCategory> SalesCategories { get; set; }

        #endregion

        #region Constructor

        public PolicyContext(DbContextOptions contextOptions, ILoggerFactory loggerFactory) : base(contextOptions)
        {
            _loggerFactory = loggerFactory;
        }

        #endregion

        #region Methods

        #region Protected Methods

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(_loggerFactory);

            optionsBuilder.EnableSensitiveDataLogging();
            optionsBuilder.EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder
                .ApplyConfiguration(new DomainBaseConfiguration())
                .ApplyConfiguration(new SalesCategoryConfiguration())
                .ApplyConfiguration(new EntityStatusConfiguration())
                ;
        }

        #endregion

        #endregion
    }
}

using Domain.Entities;

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace Infrastructure.Domain.Persistence.EntityConfigurations
{
    internal class DomainBaseConfiguration : IEntityTypeConfiguration<DomainBase>
    {
        public void Configure(EntityTypeBuilder<DomainBase> builder)
        {
            builder.ToTable(nameof(DomainBase),"CommonSchema");

            builder.HasKey(f => f.Id);
            builder.Property(f=> f.RowVersion).IsRowVersion();
            builder.Property(f => f.CreatedBy).IsRequired();
            builder.Property(f => f.LastModifiedBy).IsRequired();
        }
    }
}

using System;
using System.Linq;

using Domain.Enums;

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace Infrastructure.Domain.Persistence.EntityConfigurations
{

    internal class EntityStatusConfiguration : IEntityTypeConfiguration<EntityStatusEntity>
    {
        public void Configure(EntityTypeBuilder<EntityStatusEntity> builder)
        {
            builder.ToTable(nameof(EntityStatus),"CommonSchema");
            builder.HasKey(f => f.Id);
            builder.Property(f=> f.Name).HasMaxLength(200).IsRequired();        

            builder.HasData(Enum.GetValues<EntityStatus>()
                .Select(e => new EntityStatusEntity() { Id = e, Name = e.ToString()}));

        }
    }
}

using System;

using Domain.Entities;
using Domain.Enums;

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace Infrastructure.Domain.Persistence.EntityConfigurations
{
    internal class SalesCategoryConfiguration : IEntityTypeConfiguration<SalesCategory>
    {
        public void Configure(EntityTypeBuilder<SalesCategory> builder)
        {
            builder.ToTable(nameof(SalesCategory), "PolicySchema");

            builder.Property(f=> f.Name).HasMaxLength(200).IsRequired();
            builder.HasIndex(f => f.Name).IsUnique();

            builder.Property(f=> f.Code).HasMaxLength(2).IsFixedLength().IsRequired();

            builder.Property(f => f.Status).HasColumnName("StatusId");

            builder.HasCheckConstraint($"CK_{nameof(SalesCategory)}_StatusId", $"[StatusId] IN ({(Int32)EntityStatus.New},{(Int32)EntityStatus.Draft},{(Int32)EntityStatus.Approved})");

        }
    }
}

Generated Migration

using System;
using Microsoft.EntityFrameworkCore.Migrations;

namespace Infrastructure.Domain.Persistence.Migrations
{
    public partial class Initial : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.EnsureSchema(
                name: "CommonSchema");

            migrationBuilder.EnsureSchema(
                name: "PolicySchema");

            migrationBuilder.CreateTable(
                name: "DomainBase",
                schema: "CommonSchema",
                columns: table => new
                {
                    Id = table.Column<long>(type: "bigint", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    CreatedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
                    CreatedBy = table.Column<string>(type: "nvarchar(max)", nullable: false),
                    LastModifiedBy = table.Column<string>(type: "nvarchar(max)", nullable: false),
                    LastModifiedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
                    RowVersion = table.Column<byte[]>(type: "rowversion", rowVersion: true, nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_DomainBase", x => x.Id);
                    table.CheckConstraint("CK_SalesCategory_StatusId", "[StatusId] IN (0,1,2)");
                });

            migrationBuilder.CreateTable(
                name: "EntityStatus",
                schema: "CommonSchema",
                columns: table => new
                {
                    Id = table.Column<long>(type: "bigint", nullable: false),
                    Name = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_EntityStatus", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "SalesCategory",
                schema: "PolicySchema",
                columns: table => new
                {
                    Id = table.Column<long>(type: "bigint", nullable: false),
                    Name = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: false),
                    Code = table.Column<string>(type: "nchar(2)", fixedLength: true, maxLength: 2, nullable: false),
                    StatusId = table.Column<long>(type: "bigint", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_SalesCategory", x => x.Id);
                    table.CheckConstraint("CK_SalesCategory_StatusId", "[StatusId] IN (0,1,2)");
                    table.ForeignKey(
                        name: "FK_SalesCategory_DomainBase_Id",
                        column: x => x.Id,
                        principalSchema: "CommonSchema",
                        principalTable: "DomainBase",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.InsertData(
                schema: "CommonSchema",
                table: "EntityStatus",
                columns: new[] { "Id", "Name" },
                values: new object[] { 0L, "New" });

            migrationBuilder.InsertData(
                schema: "CommonSchema",
                table: "EntityStatus",
                columns: new[] { "Id", "Name" },
                values: new object[] { 1L, "Draft" });

            migrationBuilder.InsertData(
                schema: "CommonSchema",
                table: "EntityStatus",
                columns: new[] { "Id", "Name" },
                values: new object[] { 2L, "Approved" });

            migrationBuilder.CreateIndex(
                name: "IX_SalesCategory_Name",
                schema: "PolicySchema",
                table: "SalesCategory",
                column: "Name",
                unique: true,
                filter: "[Name] IS NOT NULL");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "EntityStatus",
                schema: "CommonSchema");

            migrationBuilder.DropTable(
                name: "SalesCategory",
                schema: "PolicySchema");

            migrationBuilder.DropTable(
                name: "DomainBase",
                schema: "CommonSchema");
        }
    }
}

After Generating If I try to remove the migration I get 

PM> Remove-Migration
Build started...
Build succeeded.
System.ArgumentException: An item with the same key has already been added. Key: Check: CK_SalesCategory_StatusId "[StatusId] IN (0,1,2)"
   at System.Collections.Generic.Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffContext.AddMapping[T](T source, T target)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffCollection[T](IEnumerable`1 sources, IEnumerable`1 targets, DiffContext diffContext, Func`4 diff, Func`3 add, Func`3 remove, Func`4[] predicates)+MoveNext()
   at System.Linq.Enumerable.ConcatIterator`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.Diff(ITable source, ITable target, DiffContext diffContext)+MoveNext()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffCollection[T](IEnumerable`1 sources, IEnumerable`1 targets, DiffContext diffContext, Func`4 diff, Func`3 add, Func`3 remove, Func`4[] predicates)+MoveNext()
   at System.Linq.Enumerable.ConcatIterator`1.MoveNext()
   at System.Linq.Enumerable.Any[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.HasDifferences(IRelationalModel source, IRelationalModel target)
   at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsSc

affolder.RemoveMigration(String projectDir, String rootNamespace, Boolean force, String language)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.RemoveMigration(String contextType, Boolean force)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigrationImpl(String contextType, Boolean force)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
An item with the same key has already been added. Key: Check: CK_SalesCategory_StatusId "[StatusId] IN (0,1,2)"
PM>

EF Core version: 5.0.3 EF Core Tools version: 5.0.3 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: (e.g. .NET 5.0) Operating system: Windows 10 Pro N Version 10.0.19042 Build 19042 IDE: Visual Studio 2019 16.8.5

tyler-boyd commented 3 years ago

I've been running into this too. Is there a known mitigation/workaround for it? Apart from just removing all check constraints on TPT relations?

ajcvickers commented 3 years ago

@tyler-boyd The workaround is to edit the generated migration to remove the extra check constraint.

koimad commented 2 years ago

Update to Issue, If I add another class as below, so both have a status.

using System; using Domain.Attributes; using Domain.Enums;

namespace Domain.Entities { [SecurityKey(Key = EntityAccessKey.SalesPolicyViewer)] public class SalesPolicy : DomainBase { public String Name { get; set; }

    public EntityStatus Status { get; set; }

} }

using System; using Domain.Entities; using Domain.Enums; using Laraue.EfCoreTriggers.Common.Extensions; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace Infrastructure.Domain.Persistence.EntityConfigurations.Entity;

internal class SalesPolicyConfiguration : IEntityTypeConfiguration { public void Configure(EntityTypeBuilder builder) { builder.ToTable(nameof(SalesPolicy), "PolicySchema");

    builder.Property(f => f.Name).HasMaxLength(200).IsRequired();
    builder.HasIndex(f => f.Name).IsUnique();

    builder.Property(f => f.Status).HasColumnName("StatusId");

    builder.HasCheckConstraint($"CK_{nameof(SalesPolicy)}_StatusId", $"[StatusId] IN ({(Int32)EntityStatus.New},{(Int32)EntityStatus.Draft},{(Int32)EntityStatus.Approved})");

}

}

When I generate I get the following

using System; using Microsoft.EntityFrameworkCore.Migrations;

nullable disable

namespace Infrastructure.Domain.Persistence.Migrations { public partial class Initial : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.EnsureSchema( name: "CommonSchema");

        migrationBuilder.EnsureSchema(
            name: "PolicySchema");

        migrationBuilder.CreateTable(
            name: "DomainBase",
            schema: "CommonSchema",
            columns: table => new
            {
                Id = table.Column<long>(type: "bigint", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                AccessKey = table.Column<long>(type: "bigint", nullable: false),
                CreatedDate = table.Column<DateTime>(type: "datetime2", nullable: false, defaultValueSql: "SYSDATETIME()"),
                CreatedBy = table.Column<string>(type: "nvarchar(max)", nullable: false),
                LastModifiedBy = table.Column<string>(type: "nvarchar(max)", nullable: false),
                LastModifiedDate = table.Column<DateTime>(type: "datetime2", nullable: false, defaultValueSql: "SYSDATETIME()"),
                RowVersion = table.Column<byte[]>(type: "rowversion", rowVersion: true, nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_DomainBase", x => x.Id);
                **table.CheckConstraint("CK_SalesCategory_StatusId", "[StatusId] IN (0,1,2)");
                table.CheckConstraint("CK_SalesPolicy_StatusId", "[StatusId] IN (0,1,2)");**
            });

        migrationBuilder.CreateTable(
            name: "EntityAccessKey",
            schema: "CommonSchema",
            columns: table => new
            {
                Id = table.Column<long>(type: "bigint", nullable: false),
                Name = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_EntityAccessKey", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "EntityStatus",
            schema: "CommonSchema",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false),
                Name = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_EntityStatus", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "SalesCategory",
            schema: "PolicySchema",
            columns: table => new
            {
                Id = table.Column<long>(type: "bigint", nullable: false),
                Name = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: false),
                Code = table.Column<string>(type: "nchar(2)", fixedLength: true, maxLength: 2, nullable: false),
                StatusId = table.Column<int>(type: "int", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_SalesCategory", x => x.Id);
                table.CheckConstraint("CK_SalesCategory_StatusId", "[StatusId] IN (0,1,2)");
                table.ForeignKey(
                    name: "FK_SalesCategory_DomainBase_Id",
                    column: x => x.Id,
                    principalSchema: "CommonSchema",
                    principalTable: "DomainBase",
                    principalColumn: "Id");
            });

        migrationBuilder.CreateTable(
            name: "SalesPolicy",
            schema: "PolicySchema",
            columns: table => new
            {
                Id = table.Column<long>(type: "bigint", nullable: false),
                Name = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: false),
                StatusId = table.Column<int>(type: "int", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_SalesPolicy", x => x.Id);
                table.CheckConstraint("CK_SalesPolicy_StatusId", "[StatusId] IN (0,1,2)");
                table.ForeignKey(
                    name: "FK_SalesPolicy_DomainBase_Id",
                    column: x => x.Id,
                    principalSchema: "CommonSchema",
                    principalTable: "DomainBase",
                    principalColumn: "Id");
            });

        migrationBuilder.InsertData(
            schema: "CommonSchema",
            table: "EntityAccessKey",
            columns: new[] { "Id", "Name" },
            values: new object[,]
            {
                { 0L, "None" },
                { 1L, "SalesPolicyViewer" },
                { 2L, "SalesCategoryViewer" }
            });

        migrationBuilder.InsertData(
            schema: "CommonSchema",
            table: "EntityStatus",
            columns: new[] { "Id", "Name" },
            values: new object[,]
            {
                { 0, "New" },
                { 1, "Draft" },
                { 2, "Approved" }
            });

        migrationBuilder.CreateIndex(
            name: "IX_SalesCategory_Name",
            schema: "PolicySchema",
            table: "SalesCategory",
            column: "Name",
            unique: true,
            filter: "[Name] IS NOT NULL");

        migrationBuilder.CreateIndex(
            name: "IX_SalesPolicy_Name",
            schema: "PolicySchema",
            table: "SalesPolicy",
            column: "Name",
            unique: true,
            filter: "[Name] IS NOT NULL");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "EntityAccessKey",
            schema: "CommonSchema");

        migrationBuilder.DropTable(
            name: "EntityStatus",
            schema: "CommonSchema");

        migrationBuilder.DropTable(
            name: "SalesCategory",
            schema: "PolicySchema");

        migrationBuilder.DropTable(
            name: "SalesPolicy",
            schema: "PolicySchema");

        migrationBuilder.DropTable(
            name: "DomainBase",
            schema: "CommonSchema");
    }
}

}

The Check are both generated on the base table, and I can remove them.

However if I try to remove the migration then the command errors as below.

Remove-Migration Build started... Build succeeded. System.ArgumentException: An item with the same key has already been added. Key: Check: CK_SalesPolicy_StatusId "[StatusId] IN (0,1,2)" at System.Collections.Generic.Dictionary2.TryInsert(TKey key, TValue value, InsertionBehavior behavior) at System.Collections.Generic.Dictionary2.Add(TKey key, TValue value) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffContext.AddMapping[T](T source, T target) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffCollection[T](IEnumerable1 sources, IEnumerable1 targets, DiffContext diffContext, Func4 diff, Func3 add, Func3 remove, Func4[] predicates)+MoveNext() at System.Linq.Enumerable.ConcatIterator1.MoveNext() at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.Diff(ITable source, ITable target, DiffContext diffContext)+MoveNext() at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffCollection[T](IEnumerable1 sources, IEnumerable1 targets, DiffContext diffContext, Func4 diff, Func3 add, Func3 remove, Func4[] predicates)+MoveNext() at System.Linq.Enumerable.ConcatIterator1.MoveNext() at System.Linq.Enumerable.Any[TSource](IEnumerable1 source) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.HasDifferences(IRelationalModel source, IRelationalModel target) at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsScaffolder.RemoveMigration(String projectDir, String rootNamespace, Boolean force, String language) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.RemoveMigration(String contextType, Boolean force) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigrationImpl(String contextType, Boolean force) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigration.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_01.b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) An item with the same key has already been added. Key: Check: CK_SalesPolicy_StatusId "[StatusId] IN (0,1,2)"

So the migration cannot be removed and needs to be removed manually.