.Net 8 WebAPI使用Snowflake.Data V4.2调用GetInsertCommand报错求助

环境与问题简述 #

在.NET 8.0 Web API应用中使用Snowflake.Data V4.2版本，调用DbCommandBuilder.GetInsertCommand()时抛出异常：

Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.

相关代码 #

SnowflakeDbConnection snowConnection = (SnowflakeDbConnection)databaseConnection;
SnowflakeDbDataAdapter snowAdapter = new SnowflakeDbDataAdapter(request.TableSql, snowConnection);
SnowflakeDbCommandBuilder builder = new SnowflakeDbCommandBuilder(snowAdapter);
snowAdapter.InsertCommand = builder.GetInsertCommand();

异常堆栈信息 #

PrivacyEtl.Components.Server.Adapters.PEtlAdapter+<PutData>d__25:MoveNext
Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.
   at System.Data.Common.DbCommandBuilder.BuildInformation(DataTable schemaTable)
   at System.Data.Common.DbCommandBuilder.BuildCache(Boolean closeConnection, Boolean useColumnsForParameterNames)
   at System.Data.Common.DbCommandBuilder.GetInsertCommand(DataRow dataRow, Boolean useColumnsForParameterNames)
   at PrivacyEtl.Components.Server.Adapters.PEtlAdapter.PutData(InsertDataRequestModel request, Object databaseConnection) in

涉及表结构（已定义主键） #

create or replace TABLE SALES.BASE.CU_CREDIT_CARD_BALANCE_DEIDENTIFIED (
    ROW_ID NUMBER(38,0) NOT NULL autoincrement start 1 increment 1 order,
    CITY VARCHAR(100),
    STATE_CODE VARCHAR(10),
    ZIP VARCHAR(10),
    CC_NUMBER VARCHAR(100),
    CC_BALANCE NUMBER(10,2),
    CC_TYPE VARCHAR(20),
    MIN_PAYMENT NUMBER(10,2),
    POLYID VARCHAR(50),
    constraint pk_cc_balance_deidentified primary key (row_id) 
);

已尝试操作 #

• 将主键定义从列内声明改为独立的Primary Key子句，问题未解决。

需求背景 #

应用需处理任意客户数据库的任意表，开发阶段无法预知表结构，仅能捕获错误提示用户设置主键，无法自行修改表结构。相同逻辑在SQL Server、Teradata等其他数据库中可正常运行。

疑问 #

1. 该问题的根本原因是什么？
2. 有哪些可行的解决办法？

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