Entity Framework Core 6 Migration Adds Columns Not in Entity

Question

I have a .NET Core 6 project in Visual Studio 2022 using Entity Framework Core 6 Code First. I created a migration that changes some entity properties and adds some foreign keys between some tables in a SQL Server 2019 database. The migration adds some columns to a table and sets up some foreign keys using those columns. SQL Server Management Studio shows these columns to be invalid. The main class is:

namespace VerityLearn.Domain
{

    public class UserExamTopicResult
    {
        public int ExamUserId { get; set; }
        public int TopicId { get; set; }
        public int CourseId { get; set; }
        public int? AssocCourseTopicId { get; set; }
        public int SequenceNumber { get; set; }

        #region Navigation Properties
        public ExamUser ExamUser { get; set; }
        public Topic Topic { get; set; }
        public Course Course { get; set; }
        public ExamTopicCount ExamTopicCount { get; set; }
        public virtual AssocCourseTopic AssocCourseTopic { get; set; }
        #endregion // Navigation Properties
    } // end public class UserExamTopicResult
} // end namespace VerityLearn.Domain

The reference to the ExamTopicCount entity was added and the plan is to set up a foreign key relationship between these entities. The ExamTopicCount class is:

using System.Collections.Generic;

namespace VerityLearn.Domain
{
    public class ExamTopicCount
    {
        public int ExamId { get; set; }
        public int TopicId { get; set; }
        public bool IsAssociatedCourseTopic { get; set; }
        public int TopicCount { get; set; }

        #region Navigation Properties
        public Exam Exam { get; set; }
        public Topic Topic { get; set; }
        public List<UserExamTopicResult> UserExamTopicResults { get; set; }
        #endregion // Navigation Properties
    } // end public class ExamTopicCount

} // end namespace VerityLearn.Domain

The migration uses the UserExamTopicResultConfiguration and ExamTopicCountConfiguration classes.

UserExamTopicResultConfiguration:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using VerityLearn.Domain;

namespace VerityLearn.DataAccess
{
    public class UserExamTopicResultConfiguration : 
        IEntityTypeConfiguration<UserExamTopicResult>
    {
        public void Configure(EntityTypeBuilder<UserExamTopicResult> builder)
        {
            builder.ToTable("UserExamTopicResults");
            builder.HasKey(uet => new { uet.ExamUserId, uet.TopicId});
            builder.Property(uet => uet.ExamUserId)
            .HasColumnType("int");
            builder.Property(uet => uet.TopicId)
            .HasColumnType("int");
            builder.Property(uet => uet.CourseId)
            .HasColumnType("int")
            .IsRequired();
            builder.Property(uet => uet.AssocCourseTopicId)
            .HasColumnType("int")
            .IsRequired(false);
            builder.Property(uet => uet.TopicScore)
            .HasColumnType("float(24)")
            .IsRequired()
            .HasDefaultValue(0F);
            builder.HasOne(uet => uet.Course)
            .WithMany(c => c.UserExamTopicResults)
            .OnDelete(DeleteBehavior.Restrict);
            builder.HasOne(uet => uet.ExamUser)
            .WithMany(eu => eu.UserExamTopicResults);
            builder.HasOne(uet => uet.Topic)
            .WithMany(t => t.UserExamTopicResults);
        } // end public void Configure(EntityTypeBuilder<UserExamTopicResult> builder)
    } // end public class UserExamTopicResultConfiguration : ...
} // end namespace VerityLearn.DataAccess

ExamTopicCountConfiguration:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using VerityLearn.Domain;

namespace VerityLearn.DataAccess
{
    public class ExamTopicCountConfiguration : IEntityTypeConfiguration<ExamTopicCount>
    {
        public void Configure(EntityTypeBuilder<ExamTopicCount> builder)
        {
            builder.ToTable("ExamTopicCounts");
            builder.HasKey(etc => new { etc.ExamId, etc.TopicId });
            builder.Property(etc => etc.ExamId)
            .HasColumnType("int");
            builder.Property(etc => etc.TopicId)
            .HasColumnType("int");
            builder.Property(etc => etc.IsAssociatedCourseTopic)
            .HasColumnType("bit")
            .IsRequired()
            .HasDefaultValue(false);
            builder.Property(etc => etc.TopicCount)
            .HasColumnType("int")
            .IsRequired()
            .HasDefaultValue(0);
            builder.HasOne(etc => etc.Exam)
            .WithMany(e => e.ExamTopicCounts)
            .HasForeignKey(etc => etc.ExamId);
            builder.HasOne(etc => etc.Topic);
            builder.HasMany(etc => etc.UserExamTopicResults)
                .WithOne(uetr => uetr.ExamTopicCount);
        } // end public void Configure(EntityTypeBuilder<ExamTopicCount> builder)
    } // end public class ExamTopicCountConfiguration : ...
} // end namespace VerityLearn.DataAccess

