EC Core ExecuteSqlCommandAsync Truncate with Param Doesn't Work

Question

I have seen this thread: How to pass parameters to the DbContext.Database.ExecuteSqlCommand method?

I have a feeling EF Core is preventing me from using a Parameterized query to truncate tables in an Azure Sql Database from working.

I have tried:

var tableName = csvEntity.FileName.Replace(".csv", string.Empty);
var tableNameParam = new SqlParameter("@TableName", tableName);
await DbContext.Database.ExecuteSqlCommandAsync("TRUNCATE TABLE @TableName", tableNameParam);

And:

await DbContext.Database.ExecuteSqlCommandAsync("TRUNCATE TABLE @{0}", tableNameParam);

And:

await DbContext.Database.ExecuteSqlCommandAsync($"TRUNCATE TABLE {tableName}");

And:

await DbContext.Database.ExecuteSqlCommandAsync("TRUNCATE TABLE {tableName}", tableName);

But all result in some variation of:

Error: Incorrect syntax near '@TableName'.

BUT if I run

await DbContext.Database.ExecuteSqlCommandAsync("TRUNCATE TABLE AllStarFull");

We're all good!

Can you not use a variable as a table name in a truncate statement with ExecuteSqlCommandAsync

Some screen shots:

Exception:

3rd Attempt Before Exception

3rd Attempt Exception

No, parameters cannot be used to pass table names. Saying that I wonder why your `$"TRUNCATE TABLE {tableName}"` is not working. Are you sure you wrote it exactly as shown here? Can you show us what is your `tableName` variable when you tried this? — prinkpan, Aug 31 '19 at 02:49
The screenshot shows your first attempt. Could you please show me the third attempt in your question. One where you used `await DbContext.Database.ExecuteSqlCommandAsync($"TRUNCATE TABLE {tableName}");` — prinkpan, Aug 31 '19 at 03:02
Ok, I guess this may be happening because when you pass the SQL to EF it is dynamic. Can you please try `var sqlQuery = $"TRUNCATE TABLE {tableName}"` and then use `await DbContext.Database.ExecuteSqlCommandAsync(SqlQuery);` — prinkpan, Aug 31 '19 at 03:16
Yup, that did it. Your first comment is the real answer. But the work around is bonus! Thanks!! — mwilson, Aug 31 '19 at 03:20
@PriyankPanchal If you can put all that into an answer, I'm happy to accept it! — mwilson, Aug 31 '19 at 03:21
Yup, doing that right away with right explanation for others who come here. — prinkpan, Aug 31 '19 at 03:22

score 3 · Accepted Answer · answered Aug 31 '19 at 03:34

Your first attempt

await DbContext.Database.ExecuteSqlCommandAsync("TRUNCATE TABLE @TableName", tableNameParam);

is not working because table name cannot be passed as a parameter to the SQL statement.

Your second attempt

await DbContext.Database.ExecuteSqlCommandAsync("TRUNCATE TABLE @{0}", tableNameParam);

is not working because replacing {0} needs you to use string.Format

Your third attempt

await DbContext.Database.ExecuteSqlCommandAsync($"TRUNCATE TABLE {tableName}");

is not working because Entity Framework needs a constant string as raw SQL.

Your fourth attempt

await DbContext.Database.ExecuteSqlCommandAsync("TRUNCATE TABLE {tableName}", tableName);

is not working because ExecuteSqlCommandAsync cannot map parameters correctly.

To solve this, we can use your third attempt a little differently as shown below.

var sqlQuery = $"TRUNCATE TABLE {tableName}";
await DbContext.Database.ExecuteSqlCommandAsync(sqlQuery);

This ensures that when the query is passed to Entity Framework, it knows exactly what is required.

There's a bit missing from the explanation. EF Core will silently replace interpolated strings with parameterized queries, if you put the format string directly in the raw SQL methods. That's why you have to build the string on a seperate line here. See https://learn.microsoft.com/en-us/ef/core/querying/raw-sql — David Browne - Microsoft, Aug 31 '19 at 15:46

EC Core ExecuteSqlCommandAsync Truncate with Param Doesn't Work

1 Answers1