dotnetcore / dotnetcore/FreeSql
InsertOrUpdate下使用UpdateSet的行为让人困惑
- Dominant language
- C#
- Stars
- 4.4k
- Forks
- 910
- PR merge metrics
- No merged PRs in 30d
Description
#### 问题描述及重现代码:
假设有TestEntity表,实体定义如下:
```csharp
public class TestEntity
{
[Column(IsPrimary = true, IsIdentity = true)]
public int Id { get; set; }
public int Key { get; set; }
public int Year { get; set; }
public int Count { get; set; }
}
```
现需要实现逻辑:创建一个实例,如果表中没有则插入新行;否则将表中命中的行Count列加1。
很容易写出以下代码:
```csharp
var statEntity = new TestEntity()
{
Key = 1,
Year = 2026
};
var sql = freeSql.InsertOrUpdate()
.SetSource(statEntity, p => new { p.Key })
.UpdateSet((a, b) => a.Count == a.Count + 1)
.ToSql();
```
但实际生成的SQL是:
```sql
MERGE INTO [TestEntity] t1
USING (SELECT 0 as [Id], 1 as [Key], 2026 as [Year], 0 as [Count] ) t2 ON (t1.[Key] = t2.[Key])
WHEN MATCHED THEN
update set [Year] = t2.[Year], [Count] = (t1.[Count] + 1) -- Year被更新为2026!
WHEN NOT MATCHED THEN
insert ([Key], [Year], [Count])
values (t2.[Key], t2.[Year], t2.[Count]);
```
调整代码v1:
```csharp
var sql = freeSql.InsertOrUpdate()
.SetSource(statEntity, p => new { p.Key })
.UpdateSet((a, b) => a.Count == a.Count + 1)
.UpdateColumns(a => a.Count)
.ToSql();
```
SQL符合预期:
```sql
MERGE INTO [TestEntity] t1
USING (SELECT 0 as [Id], 1 as [Key], 2026 as [Year], 0 as [Count] ) t2 ON (t1.[Key] = t2.[Key])
WHEN MATCHED THEN
update set [Count] = (t1.[Count] + 1)
WHEN NOT MATCHED THEN
insert ([Key], [Year], [Count])
values (t2.[Key], t2.[Year], t2.[Count]);
```
调整代码v2:
```csharp
var sql = freeSql.InsertOrUpdate()
.SetSource(statEntity, p => new { p.Key })
.UpdateSet((a, b) => a.Count == a.Count + 1)
.UpdateColumns(a => a.Year) // 故意为之
.ToSql();
```
SQL:
```sql
MERGE INTO [TestEntity] t1
USING (SELECT 0 as [Id], 1 as [Key], 2026 as [Year], 0 as [Count] ) t2 ON (t1.[Key] = t2.[Key])
WHEN MATCHED THEN
update set [Year] = t2.[Year], [Count] = (t1.[Count] + 1) -- UodateColumn约束失效
WHEN NOT MATCHED THEN
insert ([Key], [Year], [Count])
values (t2.[Key], t2.[Year], t2.[Count]);
```
调整UpdateColumns和UpdateSet顺序,对结果没有影响。
以上行为实在让人困惑,尤其文档中没有明确UpdateColumn和UpdateSet的组合关系,极易让开发者误会导致数据被错误覆盖。
#### 数据库版本
SqlServer 2016
#### 安装的Nuget包
3.5.309
#### .net framework/. net core? 及具体版本
.NET 10
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by reproducing the SQL Server 2016 case with InsertOrUpdate, SetSource, UpdateSet, and UpdateColumns, then compare the generated ToSql output for both UpdateColumns variants. Done means the interaction is made consistent with the documented behavior and the reported Year overwrite is either corrected or clearly documented.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100