EF CodeFirst: Either the parameter @objname is amb

问题:

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'

回答1:

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.

回答2:

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.

回答3:

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.

回答4:

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.

回答5:

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.

回答6:

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

回答7:

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