时间戳

首页 > 解决方案 > Entity Framework Core 批量更新底层集合

c# - Entity Framework Core 批量更新底层集合

问题描述

是否有 Entity Framework Core 的已知扩展可以像此 SqlRaw 示例中那样执行此批量更新？

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)

所有的表都是相关的，即Project -> Station -> Assembly，我想为具有特定ProjectID的同一项目下的所有程序集更新一些值，如上面的语句所示。

ps 扩展我见过，直到知道可以进行批量更新，但在第一个表上，而不是在主实体上的相关表上。

楷模：

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

标签： c#entity-frameworkentity-framework-core

解决方案

我会建议linq2db.EntityFrameworkCore（免责声明：我是创作者之一）

然后您可以执行以下操作：

var updateQuery =
    from a in ctx.Assembly
    join s in ctx.Station on a.SationId equals s.ID
    join p in ctx.Project on s.ProjectId equals p.ID
    where p.ID == projectId
    select a;

var recordsAffected = await updateQuery
    .Set(a => a.OnHold, onHold)
    .Set(a => a.UpdatedWith, updatedWith)
    .Set(a => a.UpdatedAt, a => Sql.CurrentTimeStamp)
    .Set(a => a.UpdatedById, updatedById)
    .UpdateAsync();

如果您定义了适当的导航属性，则可以简化查询并删除连接。

var recordAffected = await ctx.Assembly
    .Where(a => a.Station.ProjectId == projectId)
    .Set(a => a.OnHold, onHold)
    .Set(a => a.UpdatedWith, updatedWith)
    .Set(a => a.UpdatedAt, a => Sql.CurrentTimeStamp)
    .Set(a => a.UpdatedById, updatedById)
    .UpdateAsync();

推荐阅读