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

Question

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.

There is one reason `classNameId` is present into your data-table but that fields missing into data model class so first verify it. — Kalpesh Boghara, Jul 03 '19 at 06:31
No there is no any column in Data-table as well as data model named ClassNameId. Its just Id in data model as well as data table @KalpeshBoghara — karan chavan, Jul 03 '19 at 06:34
So please share full error message. Might be you miss something, — Kalpesh Boghara, Jul 03 '19 at 06:35
Ok, haven't you renamed `classNameId` to `Id`? Or it was `Id` from the beginning. — Ali Bahrami, Jul 03 '19 at 06:38
I have not renamed classNameId to Id, It was Id from begining only @AliBahrami — karan chavan, Jul 03 '19 at 06:43
note that Fluent API is the name for the syntax of your model editing scheme (modelBuilder.Entity<>...) in contrast to data annotations ([ForeignKey...]) and is not directly related to querying your data. You most likely meant the LINQ method syntax (though I can only assume, because you didn't provide any querying code). — DevilSuichiro, Jul 03 '19 at 06:48

Ivan Stoev · Accepted Answer · 2019-07-03T07:41:05.353

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).

I have tried with your fluent API solution , now it gives me error as Invalid column name ClassNameId1, Previously it was ClassNameId — karan chavan, Jul 03 '19 at 07:05
No way with the classes from the post. Most likely you *do* have reference navigation property in `OtherClass` pointing to `ClassName` (something like `public ClassName ClassName { get; set; }`) not shown in the post, don't you? If that's true, simply specify it in `WithOne`, e.g. `.WithOne(e => e.ClassName)`. And please include all relevant properties in the questions (even they are not the real model, we understand that). Relationship fluent API must match exactly the presence/absence of a navigation property in either side. — Ivan Stoev, Jul 03 '19 at 07:17
Yes I had a property public ClassName ClassName { get; set; }) in my class, Just left out that while adding the code. — karan chavan, Jul 03 '19 at 07:32
And you shouldn't. Because having that opens other possible `ForeignKey` attribute solutions, and changes the fluent API solution. These little details are quite important. Let me update the answer for clarity. — Ivan Stoev, Jul 03 '19 at 07:42

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

1 Answers1