Entity Framework Core bulk update underlying collections

Question

Is there a known extension for Entity Framework Core that can do this bulk update as in this SqlRaw example?

dbc.Database.ExecuteSqlRawAsync(
            "UPDATE [Assembly] SET OnHold = @OnHold, UpdatedWith = @UpdatedWith, UpdatedAt = @UpdatedAt, UpdatedById = @UpdatedById FROM [Assembly] INNER JOIN Station ON [Assembly].StationID = Station.ID INNER JOIN Project ON Station.ProjectID = Project.ID WHERE Project.ID = @ProjectID",
            onHold, updatedWith, updatedAt, updatedById, projectID)

All the tables are related, i.e. Project -> Station -> Assembly, and I want to update some values for all assemblies that are under the same project with specific ProjectID, as shown in the above statement.

p.s. Extensions I have seen until know can do bulk update, but on the first table, not on the related tables on the main entity.

Models:

public partial class Assembly
{
    public Assembly()
    {
    }

    public int Id { get; set; }
    public int StationId { get; set; }
    public string Name { get; set; }        
    public string UpdatedWith { get; set; }
    public DateTime? UpdatedAt { get; set; }
    public bool OnHold { get; set; }

    public virtual Station Station { get; set; }
}

public partial class Station
{
    public Station()
    {
        Assemblies = new HashSet<Assembly>();            
    }

    public int Id { get; set; }
    public int ProjectId { get; set; }
    public string Name { get; set; }   

    public virtual Project Project { get; set; }
    public virtual ICollection<Assembly> Assemblies { get; set; }
}

public partial class Project
{
    public Project()
    {            
        Stations = new HashSet<Station>();
    }

    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Station> Stations { get; set; }
}

Answer 1

The IQueryable.ToQueryString method introduced in Entity Framework Core 5.0 may help reduce/simplify the raw SQL used in your code. This method will generate SQL that can be included in a raw SQL query to perform a bulk update of records identified by that query.

For example:

var query =
    from a in dbc.Assembly
    join s in dbc.Station on a.StaionId equals s.Id
    join p in dbc.Project on s.ProjectId equals p.Id
    where p.Id == projectId
    select a.Id;

var sql = $@"
    UPDATE Assembly
    SET OnHold = {{0}}, UpdatedWith = {{1}}, UpdatedAt = {{2}}, UpdatedById = {{3}}
    WHERE Id IN ({query.ToQueryString()})
";

dbc.Database.ExecuteSqlRawAsync(sql, onHold, updatedWith, updatedAt, updatedById);

The major drawback of this approach is the use of raw SQL. However I don't know of any reasonable way to avoid that with current Entity Framework Core capabilities - you're stuck with this caveat, or the caveat of introducing a dependency on another library such as linq2db.EntityFrameworkCore as mentioned in another answer here.

If (when) the following issue is addressed in the future then we are likely to have a better answer to this question: Bulk (i.e. set-based) CUD operations (without loading data into memory) #795

Answer 2

Just a little patience there will be the built-in BulkUpdate() as well as BulkDelete methods in EFCore which will be delivered in EFCore 7.0

context.Customers.Where(...).BulkDelete();
context.Customers.Where(...).BulkUpdate(c => new Customer { Age = c.Age + 1 });
context.Customers.Where(...).BulkUpdate(c => new { Age = c.Age + 1 });