Entity Framework Code First AddOrUpdate method insert Duplicate values

Go To StackoverFlow.com

53

I have simple entity:

public class Hall
{
    [Key]
    public int Id {get; set;}

    public string Name [get; set;}
}

Then in the Seed method I use AddOrUpdate to populate table:

var hall1 = new Hall { Name = "French" };
var hall2 = new Hall { Name = "German" };
var hall3 = new Hall { Name = "Japanese" };

context.Halls.AddOrUpdate(
    h => h.Name,
    hall1,
    hall2,
    hall3
);

Then I run in the Package Management Console:

Add-Migration Current
Update-Database

It's all fine: I have three rows in the table "Hall". But if I run in the Package Management Console Update-Database again I have already five rows:

Id  Name
1   French
2   Japaneese
3   German
4   French
5   Japanese

Why? I think it is should be three rows again, not five. I tried to use Id property instead of Name but it does not make the difference.

UPDATE:

This code produces the same result:

var hall1 = new Hall { Id = 1, Name = "French" };
var hall2 = new Hall { Id = 2, Name = "German" };
var hall3 = new Hall { Id = 3, Name = "Japanese" };

context.Halls.AddOrUpdate(
                h => h.Id,
                hall1);

context.Halls.AddOrUpdate(
                h => h.Id,
                hall2);

context.Halls.AddOrUpdate(
                h => h.Id,
                hall3);

Also I have the latest EntityFramework installed via nuget.

2012-04-04 08:19
by Y.Yanavichus
What happens if you use separate call to AddOrUpdate for every record? It is quite strange that you have 5 records. It means that it worked once so there must be something special in your - Ladislav Mrnka 2012-04-04 08:34
@Ladislav Mrnka: The same thing for separate calls - Y.Yanavichus 2012-04-04 08:37
Look at this: - http://thedatafarm.com/blog/data-access/take-care-with-ef-4-3-addorupdate-method - Kirin Yao 2012-04-05 06:31
Thanks a lot. I've already seen this page. Unfortunately, I haven't found any interesting in this article that can solve my issue - Y.Yanavichus 2012-04-05 13:38
See my answer below. I spent a few hours debugging this one, and it's because I was modifying the Entity state info by overriding the SaveChanges() method in my context file. Full explanation below. Populating the database with the seed method should not require any lookup by Id -- the whole point is to make things easy, and finally I was able to figure out how to make this work by looking up by a property name other than Id. We should not be managing Id columns in this seed method - firecape 2013-07-28 21:30


61

Ok I was banging my face off the keyboard for an hour with this. If your table's Id field is an Identity field then it won't work so use a different one for identifierExpression. I used the Name property and also removed the Id field from the new Hall {...} initializer.

This tweak to the OPs code worked for me so I hope it helps someone:

protected override void Seed(HallContext context)
{
    context.Halls.AddOrUpdate(
        h => h.Name,   // Use Name (or some other unique field) instead of Id
        new Hall
        {
            Name = "Hall 1"
        },
        new Hall
        {
            Name = "Hall 2"
        });

    context.SaveChanges();
}
2013-03-14 15:51
by Ciaran Bruen
It works like it designed to. If you insert a value into a table then SQL assigns the next value in the DB. If you don't want SQL/EF to calculate the next Key to use then you need to turn that off for you entity. I've posted an answer farther down that explains this - Anthony Nichols 2016-01-22 17:57
didn't work for me. What version of EF are you using - Campinho 2017-09-23 00:44
@Campinho EF 4.1 Code First from what I remembe - Ciaran Bruen 2017-09-25 09:43
If you are looking for a way to do multi column unique row, see this question... https://stackoverflow.com/questions/35452497/entity-framework-seed-addorupdate-with-multi-column-index-as-identifie - vbp13 2018-02-16 22:41


9

This code works:

public Configuration()
{
    AutomaticMigrationsEnabled = true;
}

protected override void Seed(HallContext context)
{
    context.Halls.AddOrUpdate(
        h => h.Id,
        new Hall
        {
            Id = 1,
            Name = "Hall 1"
        },
        new Hall
        {
            Id = 2,
            Name = "Hall 2"
        });

    context.SaveChanges();
}
2012-11-20 08:06
by Y.Yanavichus
It might work, but it's not correct. ID is generated on the server. Run it and SQL will use 1 and 2 for the first two ID's. Then delete those rows and run it again, Id = 1, will be ignored and SQL will use ID=3, 4 when they are inserted next. Or change the Id's to 99, 100 in the code above. Drop the DB, Run the seed method and it won't use 99, 100, but SQL will use 1 and 2. Also, if those seed rows are deleted, each seed run will insert the same data again because ID won't match. Much better to use Name as the unique field - RickAndMSFT 2015-02-10 01:20
So you suggest to mark Ciaran Bruen's answer as correct? I just can not check it no - Y.Yanavichus 2015-02-13 09:21


8

I know this is an old question, but the right answer is that if you are setting the id # yourself and you want to use AddOrUpdate then you need to tell EF/SQL that you don't want it to generate the ID #.

modelBuilder.Entity<MyClass>().Property(p => p.Id)
    .HasDatabaseGeneratedOption(System.ComponentModel
    .DataAnnotations.Schema.DatabaseGeneratedOption.None); 

