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> 

Answer 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

Answer 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.

Answer 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.

Answer 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.

Answer 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.

Answer 6

Make sure your solutions startup project has the correct connectionstring in the config file. Or set the -StartUpProjectName parameter when executing the update-database command. The -StartUpProjectName parameter specifies the configuration file to use for named connection strings. If omitted, the specified project’s configuration file is used.

Here is a link for ef-migration command references http://coding.abel.nu/2012/03/ef-migrations-command-reference/

Answer 7

I had the same problem and after three hours of struggling I found out what was going on

In my case, when I wanted to migrate for the first time, in the up() method, the default code wants to create the tables that already existed so I got the same error as you

To solve it, just deleted those codes and write what I wanted. For example, I wanted to add a column so i just wrote

migrationBuilder.AddColumn<string>(
            name: "fieldName",
            table: "tableName",
            nullable: true);

Answer 8

Delete rows from dbo_MigrationHistory table or delete the table and run

update-database -verbose

It will run all the migrations in your project one by one

Answer 9

Note: not recommended solution. but quick fix in some cases.

For me, dbo._MigrationHistory in production database missed migration records during publish process, but development database had all migration records.

If you are sure that production db has same-and-newest schema compared to dev db, copying all migration records to production db could resolve the issue.

You can do with VisualStudio solely.

1. Open 'SQL Server Object Explorer' panel > right-click dbo._MigrationHistory table in source(in my case dev db) database > Click "Data Comparison..." menu.
2. Then, Data Comparison wizard poped up, select target database(in my case production db) and click Next.
3. A few seconds later, it will show some records only in source database. just click 'Update Target' button.
4. In browser, hit refresh button and see the error message gone.

Note that, again, it is not recommended in complex and serious project. Use this only you have problem during ASP.Net or EntityFramework learning.

Answer 10

Same case (no DB and MigrationHistory table on Server). My steps:

1. I deleted Migration data from Up and Down section of my first migration.
2. Update Database with empty migration (MigrationHistory table was created)
3. Add your REAL migration and update database with it.

Answer 11

In my case, the issue was in Seeder. I was calling _ctx.Database.EnsureCreated() inside of it and as far as I understood, the update database command has successfully executed, but then seeder tried to create database "second" time.

How to address:

1. Do nut run update, just start application and call EnsureCreated(). Database will be created/updated
2. Comment out or remove seeder.

Answer 12

Another edge-case EF Core scenario.

Check you have a Migrations/YOURNAMEContextModelSnapshot.cs file.

as detailed in - https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/#create-a-migration

