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

asp.net asp.net-mvc c# entity-framework sql

Question

I'm attempting to gather data from a few of my models that are connected to my main employee model using foreign keys. I can access each model normally if I map them out separately, however doing so requires me to navigate to several different web pages.

In order to work with them, I'm attempting to combine multiple of my models into essentially one controller. Unfortunately, I encounter an odd problem when attempting to access these models:

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

After looking through my code, it appears that the only placephone_types_phone_type_id is in my migration code appears. Any assistance would be greatly appreciated as I am very new to C# and Asp.Net in general.

The code for my model is as follows:

[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 relevant code, in my opinion:

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

EDIT I believe I've solved the problem, but if there is another solution, I would much want to hear it. My response was to add the following:[ForeignKey("phone_type")] Above this line, directly:public virtual phone_types phone_types { get; set; } in myphone_manager class.

1
16
4/28/2014 8:36:28 PM

Accepted Answer

More investigation revealed that I may have had a somewhat unusual problem. I tried a number of the remedies suggested here and on numerous other websites, but very little appeared to make the problem go away.

However, the remedy I provided at the bottom of my initial article appears to be effective and is still standing, therefore I think it is a workable solution to my issue.

To give you a rough idea of what was going on, MVC EF was trying to find a fk/pk link between two models, but it was unsuccessful since the column names in the two models weren't the same. If I attempted to obtain every email fromemail_manager utilizing theemail_types table, it wasn't a problem, but going back and obtaining the data fromemail_types from email_manager threw mistakes.

Since the two tables' column names varied, EF attempted to build a new column to hold the relationship, but since no such column already existed, an error was raised. This may be fixed by simply informing EF what the foreign key column is, which is done by using[ForeignKey("email_type")] above the collection where the parent model is housed.

As an illustration, my newemail_types and email_manager These were the models:

    [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; }
    }
10
4/29/2014 2:52:46 PM

Popular Answer

Your problem is that the databases that your connection strings for the data layer and web layer point to are different.

For instance, a data layer reading a web application linking to a test database.

Update connection strings to use the same database, if necessary.

or

Ensure that the tables and columns in both databases match.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow