Truncate table feature

[GSPP]

It would be useful if Entity Framework supported truncating tables. The feature would consist of the following:

1. There's a Truncate method for each table.
2. The store decides how to execute that. When available, TRUNCATE TABLE seems like a good choice.
3. All currently loaded entities from that set are marked deleted.
4. There must be a way to feature-detect whether truncate support is available.
5. Maybe there should be a way to control whether TRUNCATE or DELETE is being used. DELETE works in more cases and it does not reset the IDENTITY counter. TRUNCATE is not just a faster way of doing the same thing. On the other hand TRUNCATE could be used in conjunction with CHECKIDENT(RESEED) to preserve the IDENTITY value. Maybe that should be an option as well. I think it's important that this feature behaves identically across stores by default. So maybe the default should be to preserve the identity value.
6. Maybe child tables should be automatically truncated so that a whole graph of tables can be truncated? This makes the feature much more difficult. The parent table can only be truncated after removing foreign keys.
7. I propose the following options:
   • enum TruncateCommand { Automatic, ForceTruncate, ForceDelete }
   • enum TruncateIdentityTreatment { StoreControlled, PreserveIdentity, ResetIdentity }. StoreDecided is the fastest option that does whatever the store does by default (SQL Server will reset).

This feature would be better than just executing SQL manually because:

1. It's cross-platform by virtue of the EF store abstraction.
2. It's more typesafe and refactoring-friendly.
3. It updates the entity state of loaded entities.
4. It's less work.

[Vasim-DigitalNexus]

This would be useful, however, with foreign key constraints you may be better of Dropping the tables and recreating them than Truncate/Delete

[GSPP]

