Invalid Column name when using savechanges() in en

问题:

So here is the deal, I have changed my database schema, and changed the PK for one of my tables and I have removed everything related to the old PK (FK reference in another tables).

However I have this exception when I insert a new entity using savechanges() method

ex = {"An error occurred while updating the entries. See the inner exception for details."}

And the inner exception is

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

and nothing solved my issue, so as I deleted my whole edmx and created a new one also it didn't work.

ps: I am using database first approach

回答1:

Non of the solutions worked because the problem was actually in the SQL server database.

I ran SQL Profiler and executed the program insert statement, I found out that the error is in one of the table's triggers which has the Previous column name, changed that to the new PK and it finally worked.

So if anyone have a similar problem, the above links in the post might help or you should check if the problem was actually happening the Database server.

回答2:

Remove/Delete the table from the ef model(ensure that it is removed by looking at the model browser view).

Rebuild the solution.

Make sure your table has been saved. Check your connection strings as well that you are referencing the correct db.

Add the table again via the update model tool.

EDIT: read the question better

回答3:

Create your own ModelKeyBuilder class.

class MyOwnModelKeyBuilder
{

    public static void BuildCompositeKeys(DbModelBuilder modelBuilder)
    {
       modelBuilder.Entity<YourEntity>()
       .Ignore(x=>x.Audit_ID)
       .HasKey(x=>x.NewPrimaryKey);
    }
}

And in your Model.Context.cs

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     MyOwnModelKeyBuilder.BuildCompositeKeys(modelBuilder); 
}

It may not be a straight solution because I don't know the tables structure but it may help you.

回答4:

In may case, we found defining the database elemenents in the context fixed the issue.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("database_schema");
            modelBuilder.Entity<EntityNameInModel>().ToTable("table_in_database").HasKey(ats => ats.id);
            modelBuilder.Entity<EntityNameInModel>().Property(ats => ats.user_name).HasColumnName("user_name");
}

You will need to define every schema, table, and field in this way... but you'll have absolute control in your code after. Good luck!