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

Question

I'm trying to get information from some of my models that have a foreign key relationships to my main employee model. If I map out each model individually, I can access them like normal with no problems, but I have to visit multiple different web pages to do so.

I'm trying to merge several of my models into essentially a single controller, and work with them this way. Unfortunately, when I try to access these models I get a strange error:

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

After searching through my code, apparently the only location phone_types_phone_type_id appears is in my migration code. I'm incredibly new at C# and Asp.Net in general so any help is appreciated.

Here is the code for my model:

[Table("employee.employees")]
public partial class employees1
{
    public employees1()
    {
        employee_email_manager = new List<email_manager>();
        employee_employment_history = new HashSet<employment_history>();
        employee_job_manager = new HashSet<job_manager>();
        employee_phone_manager = new HashSet<phone_manager>();
        this.salaries = new HashSet<salary>();
    }

    [Key]
    public int employee_id { get; set; }
    [Display(Name="Employee ID")]
    public int? assigned_id { get; set; }

    [Display(Name="Web User ID")]
    public int? all_id { get; set; }

    [Required]
    [StringLength(50)]
    [Display(Name="First Name")]
    public string first_name { get; set; }

    [StringLength(50)]
    [Display(Name="Last Name")]
    public string last_name { get; set; }

    [Column(TypeName = "date")]
    [Display(Name="Birthday")]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
    public DateTime birth_day { get; set; }

    [Required]
    [StringLength(1)]
    [Display(Name="Gender")]
    public string gender { get; set; }

    [Required]
    [StringLength(128)]
    [Display(Name="Social")]
    public string social { get; set; }

    [Required]
    [StringLength(128)]
    [Display(Name="Address")]
    public string address_line_1 { get; set; }

    [StringLength(50)]
    [Display(Name="Suite/Apt#")]
    public string address_line_2 { get; set; }

    [Required]
    [StringLength(40)]
    [Display(Name="City")]
    public string city { get; set; }

    [Required]
    [StringLength(20)]
    [Display(Name="State")]
    public string state { get; set; }

    [Required]
    [StringLength(11)]
    [Display(Name="Zip")]
    public string zip { get; set; }

    [Column(TypeName = "date")]
    [Display(Name="Hire Date")]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
    public DateTime hire_date { get; set; }

    [Column(TypeName = "date")]
    [Display(Name="Separation Date")]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
    public DateTime? termination_date { get; set; }

    [StringLength(70)]
    [Display(Name="Emergency Contact Name")]
    public string emergency_contact_name { get; set; }

    [StringLength(15)]
    [Display(Name = "Emergency Contact Number")]
    public string emergency_contact_phone { get; set; }

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

    public virtual ICollection<phone_manager> employee_phone_manager { get; set; }

    [Table("employee.phone_manager")]
    public partial class phone_manager
    {
        [Key]
        public int phone_id { get; set; }

        public int employee_id { get; set; }

        [Required]
        [StringLength(15)]
        public string phone_number { get; set; }

        [StringLength(5)]
        public string phone_extension { get; set; }

        public int phone_type { get; set; }

        [Column(TypeName = "date")]
        public DateTime date_added { get; set; }

        public bool deleted { get; set; }

        public virtual employees1 employees1 { get; set; }

        public virtual phone_types phone_types { get; set; }
    }

    [Table("employee.phone_types")]
    public partial class phone_types
    {
        public phone_types()
        {
            phone_manager = new HashSet<phone_manager>();
        }

        [Key]
        public int phone_type_id { get; set; }

        [Required]
        [StringLength(50)]
        public string phone_type_name { get; set; }

        public virtual ICollection<phone_manager> phone_manager { get; set; }
    }
}

And the pertinent code from my view:

        @foreach (var item in Model.employee_phone_manager)
        {
            @Html.DisplayFor(modelItem => item.phone_number); 
            @: - 
            @Html.DisplayFor(modelItem => item.phone_type);
            <br />
        }

EDIT I may have found out the issue, but I'll definitely take more input if there is another option. My solution was to take and add the following: [ForeignKey("phone_type")] directly above this line: public virtual phone_types phone_types { get; set; } in my phone_manager class.

Solution 1

After doing quite a bit more research, it seems like I had a fairly unique issue. I attempted several of the fixes listed both on here and many other sites, but almost nothing seemed to fix the issue.

However, the solution I listed at the bottom of my original post seems to be working, and holding up well, so I believe it to be a fairly adequate solution to my problem.

To somewhat outline what was occurring, MVC EF was attempting to find a fk/pk relationship across two models, but since the column names across the models were different, it wasn't able to map them properly. If I were to trying to get all the emails from email_manager by using the email_types table, it wasn't an issue, but moving backwards, and grabbing the information from email_types from email_manager threw errors.

Since the column names between the two tables are different, EF tried to create a column to house the relationship, but since no such column existed, an error was thrown. To correct this, all that's necessary is to tell EF what the foreign key column actually is, and that is done by using [ForeignKey("email_type")] above the collection that houses the parent model.

So for example, my new email_types and email_manager models were as follows:

    [Table("employee.email_manager")]
    public partial class email_manager
    {
        [Key]
        public int email_id { get; set; }

        public int employee_id { get; set; }

        [Required]
        [StringLength(255)]
        public string email { get; set; }

        public int email_type { get; set; }

        [Column(TypeName = "date")]
        public DateTime date_added { get; set; }

        public bool deleted { get; set; }
        [ForeignKey("email_type")]
        public virtual email_types email_types { get; set; }

        public virtual employees1 employees1 { get; set; }
    }

    [Table("employee.email_types")]
    public partial class email_types
    {
        public email_types()
        {
            email_manager = new HashSet<email_manager>();
        }

        [Key]
        public int email_type_id { get; set; }

        [Required]
        [StringLength(50)]
        public string email_type_name { get; set; }

        public virtual ICollection<email_manager> email_manager { get; set; }
    }

Solution 2

Your issue is that your connection string in data layer and connection string in web layer are pointing to different databases.

e.g. data layer reading dev database webapp pointing to test database.

Either update connection strings to point to the same database.

or

Make sure your both database have same tables and columns.

Solution 3

I had the similar issue. What happens is that in the database foreign keys are created and it starts mapping both the models and then throws an exception. Best way is to avoid foreign key creation by using [NotMapped] as you could use complex models and also avoid creation of Foreign Key.

Comments

[Nick Gallimore]

It's nice to be reminded to look at your connection string. Double check this.

[John Pankowicz]

I spent a day trying everything, till I found your answer. That was my problem! I wished I'd seen this first.