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

Question

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

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

Answer 1

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