How avoid adding duplicates to database managed by EntityFramework caused by Seed method?

Question

Every time I run the application same objects are added to the database(duplicates).

My Global.asax:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;
using WebApplication2.Migrations;
using WebApplication2.Models;


namespace WebApplication2 {
    public class MvcApplication : System.Web.HttpApplication {
        protected void Application_Start() {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<ApplicationDbContext, Configuration>()); 
            //Database.SetInitializer(new DropCreateDatabaseAlways<ApplicationDbContext>());
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
        }
    }
}

and My Configuration.cs with Seed method:

namespace WebApplication2.Migrations
{
    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;
    using WebApplication2.Models;

    internal sealed class Configuration : DbMigrationsConfiguration<WebApplication2.Models.ApplicationDbContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            ContextKey = "WebApplication2.Models.ApplicationDbContext";
        }

        protected override void Seed(WebApplication2.Models.ApplicationDbContext context) {
            var persons = new List<Person> { 
         new Person{FirstName = "John", LastName = "Doe", CellNumber = "123-456-789", SecondaryPhoneNumber = "98873213", Address = "1street 2",BirthDate = DateTime.Now.Date, Pesel = "312312312", Notes = "Annoying"},
         new Person{FirstName = "Anna", LastName = "Doe", CellNumber = "113-456-789", SecondaryPhoneNumber = "98873213", Address = "1street 2",BirthDate = DateTime.Now.Date, Pesel = "548555672", Notes = "Less Annoying"}
        };

        persons.ForEach(person => context.Persons.AddOrUpdate(person));
        context.SaveChanges();

        var meetings = new List<Meeting>{
            new Meeting{PersonId = 1, Body = "Body of meeting", Date = DateTime.Now}
        };

        meetings.ForEach(meeting => context.Meetings.AddOrUpdate(meeting));
        context.SaveChanges();

        var statuses = new List<Status> {
            new Status{Name = "OK"},
            new Status {Name = "NOT_OK"}
        };

        statuses.ForEach(status => context.Statuses.AddOrUpdate(status));
        context.SaveChanges();

        }
    }
}

Every time I run the app Seed adds duplicate records:

I needed to comment contents of Seed method to prevent adding duplicates.

Question: (1) What should I change so Seed method will be run only to recreate database after migration?

EDIT:

In the Seed method there is comment:

  //  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" }
            //    );
            //

but my method is called ALWAYS, not only after migrations. Why is it so?

Royster

Answer 1

From this page (about halfway down), which was sourced from this answer

Note: Adding code to the Seed method is one of many ways that you can insert fixed data into the database. An alternative is to add code to the Up and Down methods of each migration class. The Up and Down methods contain code that implements database changes. You'll see examples of them in the Deploying a Database Update tutorial.

You can also write code that executes SQL statements by using the Sql method. For example, if you were adding a Budget column to the Department table and wanted to initialize all department budgets to $1,000.00 as part of a migration, you could add the folllowing line of code to the Up method for that migration:

Sql("UPDATE Department SET Budget = 1000");

You might also look into using the AddOrUpdate method, as referenced in this answer, which should also work for your purposes.

I quickly changed the code I obtained from the answer linked above, so bear with me if there's an issue with the code below. The concept should still be relatively clear, I believe.

context.People.AddOrUpdate(c => c.PK, new Person() { PK = 0, FirstName = "John", ... })
context.People.AddOrUpdate(c => c.PK, new Person() { PK = 1, FirstName = "Anna", ... })

Zenger

I used AddOrUpdate right now and it still added duplicates. – Royster

I just would like to make it run seed method after migration to recreate database form scratch. – Royster

This question appears to offer a better explanation of using the AddOrUpdate method when you don't know the primary key of the data. – Zenger

Why do you define Id(PK) explicitly? Shouldn't Entity Framework do it? – Royster

