CODEWITHSUNDEEP
entity-frameworkentity-framework-4entity-framework-5
Juan Pablo Gomez
Juan Pablo Gomez

Reputation: 5524

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

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?

Upvotes: 25

Views: 19396

Answers (6)

H.A.H.
H.A.H.

Reputation: 3887

Just want to share my case. If we have:

  1. Navigation property (MyEntity of type MyEntity)
  2. Id property (MyEntityId of type int)

And we try to reference: MyEntity with property Id of type short

Ef core (using version 8.0.2) will complain about "MyEntityId1" column.

Changing MyEntityId to match the type short solves the problem.

(It is very unlikely that this will be your case, but if you are trying to reference a bunch of "keyless" entities based on views, it is worth checking out)

Upvotes: 0

Mike
Mike

Reputation: 1876

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

Upvotes: 4

Leonardo Mora
Leonardo Mora

Reputation: 369

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; }
}

Upvotes: 2

Ty H.
Ty H.

Reputation: 953

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); }

Upvotes: 5

Robert Kaucher
Robert Kaucher

Reputation: 1861

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.

Upvotes: 11

zs2020
zs2020

Reputation: 54504

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

Upvotes: 13

Related Questions