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
– prinkpan, Commented Aug 31, 2019 at 2: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
– prinkpan, Commented Aug 31, 2019 at 3:02
Yup, doing that right away with right explanation for others who come here. — prinkpan
– prinkpan, Commented Aug 31, 2019 at 3:22

prinkpan · Accepted Answer · 2019-08-31 03:34:40Z

4

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.

answered Aug 31, 2019 at 3:34

prinkpan

2,2661 gold badge23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Browne - Microsoft Over a year ago

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 learn.microsoft.com/en-us/ef/core/querying/raw-sql

Collectives™ on Stack Overflow

EC Core ExecuteSqlCommandAsync Truncate with Param Doesn't Work

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related