What would be an efficient way to delete a set of records matching a condition in Server-side ESQ?
In the academy documentation, there is limited information about using the ESQ/ORM server-side methods to delete data, with only 2 or 3 toy examples. We need to efficiently delete large volumes of data using server-side ESQ in one of our processes, but we can't see anything about bulk deleting while still firing off any event-based triggers on the entity or generating change log records for it. Obviously the direct Terrasoft.Core.DB delete statement would be very efficient, but would skip the Creatio logic where needed.
We've been able to delete records by iterating over a collection of entities matching the required filtering condition retrieved by using GetEntityCollection and then for each entity in the collection, running a FetchFromDB on its Id before deleting it, but this seems like quite a few steps more than should be required and doesn't do it in a batched way.
Any help would be greatly appreciated.
Like
Hello Harvey,
You can use out-of-the-box examples of bulk deletion logic. For example we can take the BulkEmailTargetArchiver class and the DeleteArchiveLevel method in it:
private void DeleteArchiveLevel(string sourceSchemaName, string targetSchemaName) { int processedRecords; do { var deleteQuery = new Delete(_userConnection).From(sourceSchemaName) .Where("Id").In(new Select(_userConnection) .Top(_batchSize) .Column("s", "Id") .From(sourceSchemaName).As("s") .Where().Exists(new Select(_userConnection) .Column(Column.Parameter(1)) .From(targetSchemaName) .Where(targetSchemaName, "Id") .IsEqual("s", "Id"))); processedRecords = deleteQuery.Execute(_dbExecutor); } while (processedRecords != 0); }
This is just one example of the logic. You can use:
select * from SysSchemaSource
where Source like '%DeleteQuery%'
to get other schemas in the application configuration with delete queries examples and build your own one using all examples you may find in the system.
Hi Oleg,
using these Delete classes bypasses Creatio’s internal logic though, right? So change logs will be inaccurate and any BPs that should be triggered on delete would not be triggered etc. This seems risky to me as important processes could hang off this, and I don’t know what other internal aspects of Creatio might be relying on logic that Creatio manages at an application level.
Is there any way to bulk delete while respecting that?