EntityFramework : Invalid column name *_ID1

Question

I am trying to implement DbContext for couple of tables called 'Employee' and 'Department' Relationship between Employee and Department is many to one. i.e. department can have many employees.

Below are the EntityFramework classes I designed ( CodeFirst approach )

    [Table("Employee")]
    public class Employee
    {
        [DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Column("Name")]
        public string Name { get; set; }

        [Column("Department_ID")]        
        public int Department_ID { get; set; }

        public virtual Department Department { get; set; }
    }

[Table("Department")]
    public class Department
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        [Column("Name")]        
        public string Name { get; set; }

        public virtual ICollection<Employee>  Employees { get; set; }
    }

While adding Employee record I am getting below exception

"Invalid column name 'Department_ID1'." 

I am not sure why EF is referring to Department_ID1. Do I need to add configuration in OnModelCreating method of DbContext?

I am using EF version 6.1.1

Answer 1

Hi After spending some time I could fix this problem by using ForeignKey attribute on public virtual Department Department { get; set; } property of Employee class.

Please see below code.

 [Table("Employee")]
public class Employee
{
    [DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Column("Name")]
    public string Name { get; set; }

    [Column("Department_ID")]        
    public int Department_ID { get; set; }

    [ForeignKey("Department_ID")]
    public virtual Department Department { get; set; }
}

This fixed my problem. Are there any other solution to fix this? Using fluent API?

Answer 2

I've also gotten this problem in my EF one-many deals where the one has a List of the many property and my mapping didn't specify that property. For example take:

public class Notification
{
    public long ID { get; set; }     

    public IList<NotificationRecipient> Recipients { get; set; }
}

then

public class NotificationRecipient
{
    public long ID { get; set; }

    public long NotificationID { get; set; }

    public Notification Notification { get; set; }
}

Then in my mapping, the way that caused the Exception (the incorrect way):

builder.HasOne(x => x.Notification).WithMany()
    .HasForeignKey(x => x.NotificationID);

What fixed it (the correct way) was specifying the WithMany property:

builder.HasOne(x => x.Notification).WithMany(x => x.Recipients)
    .HasForeignKey(x => x.NotificationID);

Answer 3

For me, the issue was resolved by removing a (duplicate?) virtual property.

Using the OP's example:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Department_ID { get; set; }
    public virtual Department Department { get; set; }
}

public class Department
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Employee>  Employees { get; set; }
}

Turns into:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Department_ID { get; set; }
}

public class Department
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Employee>  Employees { get; set; }
}