Entity structure 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 execute 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); } 

This is throw this message:

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

Trying to solve this problem, I added this free API code to 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 adding a many-to-one display. Use the pure Fluent API and you must remove the [ForeignKey] annotations.

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

Answer 2

I got this error regarding a column without a foreign key and spent too much time trying to figure out the error. This was in my code, not in EF or the database. I just thought I typed

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

But what I typed was

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

I suppose I looked at the above line and set t.Revision instead of t.DistributionClass . And no matter how long I looked at her, I could not see my mistake. If you are lucky, it will save a little other poor soul.

Answer 3

I had a similar problem when I was getting an internal exception, with the number one being added to the field name. Come to find out because I copied and pasted and forgot to change the name of the property. For instance:

 Property(x => x.SomeProperty).HasColumnName("SomeProperty").IsOptional(); Property(x => x.SomeProperty).HasColumnName("SomeOtherProperty").IsOptional(); 

Notice that I changed the column name in quotation marks, but I accidentally left the same property in my lambda. This is me a little. Hope this helps.

 Property(x => x.SomeProperty).HasColumnName("SomeProperty").IsOptional(); Property(x => x.SomeOtherProperty).HasColumnName("SomeOtherProperty").IsOptional(); 

This fixed the problem.

Answer 4

I had this problem in my Item table on the property (column) that I just added, and how frustrating it is!

It turns out I had the List property in the data model for Order, and because I did not ignore it in this configuration, it causes the same problem in the Item table. This would not happen, except that both tables had a property with the same name, so I had to do it ... which I had to do anyway.

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

Answer 5

Hi guys. In my case, I had legacy code with two classes with different names of the same foreign key. Add an annotation that references the correct column and an attribute name with the same name in other classes. Then the ForeignKey annotation performs a mapping between 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; } }