@Vasimovic recreating a table is really hard programmatically (that's why the SMO scripting options are so full of bugs - really hard). Disabling and reenabling FKs is a strategy that is more viable.

It's still a bit problematic because it requires DDL rights and takes on really invasive locks.

I would consider a "tree truncate" EF feature to be very useful. Maybe a volunteer can take this ticket on? It would take diligence to precisely solve all these issues but I see nothing impossible about it.

[Vasim-DigitalNexus]

@GSPP Yes, I agree, this is hard programmatically; either way this would be very useful feature to have

[gdoron]

IMHO, ORM Should not have a TRUNCATE API (or any other DDL API, except if it's in a migration).
If you really need to truncate a table, you can always use SqlCommand.

[roji]

@gdoron .NET collections have Clear(), which seems to correspond to TRUNCATE somewhat; this would be in line EFCore's policy of treating DbSets as regular IEnumerables with Linq.

But the more advanced/tweakable behavior (preserve/reset identity, cascading truncate) is something else and may be too complicated/tweakable for a general implementation in EFCore.

[gdoron]

@roji Clear doesn't sound like truncate table.
Had I had to guess, I would have said it's clearing the tracked entities.

Truncate is a risky feature and as such should be as explicit as possible, not to say there is much more to it than just deleting the rows, like the OP mentioned, dependencies, identity etc'.

[GSPP]

What is so risky about that feature? Are you concerned about data loss? I don't foresee a developer accidentally calling truncate on a production table anymore than he would destroy data accidentally in other ways (mySet.RemoveAll(mySet.AsEnumerable())). It's quite clear that "Truncate" means "all gone" :)

If you are concerned about data loss you probably don't like any bulk API as part of EF either. I don't think many people would share this risk assessment. It's really hard to imagine a misuse of a truncate API.

I would really like EF to expose more database power in general (such as query hints). EF is not meant to remove database features. It's supposed to make them easier to access and to remove cross-plat differences.

[ilmax]

I completely agree with @gdoron, the truncate statement should not be made a first class citizen of EF, please note that truncate is a DDL statement (at least in oracle dialect) and so it will implicitly perform a commit on the current transaction.

[GSPP]

@ilmax maybe you should explain your reasoning. Just expressing disagreement does not move the discussion forward. I have invited gdordon already to do the same.

[roji]

@gdoron, .NET Collection.Clear() "removes all elements from the collection". This seems to correspond quite well to truncating a table, and one of the major points of EF is to expose database operations as .NET native programming constructs (i.e. LINQ). Having said that I don't think the naming here is extremely important, if there's a consensus that "truncate" expresses things better then why not.

I just looked into it and it seems that the SQL:2008 does include a truncate command, including the reset/preserve identity features. It's unclear whether the cascading feature is also part of the standard. Here's the PostgreSQL docs on TRUNCATE including some info on the standard

I'm not sure why an ORM shouldn't have a truncate API. At least to my mind, truncate doesn't involve a schema change and is logically equivalent to delete, so calling it DDL is odd. Note that in PostgreSQL truncate is transaction-safe - it doesn't trigger a commit and can even be rolled back. So if this is a standard SQL feature, why not make it available to users in an ORM?

On a last note, I'm against the suggestion to allow the user to control whether DELETE or TRUNCATE is used. This is an internal concern of the specific database provider, and in any case, if the user wants to do a delete they can always simply simply call Remove on the DbSet. However, a provider for a database which doesn't support TRUNCATE natively could implement the API via a DELETE (but would throw a NotSupportedException if identity reset is requested, etc).

[gdoron]

@GSPP I don't think EF should have database hints either... just like I don't want an API for disabling/enabling FK.

All these features are advanced and things that IMHO if you need them, you should take control and use raw SQL.

If it's not a typical CRUD operation (and I don't consider TRUNCATE a CRUD operation, it's much more than DELETE Foo), I will do it myself.

BTW, I have never had the need to truncate a table as part of an application.

[gdoron]

@roji I have only vague memories from Oracle (last time I used it was in the army), but if I'm not mistaken, TRUNCATE is in fact DDL on ORACLE.
You can't rollback TRUNCATE nor use the built-in snapshot for recovering.
Just like all schema changes on Oracle.

Regardless, it's so uncommon to use TRUNCATE as part of an application, that I'm surprised it was even suggested.
Next thing is having an API for partitioning?

[roji]

@gdoron can you provide a bit more reasoning? Sure, truncate is rare, and I definitely wouldn't prioritize this very high in the list, but why be against it? It seems to be well-defined and can be useful in some applications, even if you specifically haven't run into a scenario needing it.

I still don't understand how truncate is DDL. So what if Oracle can't roll it back? Does un-rollback-ability mean that something is DDL? DDL is about defining your database schema, which truncate does not do. And as I said above, PostgreSQL does happen to allow rolling back truncate.

IMHO a good guideline for covering a feature in EFCore is whether it exists in the SQL standard (or is widely-supported across databases). If it's well-defined and portable across databases, why not support it?

[gdoron]

@roji my reasoning is simple, if it is not a simple CRUD operation, it's not an ORM feature.
ORM is not a DB managing tool.

EF is an ORM and a Migrator tool, so this is why I wrote several comments above

ORM Should not have a TRUNCATE API (or any other DDL API, except if it's in a migration).

Anyway, there's no right and wrong here, it's matter of what is the scope MS wants EF to have.
And by the way, there are so many non crucial ORM features in EF Core that many trivial ORM features weren't implemented at all (VIEWS, SP etc.) or were not optimized (JOINS, async operations etc.).
It's all about of what you're focusing on.

[roji]

@gdoron you still haven't explained why TRUNCATE is a DDL API rather than a data manipulation operation, logically similar to DELETE, At its most basic form truncate simply deletes all records from a database. Arguments such as rollback or Oracle support don't seem very relevant here.

But I do agree with you that there are much more important features missing at this point, and this is a low-priority feature request. I guess MS will have to make a decision whether they think this belongs or not.

[ilmax]

@GSPP yes, sorry if my prev comments look surly, that was not intentional.

Once again @gdoron comment sum up perfectly my pov.

In EF there is a nice separation between DDL that are handled by migrations and DML that are handled by the update pipeline.

The truncate statement is a tricky one because it looks very much like a DML command, in fact it deletes all table rows, but is declared as DDL in Oracle and Sql Server too and thus needs special grants

Just to sum up

• Sql server allows rollback on truncate, needs special grants
• Oracle disallow rollbacks after truncate, needs special grants
• SQLite not supported
• PostrgreSQL allows rollback, special grants?

So it's quite challenging to implement it in EF and make it work across providers consistently.

ATM we can already fire a truncate command to the dB via ADO, so IMHO there are a bunch of ORM critical feature to implement before truncate - and given the different implementations by dB providers I think it's not worth the effort.

[roji]

It feels like I'm repeating myself, but I really wish you guys would stop saying "truncate is DDL" without explaining what you mean by that (and I don't really care how Oracle/SqlServer declare things). Again, logically truncate deletes rows and doesn't touch schema, so it seems like DML to me. Anything else - rollback, transactions, special rights - doesn't seem relevant. I'd just appreciate a response to that.

The fact that a feature isn't supported by a provider isn't very important. PostgreSQL doesn't support computed columns or nested transactions, SQLite doesn't support a lot of things. The point of EFCore isn't to implement the lowest common denominator of databases - it's allowed for a database provider to not support features.

It's also OK for providers to vary on behavior (i.e. rollback after truncate). PostgreSQL and SqlServer have different date/time precision and range, this doesn't mean that date/time isn't supported in EFCore.

Finally, special grants really aren't the concern of EFCore. AFAIK if I create my database with EFCore I'm also the owner of that database and can do what I want with it (this is at least the situation with PostgreSQL). Of course truncate rights can be revoked, but so can rights to modify data - so that doesn't seem like a very relevant argument.

This really isn't a complicated feature: just a strongly-typed way of issuing truncate commands to the database and clearing all tracked entities. If you're saying it's a low-priority without much value, then I actually agree. If you have an argument against the feature, that's something else entirely.

[gdoron]

@roji

It feels like I'm repeating myself, but I really wish you guys would stop saying "truncate is DDL" without explaining what you mean by that (and I don't really care how Oracle/SqlServer declare things)