You have to somehow inform entity framework that you don't want these duplicates. The simplest way to do that is to provide the key. In a previous comment I included a link that should allow you to tell entity framework to look for existing records using arbitrary fields, but I felt that including that within my answer would add some unnecessary complexity. Perhaps I was wrong in making that decision. But you're right, entity framework will assign the primary keys on its own (so long as you don't), which is the more correct approach. – Zenger

Answer 2

You have full access to the context in the Seed method, so you can query to see if data already exists.

For example, you can seed the tables only if they're empty...

protected override void Seed(WebApplication2.Models.ApplicationDbContext context) {

    if (!context.Persons.Any())
    {   
        var persons = new List<Person> { 
            new Person{FirstName = "John", LastName = "Doe", CellNumber = "123-456-789", SecondaryPhoneNumber = "98873213", Address = "1street 2",BirthDate = DateTime.Now.Date, Pesel = "312312312", Notes = "Annoying"},
            new Person{FirstName = "Anna", LastName = "Doe", CellNumber = "113-456-789", SecondaryPhoneNumber = "98873213", Address = "1street 2",BirthDate = DateTime.Now.Date, Pesel = "548555672", Notes = "Less Annoying"}
        };

        persons.ForEach(person => context.Persons.Add(person));
        context.SaveChanges();
    }

    if (!context.Meetings.Any())
    {
        var meetings = new List<Meeting>{
            new Meeting{PersonId = 1, Body = "Body of meeting", Date = DateTime.Now}
        };

        meetings.ForEach(meeting => context.Meetings.Add(meeting));
        context.SaveChanges();
    }

    if (!context.Statuses.Any())
    {
        var statuses = new List<Status> {
            new Status{Name = "OK"},
            new Status {Name = "NOT_OK"}
        };

        statuses.ForEach(status => context.Statuses.Add(status));
        context.SaveChanges();
    }

}

You can also use AddOrUpdate, but you need to tell EF how to check if the record exists using the first parameter...

protected override void Seed(WebApplication2.Models.ApplicationDbContext context) {

    var persons = new List<Person> { 
        new Person{FirstName = "John", LastName = "Doe", CellNumber = "123-456-789", SecondaryPhoneNumber = "98873213", Address = "1street 2",BirthDate = DateTime.Now.Date, Pesel = "312312312", Notes = "Annoying"},
        new Person{FirstName = "Anna", LastName = "Doe", CellNumber = "113-456-789", SecondaryPhoneNumber = "98873213", Address = "1street 2",BirthDate = DateTime.Now.Date, Pesel = "548555672", Notes = "Less Annoying"}
    };

    persons.ForEach(person => context.Persons.AddOrUpdate(p => new { p.FirstName, p.LastName }, person));
    context.SaveChanges();

    var meetings = new List<Meeting>{
        new Meeting{PersonId = 1, Body = "Body of meeting", Date = DateTime.Now}
    };

    meetings.ForEach(meeting => context.Meetings.AddOrUpdate(m => m.Body, meeting));
    context.SaveChanges();

    var statuses = new List<Status> {
        new Status{Name = "OK"},
        new Status {Name = "NOT_OK"}
    };

    statuses.ForEach(status => context.Statuses.AddOrUpdate(s => s.Name, status));
    context.SaveChanges();

}

Ardy

This is an idea. Could you please look at the EDIT in OP, please? – Royster

I don't know why but it does not in my case it still adds duplicates happily(please see code in the OP) I usd AddOrUpdate. – Royster

Updated answer with how you'd use AddOrUpdate – Ardy

Answer 3

Question: (1) What should I change so Seed method will be run only to recreate database after migration?

If you only need to seed data when your database is created. In this case, you can create a Database Initialiser from CreateDatabaseIfNotExist Initialiser. Then in the DatabaseInitialiser class, you can override the Seed Method with your data there, instead of the MigrationConfiguration class. Further information can be found in attached link.

Database Initialization Strategies in Code-First:

but my method is called ALWAYS, not only after migrations. Why is it so?

In migration configuration. the seed method will be called every time the database migration happens. That is why your seed method is called all the time.

Morocco

But migration happens only when model is changed, not every run? Or is it? – Royster

In the link attached, there is one more initialiser I didn't mention, migratedbtolatestversion. This is similar to this one you are using in configuration.cs – Morocco

In the link above, it didn't mention migratedbtolatestversion initialiser. This is similar to this one you are using in configuration.cs. The problem here could be caused by enabling auto migration. Because every time , ef will need to calculate whether there is migration needed or not, so your configuration.cs is called everytime, hence,seed was called every time. I think the EF description above is not very clear about its actual behaviour. Maybe disable auto migration first and see if behaviour will be changed. Or move seed override out of config into a MigrateDbToLatestverion initialiser. – Morocco

Answer 4

var paidOutType = new List<PaidOutType>
                {
                    new PaidOutType { PaidOutTypeID = 1, Code = "001", Description = "PAID OUT 1", PType = "1", Amount = 0, IsSalesSummery = true,DayFrom=1,DayTo=31 },
                    new PaidOutType { PaidOutTypeID = 2, Code = "002", Description = "PAID OUT 2", PType = "1", Amount = 0, IsSalesSummery = true,DayFrom=1,DayTo=31 },
                    new PaidOutType { PaidOutTypeID = 3, Code = "002", Description = "PAID OUT 3", PType = "1", Amount = 0, IsSalesSummery = true,DayFrom=1,DayTo=31 },
                };
                paidOutType.ForEach(u => smartPOSContext.PaidOutType.AddOrUpdate(u));
                smartPOSContext.SaveChanges();

Bullshit

Answer 5

This worked for me

1. Delete all the rows in the table
2. Reset the incremental identity to 0 if it is activated (the primary keys specified in the seed() must match those in the database table so that they do not duplicate)
3. Specify the primary keys in the 'seed' method
4. Run the seed () method several times and you check if they duplicated

Loquacious

Answer 6

First, reset your primary key to be sure that there would be no duplicate keys

// reset identity autoincrement to 0
context.Database.ExecuteSqlCommand("DBCC CHECKIDENT('tableName', RESEED, 0)");

Then use AddOrUpdate method to seed data

context.People.AddOrUpdate(new Person
{
    Id = 1,
    Name = "John Doe"
});

Mylonite