Entity Framework提示‘无效列名ID’问题求助

Hey there, let's work through why you're hitting this error with your FDTransaction table and Entity Framework. This issue almost always boils down to a mismatch between your model class and how EF is mapping it to your database table. Here are the most common fixes to try:

1. Explicitly Map the Primary Key and Column Name #

EF relies on conventions (like looking for a property named Id or FDTransactionId as the primary key), but your database uses ID (uppercase). Even though SQL Server is case-insensitive, explicit mapping eliminates ambiguity.

Option A: Use Data Annotations #

Add attributes to your model class to tie properties directly to database columns:

[Table("FDTransaction")] // Ensure EF targets the correct table name
public class FDTransaction
{
    [Key]
    [Column("ID")] // Explicitly map to the database's ID column
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] // Tell EF this is an auto-incrementing column
    public int ID { get; set; }

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

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

    [Column("Account_No")]
    [DataType("numeric(25,0)")]
    public decimal? Account_No { get; set; } // Use decimal instead of int for numeric database type

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

    [Column("Interest_Percent")]
    [DataType("decimal(18,0)")]
    public decimal? Interest_Percent { get; set; } // Match database's decimal type

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

    [Column("MaturityDate")]
    public DateTime? MaturityDate { get; set; }

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

Option B: Use Fluent API (in your DbContext) #

If you prefer code-based configuration over attributes, set up the mapping in your OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<FDTransaction>(entity =>
    {
        entity.ToTable("FDTransaction"); // Bind to the correct table

        entity.HasKey(e => e.ID); // Set ID as primary key
        entity.Property(e => e.ID)
            .HasColumnName("ID")
            .ValueGeneratedOnAdd(); // Mark as auto-incrementing

        // Map other properties to their database columns
        entity.Property(e => e.Account_No)
            .HasColumnName("Account_No")
            .HasColumnType("numeric(25,0)");

        entity.Property(e => e.Interest_Percent)
            .HasColumnName("Interest_Percent")
            .HasColumnType("decimal(18,0)");

        // Repeat for remaining properties as needed
    });
}

2. Check for Table Name Mismatches #

EF's default convention pluralizes table names (e.g., it might look for FDTransactions instead of your singular FDTransaction). The [Table] attribute or Fluent API ToTable call above fixes this, but double-check that EF isn't targeting the wrong table entirely.

3. Verify Your Connection String #

It sounds basic, but make sure your app's connection string is pointing to the correct database where you created the FDTransaction table. Accidentally connecting to a test database or old schema without the table will throw this error.

4. Fix Migration Inconsistencies #

If you're using EF Migrations, your migration scripts might be out of sync with the actual database schema. Try regenerating migrations and updating the database:

# In Package Manager Console or CLI
Add-Migration FixFDTransactionIdMapping
Update-Database

This will ensure EF's schema understanding matches your real database.

If none of these fixes work, share your full model class and DbContext code, and we can troubleshoot further!

内容的提问来源于stack exchange，提问作者sachin kulkarni