C# ASP.NET MVC /Entity Framework 错误 - 无效的列名“Environment_Id”

ASP.NET MVC /Entity Framework Error - Invalid column name 'Environment_Id'

提问by user2696668

I'm new to ASP.NET MVC and EF hopefully this is not a silly question

我是 ASP.NET MVC 和 EF 的新手，希望这不是一个愚蠢的问题

When i pass model to view i'm getting this error - Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'Environment_Id'.

当我通过模型查看时，我收到此错误 -异常详细信息：System.Data.SqlClient.SqlException：无效的列名“Environment_Id”。

Model nor database table has a property by that name. Could any guide me on this?.

模型或数据库表具有该名称的属性。任何人都可以指导我吗？

  **Here is the Version Model Class**

  public partial class Version
  {
    public Version()
    {
        this.ProfileVersions = new List<ProfileVersion>();
        this.ServerInfoes = new List<ServerInfo>();
    }

    public int Id { get; set; }
    public string Number { get; set; }
    public string Tag { get; set; }
    public string Owner { get; set; }
    public string Approver { get; set; }
    public string Description { get; set; }
    public virtual ICollection<ProfileVersion> ProfileVersions { get; set; }
    public virtual ICollection<ServerInfo> ServerInfoes { get; set; }
}

**Profile Version Class**

public partial class ProfileVersion
{
    public ProfileVersion()
    {
        this.PlatformConfigurations = new List<PlatformConfiguration>();
    }

    public int Id { get; set; }
    public int ProfileId { get; set; }
    public int EnvironmentId { get; set; }
    public int VersionId { get; set; }
    public Nullable<bool> Locked { get; set; }
    public string LockedBy { get; set; }
    public string Comments { get; set; }
    public Nullable<int> Active { get; set; }
    public virtual Environment Environment { get; set; }
    public virtual ICollection<PlatformConfiguration> PlatformConfigurations { get;  
                                                                             set; }
    public virtual PlatformProfile PlatformProfile { get; set; }
    public virtual Version Version { get; set; }
}

**ServerInfo** 
public partial class ServerInfo
{
    public ServerInfo()
    {
        this.PlatformConfigurations = new List<PlatformConfiguration>();
    }

    public int Id { get; set; }
    public string ServerName { get; set; }
    public int ProfileId { get; set; }
    public int VersionId { get; set; }
    public int EnvironmentId { get; set; }
    public string ServerType { get; set; }
    public Nullable<short> Active { get; set; }
    public string Domain { get; set; }
    public string Location { get; set; }
    public string IP { get; set; }
    public string Subnet { get; set; }
    public string Gateway { get; set; }
    public Nullable<int> VLan { get; set; }
    public string DNS { get; set; }
    public string OS { get; set; }
    public string OSVersion { get; set; }
    public string Func { get; set; }
    public Nullable<short> IISInstalled { get; set; }
    public string ADDomainController { get; set; }
    public string ADOrganizationalUnit { get; set; }
    public string ADGroups { get; set; }
    public string LastError { get; set; }
    public Nullable<System.DateTime> LastUpdate { get; set; }
    public virtual Environment Environment { get; set; }
    public virtual ICollection<PlatformConfiguration> PlatformConfigurations { get;      
                                                                             set; }
    public virtual PlatformProfile PlatformProfile { get; set; }
    public virtual Version Version { get; set; }
    public virtual VMConfiguration VMConfiguration { get; set; }
}

 **Controller Code-**

 public ViewResult Index(string id )
    {

        var profileVerList = from ver in _context.Versions
                                where !(from pfv in _context.ProfileVersions
                                    select pfv.VersionId).Contains(ver.Id)
                                select ver;

        var bigView = new BigViewModel
        {
            VersionModel = profileVerList.ToList(),                
        };

        return View(model: bigView);
    }


**In the View where the exception is thrown**

 @Html.DropDownList(
            "SelectedVersionID", 
            new SelectList(
                Model.VersionModel.Select(x => new { Value = x.Id, Text = x.Number}),
                "Value",
                "Text"
                )
            )

采纳答案by Chris Pratt

In your ProfileVersionand ServerInfoentities you have an Environmentnavigation property. By default, Entity Framework will try to create a database column called [Property Name]_[Referenced class PK]. In your scenario, that's Environment_Id. The problem, right now, is that you have not done a migration to have this database column created.

在您的ProfileVersion和ServerInfo实体中，您有一个Environment导航属性。默认情况下，实体框架将尝试创建一个名为[Property Name]_[Referenced class PK]. 在你的场景中，那是Environment_Id. 现在的问题是您还没有进行迁移以创建此数据库列。

If I had to imagine what happened here, I'd say you first created the classes with EnvironmentIdproperties, migrated, then later decided to add the navigation properties, Environmentto each, expecting EF to associate that with your existing EnvironmentIdproperties. That's where you went wrong. As I said above, EF convention is to look for a database column named Environment_Id, so if you want EF to use EnvironmentIdinstead, you just need to tell it so with the ForeignKeydata annotation:

