none
EF 4.3.1 Migrations Seeding not working as expected RRS feed

  • Question

  • I am using EF 4.3.1 migrations and I have the Configuration class in which I have the following code:

        internal sealed class Configuration : DbMigrationsConfiguration<DbContext>
        {
            public Configuration()
            {
                AutomaticMigrationsEnabled = false;
            }
        
            protected override void Seed(PayByPhoneDbContext context)
            {
                context.Roles.AddOrUpdate(r => r.Name, new Role { Name = "A" }, new Role { Name = "B" });
                context.Administrators.AddOrUpdate(a => a.Email, new Administrator { Email = "a@a.com" Name = "A", Role = context.Roles.Local.SingleOrDefault(role => role.Name == "A") });
            }
        }

    Now when I run the migrate command (part of MSBuild script) when the DB does not exist, tables are created and the seeding takes place as expected. But when I run the migrate command on an existing database without any migrations I get an error on running the migrate command:

        No pending explicit migrations.
        Running Seed method.
        System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Administrators_Roles_RoleId". The conflict occurred in database "xxxDB", table "dbo.Roles", column 'Id'.
        The statement has been terminated.
           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
           at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
           at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
           at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
           at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
           at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
           at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
           --- End of inner exception stack trace ---
           at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
           at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
           at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
           at System.Data.Entity.Internal.InternalContext.SaveChanges()
           --- End of inner exception stack trace ---
           at System.Data.Entity.Internal.InternalContext.SaveChanges()
           at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
           at System.Data.Entity.DbContext.SaveChanges()
           at System.Data.Entity.Migrations.DbMigrator.SeedDatabase()
           at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.SeedDatabase()
           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.Update(String targetMigration)
           at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
           at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
           at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()

    Running SQL Server profiler I found the error occurred when executing:

        exec sp_executesql N'update [dbo].[Administrators]
        set [RoleId] = @0
        where ([Id] = @1)
        ',N'@0 int,@1 bigint',@0=0,@1=1

    What is the right way to seed data with foreign keys in the Seed method?
    Monday, May 7, 2012 2:58 PM

Answers

  • Hi Achinth,

    Welcome to MSDN Forum.

    The code seems no problem. I have tried to repro the issue, but every thing works well. Below is the steps how I repro the issue, if anywhere different with yours, please feel free to correct me.

    1. Write the models as below

    class Team
        {
            public int TeamId { get; set; }
            public string TeamName { get; set; }
            public List<Player> PlayerList { get; set; }
        }
    
        class Player
        {
            public int PlayerId { get; set; }
            public string PlayerName { get; set; }
            public int TeamId { get; set; }
            public Team Team { get; set; }
        }
    
        class MyContext : DbContext
        {
            public DbSet<Team> teamSet { get; set; }
            public DbSet<Player> playerSet { get; set; }
        }

    2. Write the code to generate database.

    class Program
        {
            static void Main(string[] args)
            {
                using (MyContext context = new MyContext())
                {
                    context.Database.Create();
                }
            }
        }

    3. Input "Enable-Migrations" in Package Manager Console. Then, a Migrations folder appears in the project.

    4. Open the configuration file in the Migrations folder and write the code below to the Seed method.

    protected override void Seed(ConsoleApplication2.MyContext context)
            {
                //  This method will be called after migrating to the latest version.
    
                //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
                //  to avoid creating duplicate seed data. E.g.
                //
                //    context.People.AddOrUpdate(
                //      p => p.FullName,
                //      new Person { FullName = "Andrew Peters" },
                //      new Person { FullName = "Brice Lambson" },
                //      new Person { FullName = "Rowan Miller" }
                //    );
                //
                context.teamSet.AddOrUpdate(x => x.TeamName, new Team { TeamName = "team1" });
                context.playerSet.AddOrUpdate(x => x.PlayerName, new Player { PlayerName = "player1", Team = context.teamSet.Local.SingleOrDefault(t => t.TeamName == "team1") });
            }

    5. Input "Update-Database" in Package Manager Console. Then, the records have inserted into the database.

    The database is exist, the code is similar with yours, but no exception is thrown out. To confirm it, I modified the "team1" as "team2" and then update database again, a new record has inserted into Team table, and the Player table has no changes. Still no exception  is thrown.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Tuesday, May 8, 2012 6:09 AM
    Moderator

