1

I have several related domain models that are triggering the exception SqlException: Invalid column name 'ChecklistTemplate_Id'.

My domain model looks like:

public class Assignment
{
    public long Id { get; set; }
    public long ChecklistId { get; set; }
    public DateTime InspectionDate { get; set; }
    public long JobId { get; set; }
    public Guid? EmployeeId { get; set; }
    // TODO: Make the completion a nullable date time in the database and here
    public DateTime CompletionDate { get; set; }

    public virtual Job Job { get; set; }
    public virtual Checklist Checklist { get; set; }
    public virtual IList<Image> Images { get; set; }
    public virtual IList<Attachment> Attachments { get; set; }
    public virtual IList<Equipment> Equipments { get; set; }
}

My EntityTypeConfiguration class looks like:

internal class AssignmentConfiguration : EntityTypeConfiguration<Assignment>
{
    public AssignmentConfiguration()
    {
        ToTable("Assignment");

        HasKey(k => k.Id);

        Property(a => a.ChecklistId)
            .IsRequired();
        Property(a => a.CompletionDate)
            .IsOptional();
        Property(a => a.EmployeeId)
            .IsOptional();
        Property(a => a.Id)
            .IsRequired();
        Property(a => a.InspectionDate)
            .IsRequired();
        Property(a => a.JobId)
            .IsRequired();

        HasRequired(a => a.Job)
            .WithMany(a => a.Assignments)
            .HasForeignKey(a => a.JobId);

        HasRequired(a => a.Checklist)
            .WithOptional(a => a.Assignment);

        HasMany(a => a.Images)
            .WithRequired(a => a.Assignment)
            .HasForeignKey(a => a.InspectionId);
    }
}

The Checklist domain model has a ChecklistTemplate navigation property with the join:

HasMany(a => a.CheckLists)
            .WithRequired(a => a.ChecklistTemplate)
            .HasForeignKey(a => a.ChecklistTemplateId);

There is a one to one between Assignment and Checklist as seen in the Assignment entity configuration.

And yes, we are including the configuration in the DBContext.

Also, I looked at Entity Framework 6 creates Id column even though other primary key is defined and that doesn't seem to apply.

Russel Madere
  • 13
  • 4

1 Answers1

0

I dont have a satisfactory answer to that but I have had a lot of trouble with ef6. This is because there is a navigation which is not defined or wrongly defined. So ef6 creates it on-the-fly on proxy classes and you cry for hours. I hope you find out the problem soon.

And the navigation you stated is one-to-many. Be careful.

  • So HasRequired(a => a.Checklist).WithOptional(a => a.Assignment); is one one to many? The ChecklistTemplate to Checklist on one to many. – Russel Madere Aug 19 '20 at 20:46
  • We found the problem. When I, as DBA, cleaned up the database joins, I did not fully remove navigation properties. If you run into this, step back and stop rushing, make sure there are no extraneous navigation properties and slap yourself in the head when you find one. @Hasan, thanks for the pointer. – Russel Madere Aug 20 '20 at 13:08