dotnetcore / dotnetcore/FreeSql

InsertOrUpdate.UpdateColumns 生成的sql 只保留 主键+更新列 ,无关列不要生成

Open
#2,142 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
4.4k
Forks
910
PR merge metrics
No merged PRs in 30d

Description

### 运行环境
1. postgres
2. net 6
3. FreeSql.Provider.Postgresql:3.2.813

### 演示代码

```c#
[Table(Name = "test_artwork_info")]
public class TestArtworkInfo
{
[Column(Name = "datecode", IsPrimary = true)] public int DateCode { get; set; }
[Column(Name = "platform", IsPrimary = true)] public int Platform { get; set; }
[Column(Name = "artwork_id", IsPrimary = true)] public string ArtworkId { get; set; }
[Column(Name = "monthcode", IsPrimary = true)] public int MonthCode { get; set; }

[Column(Name = "user_id")] public string UserId { get; set; }

[Column(Name = "tags")] public string[] Tags { get; set; }

[Column(Name = "auth", DbType = "jsonb")] public string Auth { get; set; } // 如果需要更复杂类型可以用 JObject 或自定义 class

[Column(Name = "title")] public string Title { get; set; }

[Column(Name = "addtime")] public DateTime AddTime { get; set; }
}
```

```
var model = new TestArtworkInfo()
{
DateCode = 20251008,
Platform = 8,
ArtworkId = "ac46b2ef0f4244c08bbf9e2769d74ac0",
MonthCode = 202510,
UserId = string.Empty
};

model.Tags = new[] { "48d7f37033" };
model.Title = "test";

//1. 定义一个完整的model , 差量更新 ,全量更新
await context.MergeIntoAsync(
source: new List { model },
monthCodeSelector: s => s.MonthCode, //分区键
updateColumns: s => new { s.Tags, s.Title });
```

```
public static async Task MergeIntoAsync(
this IFreeSql context,
IEnumerable source,
Expression> monthCodeSelector,
Expression> updateColumns = null) where T1 : class
{
if (source == null || !source.Any())
{
return;
}

var tableName = GetTableName();

var keySelector = monthCodeSelector.Compile();

var groups = source.GroupBy(keySelector);

foreach (var group in groups)
{
try
{
var insert = context
.InsertOrUpdate()
.AsTable($"{tableName}_{group.Key}")
.SetSource(group);

if (updateColumns != null)
insert.UpdateColumns(updateColumns);

await insert.ExecuteAffrowsAsync();
}
catch (Exception e)
{
if (!e.Message.Contains("#Temp_"))
{
throw new Exception("无法删除Temp ,请检查class和表是否一致");
}
}
}
}
```

### 生成的sql
```
INSERT INTO "yunlue_bussiness"."test_artwork_info_202510"("datecode", "platform", "artwork_id", "monthcode", "user_id", "tags", "auth", "title", "addtime") VALUES(20251008, 8, 'ac46b2ef0f4244c08bbf9e2769d74ac0', 202510, '', ARRAY['48d7f37033']::varchar[], NULL, 'test', '0001-01-01 00:00:00.000000')
ON CONFLICT("datecode", "platform", "artwork_id", "monthcode") DO UPDATE SET
"tags" = EXCLUDED."tags",
"title" = EXCLUDED."title"

```

**这里的sql能不能优化下 ,自动去掉user_id,auth,addtime, 只留下 主键和更新字段**

```
INSERT INTO "yunlue_bussiness"."test_artwork_info_202510"("datecode", "platform", "artwork_id", "monthcode","tags", "title") VALUES(20251008, 8, 'ac46b2ef0f4244c08bbf9e2769d74ac0', 202510, ARRAY['48d7f37033']::varchar[], 'test')
ON CONFLICT("datecode", "platform", "artwork_id", "monthcode") DO UPDATE SET
"tags" = EXCLUDED."tags",
"title" = EXCLUDED."title"
```
**大宽表能减少大量的生成sql **

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.