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

invalid column name entity framework database first
system.data.sqlclient.sqlexception invalid column name entity framework
invalid column name in asp net
mvc invalid column name
invalid column name id entity framework
invalid column name 'id
invalid column name c#
invalid column name 'user_id

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

When i pass model to view i'm getting this error - Exception Details: System.Data.SqlClient.SqlException: Invalid column name '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"
                )
            )

In your ProfileVersion and ServerInfo entities you have an Environment navigation 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.

If I had to imagine what happened here, I'd say you first created the classes with EnvironmentId properties, migrated, then later decided to add the navigation properties, Environment to each, expecting EF to associate that with your existing EnvironmentId properties. 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 EnvironmentId instead, you just need to tell it so with the ForeignKey data annotation:

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

[SOLVED] - Entity Framework, [SOLVED] - Entity Framework - Invalid Column Name 'User_ID" I have a project written with C# on the top of ASP.NET MVC 5 framework. if I want to access the MainClassRoom navigation property I get the following error. Browse other questions tagged c# asp.net asp.net-mvc entity-framework or ask your own question. The Overflow Blog Podcast 235: An emotional week, and the way forward

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

Invalid column name in entity framework, So, I removed the code from those columns and updated my DB. But, when I try to authenticate a user, I get this error: Invalid column name  Can you make sure that your Portfolios table in the database has "LastName" and "PurchaseDate" columns. If you are using Entity Framework and the columns are not in DB, try running the "update-database" command one more time.

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.

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:

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 int field called LASTACTION in my main table. In that model I had declared the ForeignKey relationship:

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:

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

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

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:

[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 ProfileVersion class, in the question, above, assuming that EnvironmentId is the Primary Key in a table called Environment (that model is not shown above).

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_actions in the MY_ACTIONS model and the this.o_actions = new HashSet<MY_ACTIONS>(); line and it all went through fine.

There are many such lists and ICollections in 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.

[Solved] Getting invalid column name issue with entity framework , The error is pretty explicit: Invalid column name 'GcdId' Compare your production database against your development DB - at a guess you have  [SOLVED] - Getting Invalid column name 'EmploymentTypeEntityEmploymentTypeID in Entity framework core

SqlException: Invalid column name, Generally speaking, that error occurs when your entities are out of sync with your database. Based on the current state of your entity classes,  Entity Framework Identity invalid column name “*_ID” when try to FindByName 0 Errors Occur when switching from Development to Staging Environment - ASP.Net Core MVC

Invalid Column Name · Issue #9866 · dotnet/efcore · GitHub, Executing an operation to get and "Tasks" entity from DB throws an "System.Data. Exception message: "Invalid column name 'BedroomsIdBedroom'". Stack trace: at Execute[TResult](Expression expression) at System.Linq.Queryable. First error I hit was the entityTypes don't have PK configured. InnerException = {"Invalid column name 'Audit_ID'."} the Audit_ID is the old PK. I have tried this "Invalid column name" when trying to insert data into database using SQL. this Invalid column name when trying to add an entity to a database using DbContext. this Invalid column name after mapping

SqlException: Invalid column name '…' | ASP.NET Core, To solve this error, you can use the “Entity Framework Code First Migrations” approach. Step 1 : In the PMC ( Package Manager Console ), enter  The Column attribute can be applied to one or more properties in an entity class to configure the corresponding column name, data type and order in a database table. The Column attribute overrides the default convention. As per the default conventions in EF 6 and EF Core, it creates a column in a db table with the same name and order as the

Comments
  • Does either ProfileVersion or ServerInfo have a Environment property?
  • Yes -they public int EnvironmentId { get; set; } @Olav Nybø
  • Could you update your post and add the ProfileVersion and ServerInfo classes?
  • Updated!..@ Olav Nybø
  • The problem is most likely in the Environment class. Can you show this class also (it's not in your code snippet above)?
  • I added foreign key data notation for EnvironmentId property in ProfileVersion and ServeInfo but still having the same issue ..@Chris Pratt
  • Is the EnvironmentId column in your table set as a foreign key at the database level?