SQL created unwanted column for one to many relationship

Question

I'm using ASP.NET MVC to setup a database, but when I run update-database, the database automatically created another column JobListingJobId to store the FK of the JobApplication table.

Here are all my classes - Employer:

public class Employer : ApplicationUser
{
    public string? Company { get; set; }
    public virtual ICollection<JobListing>? Jobs { get; set; }
}

JobApplication:

public class JobApplication
{
    [Key]
    public int JobApplicationId { get; set; }
    //Attributes
    [Display(Name = "Job")]
    public int JobId { get; set; }
    [Display(Name = "Job Seeker Name")]
    public string JobSeekerId { get; set; }
    [Display(Name = "Job Seeker Name")]
    public virtual JobSeeker? JobSeeker { get; set; }
    [Display(Name = "Job")]
    public virtual JobListing? JobListing { get; set; }
}

JobCategory:

public class JobCategory
{
    [Key]
    public int JobCategoryId { get; set; }
    //Attributes
    public virtual ICollection<JobListing>? JobListing { get; set; }
}

JobListing:

public class JobListing
{
    [Key]
    public int JobId { get; set; }
    //Attributes
    [Display(Name = "Employer Name")]
    public string? EmployerId { get; set; }
    [Display(Name = "Category Name")]
    public int JobCategoryId { get; set; }
    public virtual Employer? Employer { get; set; }
    public virtual JobCategory? JobCategory { get; set; }
    public virtual ICollection<JobApplication>? JobApplications { get; set; }
}

JobSeeker:

public class JobSeeker : ApplicationUser
{
    public string? Skill { get; set; }
    public virtual ICollection<JobApplication>? JobApplications { get; set; }
}

I created JobApplication, JobListing and JobCategory in the ApplicationDbContext:

public DbSet<JobListing> JobListings { get; set; }
public DbSet<JobCategory> JobCategories { get; set; }
public DbSet<JobApplication> JobApplications { get; set; }

and later on, I tried to explicitly specify set the FK for JobApplication:

builder.Entity<JobApplication>()
       .HasOne(ja => ja.JobListing)
       .WithMany(jl => jl.JobApplications)
       .HasForeignKey(ja => ja.JobId)
       .IsRequired();

But when I run update-database, I get this error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_JobApplications_JobListings_JobId". The conflict occurred in database "FPTJob", table "dbo.JobListings", column 'JobId'.

Answer 1

1. I would rename your JobId to JobListingId in both tables. Or alternatively rename the JobListing entity to Job.

2. If you don't already have data in your database, you can delete it and run update-database to have it re-created using your new code.