SetSource批量更新使用CAST函数转换字符串非预期异常

[misgw]

问题描述及重现代码：

简述: SQL Server CAST函数转为varchar默认长度为30, AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA11 => AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA22 => AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA33 => AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 此时主键值相同

解决: 1.增加更新时参数自定义varchar长度 2.字符型不进行CAST转换

脚本

--表结构
CREATE TABLE table_a(
    Field_A bigint NOT NULL,
    Field_B varchar(50) NOT NULL,
    Field_C int NOT NULL,
    CONSTRAINT pk_table_a PRIMARY KEY(Field_A,Field_B)
);
--模拟数据
INSERT INTO table_a(Field_A,Field_B,Field_C) 
VALUES 
(100000000,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA11',16),
(100000000,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA22',16),
(100000000,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA33',16);
--验证字符串使用CAST函数后默认长度为30, 当数据长度超出30时产生溢出被截断导致更新异常
SELECT 
Field_B,
LEN(Field_B) Field_B_Length,
CAST([Field_B] as varchar) CAST_Field_B,
LEN(CAST([Field_B] as varchar)) CAST_Field_B_Length,
CASE WHEN CAST([Field_B] as varchar) = Field_B THEN 'true' ELSE 'false' END eq
FROM table_a;
--SetSource生成的sql
UPDATE [table_a] SET [Field_C] = 
CASE (cast([Field_A] as varchar) + '+' + cast([Field_B] as varchar)) 
WHEN cast(100000000 as varchar) + '+' + cast(N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA11' as varchar) THEN 0
WHEN cast(100000000 as varchar) + '+' + cast(N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA22' as varchar) THEN 1
WHEN cast(100000000 as varchar) + '+' + cast(N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA33' as varchar) THEN 2 END 
WHERE (([Field_A] = 100000000 AND [Field_B] = N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA11')
OR ([Field_A] = 100000000 AND [Field_B] = N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA22')
OR ([Field_A] = 100000000 AND [Field_B] = N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA33'));

原始数据

Field_A	Field_B	Field_C
100000000	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA11	16
100000000	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA22	16
100000000	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA33	16

预期更新后的数据

Field_A	Field_B	Field_C
100000000	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA11	0
100000000	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA22	1
100000000	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA33	2

实际更新后的数据

Field_A	Field_B	Field_C
100000000	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA11	0
100000000	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA22	0
100000000	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA33	0

FreeSql.Update<table_a>().SetSource(table_a_list).UpdateColumns(p => new { p.Field_C }).ExecuteAffrows();

数据库版本

SQL Server 2019

安装的Nuget包

FreeSql 3.2.680 FreeSql.Provider.SqlServer 3.2.680

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

.NET 6

[2881099]

换成 cast(.. varchar(2000)) 吧