Syntax to call a parameterized function in Entity Framework with one of the parameter being INTERVAL

Question

I have a call in Entity Framework like this:

// storageConfiguration and retentionPolicy are some models
string storageId = storageConfiguration.StorageId;
int retentionDays = retentionPolicy.Retention;

// This determines if "day" takes a 's' or no
string dayOrDays = $"day{(retentionDays > 1 ? "s" : string.Empty)}";

// This gives a result like "9 days"
string retentionDaysValue = $"{retentionDays} {dayOrDays}";
string command = $"SELECT add_retention_policy('{storageId}', INTERVAL '{retentionDaysValue}');"

// Now the query is executed
await dbContext.Database.ExecuteSqlRawAsync(command);

This code works without problems. Now I want to parameterize this call. The first parameter, storageId, seems to work correctly, but I'm unable to find the correct syntax for the second one, retentionDaysValue.

I tried multiple variations on this:

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);

I get an exception when executing:

42601: syntax error at or near "$2"

which as I understand it means the second parameter is in error.

Some of the variations I tried:

// INTERVAL included in the parameter
string parameterizedCommand = "SELECT add_retention_policy(@storageId, @retentionDays);";

// "day" or "days" outside the parameter where "retentionDays" contains only a number
string parameterizedCommand = $"SELECT add_retention_policy(@storageId, INTERVAL @retentionDays '{dayOrDays}');";

etc.

What would be the correct syntax to use in this case if I want to parameterize the retentionDaysValue component of the call?

Answer 1

You seem to be under a misapprehension that parameterization does some kind of find/replace. It does not. It uses placeholders as actual values, which you bind values to.

So the syntax INTERVAL 'some interval here' is only for literal values, not for parameters. Just use the parameter name instead.

Also, the parameter should be passed as TimeSpan, not int, and you should ideally specify the parameter type exelicitly.

var storageIdParameter = new NpgsqlParameter("@storageId", NpgsqlDbType.Varchar, 50) { Value = storageConfiguration.StorageId };
var retentionDaysParameter = new NpgsqlParameter("@retentionDays", NpgsqlDbType.Interval) { Value = TimeSpan.FromDays(retentionPolicy.Retention) };
const string parameterizedCommand = @"
SELECT add_retention_policy(@storageId, @retentionDays);
";
await dbContext.Database.ExecuteSqlRawAsync(parameterizedCommand, storageIdParameter, retentionDaysParameter);

Note that ExecuteSqlRawAsync does not return the result from that function, it returns the number of rows affected. You probably want SqlQueryRaw<int>(..).FirstAsync instead