CODEWITHSUNDEEP
c#entity-frameworkef-code-firstentity-framework-migrations
Saber Amani
Saber Amani

Reputation: 6489

EF CodeFirst: Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong

I've a table named EducationTypes and an Entity named EducationType, I renamed one of entity properties, now I'm frequently getting Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong. How can I solve this issue?

The generated SQL Script:

EXECUTE sp_rename @objname = N'dbo.EducationTypes.nvarchar', @newname = N'EducationTypeTitle', @objtype = N'COLUMN'

Upvotes: 72

Views: 101052

Answers (12)

Harry .Naeem
Harry .Naeem

Reputation: 1331

I got this error when I was updating my database using code first. I created a table with a foreign key and somebody else renamed that foreign key. Now when I tried to update database, it threw this exception.

I performed these steps to overcome this issue:

  • Had to delete that table from my database
  • Tracked which migration added that table and deleted it from the migrations history in SQL server
  • In the end ran update-database again and my database updated successfully.

Upvotes: 0

marcolauro23
marcolauro23

Reputation: 31

I just had this error and solved this modifying the migration partial class, the migration tried to rename an index and the index did not exist, I just change the migration code like this:

  1. Delete de rename on index method
  2. Set the create index in the Up void
  3. Set the drop index in the Down void

this solved my problem.

Upvotes: 0

Niklas
Niklas

Reputation: 1000

this happened to me because automatic migrations were set to true and one of the programmers who is new added migrations to the project so on updating the database it would get confused. solved it by removing the existing migration from the project and counting on automatic updates again.

Upvotes: -1

Asma Liaquat
Asma Liaquat

Reputation: 1

I resolved this error by deleting all the old migrations from My database Migration history table in SQL server and then adding a new one but only for the desired changes and then updated database.It worked fine.

Upvotes: -1

Sapan Ghafuri
Sapan Ghafuri

Reputation: 555

To me it happened when:

  • Added a new migration (migratoin1)
  • Updated on the local database
  • Then deleted the same migration (migratoin1)
  • Then added with the same name (migratoin1) another migration
  • Then applied to the local database and published.

Deleting the migration file (migratoin1) solved my problem.

Upvotes: 0

ShhTot
ShhTot

Reputation: 77

Steer clear of reserved words or class names in your migration title.

This happened to me when I named a migration "Init" - renamed to "InitialCreate" and all worked perfectly

Upvotes: 3

Br2
Br2

Reputation: 167

I just had the same issue, also after refactoring. For me, the problem was caused by a migration that was refactored as well.

The result was that another migration could not be executed because that migration was looking for a table by searching it's old name.

Reverting the changes in the migration solved this issue.

Upvotes: 2

radu florescu
radu florescu

Reputation: 4363

Actually this error also happens when you just deleted the database, and your context does not realize that your database is not there.

I recreated the database, and now the error was resolved.

P.S. make sure you check database is still there when you try to run the update-database

Upvotes: 0

garryp
garryp

Reputation: 5776

I got this with Entity Framework 6 when trying to rename a foreign key in my migrations script using the Sql(" ... ") method. The workaround I had was to use square brackets around the name:

i.e. changing this:

sp_rename 'FK_dbo.tablename_dbo.othertablename_fieldname', 'FK_dbo.tablename_dbo.othertablenewname_fieldnewname', 'object'

...to this:

sp_rename '[FK_dbo.tablename_dbo.othertablename_fieldname]', 'FK_dbo.tablename_dbo.othertablenewname_fieldnewname', 'object'

SQL Server is then able to find the foreign key.

Upvotes: 4

Amirhossein Mehrvarzi
Amirhossein Mehrvarzi

Reputation: 18954

This is because of name Conflict of Class (Model) names with other reserved or generated ones, when auto creates the tables and ... .

Considering that EF Code First creates the intervene tables to relate 2 or more tables using name of tables for derived intervene table, so when you use a class name that employs a name like the intervene tables, we'll get such this ambiguous error.

For example if you have a Question class which has an Answer navigation property the internal model metadata will contain a reference called QUESTION_ANSWER

To solve this, try to change the class names (used for generating tables) and ensure their uniqueness.

Upvotes: 14

RizJa
RizJa

Reputation: 2041

If you're using Code First and have (an) existing Migration script(s) and are trying to overwrite a change (i.e. renaming a column) that has since been deleted, then you'll get that error output. Simplest way is to delete the migration script, Add-Migration via NuGet, and then update the database.

Upvotes: 43

MDWeb
MDWeb

Reputation: 41

Just spent far too much time trying to figure out why this was happening on a production database I can only access via mylittlesql. Couldn't reproduce the problem but made this script from bits of sp_rename so when it does happen next time I can find out exactly why. Yes is overkill, but might help somebody else.

There is an issue if you ever somehow manage to get '[' or ']' into the actual column name as stored in sys.columns, (? 'nvarchar' as your column name ???? ). PARSENAME doesn't cope with []'s and returns null, so sp_rename won't work.

This will only help diagnose the issue for the 'column' case with the error code 15248 which is where I keep having this issue:

declare @objname nvarchar(1035) = N'dbo.EducationTypes.nvarchar' -- input to sp_rename
declare @newname sysname = N'EducationTypeTitle' -- input to sp_rename

declare @UnqualOldName  sysname,
@QualName1      sysname,
@QualName2      sysname,
@QualName3      sysname,
@OwnAndObjName  nvarchar(517),  
@SchemaAndTypeName  nvarchar(517),  
@objid          int,
@xtype          nchar(2),
@colid          int,
@retcode        int

select @UnqualOldName = parsename(@objname, 1),
        @QualName1 = parsename(@objname, 2),
        @QualName2 = parsename(@objname, 3),
        @QualName3 = parsename(@objname, 4)
print 'Old Object Name = ''' + convert(varchar,isnull(@UnqualOldName ,'')) + ''''
-- checks that parsename is getting the right name out of your @objname parameter
print 'Table name:'
if @QualName2 is not null
begin
print QuoteName(@QualName2) +'.'+ QuoteName(@QualName1)
select @objid = object_id(QuoteName(@QualName2) +'.'+ QuoteName(@QualName1))
end
else
begin
print QuoteName(@QualName1)
select @objid = object_id(QuoteName(@QualName1))
end
-- check if table is found ok
print 'Table Object ID = ''' + convert(varchar,isnull(@objid ,-1)) + ''''
select @xtype = type from sys.objects where object_id = @objid
print '@xtype = ''' + convert(varchar,isnull(@xtype,'')) + ''' (U or V?)'
if (@xtype in ('U','V'))
begin
print 'select @colid = column_id from sys.columns where object_id = ' + 
    convert(varchar,isnull(@objid,0)) + ' and name = ''' +
        @UnqualOldName + ''''

    select * from sys.columns where object_id = @objid -- and name = @UnqualOldName
    select @colid = column_id from sys.columns 
    where object_id = @objid and name = @UnqualOldName
    print 'Column ID = ''' + convert(varchar,isnull(@colid,-1)) + ''''
end

This will output some helpful messages in the Messages tab (of SSMS or whatever you are using) and the table fields in the Results tab.

Good luck.

Upvotes: 2

Related Questions