There is already an object named in the database

Question

Update-Database failed from Package Manager Console. I've used Entity Framework 6.x and code-first approach. Error is

"There is already an object named 'AboutUs' in the database."

How can I solve this problem?

internal sealed class Configuration 
    : DbMigrationsConfiguration<Jahan.Blog.Web.Mvc.Models.JahanBlogDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = false;
    }

    protected override void Seed(Jahan.Blog.Web.Mvc.Models.JahanBlogDbContext context)
    {

    }
}

My DbContext is:

public class JahanBlogDbContext : IdentityDbContext<User, Role, int, UserLogin, UserRole, UserClaim>
{
    public JahanBlogDbContext()
        : base("name=JahanBlogDbConnectionString")
    {
        Database.SetInitializer(new DropCreateDatabaseIfModelChanges<JahanBlogDbContext>());
    }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        modelBuilder.Entity<Comment>().HasRequired(t => t.Article).WithMany(t => t.Comments).HasForeignKey(d => d.ArticleId).WillCascadeOnDelete(true);
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>().ToTable("User");
        modelBuilder.Entity<Role>().ToTable("Role");
        modelBuilder.Entity<UserRole>().ToTable("UserRole");
        modelBuilder.Entity<UserLogin>().ToTable("UserLogin");
        modelBuilder.Entity<UserClaim>().ToTable("UserClaim");
    }

    public virtual DbSet<Article> Articles { get; set; }
    public virtual DbSet<ArticleLike> ArticleLikes { get; set; }
    public virtual DbSet<ArticleTag> ArticleTags { get; set; }
    public virtual DbSet<AttachmentFile> AttachmentFiles { get; set; }
    public virtual DbSet<Comment> Comments { get; set; }
    public virtual DbSet<CommentLike> CommentLikes { get; set; }
    public virtual DbSet<CommentReply> CommentReplies { get; set; }
    public virtual DbSet<ContactUs> ContactUs { get; set; }
    public virtual DbSet<Project> Projects { get; set; }
    public virtual DbSet<ProjectState> ProjectStates { get; set; }
    public virtual DbSet<ProjectTag> ProjectTags { get; set; }
    public virtual DbSet<Rating> Ratings { get; set; }
    public virtual DbSet<Tag> Tags { get; set; }
    public virtual DbSet<AboutUs> AboutUs { get; set; }
}

Package Manage Console:

PM> update-database -verbose -force
Using StartUp project 'Jahan.Blog.Web.Mvc'.
Using NuGet project 'Jahan.Blog.Web.Mvc'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'Jahan-Blog' (DataSource: (local), Provider: System.Data.SqlClient, Origin: Configuration).
No pending explicit migrations.
Applying automatic migration: 201410101740197_AutomaticMigration.
CREATE TABLE [dbo].[AboutUs] (
    [Id] [int] NOT NULL IDENTITY,
    [Description] [nvarchar](max),
    [IsActive] [bit] NOT NULL,
    [CreatedDate] [datetime],
    [ModifiedDate] [datetime],
    CONSTRAINT [PK_dbo.AboutUs] PRIMARY KEY ([Id])
)
System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'AboutUs' in the database.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.<ExecuteStatements>b__2e()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)
   at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, VersionedModel sourceModel, VersionedModel targetModel, Boolean downgrading)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, VersionedModel sourceModel, VersionedModel targetModel, Boolean downgrading)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.<Update>b__b()
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run()
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
   at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0()
   at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
ClientConnectionId:88b66414-8776-45cd-a211-e81b2711c94b
There is already an object named 'AboutUs' in the database.
PM> 

Solution 1

it seems there is a problem in migration process, run add-migration command in "Package Manager Console":

Add-Migration Initial -IgnoreChanges

do some changes, and then update database from "Initial" file:

Update-Database -verbose

Edit: -IgnoreChanges is in EF6 but not in EF Core, here's a workaround: https://stackoverflow.com/a/43687656/495455

Solution 2

Maybe you have changed the namespace in your project!
There is a table in your data base called dbo.__MigrationHistory. The table has a column called ContextKey.
The value of this column is based on your namespace. for example is "DataAccess.Migrations.Configuration".
When you change the namespace, it causes duplicate table names with different namespaces.
So, after you change namespace in code side, change the namespace in this table in database, too, (for all rows).
For example, if you change the namespace to EFDataAccess, then you should change the values of ContextKey column in dbo.__MigrationHistory to "EFDataAccess.Migrations.Configuration".
Then in code side, in Tools => Package Manager Console, use the update-database command.

Another option instead of changing the context value in the database is to hard code the context value in your code to the old namespace value. This is possible by inheriting DbMigrationsConfiguration<YourDbContext> and in the constructor just assign the old context value to ContextKey, than inherit from MigrateDatabaseToLatestVersion<YourDbContext, YourDbMigrationConfiguration> and leave that class empty. The last thing to do is call Database.SetInitializer(new YourDbInitializer()); in your DbContext in a static constructor.

I hope your problem will be fixed.

Solution 3

"There is already an object named 'AboutUs' in the database."

This exception tells you that somebody has added an object named 'AboutUs' to the database already.

AutomaticMigrationsEnabled = true; can lead to it since data base versions are not controlled by you in this case. In order to avoid unpredictable migrations and make sure that every developer on the team works with the same data base structure I suggest you set AutomaticMigrationsEnabled = false;.

Automatic migrations and Coded migrations can live alongside if you are very careful and the only one developer on a project.

There is a quote from Automatic Code First Migrations post on Data Developer Center:

Automatic Migrations allows you to use Code First Migrations without having a code file in your project for each change you make. Not all changes can be applied automatically - for example column renames require the use of a code-based migration.

