Parameterized Raw SQL Update not working in EF Core

Question

I am trying to parameterize a SQL statement that I am executing with Entity Framework Core 6. The parameters are not working as expected. I expect all of these statements to work but they do not:

await _context.Database.ExecuteSqlInterpolatedAsync(
            $@"UPDATE Schema.Table 
                SET  {columnName}={Convert.ToByte(value)} 
                WHERE Id = {id}");

await _context.Database.ExecuteSqlRawAsync(
            @"UPDATE Schema.Table 
                SET  {0}={1} 
                WHERE Id= {2}", columnName, Convert.ToByte(value), id);

var p0 = new SqlParameter("@column", columnName);
var p1 = new SqlParameter("@value", Convert.ToByte(value));
var p2 = new SqlParameter("@id", id);
await _context.Database.ExecuteSqlRawAsync(
            @"UPDATE Schema.Table 
                SET  @column=@value 
                WHERE Id = @id", p0, p1, p2);

I did get it to work with these two queries but they are not properly parameterized:

await _context.Database.ExecuteSqlRawAsync(
            $@"UPDATE Schema.Table
                SET  {columnName}={{0}} 
                WHERE Id = {{1}}", Convert.ToByte(value), id);

var cmdUpdate = $"UPDATE Schema.Table " +
                $"SET  {columnName}={Convert.ToByte(value)} " +
                $"WHERE Id = {id}";
            await _context.Database.ExecuteSqlRawAsync(cmdUpdate);

The problem seems to be with parameterizing the column name but I can not find anything that says I can't do this. Is there a way to parameterize the column name?

Answer 1

I can not find anything that says I can't do this

But you won't find anything saying you can. That fact, along with your own testing, shows us that it's just not allowed. It has nothing to do with Entity Framework; it's a limitation in SQL Server. Entity Framework uses sp_executesql to execute queries with parameters, and parameters just won't work for column or table names. You're certainly not the first to ask.

As you found, you can use C# string interpolation, but be careful. If your columnName variable is taken from user input, it will open you up to SQL injection. So make sure it's a real column name before executing your query.