How to set ExecuteSqlCommand with Table variable-CodePudding

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?

CodePudding user response：

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).

CodePudding user response：

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}]);
    ");