AddOrUpdate works not as expected and produces duplicates

Question

I'm using Code-First DBContext-based EF5 setup. In `DbMigrationsConfiguration.Seed` I'm trying to fill DB with default dummy data. To accomplish this task, I use `DbSet.AddOrUpdate` method. The simplest code to illustrate my aim: ``` j = 0; var cities = new[] { "Berlin", "Vienna", "London", "Bristol", "Rome", "Stockholm", "Oslo", "Helsinki", "Amsterdam", "Dublin" }; var cityObjects = new City[cities.Length]; foreach (string c in cities) { int id = r.NextDouble() > 0.5 ? 0 : 1; var city = new City { Id = j, Name = c, Slug = c.ToLowerInvariant(), Region = regions[id], RegionId = regions[id].Id, Reviewed = true }; context.CitySet.AddOrUpdate(cc => cc.Id, city); cityObjects[j] = city; j++; } ``` I've tried to use/omit `Id` field as well as to use `Id`/`Slug` property as update selector. when `Update-Database` is run, `Id` field is ignored and the value is generated automatically by SQL Server and DB is filled with duplicates; `Slug` selector allows duplicates and on subsequent runs produc…

Accepted Answer

First (no answer yet), `AddOrUpdate` can be called with an array of new objects, so you can just create an array of type `City[]` and call `context.CitySet.AddOrUpdate(cc => cc.Id, cityArray);` once.

(edited)

Second, `AddOrUpdate` uses the identifier expression (`cc => cc.Id`) to find cities with the same `Id` as the ones in the array. These cities will be updated. The other cities in the array will be inserted, but their `Id` values will be generated by the database, because `Id` is an identity column. It can not be set by an insert statement. (Unless you set Identity Insert on). So when using `AddOrUpdate` for tables with identity columns you should find another way to identify records because the Id values of existing records are unpredictable.

In you case you used `Slug` as identifier for `AddOrUpdate`, which should be unique (as per your comment). It is not clear to me why that does not update existing records with matching `Slug`s.

I set up a little test: add or update an entity with an Id (iedntity) and a unique name:

```
var n = new Product { ProductID = 999, ProductName = "Prod1", UnitPrice = 1.25 };
Products.AddOrUpdate(p => p.ProductName, n);
SaveChanges();
```

When "Prod1" is not there yet, it is inserted (ignoring Id 999). If it is and `UnitPrice` is different, it is updated.

Looking at the emitted queries I see that EF is looking for a unique record by name:

```
SELECT TOP (2) 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[ProductName] AS [ProductName], 
[Extent1].[UnitPrice] AS [UnitPrice]
FROM [dbo].[Products] AS [Extent1]
WHERE N'Prod1' = [Extent1].[ProductName]
```

And next (when a match is found and `UnitPrice` is different)

```
update [dbo].[Products]
set [UnitPrice] = 1.26
where ([ProductID] = 15)
```

This shows that EF found one record and now uses the key field to do the update.

I hope that seeing this example will shed some light on your situation. Maybe you should monitor the sql statements as well and see if anything unexpected happens there.

AddOrUpdate works not as expected and produces duplicates

Solution

Problem