System.Data.SqlClient.SqlException：无效的列名称phone_types_phone_type_id“ [英] System.Data.SqlClient.SqlException: Invalid column name 'phone_types_phone_type_id'

问题描述

我想从我的一些模式，有我的主要员工模型外键关系的信息。如果我每个模型单独绘制出，我可以访问他们像正常的，没有任何问题，但我要拜访多个不同的网页，这样做。

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：无效的列名称phone_types_phone_type_id

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

通过我的code搜索后，显然的唯一位置 phone_types_phone_type_id 出现在我的移民code。我在C＃Asp.Net一般的令人难以置信的新的，因此任何帮助是AP preciated。

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.

下面是code代表我的模型：

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; }
    }
}

从我的观点相关code：

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 />
        }

修改我可能已经发现了问题，但我肯定会采取更多的输入，如果有另一种选择。我的解决办法是采取并添加以下内容： [ForeignKey的（PHONE_TYPE）] 直接在此线之上： public虚拟phone_types phone_types {搞定;组; } 在我的 phone_manager 类。

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.

推荐答案

做相当多一点研究，就好像我有一个相当独特的问题。我尝试了好放在这里都列出的修复和许多其他网站，但几乎没有什么似乎解决这​​个问题。

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.

要稍微勾勒出了什么发生，MVC EF试图找到跨越两个车型FK / PK关系，但因为整个模型中的列名不同，它不能正确映射。如果我尝试使用 email_types 表来获取所有从 email_manager 的电子邮件，这不是一个问题，但向后移动，并从 email_manager 抓住从 email_types 信息扔错误。

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.

由于这两个表之间的列名不同，EF尝试创建一个列安置的关系，但因为没有这样的列存在，被抛出一个错误。为了解决这个问题，所有的必要的是要告诉EF什么外键列实际上是，这是通过使用 [ForeignKey的（EMAIL_TYPE）] 集合上面做到了房屋的父模型。

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.

因此​​，例如，我的新 email_types 和 email_manager 模型如下：

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; }
    }

这篇关于System.Data.SqlClient.SqlException：无效的列名称phone_types_phone_type_id“的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！