Question posted in
The official documentation can be found here.

Asp.net – SQL created unwanted column for one to many relationship

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 it shows 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’.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    The unwanted one-to-many relationship you’re experiencing might be due to Entity Framework Core’s conventions for relationship discovery. EF Core will try to infer relationships between entities based on naming conventions and property types unless explicitly configured otherwise.

    In your case, you have defined a relationship between JobListing and JobApplication using the JobId property in the JobApplication class:

    and in your jobapplication class
    public int JobId { get; set; }
    public virtual JobListing? JobListing { get; set; }

    based on above reference you have given in class it will create a one to many reference in your sql.

    you can try adding the following property in your jobapplication class and then run the update-database command

      [ForeignKey("JobListing")] // Explicitly indicate the FK
public int JobId { get; set; }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search