The migration generated a class, ExamUserUserExamTopicResultExamTopicCount : Migration.

using Microsoft.EntityFrameworkCore.Migrations;

namespace VerityLearn.DataAccess.Migrations
{
    public partial class ExamUserUserExamTopicResultExamTopicCount : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "TopicCount",
                table: "UserExamTopicResults");

            // Added column by migration framework
            migrationBuilder.AddColumn<int>(
                name: "ExamId",
                table: "UserExamTopicResults",
                type: "int",
                nullable: true);

            // Added column by migration framework
            migrationBuilder.AddColumn<int>(
                name: "ExamTopicCountExamId",
                table: "UserExamTopicResults",
                type: "int",
                nullable: true);

            // Added column by migration framework
            migrationBuilder.AddColumn<int>(
                name: "ExamTopicCountTopicId",
                table: "UserExamTopicResults",
                type: "int",
                nullable: true);

            migrationBuilder.CreateIndex(
                name: "IX_UserExamTopicResults_ExamId",
                table: "UserExamTopicResults",
                column: "ExamId");

            migrationBuilder.CreateIndex(
                name: "IX_UserExamTopicResults_ExamTopicCountExamId_ExamTopicCountTopicId",
                table: "UserExamTopicResults",
                columns: new[] { "ExamTopicCountExamId", "ExamTopicCountTopicId" });

            migrationBuilder.AddForeignKey(
                name: "FK_UserExamTopicResults_Exams_ExamId",
                table: "UserExamTopicResults",
                column: "ExamId",
                principalTable: "Exams",
                principalColumn: "ExamId",
                onDelete: ReferentialAction.Restrict);

            migrationBuilder.AddForeignKey(
                name: "FK_UserExamTopicResults_ExamTopicCounts_ExamTopicCountExamId_ExamTopicCountTopicId",
                table: "UserExamTopicResults",
                columns: new[] { "ExamTopicCountExamId", "ExamTopicCountTopicId" },
                principalTable: "ExamTopicCounts",
                principalColumns: new[] { "ExamId", "TopicId" },
                onDelete: ReferentialAction.Restrict);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(
                name: "FK_UserExamTopicResults_Exams_ExamId",
                table: "UserExamTopicResults");

            migrationBuilder.DropForeignKey(
                name: "FK_UserExamTopicResults_ExamTopicCounts_ExamTopicCountExamId_ExamTopicCountTopicId",
                table: "UserExamTopicResults");

            migrationBuilder.DropIndex(
                name: "IX_UserExamTopicResults_ExamId",
                table: "UserExamTopicResults");

            migrationBuilder.DropIndex(
                name: "IX_UserExamTopicResults_ExamTopicCountExamId_ExamTopicCountTopicId",
                table: "UserExamTopicResults");

            migrationBuilder.DropColumn(
                name: "ExamId",
                table: "UserExamTopicResults");

            migrationBuilder.DropColumn(
                name: "ExamTopicCountExamId",
                table: "UserExamTopicResults");

            migrationBuilder.DropColumn(
                name: "ExamTopicCountTopicId",
                table: "UserExamTopicResults");

            migrationBuilder.AddColumn<int>(
                name: "TopicCount",
                table: "UserExamTopicResults",
                type: "int",
                nullable: false,
                defaultValue: 0);
        }
    }
}

After the migration I ran a query on the UserExamTopicResults table and got the following.

I don't understand what is happening and how it can be resolved. Any input would be apprecieated.

Thanks, Leonard

Answer 1

I found my problem. I did not completely add the DbSet and configuration to my DbContext class. It seems that leaving this information out causes the migration processing to make "guesses" concerning how to create or change the database tables. I needed to add the following lines to the DbContext.

/// <summary>
/// AssocCourseTopicExam DbSet
/// </summary>
public DbSet<AssocCourseTopicExam> AssocCourseTopicExams { get; set; }
...
protected override void OnModelCreating(ModelBuilder builder)
{
...
builder.ApplyConfiguration(new ExamAssocCourseTopicConfiguration());
...
}

After making this change the migration proceeded as expected.

Answer 2

builder.HasKey(uet => new { uet.ExamUserId, uet.TopicId});

This line are can be removed , the new keyword creates extra 2 property for you. Instead of using new try this maybe :

builder.HasKey(t => t.ExamUserId);
builder.HasKey(t => t.TopicId)

After that to reflect new changes to Migration you can delete Migrations folder and run

dotnet ef database drop
dotnet ef migrations add Initial
dotnet ef update database

I tried these way but i had not any data , so be careful about data loss.