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