SQL Server Entity Framework error - Either the parameter @objname is ambiguous or the claimed @objtype (Index) is wrong

amrnrhlw 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(91)

I am trying to delete few existing columns of 2 tables using code first approach, when I am running the command

update-database -Verbose

in the Nuget package manager console I get the exception shown.

EXECUTE sp_rename @objname = N'euts.PRODUCT.IX_ID_PRODUCT_PARENT', 
                  @newname = N'IX_PRODUCT_PARENT_ID_PRODUCT', 
                  @objtype = N'INDEX'

System.Data.SqlClient.SqlException: Either the parameter @objname is ambiguous or the claimed @objtype (Index) is wrong

I have a table called PRODUCT and that table has a columns ID_PRODUCT , ID_PRODUCT_PARENT . The ID_PRODUCT_PARENT has foreign key relationship with ID_PRODUCT column of the same PRODUCT table.

I am trying to delete ID_PRODUCT_PARENT column from the PRODUCT table.

I also modified the below line of code in the DbContext class by removing the

HasForeignKey(x => ID_PRODUCT_PARENT)

modelBuilder.Entity<PRODUCT>()
       .HasOptional(e => e.PRODUCT_PARENT)
       .WithMany(e => e.PRODUCT_CHILDREN)
       .HasForeignKey(x => ID_PRODUCT_PARENT)

Going by the SQL command which EF has generated its trying to rename INDEX, when I checked the SQL Server database, there is no INDEX on PRODUCT table with name IX_ID_PRODUCT_PARENT , not sure why Entity Framework generated a SQL query that is trying to rename Index when I am trying to drop columns.

I am expecting to drop columns and also the foreign key constraint of ID_PRODUCT_PARENT on PRODUCT Table.

Any suggestions on how to resolve this exception ?

Note - the Entity Framework generated SQL commands have code to drop the columns I wanted but the 2nd line of the code is

EXECUTE sp_rename @objname = N'euts.PRODUCT.IX_ID_PRODUCT_PARENT', 
                  @newname = N'IX_PRODUCT_PARENT_ID_PRODUCT', 
                  @objtype = N'INDEX'

which is causing the exception, after this line it has the SQL code to drop the columns

sql-server

来源：https://stackoverflow.com/questions/77210260/entity-framework-error-either-the-parameter-objname-is-ambiguous-or-the-claim

1条答案

按热度按时间

w6mmgewl1#

EF Code First works fine if nobody modifies the database structure from outside EF.

Mixing Code First with manually modifying the database is not a good idea, because conflicts as the one your having may arise.

The reason why EF is trying to drop an index is because the column that your trying to delete has an index (in EF previous database model). So it will first delete the index and the drop the column.

The reason why are you having this disconnect between EF model and the SQL model is because either the database was not created with EF Code First or someo manually dropped the index in the database.

I see two solutions:

Regenerate the EF model using the scaffold command. This will Regenerate all your c# classes based on the database definition. It can be quite difficult to then adapt the code to make it work again.
Manually add the index to the database so the migration can run successfully. The biggest risk here is that you don't know what else might be different between EF model and SQL model, so you may encounter additional issues down the road.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Entity Framework error - Either the parameter @objname is ambiguous or the claimed @objtype (Index) is wrong

1条答案

相关问题

热门标签

最新问答