Entity framework Invalid Column name, EF adds number 1 to primary key

Question

I have these two entities:

public partial class Suscriptores
{
    public Suscriptores()
    {
       this.Publicacion = new HashSet<Publicacion>();
    }

    [Key]
    public int IdSuscriptor { get; set; }
    public string LogoSuscriptor { get; set; }
    public string Identificacion { get; set; }
    public string Nombre { get; set; }
    public string Direccion { get; set; }
    public string Telefono { get; set; }
    public string Email { get; set; }
    public string Fax { get; set; }
    public string Home { get; set; }

    public virtual ICollection<Publicacion> Publicacion { get; set; }
}

public partial class Publicacion
{
    public Publicacion()
    {
        this.Edictos = new HashSet<Edictos>();
    }

    [Key]
    public decimal IdPublicacion { get; set; }
    public System.DateTime FechaPublicacion { get; set; }
    public string IdUsuario { get; set; }
    public System.DateTime FechaPublicacionHasta { get; set; }
    public System.DateTime FechaArchivoHasta { get; set; }
    public int IdSuscriptor { get; set; }
    public decimal IdTipoPublicacion { get; set; }

    [ForeignKey("IdSuscriptor")]
    public virtual Suscriptores Suscriptores { get; set; }
}

When I try to run this query:

public ActionResult DetailsVSTO(string Identificacion)
{
    var SusQ = from s in db.Suscriptores
               where s.Identificacion == Identificacion
               select s;

    return Json(SusQ.First(), JsonRequestBehavior.AllowGet);
}

It throw this message:

System.Data.SqlClient.SqlException: Invalid column name 'Suscriptores_IdSuscriptor1'

Trying to solve this problem, I added this fluent API code at DBContext:

modelBuilder.Entity<Suscriptores>()
    .HasMany(x => x.Publicacion)
    .WithRequired(x => x.Suscriptores)
    .Map(a => a.MapKey("IdSuscriptor"));

But the problem persists. How can I solve this?

Answer 1

Try add a many-to-one mapping as well. Please use pure Fluent API, and you should remove the [ForeignKey] annotations.

modelBuilder.Entity<Publicacion>()
            .HasRequired(x => x.Suscriptores)
            .WithMany(x => x.Publicacion);

Answer 2

I received this error in relation to a non-foreign key column and wasted far too much time trying to figure out the error. It was in my code, not in EF or the database. I had simply thought that I had typed

this.Property(t => t.Revision).HasColumnName("Revision");
this.Property(t => t.DistributionClass).HasColumnName("DistributionClass");

But what I had typed was

this.Property(t => t.Revision).HasColumnName("Revision");
this.Property(t => t.Revision).HasColumnName("DistributionClass");

I suppose I was looking at the line above and put t.Revision instead of t.DistributionClass. And no matter how long I looked at it I could not see my own mistake. With any luck this will save some other poor soul some time.

Answer 3

I had this issue in my Item table on a property (column) I had just added, and how frustrating!

Turns out I had a List property in the data model for Order, and because I did not Ignore it in that configuration it cause this same issue in the Item table. This would not have happened except that both tables had a property of the same name, so I had to do this... which I should have done anyways.

public OrderConfiguration() { Ignore(p => p.Items); }

Answer 4

Hello Guys In my case I had a legacy code with two classes with different names of the same foreign key. Adding the Annotation doing reference to the correct column and the name of attribute with the same name in other classes.then the annotation ForeignKey doing match between the both columns.

[Table("LFile")]
public partial class LFile
{
    [Key]
    public int FileId { get; set; }

    [StringLength(500)]
    public string FileName { get; set; }

    public int? LRRFileDetailId { get; set; }

    public byte[] FileData { get; set; }

    public FileType Type { get; set; }

    [Column("LUpload_Id")] //Foreign Key of the class
    public int? LUploadId { get; set; }

    [ForeignKey("LUploadId")] //Foreign key inherited
    public virtual LParserProcess LParserProcess { get; set; }
}

Answer 5

I was getting the same error SqlException message where the number 1 was being appended to my field names.

I was able to solve it once I realised that I had incorrectly assumed the [ForeignKey] annotations refer to the field name as it is in the database. Instead they should match the property name as defined in the model.

So for example:

[Column("Organisation_Account_Manager")]
[Display(Name = "Organisation_Account_Manager_ID")]
[DisplayFormat(NullDisplayText = "Unknown")]
public int? Organisation_Account_Manager_ID { get; set; }

[Display(Name = "Account Manager")]
[ForeignKey("Organisation_Account_Manager_ID")]
public Contact Account_Manager { get; set; }

In this example it will work, because [ForeignKey("Organisation_Account_Manager_ID")] is an exact match to public int? Organisation_Account_Manager_ID. Previously my [ForeignKey] annotation was using Organisation_Account_Manager, which is the field name in the database - but this was incorrect.