locked
I need help with this error "Unable to generate an explicit migration because the following explicit migrations are pending" RRS feed

  • Question

  • User1451609391 posted

    Hello. I am working on an mvc5 project. I have successfully been working with migrations and everything has been smooth sailing until today I tried to add a new migration and I got this error

    Unable to generate an explicit migration because the following explicit migrations are pending: [202003142233556_Birthdate, 202003150228059_JSonString, 202004010153168_UserPostsJsonString]. Apply the pending explicit migrations before attempting to generate a new explicit migration

    I tried to Update-Database a few different ways and then i got this error

    Column names in each table must be unique. Column name 'Account_Id' in table 'dbo.AspNetUsers' is specified more than once. This is not true either so i suspect that this may be stopping the update from going through. Very annoying.

    I have looked all over the internet and cannot find a solution for this. Please help thanks.

    Monday, June 1, 2020 1:44 PM

All replies

  • User475983607 posted

    The error is telling you that you created a migration at one point but did not update the database.  Simply open the migration folder an take a look at the migrations files.  I assume the last migration file is the problem but you'll need to make sure.  If the problem is the last migration and the update-database has not been successful then simply delete the most recent migration file.  If you need to go back further then issue a update-database command and target the last know good migration.   

    https://entityframework.net/knowledge-base/41451316/how-the-right-way-to-rollback-migration-with-entity-framework

    PM> Update-Database -TargetMigration:"NameOfTheLastMigration"

    EF Core

    https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/?tabs=dotnet-core-cli#remove-a-migration

    Monday, June 1, 2020 2:20 PM
  • User1451609391 posted

    Hey thanks I suspected that an update was needed but once i run that command or just update database i get this error

    Column names in each table must be unique. Column name 'Account_Id' in table 'dbo.AspNetUsers' is specified more than once.

    I don't know why it would do that because Account-Id is something i never fool around with it was part of the website account since i first made my website. How do i fix that error without deleting it or causing more problems. thanks

    Monday, June 1, 2020 2:33 PM
  • User475983607 posted

    johnboyman12

    I don't know why it would do that because Account-Id is something i never fool around with it was part of the website account since i first made my website. How do i fix that error without deleting it or causing more problems. thanks

    If you do not want to follow my advice then it is up to you to come up with a solution.  Learning migrations will pull back the curtain on migration files

    Try doing a "Find" for Account_Id in the entire solution.   Maybe you added it twice and you can remove it. 

    I'm not sure what version of EF you are using but if you have a migration file, you'll need to remove the file otherwise it will keep causing problems.  I remove the last migration file all the time.  I'll review the file and find I missed something.  So I delete the file and create another.

    Monday, June 1, 2020 2:44 PM
  • User1451609391 posted

    I did follow your advice thanks


    This is my problem.

    I cannot run this code

    Update-Database -TargetMigration:"NameOfTheLastMigration"

    Because of this error Column names in each table must be unique. Column name 'Account_Id' in table 'dbo.AspNetUsers' is specified more than once.

    I have searched everywhere and there is only one copy of account_id. That's really annoying.

    Monday, June 1, 2020 2:52 PM
  • User475983607 posted

    A mistake was made along the way.  I'm not sure how far back the mistake goes.   You are the only one that know what migration have been applied and what code changes have been made. 

    It seems odd that you cannot find "Account_Id" in the source code.  You should certainly find the "Account_Id" in the migration files where the error is generated.

    Monday, June 1, 2020 3:18 PM
  • User1451609391 posted

    Account_Id is not a migration its a field that was in dbo.AspNetUsers since I first made my website. "Account_Id is not in any source code. When i do a database update from within SQL Server Object Viewer it works fine. The problem is when i try to do any updates to the database from the package manager console. The only source that shows this field is in SQL Sever Object Viewer. It looks like this.

    CREATE TABLE [dbo].[AspNetUsers] (
        [Id]                   NVARCHAR (128) NOT NULL,
        [Birthdate]            NVARCHAR (MAX) NULL,
        [Email]                NVARCHAR (256) NULL,
        [EmailConfirmed]       BIT            NOT NULL,
        [PasswordHash]         NVARCHAR (MAX) NULL,
        [SecurityStamp]        NVARCHAR (MAX) NULL,
        [PhoneNumber]          NVARCHAR (MAX) NULL,
        [PhoneNumberConfirmed] BIT            NOT NULL,
        [TwoFactorEnabled]     BIT            NOT NULL,
        [LockoutEndDateUtc]    DATETIME       NULL,
        [LockoutEnabled]       BIT            NOT NULL,
        [AccessFailedCount]    INT            NOT NULL,
        [UserName]             NVARCHAR (256) NOT NULL,
        [JSonString]           NVARCHAR (MAX) NULL,
        [UserPostsJsonString]  NVARCHAR (MAX) NULL,
        [AlertList]            NCHAR (1000)   NULL,
        [Account_Id]           INT            NULL,
        [ActivityList]         NVARCHAR (MAX) NULL,
        CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
    );
    
    
    GO
    CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
        ON [dbo].[AspNetUsers]([UserName] ASC);
    
    
    GO
    CREATE NONCLUSTERED INDEX [IX_Account_Id]
        ON [dbo].[AspNetUsers]([Account_Id] ASC);
    
    

    Monday, June 1, 2020 3:22 PM
  • User475983607 posted

    Account_Id is not a migration its a field that was in dbo.AspNetUsers since I first made my website. "Account_Id is not in any source code. When i do a database update from within SQL Server Object Viewer it works fine. The problem is when i try to do any updates to the database from the package manager console. The only source that shows this field is in SQL Sever Object Viewer. It looks like this.

    You misunderstand code first migrations.  If this is an EF6 code first project then you should have a migration files.  One of the files tries to add an "Account_Id" column to the dbo.AspNetUsers table which causes the error message.  If you do a simple "Find" (ctrl-shift-F) in Visual Studio you should be able to locate all the files that have the string "Account_Id".  One of these will be the migration file that is causing the issue.

    By chance, did you update the table schema without going through code first?

    Monday, June 1, 2020 3:41 PM
  • User1451609391 posted

    Hello. I found a problem after using ctrl-shift-F I did not find a file for Account_Id. But i found account_Id in another two migration files and i have no idea how it got there. This is what the Birthdate Migration file looks like.

     public partial class Birthdate : DbMigration
        {
            public override void Up()
            {
                CreateTable(
                    "dbo.Accounts",
                    c => new
                        {
                            Id = c.Int(nullable: false, identity: true),
                            Name = c.String(),
                        })
                    .PrimaryKey(t => t.Id);
                
                AddColumn("dbo.AspNetUsers", "Account_Id", c => c.Int());
                CreateIndex("dbo.AspNetUsers", "Account_Id");
                AddForeignKey("dbo.AspNetUsers", "Account_Id", "dbo.Accounts", "Id");
            }
            
            public override void Down()
            {
                DropForeignKey("dbo.AspNetUsers", "Account_Id", "dbo.Accounts");
                DropIndex("dbo.AspNetUsers", new[] { "Account_Id" });
                DropColumn("dbo.AspNetUsers", "Account_Id");
                DropTable("dbo.Accounts");
            }
        }

    and

    namespace NewsSystems1.Migrations
    {
        using System;
        using System.Data.Entity.Migrations;
        
        public partial class JSonString : DbMigration
        {
            public override void Up()
            {
                DropForeignKey("dbo.AspNetUsers", "Account_Id", "dbo.Accounts");
                DropIndex("dbo.AspNetUsers", new[] { "Account_Id" });
                AddColumn("dbo.AspNetUsers", "JSonString", c => c.String());
                DropColumn("dbo.AspNetUsers", "Account_Id");
                DropTable("dbo.Accounts");
            }
            
            public override void Down()
            {
                CreateTable(
                    "dbo.Accounts",
                    c => new
                        {
                            Id = c.Int(nullable: false, identity: true),
                            Name = c.String(),
                        })
                    .PrimaryKey(t => t.Id);
                
                AddColumn("dbo.AspNetUsers", "Account_Id", c => c.Int());
                DropColumn("dbo.AspNetUsers", "JSonString");
                CreateIndex("dbo.AspNetUsers", "Account_Id");
                AddForeignKey("dbo.AspNetUsers", "Account_Id", "dbo.Accounts", "Id");
            }
        }
    }
    

    That looks weird I have no idea how that happened. I guess I should delete both migrations and then add them again and update database.

    Monday, June 1, 2020 4:00 PM
  • User475983607 posted

    Only migration files that have NOT been applied using update-database can be deleted.  I think you can delete the Birthdate migration, not sure.  To undo migration, go back to my first post.  I'm guessing you made other changes around Account_Id that you'll need to revert before adding another migration.

    Monday, June 1, 2020 4:11 PM
  • User1686398519 posted

    Hi,  johnboyman12

    • If you just added some fields or tables, you only need to execute "Update-Database -Verbose".
    • If you delete some fields or tables, then you need to execute the following instructions.
      • Enable-Migrations -EnableAutomaticMigrations -Force
      • Add-Migration Initial
        • When you have generated a file like "202005291034185_Initial" before, you need to write the command "Add-Migration Initial2" like this, and a file like "202005290916016_Initial2" will be generated.
      • Update-Database -Verbose.
    • The last method, you can delete your migrations folder and re-migration.

    Note: When there are multiple projects in your solution, you need to ensure that your default project is what you need to use when you execute migrations.

    Best Regards,

    YihuiSun

    Tuesday, June 2, 2020 1:49 AM