Entity Framework中含INTERVAL参数的函数参数化调用语法问题

我有一段Entity Framework调用代码，通过拼接字符串的方式调用数据库函数add_retention_policy，其中包含INTERVAL参数，代码运行正常。现在想要改为参数化形式，第一个参数storageId可正常参数化，但第二个与retentionDaysValue相关的INTERVAL参数始终报错（错误码42601：syntax error at or near "$2"）。尝试多种写法都无法解决，求正确的参数化语法。

原有非参数化代码 #

// storageConfiguration 和 retentionPolicy 是自定义模型
string storageId = storageConfiguration.StorageId;
int retentionDays = retentionPolicy.Retention;

// 判断"day"是否加复数后缀
string dayOrDays = $"day{(retentionDays > 1 ? "s" : string.Empty)}";

// 生成类似"9 days"的字符串
string retentionDaysValue = $"{retentionDays} {dayOrDays}";
string command = $"SELECT add_retention_policy('{storageId}', INTERVAL '{retentionDaysValue}');"

// 执行查询
await dbContext.Database.ExecuteSqlRawAsync(command);

尝试的参数化代码 #

string storageId = storageConfiguration.StorageId;
int retentionDays = retentionPolicy.Retention;
string dayOrDays = $"day{(retentionDays > 1 ? "s" : string.Empty)}";
string retentionDaysValue = $"{retentionDays} {dayOrDays}";
var storageIdParameter = new NpgsqlParameter{ParameterName = "storageId", Value = storageId};
var retentionDaysParameter = new NpgsqlParameter{ParameterName = "retentionDays", Value = retentionDaysValue};
string parameterizedCommand = "SELECT add_retention_policy(@storageId, INTERVAL @retentionDays);";
await dbContext.Database.ExecuteSqlRawAsync(parameterizedCommand, storageIdParameter, retentionDaysParameter);

报错信息：42601: syntax error at or near "$2"

其他尝试写法 #

// 将INTERVAL包含在参数内
string parameterizedCommand = "SELECT add_retention_policy(@storageId, @retentionDays);";

// 把"day"或"days"放在参数外，参数仅保留数字
string parameterizedCommand = $"SELECT add_retention_policy(@storageId, INTERVAL @retentionDays '{dayOrDays}');";

PostgreSQL的INTERVAL语法在参数化时不能直接跟在INTERVAL关键字后传字符串参数，以下是几种可行解决方案：

方案1：整数参数搭配单位计算 #

不需要处理单复数，PostgreSQL支持用整数乘以固定间隔单位的写法，自动适配语法：

string storageId = storageConfiguration.StorageId;
int retentionDays = retentionPolicy.Retention;

var parameters = new[]
{
    new NpgsqlParameter("@storageId", storageId),
    new NpgsqlParameter("@days", retentionDays)
};

string command = "SELECT add_retention_policy(@storageId, @days * INTERVAL '1 day');";
await dbContext.Database.ExecuteSqlRawAsync(command, parameters);

方案2：传递TimeSpan类型参数 #

利用Npgsql的类型映射，直接将天数转为TimeSpan，数据库会自动识别为INTERVAL类型：

string storageId = storageConfiguration.StorageId;
int retentionDays = retentionPolicy.Retention;

var parameters = new[]
{
    new NpgsqlParameter("@storageId", storageId),
    new NpgsqlParameter("@retentionInterval", TimeSpan.FromDays(retentionDays))
};

string command = "SELECT add_retention_policy(@storageId, @retentionInterval);";
await dbContext.Database.ExecuteSqlRawAsync(command, parameters);

方案3：CAST字符串参数为INTERVAL #

如果要保留字符串形式的间隔描述，可通过CAST语法显式转换参数类型：

string storageId = storageConfiguration.StorageId;
int retentionDays = retentionPolicy.Retention;
string dayOrDays = $"day{(retentionDays > 1 ? "s" : string.Empty)}";
string retentionDaysValue = $"{retentionDays} {dayOrDays}";

var parameters = new[]
{
    new NpgsqlParameter("@storageId", storageId),
    new NpgsqlParameter("@retentionStr", retentionDaysValue)
};

string command = "SELECT add_retention_policy(@storageId, CAST(@retentionStr AS INTERVAL));";
await dbContext.Database.ExecuteSqlRawAsync(command, parameters);

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