英文:
Is it possible to update multiple entities based on their IDs without fetching in EF Core?
问题
请注意,这不是批量更新,每个记录都将有其独特的更新值。
我有一个用户排序的项目列表(Id
,Name
,Position
),用户可以自由重新排序项目。现在,当用户提交一个新的排序列表时,我想更新所有它们的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 Position
s. I have a list of Id
s matching with their Position
s (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))
- The answer from this article needs to fetch ALL data of the items from the database:
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;
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论