System.Data.SqlClient.SqlException: 'Incorrect syntax near '@p0'.'

Question

I have an exception for the follow code, which seem fine to me, but just don't know why it throws an exception like this. And what does it mean

'Incorrect syntax near '@p0'.'

public static void ClearData(StoreContext context)
    {
        ExecuteDeleteSQL(context, "Categories");
        ExecuteDeleteSQL(context, "Customers");
        ResetIdentity(context);
    }

public static void ExecuteDeleteSQL(StoreContext context, string tableName)
    {
        context.Database.ExecuteSqlCommand($"Delete from BrooksStore.{tableName}");
    }

public static void ResetIdentity(StoreContext context)
    {
        var tables = new[] { "Categories", "Customers", "OrderDetails", "Orders", "Products", "ShoppingCartRecords" };
        foreach (var itm in tables)
        {
            context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT (\"BrooksStore.{itm}\", RESEED, -1);");
        }
    }

Let me know if you need more information about my project. Can anyone help me out? Many thanks.

Answer 1

I assume you're using EF Core 2, since that's where I encountered the same issue and it is identified as a breaking change for EFCore2.

The issue is that EFCore is trying to convert your string interpolation into a parametrized query, which is not allowed in that specific case.

Simple solution: Cast your interpolated string to a string.

context.Database.ExecuteSqlCommand((string)$"Delete from BrooksStore.{tableName}");

More details can be found here: https://github.com/aspnet/EntityFrameworkCore/issues/9734

When we updated EF Core to use string interpolation to do parameterization we recognized this as a breaking change for code that was already using it to do things other than parameterization. The guidance going forward is to not let EF do it's parameterization in this case, possibly by casting to string like you show. However, be careful that this does not open your code to SQL injection attacks by making sure that all data is correctly escaped, especially when coming from user input.

Answer 2

I try this. It works. You should check other things.

static void Main(string[] args)
        {
            using (HYBRIDM5_MeyerEntities context=new HYBRIDM5_MeyerEntities())
            {
                ExecuteDeleteSQL(context, "Sicil");
            }
        }

        public static void ExecuteDeleteSQL(HYBRIDM5_MeyerEntities context, string tableName)
        {
            context.Database.ExecuteSqlCommand($"Delete from dbo.{tableName}");
        }