SQLServer: ExecuteDelete without Where should translate to TRUNCATE TABLE

[bricelam]

Today, the following statement...

db.Entities.ExecuteDelete()

...translates to the following on SQL Server.

DELETE FROM [e]
FROM [Entities] AS [e]

But it would be more performant to use a truncate statement.

TRUNCATE TABLE [Entities]

[Bretttt]

Plenty of reasons why this is fundamentally different.
https://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sql.
Additionally, the contract would change because TRUNCATE does not return the number of deleted rows like the current API.
A bit of code if you want to handle this:

public static void Truncate<TEntity>(this DbSet<TEntity> dbSet)
    where TEntity : class
{
        DbContext dbContext = ((IInfrastructure<IServiceProvider>)dbSet).Instance.GetRequiredService<ICurrentDbContext>().Context;
        dbContext.Database.ExecuteSql($"TRUNCATE {dbSet.EntityType.GetSchemaQualifiedTableName()}");
}

public static Task TruncateAsync<TEntity>(this DbSet<TEntity> dbSet)
    where TEntity : class
{
    DbContext dbContext = ((IInfrastructure<IServiceProvider>)dbSet).Instance.GetRequiredService<ICurrentDbContext>().Context;
    return dbContext.Database.ExecuteSqlAsync($"TRUNCATE {dbSet.EntityType.GetSchemaQualifiedTableName()}");
}

[cincuranet]

There's simply too many differences between DELETE and TRUNCATE, so I don't think doing that silently for this specific case is a good idea. But there might be some value in having a method like ExecuteTruncate.

cc @maumar @roji

[roji]

Yeah, I tend to agree with @Bretttt and @cincuranet on this - IIRC there are some observable difference between the two. For example, unless I'm mistaken, identity columns are reset by TRUNCATE but not by DELETE... For cases where there are observable differences, it's probably better to also keep the .NET user-facing API distinct so that everything is clear and predictable, rather than having a weird thing where just adding/removing a Where clause suddenly leads to behavioral differences... @Bretttt's point about TRUNCATE not returning rows is also good and points in the direction of a separate API.

We can indeed add an API here, though this really is an extremely simply command to do via SQL TRUNCATE <table_name>. Sure, there's still some value in doing that for the user (and not forcing them to hardcode the table name), but there's also some API pollution cost of adding another method on DbSet, which very few people actually need... So I'm a bit on the fence here.

Note previous discussion on introducing TRUNCATE to EF in #5972.

[ErikEJ]

Also, TRUNCATE requires higher rights than READ or WRITE

[bricelam]

lol, clearly I’m not a SQL Server expert. I don’t think there’s much value in having a method for this. ExecuteSqlCommand is sufficient for cases where you can use it and need the perf.

Not returning the affected rows, requiring ALTER TABLE permissions, and not running DELETE triggers are definitely deal-breakers for the general case.

[roji]

@bricelam yeah, I agree... There's the slight advantage of not having to hardcode the table name (or get it from the model), but that doesn't seem worth it... As there seems to be consensus that there isn't enough value here, I'll go ahead and close for now.