We mean it's DDL, because the DB providers declare it as such...
Now, you can surely ask WHY Oracle and MS-SQL declare TRUNCATE as DDL and not DML, but that doesn't affect the fact they call it DDL.

This discussion is a bit out of scope, but googling showed me this which says almost the same thing as what we said.

[ilmax]

@roji truncate looks like a DML to me too, but that isn't the point of view of the database providers and (as you can point out from my prev comment links) truncate is declared as a DDL statement by the database providers (look at the sql server documentation page) so it's not DDL because of my definition.

Again, logically truncate deletes rows and doesn't touch schema, so it seems like DML to me. Anything else - rollback, transactions, special rights - doesn't seem relevant. I'd just appreciate a response to that.

Have te ability to truncate a table that implicitly commits my transaction (at least in oracle) seems a lot relevant and dangerous to me

The fact that a feature isn't supported by a provider isn't very important.

Right, but I'm not sure ICollection.Clear is the best spot to attach the truncate feature and implement in a provider dependent fashion or not implement at all (SQLite does not support it).

Finally, special grants really aren't the concern of EFCore. AFAIK if I create my database with EFCore I'm also the owner of that database and can do what I want with it

True, but migrations is not required to use EF, you can attach to an already existing dB and thus you only need DML grants, truncate statement break this.

If you're saying it's a low-priority without much value, then I actually agree. If you have an argument against the feature, that's something else entirely.

Since truncate is just 'delete every data in this table' I'm a bit worried by the different database implementations.

It's my preference to not have truncate implemented at all, but in the case the community demand for this feature is high, I would prefer to implement some other feature that could potentially be useful to replacing truncate like for example DeleteBy(condition) prior to this.

[GSPP]

Maybe this is a vision issue. I don't see EF limited to what it does today. There is no inherent reason EF cannot automate more database tasks. There is no reason EF must be restricted to DQL and DML. Vision and scope are not fixed. So I consider that argument "artificial" for a better lack of a word. It's an artificial restriction that is assumed but not there.

[roji]

We mean it's DDL, because the DB providers declare it as such...
Now, you can surely ask WHY Oracle and MS-SQL declare TRUNCATE as DDL and not DML, but that doesn't affect the fact they call it DDL.

This discussion is a bit out of scope, but googling showed me this which says almost the same thing as what we said.

I have trouble following your logic... You say EFCore should only implement DML (except for migrations), and when I ask "but how do you define DML/DDL" you simply say "whatever the provider declares it to be". IMHO when debating whether to include features in EFCore or not, we should use more compelling arguments than the arbitrary way in which some specific provider declares things.

The link you include reinforces this: the Oracle DBA's response response to "why is truncate DDL" is some purely implementation detail which definitely shouldn't matter in a decision such as this (and again, doesn't apply to other databases). I truly don't care how Oracle/SqlServer implement this standard SQL feature, nor whether they choose to call it DDL, DML or something else.

@ilmax,

The fact that a feature isn't supported by a provider isn't very important.

Right, but I'm not sure ICollection.Clear is the best spot to attach the truncate feature and implement in a provider dependent fashion or not implement at all (SQLite does not support it).

That makes sense to me. The behavior variation between provides (i.e. implicit commit or not) is probably enough to at least call this Truncate() rather than Clear().

Finally, special grants really aren't the concern of EFCore. AFAIK if I create my database with EFCore I'm also the owner of that database and can do what I want with it

True, but migrations is not required to use EF, you can attach to an already existing dB and thus you only need DML grants, truncate statement break this.

But again, if you're attaching to an existing database you might not have rights to change data either (e.g. UPDATE)... Your rights for a given database may be anything at all, depending on how your DBA set things up, why is that an argument to disallow truncate specifically?

[rowanmiller]

If we did support TRUNCATE I think this feature would be part of Bulk Insert/Delete (#795). That feature would already give you the ability to issued a DELETE FROM <table> (with the changes applied in-memory too)... so it's not too much of a stretch to envisage the ability to use a TRUNCATE in place of a DELETE.

[rowanmiller]

Discussed at length as a team and concluded that we aren't planning to do this in the main runtime API. It seems that a truncate operation is intended to be used as part of schema management, therefore it may fit better into the migrations side of things. Either way, the truncate statement is very simple and can be easily called from the runtime API (context.Database.ExecuteSqlCommand("TRUNCATE ...")) or in a migration (Sql("TRUNCATE ...")).

There was discussion in the thread about EF being able to be smart about dropping related data etc. This would be a difficult problem to solve in a robust way, and not something our team is planning to tackle at this stage. It's worth noting that something like this would be a very good candidate for a third party extension to EF Core.

[GSPP]

Alright!

[borisdj]

EFCore.BulkExtensions now have Truncate method, it just encapsulates simple Sql TRUNCATE command.