Efcore: ExecuteSqlInterpolated and ExecuteSqlInterpolatedAsync do not work with variable schema, table and property name

[TanvirArjel]

The following query does not work although interpolated sql string is valid:

await _dbContext.Database.ExecuteSqlInterpolatedAsync($"Delete From [{schema}].[{tableName}] Where {primaryKeyName} = {primaryKeyValue}"); // This does not work

and throws the following exception:

Invalid object name '@p0.@p1'.

If I execute the above interpolated string with ExecuteSqlRaw and ExecuteSqlRawAsync as follows it works gracefully:

var deleteCommand = $"Delete From [{schema}].[{tableName}] Where {primaryKeyName} = {primaryKeyValue}";
await _dbContext.Database.ExecuteSqlRawAsync(deleteCommand);  // This works

Am I missing anything in case of ExecuteSqlInterpolated and ExecuteSqlInterpolatedAsync and this is a just a critical bug?

[Shane32]

ExecuteSqlInterpolatedAsync replaces variables with SQL parameters to prevent SQL injection attacks. So the executed SQL would look something like the following.

Delete FROM [@p0].[@p1] Where @p2 = @p3

Since SQL does not allow variables to contain member names like the schema, tableName, or primaryKeyName, the above SQL is invalid. You would need to use ExecuteSqlRawAsync for your purpose, embedding the schema, tableName, and primaryKeyName directly into the SQL as shown in your example. The primaryKeyValue could be a parameter.

[ajcvickers]

@TanvirArjel When you say that "ExecuteSqlRaw and ExecuteSqlRawAsync as follows it works gracefully" are you aware that you're creating non-parameterized queries which are potentially susceptible to SQL injection attacks? Or are you mitigating this in some other way?

[TanvirArjel]

@ajcvickers

are you aware that you're creating non-parameterized queries which are potentially susceptible to SQL injection attacks?

Yes! I am fully aware of that. In my case it will not be a problem because only one value, primary key which is a long value is being taken from the user input. So there is no chance of sending sql injection string.

That's not the issue. Issue is why the following query does not work:

await _dbContext.Database.ExecuteSqlInterpolatedAsync($"Delete From [{schema}].[{tableName}] Where {primaryKeyName} = {primaryKeyValue}");

[ajcvickers]

@TanvirArjel Because, as @Shane32 pointed out, it's not valid SQL. The schema name and table name cannot be parameterized.

[TanvirArjel]

@ajcvickers That means the scope of ExecuteSqlInterpolated and ExecuteSqlInterpolatedAsync are very limited.

[ajcvickers]

@TanvirArjel Not sure exactly what you mean. It's limited by what T-SQL on SQL Server supports.

[acpt]

building a big update string, we cant split it on code....