连表删除很慢

[pengxinglove]

作者好：
请问一下这种连接表删除有什么好建议？
affrows = db.Select<tb_PP_Lotdetial>()
.From<Tb_LD_paint_sn>((a, b) => a.InnerJoin(x => x.FSN == b.sn && x.FINSIDELOTNO == b.lotno))
.Where((a, b) => a.FINSIDELOTNO == dto.LotNo && b.printflag == false)
.ToDelete()
.ExecuteAffrows();
if (affrows == 0)
{
throw new Exception("删除 【tb_PP_Lotdetial】 失败");
}

DELETE
FROM
[tb_PP_Lotdetial]
WHERE
(
[FINSIDELOTNO] + N',ftb_del,' + [FSN] IN (
SELECT
*
FROM
(
SELECT
a.[FINSIDELOTNO] + N',ftb_del,' + a.[FSN] AS as1
FROM
[tb_PP_Lotdetial] a
INNER JOIN [Tb_LD_paint_sn] b ON a.[FSN] = b.[sn]
AND a.[FINSIDELOTNO] = b.[lotno]
WHERE
( a.[FINSIDELOTNO] = N'103S0065-test' AND b.[printflag] = 0 )
) ftb_del
)
)

---

但如果采用 以下这种就很快
DELETE
FROM
[tb_PP_Lotdetial]
WHERE
(
[FSN] IN (
SELECT
*
FROM
(
SELECT
a.[FSN] AS as1
FROM
[tb_PP_Lotdetial] a
INNER JOIN [Tb_LD_paint_sn] b ON a.[FSN] = b.[sn]
AND a.[FINSIDELOTNO] = b.[lotno]
WHERE
( a.[FINSIDELOTNO] = N'103S0065-test' AND b.[printflag] = 0 )
) ftb_del
)
)

[2881099]

你的实体类定义的是两个联合主键吧，[FINSIDELOTNO], [FSN]

[2881099]

如果实体类多个键无法改变，可以如下解决

var sql = db.Select<tb_PP_Lotdetial, Tb_LD_paint_sn>()
    .InnerJoin((a, b) => a.FSN == b.sn && a.FINSIDELOTNO == b.lotno)
    .Where((a, b) => a.FINSIDELOTNO == dto.LotNo && b.printflag == false)
    .ToSql(a=> a.FSN);

fsql.Delete<tb_PP_Lotdetial>()
    .Where($"FSN in ({sql})")
    .ExexuteAffrows()

[pengxinglove]

这种方法可以，只是ToSql的时候字段不要重命名
var sql = db.Select<tb_PP_Lotdetial, Tb_LD_paint_sn>()
.InnerJoin((a, b) => a.FSN == b.sn && a.FINSIDELOTNO == b.lotno)
.Where((a, b) => a.FINSIDELOTNO == dto.LotNo && b.printflag == false)
.ToSql((a, b) => a.FSN, FieldAliasOptions.AsProperty);

            affrows = db.Delete<tb_PP_Lotdetial>()
                .Where($"FSN in ({sql})")
                .ExecuteAffrows();

[pengxinglove]

感谢作者的技术支持，让我们能够放心大胆地使用Freesql