如果我不得不想象这里发生了什么，我会说您首先创建了具有EnvironmentId属性的类，迁移，然后决定将导航属性添加Environment到每个类，期望 EF 将其与您现有的EnvironmentId属性相关联。那就是你出错的地方。正如我上面所说，EF 约定是查找名为 的数据库列Environment_Id，因此如果您希望 EF 使用EnvironmentId，您只需要使用ForeignKey数据注释告诉它：

[ForeignKey("Environment")]
public int EnvironmentId { get; set; }

回答by ujjaval

In My Case I have added My Primary Key Relationship to Same Key .. SO I have simply remove..

在我的情况下，我已将我的主键关系添加到同一键.. 所以我只是删除..

回答by vapcguy

I realize this question is 3 years old now, but I saw a different reason for the error - both in the original question and in my own code that was pretty similar. And, in my case, I had the same error as stated above.

我意识到这个问题现在已经 3 岁了，但我看到了错误的不同原因 - 在原始问题和我自己的代码中都非常相似。而且，就我而言，我遇到了与上述相同的错误。

I had a "MY_ACTIONS" table with an ID and Name pair that I wanted to be added to a dropdown. Here's the model:

我有一个“MY_ACTIONS”表，其中包含一个 ID 和名称对，我想将其添加到下拉列表中。这是模型：

namespace TestSite.Models
{
    public class MY_ACTIONS
    {
        //[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public MY_ACTIONS()
        {
            this.o_actions = new HashSet<MY_ACTIONS>();
        }

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

        [StringLength(100)]
        public string action_name { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<MY_ACTIONS> o_actions { get; set; }
    }
}

And to get an action to display on the dropdown it had an ID set in an intfield called LASTACTIONin my main table. In that model I had declared the ForeignKey relationship:

为了在下拉菜单上显示一个动作，它在我的主表中int调用的字段中设置了一个 ID LASTACTION。在那个模型中，我已经声明了 ForeignKey 关系：

namespace TestSite.Models
{    

    [Table("MAIN_TABLE")]
    public partial class MAIN_TABLE
    {
        [Key]
        public int MAIN_TABLE_ID { get; set; }

        public int LASTACTION { get; set; } // this would carry a number matching action_id

        [ForeignKey("LASTACTION")]
        public virtual MY_ACTIONS MY_ACTIONS { get; set; }
    }
}

I had the error Invalid column name 'MY_ACTIONS_action_id'when loading this dropdown in my view:

Invalid column name 'MY_ACTIONS_action_id'在我的视图中加载此下拉列表时出现错误：

@Html.DropDownList("lastaction", null, htmlAttributes: new { @class = "form-control" })

...for which I was using this ViewBag in my Controller function:

...为此我在我的控制器函数中使用了这个 ViewBag：

Model1 db = new Model1(); // database context

MAIN_TABLE o_main = new MAIN_TABLE();
o_main.lastaction = 2;

ViewBag.lastaction = new SelectList(db.MY_ACTIONS, "action_id", "action_name", o_main.lastaction);

If I did not have my FK relationship declared:

如果我没有声明我的 FK 关系：

[ForeignKey("LASTACTION")]
public virtual MY_ACTIONS MY_ACTIONS { get; set; }

I probably also would've had the same issue. Having the representation of a virtual instance requires linking it with some physical property. This is similar to how this:

我可能也会遇到同样的问题。拥有虚拟实例的表示需要将其与某些物理属性相关联。这类似于：

public virtual Environment Environment { get; set; }

Should be:

应该：

[ForeignKey("EnvironmentId")]
public virtual Environment Environment { get; set; }

in the ProfileVersionclass, in the question, above, assuming that EnvironmentIdis the Primary Key in a table called Environment(that model is not shown above).

在ProfileVersion课堂上，在上面的问题中，假设这EnvironmentId是一个名为的表中的主键Environment（该模型未在上面显示）。

For me, though, I already had that and I was still getting the error, so doing that still might not solve everything.

但是，对我来说，我已经有了它，但仍然出现错误，所以这样做仍然可能无法解决所有问题。

Turns out all I had to do was get rid of that ICollection<MY_ACTIONS> o_actionsin the MY_ACTIONSmodel and the this.o_actions = new HashSet<MY_ACTIONS>();line and it all went through fine.

事实证明，我所要做的就是ICollection<MY_ACTIONS> o_actions在MY_ACTIONS模型和this.o_actions = new HashSet<MY_ACTIONS>();生产线中去掉它，一切顺利。

There are many such lists and ICollectionsin play in the question above, so I would wager something is wrong with having them, as well. Start with just a plain model that represents the fields, then add in your virtual objects that represent tables linked to with foreign keys. Then you make sure your dropdown loads. Only after that should you start adding in your ICollections, HashSets, Lists<T>and other such amenities that are not actually physically part of the database - this can throw off Entity Framework into thinking it needs to do something with them that it doesn't need to do.

有很多这样的列表并且ICollections在上面的问题中起作用，所以我敢打赌，拥有它们也有问题。从一个代表字段的普通模型开始，然后添加代表与外键链接的表的虚拟对象。然后你确保你的下拉列表加载。只有在那之后，您才应该开始添加您的ICollections、HashSets、Lists<T>和其他实际上并不是数据库物理部分的其他此类便利设施 - 这会使实体框架认为它需要对它们做一些它不需要做的事情。