Recommendation for Team Environments

You can intersperse automatic and code-based migrations but this is not recommended in team development scenarios. If you are part of a team of developers that use source control you should either use purely automatic migrations or purely code-based migrations. Given the limitations of automatic migrations we recommend using code-based migrations in team environments.

Solution 4

In my case, my EFMigrationsHistory table was emptied (somehow) and when trying to run update-database I would get:

There is already an object named 'AspNetUsers' in the database

After seeing the table had been emptied it made sense that it was trying to rerun the initial migration and trying to recreate the tables.

To fix this problem I added rows into my EFMigrationsHistory table. 1 row for each migration that I knew the database was up to date with.

A row will have 2 columns: MigrationId and ProductVersion

MigrationId is the name of your migration file. Example: 20170628112345_Initial

ProductVersion is the ef version you're running. You can find this by typing Get-Package into the Package Manager Console and looking for your ef package.

Hope this is helpful for someone.

Solution 5

In my case I had re-named the assembly that contained the code-first entity framework model. Although the actual schema hadn't changed at all the migrations table called

dbo.__MigrationHistory

contains a list of already performed migrations based on the old assembly name. I updated the old name in the migrations table to match the new and the migration then worked again.

View more solutions

Comments

[Martin Johansson]

This answer led me to my mistake, I simply had the wrong project as Startup project.

[Olivier ROMAND]

Awesome, we had exactly this issue !

[Mehdiway]

If you do this you'll lose all your data

[Travis Tubbs]

What does this exactly do? Does this allow the new model to just overwrite the old?

[yigitt]

Actually this is the real cause of this error. EF is trying to create database as It cannot read which migrations that are applied to database becuse of nameSpace difference

[arame3333]

I started using manual migrations because I use Views as well as Tables in my database. I made the mistake of trying to use automatic migrations and as a result it was trying to create a table from a view. In this scenario your solution does not work, I should instead always use manual migrations. So after doing this I had to undo the changes in source control and remove the "Initial" entry from the _Migrations table.

[Enrique A. Pinelo Novelo]

Thanks this answer, helped me a lot, as Olivier ROMAND said, I had exactly this issue!

[Niklas]

i had no idea it was related to this, but somehow even removing the records of MigrationHistory table did not fix it for me... so i dropped all my tables and let EF create all of em all over again, small app, no biggie...but it fixed it for me.

[East of Nowhere]

This just leads me in an infinite loop: Package Manager Console won't let me do Add-Migration because it gives an error "Unable to generate an explicit migration because the following explicit migrations are pending..." namely the InitialCreate. But if I can't successfully run that Update-Database UNTIL there is also some Initial -IgnoreChanges, then what am I supposed to do??

[Nick]

Had to use Add-Migration Initial -IgnoreChanges -Force

[Tzvi Gregory Kaidanov]

Add-Migration : A parameter cannot be found that matches parameter name 'IgnoreChanges'.

[JordanTDN]

I was having a similar problem and this fixed it for me.

[H35am]

This is the correct answer with details, also good to mention that sometimes misspelling folder name will cause this problem.

[gneric]

I get this message: Unable to generate an explicit migration because the following explicit migrations are pending: ...

[Maverick Meerkat]

@TravisTubbs this ignores the changes you made and "fakes" that your model is synced with the db, with regards to the migration table. You still need to manually sync the two; In my case I removed the changes made to the model, did a Add-Migration, removed the content from the up/down methods before doing update-database - this returned me to the state before the breaking migration. Then I actually re-added the changes, did add-migration, and update-database as usual - this time everything synced

[Kiechlus]

Check next answer, it may also be because you have EnsureCreated() in the Context Constructor: github.com/aspnet/EntityFrameworkCore/issues/4649

[emirhosseini]

There's no such table. There's a table named '__EFMigrationsHistory' (using ef core) and it doesn't have that column.

[BrainSlugs83]

Nope. Not even relevant. namespaces haven't changed, and the context key is set manually in the initializer to a static string that has never once changed. -- Data models haven't changed, nothing has changed.

[AxleWack]

This did it for me! Thanks a million, I just learnt something now as well :)

[Mike Poole]

Looks like a good answer but you might want to check your spelling. You can also use code snippets to make it clear the last line is code. Message me if you want help with this.

[arfa]

Thanks How can i massage you

[Mike Poole]

Well done for adding the code snippet @arfa. No need for a massage :) . If you would like to message me just type @ followed by my username in the comments section.

[Ciaran Gallagher]

How did you populate the Model column?

[Jens Mander]

This worked for me. It was the other way around, though. My production db had all records in __EFMigrationHistory, while those in dev db where somehow missing (except for the initial one).

[kosist]

I had similar situation after namespace changes. Tried this fix, but it didn't work still. So I've modified manually namespaces in database table, and then it started to work.

[rakuens]

really do not recommend this, deleting productin migrations will just destroy your environemnts. why would you have to delete your migrations if they worked so far? and you connection strings wont work for every user here, shouldnt run them like that

[user2964808]

Helped for me. using .Net 5.0 and EF Core. In my case the __EFMigrationsHistory holds the filename (not context key) that was used to create the migration. The name of the file has been changed and when Update-Database was run the EF see the migration file was not run and it tried to run and failed as the Table already exists. So, I am able to resolve by updating the migration file name in __EFMigrationsHistory table. The table schema is MigrationId and ProductVersion. I have to update the MigrationId with the correct migration file to resolve the issue. Hope this helps someone! Thanks

[Avjol Sakaj]

This also happen after renaming migration, in that case just change MigrationId in dbo.__MigrationHistory to be same as migration name in code