Efcore: DELETE FROM <table name> does not work as expected in EF Core when using the Microsoft.Data.Sqlite driver

[sethjackson]

To Reproduce

Call context.Database.ExecuteSqlInterpolated($"DELETE FROM {tableName}") or call context.Database.ExecuteSqlRaw("DELETE FROM {0}", tableName).

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

public class Program
{
    public static void Main(string[] args)
    {
        var host = CreateHostBuilder(args).Build();

        using (var scope = host.Services.CreateScope())
        {
            var services = scope.ServiceProvider;

            try
            {
                var context = services.GetRequiredService<ApplicationContext>();
                DbInitializer.Initialize(context);
            }
            catch (Exception e)
            {
                var logger = services.GetRequiredService<ILogger<Program>>();
                logger.LogError(e, "An error occurred while seeding the database.");
            }
        }

        host.Run();
    }

    public static IHostBuilder CreateHostBuilder(string[] args) =>
        Host.CreateDefaultBuilder(args)
            .UseWindowsService()
            .ConfigureServices((hostContext, services) =>
            {
                services.AddDbContext<ApplicationContext>(options =>
                    options.UseSqlite(hostContext.Configuration.GetConnectionString("DefaultConnection")));
            });
}

using Microsoft.EntityFrameworkCore;

public class ApplicationContext : DbContext
{
    public ApplicationContext(DbContextOptions<ApplicationContext> options)
        : base(options)
    {
    }

    public DbSet<Post> Posts { get; set; }
}

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Post
{
    [Key]
    public int Id { get; set; }
    [Required]
    public string Title { get; set; }
}

using Microsoft.EntityFrameworkCore;

public static class DbInitializer
{
    public static void Initialize(ApplicationContext context)
    {
        context.Database.EnsureCreated();

        var entity = context.Model.FindEntityType(typeof(Post));

        if (entity != null)
        {
            var tableName = entity.GetTableName();

            context.Database.ExecuteSqlInterpolated($"DELETE FROM {tableName}");
        }
    }
}

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (1ms) [Parameters=[@p0='?' (Size = 5)], CommandType='Text', CommandTimeout='30']
      DELETE FROM @p0
fail: Program[0]
      An error occurred while seeding the database.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "@p0": syntax error'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameter
Object)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, Stri
ng sql, IEnumerable`1 parameters)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, Stri
ng sql, Object[] parameters)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlInterpolated(DatabaseFacade databaseFac
ade, FormattableString sql)
   at DbInitializer.Initialize(ApplicationContext context) in <file>:line 15
   at Program.Main(String[] args) in <file>:line 21

This does not work either:

context.Database.ExecuteSqlRaw("DELETE FROM {0}", tableName);

However this does:

context.Database.ExecuteSqlRaw("DELETE FROM Posts");

Additional context

Microsoft.EntityFramework.Core version: 3.0.0
Microsoft.Data.Sqlite version: 3.0.0
Target framework: .NET Core 3.0
Operating system: Windows 10

[bricelam]

Use string concatenation. SQLite doesn't support parameter reference tokens in that part of the SQL.

[bricelam]

 context.Database.ExecuteSqlRaw($"DELETE FROM \"{tableName.Replace("\"", "\"\"")}\"");

[sethjackson]

Ok so I can't use:

context.Database.ExecuteSqlInterpolated($"...")

then?

Is there some way for the driver to let the end user know that?
I was not sure what to do there since from what I was reading in the EF Core docs that call should work.

Also this is a limitation of SQLite itself correct?

[bricelam]

It’s a SQL syntax error. We don’t parse SQL in EF Core or ADO.NET so that super unhelpful error coming directly from SQLite is the best we can do. Their errors are notoriously vague and unhelpful ☹️

[sethjackson]

Ah ok. That makes sense.

Thanks so much for your help!! I was able to get it working with the string concatenation snippet. :)