CODEWITHSUNDEEP
linqentity-framework
Tevis
Tevis

Reputation: 739

Invalid Column Name Entity guessing a foreign key that isn't there

I don't really understand what's happening here. From my research I gather that entity is trying to guess the name of a foreign key and it's not there, so it's throwing an "Invalid Column Name" error. The problem is that the model/table in question does not have any foreign keys. So I'm really confused.

Here's the code that throws the error:

foreach (TechnologyProjectPlanModel result in results)
{
    //get approvers for plan
    int id = result.Id;

    try
    {
         List<ApprovalModel> approvers = db.ApprovalModels.Where(m => m.FormId == result.Id).Select(m => m).ToList();   //ERROR HERE

         if (approvers != null)
         {
             result.Approvers = approvers.ToList();
         }
    }
    catch (Exception e)
    { 
    }
}

Here's the ApprovalModel:

public class ApprovalModel
{
    [Key]
    public int Id { get; set; }

    public int ApprovalProcessId { get; set; }
    public int FormId { get; set; }
    public int UserId { get; set; }
    public bool? Approved { get; set; }
}

And here's the TechnologyProjectPlanModel referenced in the foreach loop:

public class TechnologyProjectPlanModel
{
    [Key]
    public int Id { get; set; }

    public int FormId { get; set; }
    public int UserId { get; set; }
    public string FormType { get; set; }
    public int Status { get; set; }
    public int Hidden { get; set; }
    public DateTime DateSubmitted { get; set; }
    public DateTime DateFinalized { get; set; }

    public List<QuoteUploadsModel> Quotes { get; set; }
    public List<ApprovalModel> Approvers { get; set; }

    [Required]
    [Display(Name = "Please Select Your School")]
    public string School { get; set; }

    [Required]
    [Display(Name = "Requestor")]
    public string Requestor { get; set; }

    [Display(Name = "Title")]
    public string Title { get; set; }

    [Required]
    [DataType(DataType.PhoneNumber)]
    [Display(Name = "Phone Number")]
    [Phone]
    public string PhoneNumber { get; set; }

    [Required]
    [DataType(DataType.EmailAddress)]
    [Display(Name = "Email Address")]
    [EmailAddress]
    public string Email { get; set; }

    [Required]
    [Display(Name = "Project Title")]
    public string ProjectTitle { get; set; }

    [Display(Name = "Requested Completion Date")]
    public DateTime RequestedCompletionDate { get; set; }

    [Required]
    [Display(Name = "Project Description")]
    public string ProjectDescription { get; set; }

    [Display(Name = "Teacher/Room Number")]
    public string TeacherGroup { get; set; }

    [Display(Name = "1")]
    public bool Grade1 { get; set; }
    [Display(Name = "2")]
    public bool Grade2 { get; set; }
    [Display(Name = "3")]
    public bool Grade3 { get; set; }
    [Display(Name = "4")]
    public bool Grade4 { get; set; }
    [Display(Name = "5")]
    public bool Grade5 { get; set; }
    [Display(Name = "6")]
    public bool Grade6 { get; set; }
    [Display(Name = "7")]
    public bool Grade7 { get; set; }
    [Display(Name = "8")]
    public bool Grade8 { get; set; }
    [Display(Name = "9")]
    public bool Grade9 { get; set; }
    [Display(Name = "10")]
    public bool Grade10 { get; set; }
    [Display(Name = "11")]
    public bool Grade11 { get; set; }
    [Display(Name = "12")]
    public bool Grade12 { get; set; }
    [Display(Name = "Kindergarten")]
    public bool Kindergarten { get; set; }

    [Display(Name = "Describe how this plan will be continued if events cause programs or equipment to no longer be available.  For example, if equipment purchased needs repair what funding source will be used for repair or replacement?  For programs that have annual subscription fees, what funds will be used to continue the program from year to year?")]
    public string Sustainability { get; set; }

    public bool MultipleFundingSource { get; set; }

    [Required(ErrorMessage="*")]     
    [Display(Name = "Funding Source")]
    public string FundingSource1 { get; set; }

    [Required(ErrorMessage = "*")]
    public string FundingSource2 { get; set; }

    [Required(ErrorMessage = "*")]
    public string FundingSource3 { get; set; }

    [Required(ErrorMessage = "*")]
    public string FundingSource4 { get; set; }

    [Required(ErrorMessage = "*")]
    public string FundingSource5 { get; set; }

    [Required]
    [Display(Name = "Total Estimated Project Costs:")]
    public float TotalEstimatedProjectCosts { get; set; }

    //----------------------Additional Information

    [Display(Name = "Additional Comments:")]
    public string AdditionalComments { get; set; }

    [Display(Name = "Additional Supporting Documents:")]
    public string AdditionalSupportingDocuments { get; set; }
}

The exact error is:

An error occurred while executing the command definition. See the inner exception for details.

Inner Exception:

Invalid column name 'TechnologyProjectPlanModel_Id'.

That column name isn't referenced anywhere in my code, so it must be inferring it from something.

Any ideas? Thanks!

Upvotes: 2

Views: 1105

Answers (2)

Michael Brown
Michael Brown

Reputation: 9153

You know if you do the mapping correctly, that block of code is unnecessary because EF will retrieve results.Approvers for you.

You can use the Attribute based mapping directly on your model or you can use the Code First Configuration to map your foreign key properly.

Once you've properly mapped the foreign key, the block of code you pasted is unnecessary, EF does the load for you.

Upvotes: 2

Ocelot20
Ocelot20

Reputation: 10800

Entity Framework relies on conventions to determine what it thinks your DB looks like. In this case, it thinks the ApprovalModel table should have a foreign key to the TechnologyProjectPlanModel table. Trimming down your entities to the relevant fields, it becomes apparent why it thinks this:

public class ApprovalModel
{
}

public class TechnologyProjectPlanModel
{
    public List<ApprovalModel> Approvers { get; set; }
}

In database terms, the relationship that would most likely exist in order for there to be multiple ApprovalModel's per TechnologyProjectPlanModel would be for ApprovalModel's to have a foreign key to TechnologyProjectPlanModel's.

How does TechnologyProjectPlanModel.Approvers get set? If it doesn't have anything to do with Entity Framework and you don't want it to try populating this property via it's conventions, you can explicitly tell it not to make that assumption by telling it the property is not mapped like so:

[NotMapped]
public List<ApprovalModel> Approvers { get; set; }

If you do have this relationship, you need to give EF some more context so it doesn't make best guess assumptions. For example, if the foreign key does exist, place it in ApprovalModel along with the relevant navigation property that can further tell EF what your DB looks like:

public class ApprovalModel
{
    public int TechnologyProjectPlanModelId { get; set; }

    // ForeignKey attribute usually not necessary unless you need to tell EF
    // about a property that doesn't follow the usual "{OtherEntityName}Id"
    // naming convention.
    [ForeignKey("TechnologyProjectPlanModelId")]
    public TechnologyProjectPlanModel TechnologyProjectPlanModel { get; set; }
}

public class TechnologyProjectPlanModel
{
    public List<ApprovalModel> Approvers { get; set; }
}

Upvotes: 3

Related Questions