在EF Core中是否可以根据它们的ID更新多个实体而无需提取数据?

huangapple go评论70阅读模式
英文:

Is it possible to update multiple entities based on their IDs without fetching in EF Core?

问题

请注意,这不是批量更新,每个记录都将有其独特的更新值。

我有一个用户排序的项目列表(IdNamePosition),用户可以自由重新排序项目。现在,当用户提交一个新的排序列表时,我想更新所有它们的Position。我有一个与它们的Position匹配的Id列表(请注意,我没有任何其他信息,比如Name,也不想丢失它)。

如何让EF Core在不必先获取它们的情况下提交一个单一的往返多个UPDATE语句?

以下是方法和它们的限制:

  • 这个方法为每个项目发送1个请求:
ctx.Products
    .Where(q => q.Id == item.Id)
    .ExecuteUpdate(q => q.SetProperty(p => p.Position, _ => item.Position))
var idList = new int[] { 1, 2, 3, 4 };

using (var db = new SomeDatabaseContext())
{
    var friends = db.Friends.Where(f => idList.Contains(f.ID)).ToList();

    friends.ForEach(a => a.msgSentBy = '1234');
    db.SaveChanges();
}
  • 通过手动修改Change Tracker的我的方法导致数据丢失(运行后,所有Name都变成了“”):
var posList = new (int, int)[]
{
    (1, 2),
    (2, 1),
    (3, 1),
};

var ctx = new TestContext();

foreach (var (id, pos) in posList)
{
    var entity = new DbItem
    {
        Id = id,
        Name = "", // 必需的属性
        Position = pos,
    };

    var entry = ctx.Attach(entity);
    entry.State = EntityState.Modified;
    entry.Property(q => q.Position).IsModified = true;
}

await ctx.SaveChangesAsync();
  • 我还尝试了另一种使用Attach的方法,但它导致了异常:
// 周围的代码类似于上面的代码
var entity = new DbItem
{
    Id = id,
    Name = "", // 必需的属性
    Position = -1, // 确保它将被更改
};

var entry = ctx.Attach(entity);
entity.Position = pos;

未处理的异常。Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException:预计数据库操作会影响1行记录,但实际影响了0行记录;数据可能在加载实体之后已被修改或删除。有关理解和处理乐观并发异常的信息,请参阅http://go.microsoft.com/fwlink/?LinkId=527962。

英文:

Note that this is NOT Bulk Update, every record will have its own unique update value.

I have a user-ordered list of items (Id, Name, Position) where user can freely reorder the items. Now when user submits a new ordered list, I would like to update all of their Positions. I have a list of Ids matching with their Positions (note that I do not have any other information like Name and do not want to lose it).

How do I make EF Core to submit a single round trip with many UPDATE statement without having to fetch them first?

Below are the approaches and their limitations:

  • This one sends 1 request for each item:
ctx.Products
   .Where(q => q.Id == item.Id)
   .ExecuteUpdate(q => q.SetProperty(p => p.Position, _ => item.Position))
var idList = new int[] { 1, 2, 3, 4 };

using (var db = new SomeDatabaseContext())
{
    var friends = db.Friends.Where(f => idList.Contains(f.ID)).ToList();

    friends.ForEach(a => a.msgSentBy = '1234');
    db.SaveChanges();
}
  • My approach by modifying the Change Tracker manually results in data loss (after running this, all Name becomes ""):
    var posList = new (int, int)[]
    {
        (1, 2),
        (2, 1),
        (3, 1),
    };

    var ctx = new TestContext();

    foreach (var (id, pos) in posList)
    {
        var entity = new DbItem
        {
            Id = id,
            Name = "", // Required property
            Position = pos,
        };

        var entry = ctx.Attach(entity);
        entry.State = EntityState.Modified;
        entry.Property(q => q.Position).IsModified = true;
    }

    await ctx.SaveChangesAsync();
  • I also tried another approach using Attach instead, however it results in exception:
// The surrounding code is similar to the above code
var entity = new DbItem
{
    Id = id,
    Name = "", // Required property
    Position = -1, // To make sure it will be changed
};

var entry = ctx.Attach(entity);
entity.Position = pos;

> Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

I am using EF Core 7.

答案1

得分: 1

Sure, here are the translated parts:

如果您确定数据存在于数据库中(或在缺失的情况下也可以正常运行),您可以尝试利用附加实体并设置状态的能力:

List<Products> entities = ...
var ctx = ...;
ctx.AttachRange(entities);
foreach (var ent in entities)
{
    ctx.Entry(ent).State = EntityState.Modified;
}

ctx.SaveChanges();

> 通过手动修改 Change Tracker 的方法会导致数据丢失

不要设置状态,更新数值:

foreach (var (id, pos) in posList)
{
    var entity = new DbItem
    {
        Id = id,
        Position = pos - 1 
    };

    var entry = ctx.Attach(entity);
    entity.Position = pos;
}
英文:

If you are sure that the data is present in database (or ok in case of failure for missing ones) you can try leveraging the ability to attach entities and set state:

List<Products> entities = ...
var ctx = ...;
ctx.AttachRange(entities);
foreach (var ent in entities)
{
    ctx.Entry(ent).State = EntityState.Modified;
}

ctx.SaveChanges();

> My approach by modifying the Change Tracker manually results in data loss

Do not set state, update the value:

foreach (var (id, pos) in posList)
{
    var entity = new DbItem
    {
        Id = id,
        Position = pos - 1 
    };

    var entry = ctx.Attach(entity);
    entity.Position = pos;
}

huangapple
  • 本文由 发表于 2023年7月18日 16:29:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/76710862.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定