If you have tried to manually re-create your database by deleting migration.cs files, be careful that your Migrations/*ContextModelSnapshot.cs file still exists.

Without it, your subsequent migrations have no snapshot on which to create the required differences and your new migrations files will look like they are re-creating everything again from scratch, you will then get the existing table error as above.

Answer 13

same happened with me.. Problem was that Actually i deleted my Database table MoviesCast and made new table and problem was that my last migration was trying to induce the deleted table MoviesCast in the database. I Solved it by simply removing all the content of last migration and simply ran Up() & down() method

public override void Up()
{
}

public override void Down()
{
}

then updated the database and simply add new migration

Answer 14

You have deleted migration folder than you are trying to run "update-database" command on Package manager console ? if so

Just manually delete all you tables Than run if update-databse(cons seed data will be deleted)

Answer 15

I had the same issue described in the answer that Elnaz gave. I had a requirement to change the namespace of the datalayer during a refactoring of our project. This caused the migrations to not see the existing migrations in the database. I found an excellent answer to this issue that James Chambers blogged.

http://jameschambers.com/2014/02/changing-the-namespace-with-entity-framework-6-0-code-first-databases/

I simply changed the following in the Migration configuration file.

public Configuration()
{
    AutomaticMigrationsEnabled = false;
    this.ContextKey = “Old_Namespace.Migrations.Configuration”;
}

Hope this helps someone else in a bind.

Answer 16

Simply execute command update-migration -Script. This generate new *.sql script which include all DB changes included in migration. In the end of code are insert commands something like this: INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion]) you can simply run this all INSERT and DB will be synchronized

Answer 17

After more than an hour of not getting any results I tried another approach, not using migrations but I did a schema compare.

In Visual Studio -> Tools -> SQL Server -> New Schema Comparison

First I created a new completely new database with EF migrations. Than I did a compare, comparing the new database with the one I wanted to update. Finally generated a migration script, and I could perform a schema update.

Answer 18

In my case (want to reset and get a fresh database),

First I has got the error message : There is already an object named 'TABLENAME' in the database.

and I saw, a little bit before:

"Applying migration '20111111111111_InitialCreate'.
Failed executing DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE MYFIRSTTABLENAME"

My database was created, but no record in migrations history.

I drop all tables except dbo.__MigrationsHistory

MigrationsHistory was empty.

Run dotnet ef database update -c StudyContext --verbose

(--verbose just for fun)

and got Done.

Answer 19

I faced the same bug as below. Then I fixed it as below:

1. Check current databases in your project:
   • dotnet ef migrations list
2. If the newest is what you've added, then remove it:
   • dotnet ef migrations remove
3. Guarantee outputs of this database must be deteled in source code: .cs/.Designer.cs files

4.Now it is fine. Try to re-add: dotnet ef migrations add [new_dbo_name]

5.Finally, try to update again, in arrangement base on migration list:

• dotnet ef database update [First]
• dotnet ef database update [Second]
• ...
• dotnet ef database update [new_dbo_name]

Hope it is helpful for you. ^^

Answer 20

Another way to do that is comment everything in Initial Class,between Up and Down Methods.Then run update-database, after running seed method was successful so run update-database again.It maybe helpful to some friends.

Answer 21

I was facing the same issue. I tried below solution : 1. deleted create table code from Up() and related code from Down() method 2. Run update-database command in Package Manager Consol

this solved my problem

Answer 22

Note: I did it because I don't have anything in my database. In my case: 1. I removed a migration by command remove-migration in Package Manager Console 2. Removed database by 'SQL Server Object Explorer' panel > on current database > right-click > Remove 3. Migrated in Package Manager Console write Add-Migration and click Enter 4. The last update by command update-database

Answer 23

In the database, query __MigrationHistory table and copy [ContextKey].

Paste it into the DbMigrationsConfiguration ConextKey as below

internal sealed class DbConfiguration: DbMigrationsConfiguration<DbContext>
    {
        public DbConfiguration()
        {
            AutomaticMigrationsEnabled = true;
            ContextKey = "<contextKey from above>";
        }

Answer 24

Faced a similar issue found that connection being used was from a publish profile even though I had changed the connection in appsettings.json. This resulted the migration trying to create a table that already existed. Amending the connection in Connected Services of the project sorted it out for me.

Answer 25

When you run update-database command it will check __EFMigrationsHistory table records in database with the EfMigration folder items in your project, So if you have a migration item in your project folder that is not exist in database ef core begin to apply those migration in database, eventually you may face with such error. So compare EfMigration folder items with with __EFMigrationsHistory table in database and make sure both of them are sync and same together, If there is any item in your project that is not in db add it's record in database to keeping sync both together.(actually it may causes becuase you probably truncate your __EFMigrationHistory table in db)

Answer 26

My issue was a bit of a one-off/odd case maybe but FWIW I was getting this error as well and the issue came from my AppDbContext.cs having an OnConfiguring() method that was pointing to a different database (which already had the object in it). Using -verbose helped me find this.

I removed the OnConfiguring method but then my Update-Database threw a different error. Eventually I realized I had to keep the OnConfiguring method in my AppDbContext.cs file and include my actual database connection string. This is of course redundant and I realize user/pass info in your code like this is old/not very secure but once I made these changes my Update-Database finally worked w/no errors. My related code below. If anyone has suggestions on how to resolve this in a cleaner way please do chime in.

// my appdbcontext.css file 
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {
        optionsBuilder.UseSqlServer("Server=myserver;Database=mydatabase;MultipleActiveResultSets=true;User ID=myuser;Password=mypass");                
    }
}

// my program.cs
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));  

Answer 27

I got the same problem when I wanted to add a new table to an existing database. My case: First I have create a database (DBShop) by first code approach and I create one table inside it (T_User). Then after I have change my model and I added two more models (Production and Shop), for creating tables inside database for these new two models, I received an error that there is a table inside database T_User. To solve this problem, after I run Add-migration CreateDB -force, before I update database, I referred to CreateDB.cs file in immigration folder and I deleted the CreateTable method relate to T_User table then I run update-database -verbose.

Answer 28

The below steps worked for me for the same issue:

Scenario:

I was trying to add 2 new fields to my existing model for Email functionality. The new fields are "IsEmailVerified" and "ActivationCode"

Steps i have followed:

1.Deleted old migration files under "Migrations" folder which are prevented me to do Update-Database 2.Reverted all my recent changes that i have did on the model

3.Run the below command:

Add-Migration -ConnectionProviderName System.Data.SqlClient -ConnectionString "Data Source=DESKTOP\SQLEXPRESS;Initial Catalog=Custom;Persist Security Info=True;User ID=sa;password=****"

4.Deleted the contents from Up() and Down() methods from migration file and left the methods empty

5.Run the below command:

Update-Database -ConnectionProviderName System.Data.SqlClient -ConnectionString "Data Source=DESKTOP\SQLEXPRESS;Initial Catalog=Custom;Persist Security Info=True;User ID=sa;password="***

6. After executes the above step, model and DB looks sync.

7. Now, i added the new properties in the model

        public bool IsEmailVerified { get; set; }
        public Guid ActivationCode { get; set; }
   

8. Run the below command:

Add-Migration -ConnectionProviderName System.Data.SqlClient -ConnectionString "Data Source=DESKTOP\SQLEXPRESS;Initial Catalog=Custom;Persist Security Info=True;User ID=sa;password="***

9. Now the migration file contains only my recent changes as below:

      public override void Up()
        {
            AddColumn("dbo.UserAccounts", "IsEmailVerified", c => c.Boolean(nullable: false));
            AddColumn("dbo.UserAccounts", "ActivationCode", c => c.Guid(nullable: false));
        }        
        public override void Down()
        {
            DropColumn("dbo.UserAccounts", "ActivationCode");
            DropColumn("dbo.UserAccounts", "IsEmailVerified");
        }
   

10. Run the below command: Update-Database -ConnectionProviderName System.Data.SqlClient -ConnectionString "Data Source=DESKTOP\SQLEXPRESS;Initial Catalog=Custom;Persist Security Info=True;User ID=sa;password="***

11.Now i successfully updated the database with additional columns.

The below is updated table after the recent changes:

Table after the update migration

Answer 29

Maybe you have changed the namespace Configuration.cs There is a table in your data base called dbo.__MigrationHistory. The table has a column called ContextKey.

new namespace  AppData.Migrations { internal sealed class Configuration }
sql command
 update   [__MigrationHistory] set [ContextKey]= 'AppData.Migrations.Configuration'

Answer 30

You can also go to edit your last migration file("201410101740197_AutomaticMigration.cs) to empty it code inside Up(), Down() functions.

public override void Up()
{
}
public override void Down()
{
}

There are some issues that can happen if your DB model (AboutUs model) code is different from DB table (more fields...), then you should remove it from the model code, or you can manually update Up/Down function in the immigration file but keep the difference.

public override void Up()
{  
  // Keep difference from model with DB table here
}        
public override void Down()
{   
  // Keep difference from model with DB table here        
}

Answer 31

In migration file check the public override void Up() method. May be you are trying to create a new db object which is already in database. So, you need to drop this object/table before creation of the db object. Just do like bellow-

DropTable("dbo.ABC"); 
CreateTable(
            "dbo.ABC",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    ..
                 }

And now run your migration Update-Database -TargetMigration: "2016_YourMigration"