How to set ExecuteSqlCommand with Table variable

Question

I have a query I would like to run via C# application. There is no option to do this outside of the application. I have the following code:

var keyGroupsToCleanUp = new List<string>
{
    "Address",
    "Manufacturer",
    "Product",
    "Customer",
    "Picture",
    "Category",
    "Vendor",
    "SS_A_Attachment",
    "SS_A_AttachmentDownload",
    "SS_MAP_EntityMapping",
    "SS_MAP_EntityWidgetMapping",
};

foreach (var keyGroup in keyGroupsToCleanUp)
{
    _databaseFacade.ExecuteSqlCommand($@"
    DELETE
    FROM GenericAttribute
    WHERE KeyGroup = {keyGroup} AND [Key] = 'CommonId'
    AND EntityId NOT IN (SELECT Id FROM [{keyGroup}]);
    ");
}

I want to loop through each name in the List and run the below query for each of them. When I try to do this, I receive the following error:

System.Data.SqlClient.SqlException (0x80131904): Invalid object name '@p1'.

From what I have gathered after searching online, this is because a Table name cannot be a string. You have to declare a variable and use this variable for the table name. I learned that a Table variable has columns that need to be declared and felt a wave of dread wash over me. None of these tables have the same column structure.

Is what I am trying to do possible? If so, how can I do it?

---

The GenericAttributes table is one large table that consists of six columns.

When I joined the project that this is being used on it had already been used to the point where it was irreplacable. You can save additional data for a database table in here by specifying the KeyGroup as the Database table. We have a table called "Address" and we save additional data in the GenericAttributes table for the Address (It does not make sense, I know). This causes a lot of issues because a relational database is not meant for this. The query I have written above looks for rows in the GenericAttributes Table that are now detached. For example, the EntityId 0 does not exist as an Id in Address, so it would be returned here. That row must then be deleted, because it is linked to a non-existant entityId.

This is an example of a query that would achieve that:

// Address
_databaseFacade.ExecuteSqlCommand(@"
DELETE
FROM GenericAttribute
WHERE KeyGroup = 'Address' AND [Key] = 'CommonId'
AND EntityId NOT IN (SELECT Id FROM [Address]);
");

I have to do this for 11 tables, so I wanted to make it a bit easier to do. Every query is written in the same way. The only thing that changes is the KeyGroup and the table that it looks for. These will both always have the same name.

Here is an example of another call for Products. They are the same, the only difference is the KeyGroup and the Table in the NOT IN statement.

// Product
_databaseFacade.ExecuteSqlCommand(@"
DELETE
FROM GenericAttribute
WHERE KeyGroup = 'Product' AND [Key] = 'CommonId'
AND EntityId NOT IN (SELECT Id FROM Product);
");

Answer 1

At a guess, you're using Entity Framework Core. The ExecuteSqlCommand method accepts a FormattableString, and converts any placeholders into command parameters. But your placeholders appear to be column/table names, which cannot be passed as parameters.

Since there's also an overload which accepts a string, which has different behaviour, this method has been marked as obsolete, and replaced by ExecuteSqlInterpolated and ExecuteSqlRaw.

Assuming none of your values can be influenced by the user, and you're happy that you're not going to introduce a SQL Injection vulnerability, you can use ExecuteSqlRaw instead:

_databaseFacade.ExecuteSqlRaw($@"
    DELETE
    FROM GenericAttribute
    WHERE KeyGroup = [{keyGroup}] AND [Key] = 'CommonId'
    AND EntityId NOT IN (SELECT Id FROM [{keyGroup}]);
    ");

Answer 2

To ensure there is no injection vulnerability, you can use dynamic SQL with QUOTENAME

_databaseFacade.ExecuteSqlRaw(@"
DECLARE @sql nvarchar(max) = N'
    DELETE
    FROM GenericAttribute
    WHERE KeyGroup = @keyGroup AND [Key] = ''CommonId''
    AND EntityId NOT IN (SELECT Id FROM ' + {0} + ');
';

EXEC sp_executesql @sql,
    N'@keyGroup nvarchar(100)',
    @keyGroup = {0};
    ", keyGroup);

Note how ExecuteSqlRaw will interpolate the string. Do not interpolate it yourself with $

Answer 3

Try following:

foreach (var keyGroup in keyGroupsToCleanUp)
{
    var sql = @"DELETE FROM GenericAttribute
                WHERE KeyGroup = @Group
                AND [Key] = 'CommonId'
                AND EntityId NOT IN (SELECT Id FROM @Group)"; // Or [@Group], depends on schema

    _databaseFacade.ExecuteSqlCommand(
        sql,
        new SqlParameter("@Group", keyGroup));

This code assumes, that ExecuteSqlCommand in your facade follows standard Microsoft pattern (same overrides as Microsoft's ones).