Search code examples
entity-framework-coresqlexception

Entity Framework Core ExecuteSqlInterpolated gives Microsoft.Data.SqlClient.SqlException

I'm looping over a bunch of tables and have to delete records in each of them having a specific column name. I was able to get that list but the following line gives me the exception: Microsoft.Data.SqlClient.SqlException (0x80131904): Must declare the table variable "@p0".

foreach (var dsw in deleteSwModels)
{
    contextCtx.Database.ExecuteSqlInterpolated($"DELETE FROM {dsw.Name} WHERE DeleteSw = 1");
}

The property Name looks like Person.Address where Person is the schema name under which the table is placed.

The version of Entity Framework Core is 3.1.8

Solution

  • I went digging further into the exception details and in the source code at Github ef core.

    I solved the problem by making use of the following statement instead:

    contextCtx.Database.ExecuteSqlRaw($"DELETE FROM {dsw.Name} WHERE DeleteSw = 1");

    I hope it helps someone else in the future.