Why EF inserts new column when the original model does not have it defined?

Question

I created a simple models and

public class Movie
{
    public Int64 MovieSer { get; set; }  //PK
    public string? MovieId { get; set; }
    public string? MovieUID { get; set; }
    public string? Title { get; set; }

    public List<Category> Categories { get; set; }

}

public class Category
{
    public Int64 CategorySer { get; set; }  //PK
    public Int64 MovieSer { get; set; }  //FK
    public string CategoryId { get; set; }
    public string StudyName { get; set; }
    public DateTime CreationDate { get; set; }
    public string StudyUID { get; set; }

}

MovieDbContext

public class MovieDbContext : DbContext
{
    public MovieDbContext(DbContextOptions<MovieDbContext> options) : base(options)
    { }

    public DbSet<Movie> Movies { get; set; }
} 

Q1: After pm>add-migration is run, the category table includes a new column MovieId = table.Column<string>(type: "nvarchar(450)", nullable: true) Why does it trying to create a column name MovieId if it does not exist in the initial model <Category> ?

Q2: Why is assigning the incorrect constraints for the table Category constraints: table =>

{
    table.PrimaryKey("PK_Movie", x => x.MovieId);

    table.ForeignKey(
        name: "FK_Category_Movie_MovieId",
        column: x => x.MovieId,
        principalTable: "Movie",
        principalColumn: "MovieId");

});

OF course when I run the application, it fails with

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'MovieId'.

BTW: if I do not include the Category table then everyting works so the issue is related to the table relationship.

Any explanation would be greatly appreciated

Answer 1

It looks like your Category table is trying to use MovieId as it's PK. This should be a CategoryId. The MovieId would be the FK back to the Movie entity/table.

Answer 2

I figured out. I needed to use the override OnModelCreating() method and specify the table relationship there using HasForeignKey() and HasPrincipalKey() methods