如何让LinqToDB生成含OVERRIDING SYSTEM VALUE的PostgreSQL插入语句？

问题场景 #

PostgreSQL中存在表tbl，结构如下：

CREATE TABLE "tbl"
(
    "id" bigint generated always as identity
        constraint pk_key primary key
);

手动插入指定ID值时，需要在SQL中添加OVERRIDING SYSTEM VALUE：

INSERT INTO contracts.loan_contracts1 (id)
OVERRIDING SYSTEM VALUE -- 允许手动插入ID值
VALUES (10);

对应的LinqToDB实体定义：

[Table("tbl", Schema = "public")]
public class TblEntity : IIdentifiable<long>
{
    [Column("id")]
    [PrimaryKey]
    [Identity]
    public long Id { get; set; }
}

使用connection.InsertWithId(new TblEntity(){Id = 10});执行插入时触发报错：

MessageText: cannot insert into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.

需求明确：

• 保留实体的[Identity]特性
• 使用LinqToDB生成插入语句
• 不使用LinqToDB特定的Value链式语法

解决方案 #

方案一：自定义PostgreSQL自动追加子句 #

通过扩展LinqToDB的PostgreSQL方言，让框架自动识别插入操作中是否包含显式赋值的Identity字段，自动添加OVERRIDING SYSTEM VALUE。

1. 创建自定义方言类

public class CustomPostgreSQLDialect : PostgreSQLDialect
{
    public override void BuildInsertClause(QueryBuilder builder, InsertStatement insert)
    {
        base.BuildInsertClause(builder, insert);
        
        // 检查是否有Identity字段被显式赋值
        var hasExplicitIdentity = insert.Columns
            .Any(col => 
                col.Expression is ColumnExpression ce 
                && ce.Column.IsIdentity 
                && col.Expression != null);
        
        if (hasExplicitIdentity)
        {
            builder.AppendLine().Append("OVERRIDING SYSTEM VALUE");
        }
    }
}

2. 注册自定义方言
   可以全局注册方言，或在创建连接时指定：

// 全局注册（应用启动时执行）
DataConnection.AddMappingSchema(new MappingSchema(new CustomPostgreSQLDialect()));

// 或单个连接指定
using var connection = new DataConnection(
    ProviderName.PostgreSQL, 
    "your_connection_string", 
    new CustomPostgreSQLDialect());

3. 执行插入
   现在使用InsertWithId插入带显式ID的实体时，LinqToDB会自动生成包含OVERRIDING SYSTEM VALUE的SQL：

var entity = new TblEntity { Id = 10 };
var insertedId = connection.InsertWithId(entity);

生成的SQL示例：

INSERT INTO public.tbl (id)
VALUES (@p0)
OVERRIDING SYSTEM VALUE
RETURNING id;

方案二：按需指定插入选项（更灵活） #

如果需要按需控制是否添加OVERRIDING SYSTEM VALUE，可以通过扩展InsertOptions实现：

1. 扩展InsertOptions

public static class InsertOptionsExtensions
{
    public static InsertOptions WithPostgreSqlOverridingSystemValue(this InsertOptions options)
    {
        options.SetExtension("PostgreSqlOverridingSystemValue", true);
        return options;
    }
}

2. 修改自定义方言
   在方言中检查扩展参数：

public class CustomPostgreSQLDialect : PostgreSQLDialect
{
    public override void BuildInsertClause(QueryBuilder builder, InsertStatement insert)
    {
        base.BuildInsertClause(builder, insert);
        
        if (insert.Options?.GetExtension<bool?>("PostgreSqlOverridingSystemValue") == true)
        {
            builder.AppendLine().Append("OVERRIDING SYSTEM VALUE");
        }
    }
}

3. 执行插入时指定选项

var entity = new TblEntity { Id = 10 };
connection.Insert(entity, options => options.WithPostgreSqlOverridingSystemValue());

内容的提问来源于stack exchange，提问作者VladimirK