SqlClient.SqlException: Invalid column name ClassNameId While fetching data from Fluent API

I am new to this EF,I am tring to fetch data from Sql Db table, and it gives me error like invalid classNameId error.

public class ClassName
{
        [Key]
        [Display(Name = "Id:")]
        public int Id { get; set; }

        [Display(Name = "Created By:")]
        [Required(ErrorMessage = "Please enter created by.")]
        [StringLength(5, MinimumLength = 2)]
        public string CreatedBy { get; set; }
        public List<OtherClass> OtherClassList{ get; set; }
}

public class OtherClass
{

        [Column("Control")]
        [Display(Name = "Control:")]
        public String Control { get; set; }

        [ForeignKey("PID")]
        [Column("PID")]
        [Display(Name = "PID:")]
        public int PID{ get; set; }
}

DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ClassName>()
                .HasKey(p => p.Id);

            modelBuilder.Entity<OtherClass>()
            .HasKey(p=> new { p.Control, p.PID});
}

I have use Fluent API for get data from MSSql.

Answer

The problem is the invalid ForeignKey annotation here

[ForeignKey("PID")]
[Column("PID")]
[Display(Name = "PID:")]
public int PID{ get; set; }

ForeignKey is confusing attribute because it changes the meaning of the argument depending on where it is applied. When applied on FK property it indicates the name of the navigation property. And when applied on navigation property it indicates the name of the FK property.

In your case, it is applied on FK property, but specifies the same property name, so it is simply ignored and EF uses the default conventional name for the FK property associated with the List<OtherClass> OtherClassList relationship, which is why you are getting the non existing column.

To fix the problem, either apply the attribute on the navigation property of the other class (because you have no navigation property in the class that needs FK):

[ForeignKey(nameof(OtherClass.PID))]
public List<OtherClass> OtherClassList{ get; set; }

or (preferably) configure it with fluent API:

modelBuilder.Entity<ClassName>()
    .HasMany(e => e.OtherClassList)
    .WithOne()
    .HasForeignKey(e => e.PID);

---

In case you have reference navigation property, in addition to the above data annotation solution, you could also use

    [ForeignKey(nameof(ClassName))]
    [Column("PID")]
    [Display(Name = "PID:")]
    public int PID{ get; set; }

or

    [ForeignKey(nameof(PID))]
    public ClassName ClassName { get; set; }

or the fluent API .WithOne() must be changed to .WithOne(e => e.ClassName).