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 produces exceptions (Sequence contains more than one element).

Is AddOrUpdate method intended to work this way? Should I perform upsert by hand?

Answer 1

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 Slugs.

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.