使用 IBaseRepository.Update 批量更新由 IncludeMany 加载的子项时出现异常

[shine1258]

问题描述及重现代码：

使用 IBaseRepository.Update 批量更新由 IncludeMany 加载的子项时出现异常

using FreeSql;
using FreeSql.DataAnnotations;

var freeSql = new FreeSqlBuilder()
    .UseConnectionString(DataType.Sqlite, "Data Source=:memory:")
    .UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText))
    .Build();

await freeSql.Ado.ExecuteNonQueryAsync(
    """
    CREATE TABLE Foo (
        Id   INTEGER PRIMARY KEY AUTOINCREMENT,
        Name TEXT NOT NULL
    );

    CREATE TABLE Bar (
        Id    INTEGER PRIMARY KEY AUTOINCREMENT,
        FooId INTEGER NOT NULL,
        Value TEXT,

        FOREIGN KEY (FooId) REFERENCES Foo(Id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
    );

    INSERT INTO Foo (Name) VALUES ('Foo A');
    INSERT INTO Foo (Name) VALUES ('Foo B');
    INSERT INTO Bar (FooId, Value) VALUES (1, 'Bar 1');
    INSERT INTO Bar (FooId, Value) VALUES (1, 'Bar 2');
    INSERT INTO Bar (FooId, Value) VALUES (2, 'Bar 3');
    """
);

var fooRepo = freeSql.GetRepository<Foo>();
var barRepo = freeSql.GetRepository<Bar>();

// IncludeMany 一对多并批量更新子项导致 System.Exception: FreeSql: Not updatable,
// data not tracked, should be queried first or Attach 异常
var foos = await fooRepo.Select.IncludeMany(f => f.Bars).ToListAsync();
var bars = foos[0].Bars;
bars.ForEach(b => b.FooId = 2);
await barRepo.UpdateAsync(bars); // <-- Exception here!

// 循环逐一更新子项无问题
// var foos = await fooRepo.Select.IncludeMany(f => f.Bars).ToListAsync();
// var bars = foos[0].Bars;
// bars.ForEach(b => b.FooId = 2);
// foreach (var bar in bars)
//     await barRepo.UpdateAsync(bar);

// 手动加载子项再批量更新也无问题
// var foos = await fooRepo.Select.ToListAsync();
// foreach (var foo in foos)
//     foo.Bars = await barRepo.Where(b => b.FooId == foo.Id).ToListAsync();
//
// var bars = foos[0].Bars;
// bars.ForEach(b => b.FooId = 2);
// await barRepo.UpdateAsync(bars);

public class Foo
{
    [Column(IsPrimary = true, IsIdentity = true)]
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    [Navigate(nameof(Bar.FooId))]
    public List<Bar> Bars { get; set; } = [];
}

public class Bar
{
    [Column(IsPrimary = true, IsIdentity = true)]
    public int Id { get; set; }

    [Navigate(nameof(FooId))]
    public Foo? Foo { get; set; }

    public int FooId { get; set; }

    public string Value { get; set; } = string.Empty;
}

异常信息

Unhandled exception. System.Exception: FreeSql: Not updatable, data not tracked, should be queried first or Attach:(1, 2, Bar 1)
   at FreeSql.DbSet`1.CanUpdate(TEntity data, Boolean isThrow)
   at FreeSql.DbSet`1.CanUpdate(IEnumerable`1 data, Boolean isThrow)
   at FreeSql.DbSet`1.UpdateRangePriv(IEnumerable`1 data, Boolean isCheck)
   at FreeSql.DbSet`1.UpdateRange(IEnumerable`1 data)
   at FreeSql.BaseRepository`1.UpdateAsync(IEnumerable`1 entitys, CancellationToken cancellationToken)
   at Program.<Main>$(String[] args) in D:\Repositories\FreeSqlIssues\Program.cs:line 44
   at Program.<Main>(String[] args)

查看异常提示需要先 Attach, 在 Update 前调用 barRepo.Attach(bars) 能解决这个问题

数据库版本

    <PackageReference Include="Microsoft.Data.Sqlite" Version="10.0.2" />

安装的Nuget包

    <PackageReference Include="FreeSql.Provider.SqliteCore" Version="3.5.305" />
    <PackageReference Include="FreeSql.Repository" Version="3.5.305" />

.net framework/. net core？ 及具体版本

<TargetFramework>net10.0</TargetFramework>

[2881099]

<PackageReference Include="FreeSql.Repository" Version="3.5.305" />
这个是针对单表（级联操作功能分散，不完整）

<PackageReference Include="FreeSql.Extensions.AggregateRoot" Version="3.5.304" />
这个是专门处理级联操作的，支持聚合根的通用概念，只需要一个仓库对象进行操作

[2881099]

我个人建议，一般业务 CRUD 用 FreeSql.Extensions.AggregateRoot

重要业务用 IFreeSql 原始 CRUD

[shine1258]

我尝试用 FreeSql.Extensions.AggregateRoot, 需求为更换 Bar 所属的 Foo

var fooRepo = freeSql.GetAggregateRootRepository<Foo>();
var foos = await fooRepo.Select.ToListAsync();
foos[1].Bars.AddRange(foos[0].Bars);
foos[0].Bars.Clear();
await fooRepo.UpdateAsync(foos);

发现执行的Sql语句是先插入再删除, 如果 Bar 表中有 UNIQUE 约束就出问题

INSERT INTO "Bar"("FooId", "Value") VALUES(@FooId_0, @Value_0); SELECT last_insert_rowid();
INSERT INTO "Bar"("FooId", "Value") VALUES(@FooId_0, @Value_0); SELECT last_insert_rowid();
DELETE FROM "Bar" WHERE ("Id" = 2 AND "FooId" = 1)
DELETE FROM "Bar" WHERE ("Id" = 1 AND "FooId" = 1)

[2881099]

我觉得你得想固定规则，如果这种用法肯定要先查出来再整体操作才安全的。

这种对象操作，不一次查询出来再保存，会非常危险。