AddOrUpdate无法正常工作并产生重复项 [英] AddOrUpdate works not as expected and produces duplicates

问题描述

我正在使用基于代码优先的基于DBContext的EF5设置。

I'm using Code-First DBContext-based EF5 setup.

在 DbMigrationsConfiguration.Seed 中， m尝试用默认的伪数据填充数据库。为了完成此任务，我使用 DbSet.AddOrUpdate 方法。

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++;
}

我尝试使用/忽略 Id 字段，以及使用 Id / Slug 属性作为更新选择器。

I've tried to use/omit Id field as well as to use Id/Slug property as update selector.

运行更新数据库时，将忽略 Id 字段，并且该值由SQL Server自动生成，并且DB中填充有重复项； Slug 选择器允许重复，并且在后续运行中会产生异常（序列包含多个元素）。

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

AddOrUpdate 方法是否打算以这种方式工作？我应该手动执行upsert吗？

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

推荐答案

首先（尚无答案）， AddOrUpdate 可以用一个新对象数组调用，因此您只需创建一个 City [] 类型的数组并调用 context.CitySet。 AddOrUpdate（cc => cc.Id，cityArray）; 一次。

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.

（已编辑）

第二， AddOrUpdate 使用标识符表达式（ cc => cc.Id ）查找城市与数组中的 Id 相同。这些城市将被更新。将插入数组中的其他城市，但是其 Id 值将由数据库生成，因为 Id 为身份列。不能通过插入语句设置。 （除非将身份插入设置为打开）。因此，对于具有标识列的表使用 AddOrUpdate 时，您应该找到另一种标识记录的方法，因为现有记录的ID值是不可预测的。

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.

如果您使用 Slug 作为 AddOrUpdate 的标识符，该标识符应该是唯一的（根据您的评论）。我不清楚为什么不更新具有匹配的 Slug s的现有记录。

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.

我设置了一个小测试：添加或更新具有ID（标识）和唯一名称的实体：

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();

当 Prod1尚不存在时，将其插入（忽略ID 999）。

如果是，并且 UnitPrice 不同，则会对其进行更新。

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

查看发出的查询看到EF正在按名称查找唯一记录：

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]

接下来（当找到匹配项且 UnitPrice 不同时）

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

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

这表明EF找到了一条记录，现在使用键字段进行更新。

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

我希望看到这个例子一些关于你的情况。也许您也应该监视sql语句，看看那里是否发生了意外情况。

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无法正常工作并产生重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！