dotnetcore / dotnetcore/FreeSql

InsertOrUpdate下使用UpdateSet的行为让人困惑

Open
#2,230 1 comment 0 reactions 0 assignees View on GitHub
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.