All replies

  • Hi Achinth,

    Welcome to MSDN Forum.

    The code seems no problem. I have tried to repro the issue, but every thing works well. Below is the steps how I repro the issue, if anywhere different with yours, please feel free to correct me.

    1. Write the models as below

    class Team
        {
            public int TeamId { get; set; }
            public string TeamName { get; set; }
            public List<Player> PlayerList { get; set; }
        }
    
        class Player
        {
            public int PlayerId { get; set; }
            public string PlayerName { get; set; }
            public int TeamId { get; set; }
            public Team Team { get; set; }
        }
    
        class MyContext : DbContext
        {
            public DbSet<Team> teamSet { get; set; }
            public DbSet<Player> playerSet { get; set; }
        }

    2. Write the code to generate database.

    class Program
        {
            static void Main(string[] args)
            {
                using (MyContext context = new MyContext())
                {
                    context.Database.Create();
                }
            }
        }

    3. Input "Enable-Migrations" in Package Manager Console. Then, a Migrations folder appears in the project.

    4. Open the configuration file in the Migrations folder and write the code below to the Seed method.

    protected override void Seed(ConsoleApplication2.MyContext context)
            {
                //  This method will be called after migrating to the latest version.
    
                //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
                //  to avoid creating duplicate seed data. E.g.
                //
                //    context.People.AddOrUpdate(
                //      p => p.FullName,
                //      new Person { FullName = "Andrew Peters" },
                //      new Person { FullName = "Brice Lambson" },
                //      new Person { FullName = "Rowan Miller" }
                //    );
                //
                context.teamSet.AddOrUpdate(x => x.TeamName, new Team { TeamName = "team1" });
                context.playerSet.AddOrUpdate(x => x.PlayerName, new Player { PlayerName = "player1", Team = context.teamSet.Local.SingleOrDefault(t => t.TeamName == "team1") });
            }

    5. Input "Update-Database" in Package Manager Console. Then, the records have inserted into the database.

    The database is exist, the code is similar with yours, but no exception is thrown out. To confirm it, I modified the "team1" as "team2" and then update database again, a new record has inserted into Team table, and the Player table has no changes. Still no exception  is thrown.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Tuesday, May 8, 2012 6:09 AM
    Moderator
  • Hi Achinth,

    I'm writing to check the status of the issue on your side. Would you mind letting me know whether the issue has solved? If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 14, 2012 2:38 AM
    Moderator
  • If you run Update-Database a second time with team1, you will have the error described.

    I just tried with your example (team / player) and it's throwing on the second Update-Database, trying to set the Player.TeamId = 0 (which breaks the relationship).

    Monday, July 16, 2012 7:18 PM
  • Hi Fred.B,

    What's the meaning of "run update-database with team1"? If you update the database again, the exception message should be conflicted with fk, because Achinth use the AddOrUpdate method. The records has already in the database, so the update will be execute, not insert command. But the pk of the team is referenced by 'Player', so the update will stop. We can use Add method instead of AddOrUpdate to avoid this.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, July 17, 2012 5:32 AM
    Moderator
  • Exactly. So the use of AddOrUpdate is totally useless for objects with FK.  You will always end up with a database exception because AddOrUpdate do NOT update FK ids if you use an object (eg.: player.team).

    The seed method is ran each time you call Update-Database, even if no changes were applied (not that it matters) thus making your code break the 2nd time you call it.

    This is problematic because it's really not user friendly for developers that constantly run update-database during development.

    IMO, the AddOrUpdate method is very counter-intuitive and error prone. Best to avoid it =\

    Tuesday, July 17, 2012 9:34 PM