Looking for Column that does not exist

Question

I am getting the message ... *Invalid column name 'PartLot_Id'*

I suppose that it is referring to the ID field of the PART_LOT in the databse. Clearly this table, and no others (not as clear but trust me) possess a field "PartLot_Id" so on the surface this makes sense.

When I wrote the corresponding entity for this table I wanted to make it a little more friendly so I changed the case of the tables and fields, and in some cases rename the fields completely (trying to distinguish business concerns from data). I did, however decorate the class and properties with the approriate attributes which is supposed to signal to EF what the actual names are in the DataStore. This has worked on all my entities up until now.

[Table("PART_LOT")]
public partial class PartLot : ModelBase
{
    [Key]
    [Column("ID")]
    public Int32 Id { get; set; }

    [Column("LOT_IDENT")]
    public String LotIdentity { get; set; }

    [Column("PART_ID")]
    public Guid? PartId { get; set; }

    [Column("COMPANYSITE_ID")]
    public Guid? CompanySiteId { get; set; }



    #region Navigation Properties

    [ForeignKey("PartId")]
    public virtual Part Part { get; set; }

    [ForeignKey("CompanySiteId")]
    public virtual Company Company { get; set; }

    public virtual ICollection<StrategicPart> StrategicParts { get; set; }

    public virtual ICollection<Product> Products{ get; set; }


    #endregion
}

It appears that EF is ignoring these attributes and implementing it's convention whichis, as I understand it, to assume that the Key field name is the Entity Name plus "Id".

Can anyone shed any light why it seems that these attributes are being ignored?

UPDATE

@kirtsen g- Thanks for your response. I feel like we might be on the right track or somewhere closer than where Iam now anyway. I am updating the OP with some additional informationm that I originally excluded in an attempt to keep the post clean and as uncluttered as possible.

The PartLot IS being references by a navigation property on another entity model, but it is also annotated correctly(?)

---

[Table("STRATEGIC_PART")]
public partial class StrategicPart : ModelBase
{
    [Key]
    [Column("ID")]
    public Int64 Id { get; set; }

    [Column("PRODUCT_ID")]
    public Guid ProductId { get; set; }

    [Column("PART_LOT_ID")]
    public Int32 PartLotId { get; set; }

    #region Navigation Properties

    [ForeignKey("ProductId")]
    public virtual Product Product { get; set; }

    [ForeignKey("PartLotId")]
    public virtual PartLot Lot { get; set; }

    #endregion
}

---

The "Lot" property of the StrategicPart model returns a "PartLot" entity (I changed the name to simply "Lot" as StrategicPart.PartLot seemed redundant), but I did assign the ForeignKeyAttribute to the "PartLotId" in an attempt to override any CodeFirst assumptions/conventions (one of my problems with the convention over configuration model).

Ya' know, it just occured to me, and I am not sure if this is potentially important or not, but the StrategicPart entity, and thusly the STRATEGIC_PART table in the database, is actually a join for a many-to-many relationship between Products and PartLots.

Thanks again!

UPDATE

@kirsten_g - Thanks for hanging in there with me!! I have added the Product class as requested.

[Table("PRODUCT")]
public partial class Product : ModelBase
{
    [Key]
    [Column("ID")]
    public Guid Id { get; set; }

    [Column("MFG_INFO_ID")]
    public Guid? ManufacturerInfoId { get; set; }

    [Column("MODEL_ID")]
    public Guid ModelId { get; set; }

    [Column("MODEL_CODE")]
    public String ModelCode { get; set; }

    [Column("CONFIG_CODE")]
    public String ConfigCode { get; set; }

    [Column("SERIAL_NUMBER")]
    public String SerialNumber { get; set; }

    [Column("FULL_SN")]
    public String FullSerialNumber { get; set; }

    [Column("SW_VERSION")]
    public String SoftwareVersion { get; set; }

    [Column("REWORKED")]
    public Boolean IsReworked { get; set; }

    [Column("DATAFILE_ID")]
    public Int32 DatafileId { get; set; }

    [Column("SILICON_ID")]
    public Guid? SiliconId { get; set; }

    [Column("IS_PART_EXCEPTION_CALCULATED")]
    public Boolean? IsPartExceptionCalculated { get; set; }

    [Column("STATUS")]
    public String Status { get; set; }

    [Column("STATUS_CHANGED_DT")]
    public DateTime StatusChangeDate { get; set; }




    #region Navigation Properties

    [ForeignKey("ModelId")]
    public virtual ProductModel Model { get; set; }

    #endregion

}

UPDATE : SOLUTION

I figured out the problem thanks to kirsten_g. By asking to see the Product class it occured to me that I had not added a reference to the STRATEGIC_PART (StrategicPart) entity in it. When I added it, it did not help but then I remembered ... STRATEGIC_PART's only purpose is to facilitate a many-to-many join.

If I had left EF to create the models itself, it would not bother with Nav Properties to the joining entity. So I manually did the same. Ignoring the StrategicPart entity I added Nav Properties from the two entities directly to each other and removed any Nav Property referencing StrategicPart. So the updated Product and PartLot classes look like ...

[Table("PRODUCT")]
public partial class Product : ModelBase
{
    [Key]
    [Column("ID")]
    public Guid Id { get; set; }

   // Removed properties for clarity. Still contatins all the properties defined above.

    #region Navigation Properties

    [ForeignKey("ModelId")]
    public virtual ProductModel Model { get; set; }

    // Added Nav Property to PartLots
    public virtual ICollection<PartLot> PartLots{ get; set; }

    #endregion

}

[Table("PART_LOT")]
public partial class PartLot : ModelBase
{
    [Key]
    [Column("ID")]
    public Int32 Id { get; set; }

   // Removed properties for clarity. Still contatins all the properties defined above.

    #region Navigation Properties

    [ForeignKey("PartId")]
    public virtual Part Part { get; set; }

    [ForeignKey("CompanySiteId")]
    public virtual Company Company { get; set; }

    // Remove Nav Property to StrategicPart
    // public virtual ICollection<StrategicPart> StrategicParts { get; set; }

    public virtual ICollection<Product> Products{ get; set; }


    #endregion
}

Now my entities reference each other properly and my error has gone away! I have marked Kirsten_g's answer as the answer with the above extension!

Thanks for everybody'd help. I hope that this helps someone else as well.

Answer 1

Code First creates a foreign key in the database using the pattern [Name of navigation property]_[Primary Key of related class]

It is probably doing this because you have set up a navigation property somewhere to PartLot, but not defined a PartLotID foreign key for the navigation to use.

See the answer here for some help