The down side to this is that when you insert a new item you need to set it's Id, so if this is done dynamically at runtime (instead of from seed data) then you will need to calculate out the next Id. Context.MyClasses.Max(c=>c.Id) + 1 works well.

2016-01-22 17:55
by Anthony Nichols


3

This can also be caused if you're setting the Entity State incorrectly. I kept getting the following error when I'd run update-database..."Sequence contains more than one matching element."

For example, I had duplicate rows being created on each update-database command (which of course is not supposed to happen when seeding data), and then the next update-database command wouldn't work at all since it found more than one match (hence the sequence error saying I have more than one matching row). That's because I had overridden SaveChanges in my context file with a method call to ApplyStateChanges...

public override int SaveChanges()
{
    this.ApplyStateChanges();
    return base.SaveChanges();
}

I was using ApplyStateChanges to ensure that when adding object graphs, Entity Framework knows explicitly whether the object is in an added or modified state. The entire explanation on how I'm using ApplyStateChanges can be found here.

And this works great (but the caveat!!)...if you're also seeding the database using CodeFirst migrations, then the above method will cause havoc for the AddOrUpdate() call within the Seed Method. So before anything else, just check your DBContext file and ensure you're not overriding SaveChanges in the way above, or you will end up getting duplicate data running the update-database command a second time, and then won't work at all the third time since there's more than one row for each matching item.

When it comes down to it, you don't need to configure the Id in AddOrUpdate()...that defeats the whole purpose of easy and initial database seeding. It works fine by something like:

context.Students.AddOrUpdate(
    p => p.StudentName,
    new Student { StudentName = "Bill Peters" },
    new Student { StudentName = "Jandra Nancy" },
    new Student { StudentName = "Rowan Miller" },
    new Student { StudentName = "James O'Dalley" },

just AS LONG as I'm not overriding the SaveChanges method in my context file with a call to ApplyStateChanges. Hope this helps.

2013-07-28 21:24
by firecape


2

This worked for me

  1. Delete all the rows in the table.
  2. Reset the incremental identity to 0. DBCC CHECKIDENT (yourtablename, RESEED, 0) (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.
2018-05-23 14:35
by Paco Francisco Rodeño Sanchez
The only option I had was seeding filtering on a unique Id column because my other fields where duplicates, so that was not possible. Thanks for your answer - Max 2018-06-25 07:14


1

I have found that AddOrUpdate works fine with fields that are not ID's. If this works for you: context.Halls.AddOrUpdate(h => h.Name, hall1, hall2, hall3)

You may want to use Hall names like 'French_test_abc_100', 'German_test_abc_100' etc.

That stops hard coded test data messing things up when you are testing your app.

2012-11-03 20:27
by Daryn


0

If object(hall)'s id is 0, it is a insertion. I think you need to double check the id field of your hall objects

2012-04-04 09:03
by weeyoung
I updated my question: the result is the same - Y.Yanavichus 2012-04-04 09:32
It should work, the logic seems to be right. Possibly, the commit is missing, or some other bugs in the code that you are not showing. possibly create a timestamp in a new column to confirm this situation - weeyoung 2012-04-04 09:38
there is nothing more in my code - Y.Yanavichus 2012-04-04 10:04


0

Is your ID field an Identity field? I was running into this same issue. When I removed the Identity status from my ID field and set the IDs going into the database, that resolved the issue.

That worked for me, since these were look-up tables and shouldn't have been identity fields, anyway.

2012-05-08 15:54
by Jason
Yes, it is an identity field. I added condition if table is not empty then seed table - Y.Yanavichus 2012-05-12 15:38


0

I think it's likely that you need to back out existing database migrations (i.e. start your database from scratch) with something like 'Update-Database TargetMigration:0' followed by 'Update-Database'.

As it is, you're not dropping the existing table or values, you're just add/updating those values. That needs to happen in order to get your desired result.

Here's a good reference for EF migrations: http://elegantcode.com/2012/04/12/entity-framework-migrations-tips/

2015-03-03 01:15
by Harvey Powers


0

I used the ID field as Identity/Key and add attributes not to assign Ids by the server. This solved the problem for me.

public class Hall
{
    [Key]
    [Required]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id {get; set;}

    public string Name [get; set;}
 }
2016-11-24 11:00
by Guntars


0

Just to Ciaren's answer, the below code of resetting the context on ModelCreating, helped me resolve similar issues. Make sure change "ApplicationContext" to your DbContext name.

public class ApplicationContext : DbContext, IDbContext
    {
        public ApplicationContext() : base("ApplicationContext")
        {
             
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
            Database.SetInitializer<ApplicationContext>(null);
            base.OnModelCreating(modelBuilder);
        }
     }

2017-08-09 11:06
by user1570636


0

I found out that for this to work, the identity position should be 0 when the seed first run. You can reset it using:

DBCC CHECKIDENT (tableName, RESEED, 0)
2018-07-17 09:50
by Brain Balaka


-1

You could have also done this:

 context.Halls.AddOrUpdate(new Hall[]{hall1,hall2, hall3});
2018-03-21 20:43
by Charles Owen
Ads