There is already an object named in the database

100

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> 
database
entity-framework
ef-code-first
entity-framework-6
ef-migrations
asked on Stack Overflow Oct 10, 2014 by Jahan

18 Answers

120

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

answered on Stack Overflow Oct 10, 2014 by Sina Amirshekari • edited Jul 23, 2018 by Jeremy Thompson
68

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.

answered on Stack Overflow Jan 24, 2016 by Elnaz • edited Feb 23, 2017 by Adam Tal
17

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

answered on Stack Overflow Oct 12, 2014 by Ilya Palkin
8

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.

answered on Stack Overflow Aug 7, 2017 by Kolby • edited Aug 7, 2017 by Kolby
6

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.

answered on Stack Overflow Nov 9, 2015 by The Senator
5

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/

answered on Stack Overflow Mar 10, 2015 by Ryan Layton
3

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.

answered on Stack Overflow Aug 11, 2016 by Youngjae • edited Nov 11, 2019 by Youngjae
2

I had same problem and after three hour struggling I find out what's going on

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

To solve it, just delete those code and write want you want. For example, I wanted to add a column so i just write

migrationBuilder.AddColumn<string>(
            name: "fieldName",
            table: "tableName",
            nullable: true);
answered on Stack Overflow Jul 5, 2019 by arfa • edited Jul 5, 2019 by adiga
1

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

answered on Stack Overflow Aug 26, 2015 by Ali Adravi
1

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.
answered on Stack Overflow Jun 18, 2018 by Chekusty • edited Jun 18, 2018 by Chekusty
1

Another edge-case EF Core scenario.

Check you have a Migrations/YOURNAMEContextModelSnapshot.cs file.

as detailed in - https://docs.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.

answered on Stack Overflow Oct 25, 2018 by WickedW
0

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

answered on Stack Overflow Apr 11, 2018 by Rasto
0

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.

answered on Stack Overflow Oct 24, 2018 by FrankyHollywood
0

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.

answered on Stack Overflow Apr 10, 2019 by JohnFI
0

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

answered on Stack Overflow Sep 15, 2019 by Mai Nguyen • edited Sep 15, 2019 by Mai Nguyen
0

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

answered on Stack Overflow Oct 6, 2019 by Chameleon
0

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)

answered on Stack Overflow Oct 9, 2019 by Alok Singh
-5

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"

answered on Stack Overflow Jan 13, 2016 by Mohammad Al-Hasan • edited Jan 13, 2016 by Mohammad Al-Hasan

User contributions licensed under CC BY-SA 3.0