Admin Tools

Purge Deleted Entities Rfc

This is a SUPER-USER tool and requires good understanding of the database and models. Please use with caution!

All entities (except for the auto generated JOIN tables) have a IsDeleted flag to implement the soft delete functionality. If the flag is set to true, the entity is no longer displayed in the system but it still exists in the database and in term all JOIN tables still have records referencing it. Also if the entity has foreign key references to it from other active or soft deleted entities, those references are not broken, the Id still being valid. In order to free up space in the database you need to purge the deleted entities that you no longer need.

A functionality that forbids deletion of referenced entities can’t be implemented due to the distributed nature of the system, for example if you soft delete an entity from a base application, plug-ins can’t prevent it being deleted because the base application doesn’t know who references it outside its scope. This is also valid for references between plug-ins. You use this functionality when too much garbage accumulates you need to clean up the database.

The functionality presented here purges (hard delete) ALL entities that are soft deleted (with the IsDeleted flag set).

WARNING: Only use this on entities that you don’t need to restore at any time.

WARNING: Executing this program will permanently delete ALL the entries whose IsDeleted flag is set, from the database.

How it works

Enter in the EntityTypeName field the type name of the entity you want to scan for soft deleted entities and click Next. If you selected the RemoveJoinTableReferences checkbox, then the records referencing our entity’s primary key will also be reported, that is, only if the correct schema, table name and foreign key field name are entered in their respective fields.

If the entity is found, the read-only fields FullTypeName, Schema and Table will show the found entity. Please check the fields before proceeding further to avoid any errors.

The system will look only for entities that are soft deleted and return a message with the number of records that will be permanently removed (hard deleted). If you click Execute the system might permanently delete the records from the database.

If any of the record’s primary key is used as a foreign key, the database will not allow you to delete those records.

In order to proceed further, you first need to remove the records containing the references to our entities primary keys.

WARNING: When looking for records referencing the primary key of our entity, ALL the records are scanned, soft deleted or not.

Removing join table entanglement

If any database table has foreign keys referencing our entity and the entities are still referenced, you won’t be able to delete the entities until the referencing entities either stop referencing  deleted entities or referencing entities are hard deleted also.

When receiving an error such as the one in the image, you need to copy the name of the referencing table in the JoinTableName field (“BarCode” in this example), the name of the foreign key field in the referencing table in the JoinForeignKeyColumnName field (“ProductDefinitionId” in this example – see the last part of the foreign key constraint name), the schema name of the referencing table in the JoinTableSchemaName field (usually the same schema as the main entity or the referencing entity, depending on the table type).

Usually the foreign key name one of the following:

  • ChildrenId (for one to one relationship between entities of the same type)
  • ParentId (for one to one relationship between entities of the same type)
  • ChildrensId (for many to many relationships between entities of the same type)
  • ParentsId (for many to many relationships between entities of the same type)
  • <main entity name>Id (when we have a reference to another entity and the naming conventions are respected)

It is possible that more than one entity references your entity. You need to repeat this steps for all the referencing entities in order to remove the references and allow the full hard deletion.

It is also possible that entities that are not soft deleted still have references to some soft deleted entities. In that case you either need to soft delete those entities in order for them to be reported by this functionality or remove the reference manually (delete or change the foreign key referencing our entity). To soft delete the referencing entities, use the provided data access functionality and delete the entity. After deleting it it should appear in the JOIN references.

Entanglement with active entities

If soft deleted entities are still referenced by active entities, and those active entities are also referenced, you won’t be able to use this functionality. If the active entities are not referenced, you could remove them like JOIN table entries.

In order to find the entities referencing soft deleted entities you need to run FindReferencesToDeletedRecordsRfc then remove them manually.

The report will try to find details about the referencing records on the Name, Description and Note columns, if they exist in the referencing data type.

The ParentId is the referencing entity’s Id (the still active entity) and the ChildId is the Id of the soft deleted entity.

What this doesn’t do

If you need to filter and only delete some of the entities you need to implement your own RFC. This only hard deleted ALL the soft deleted entities.

Due to the fact that the soft delete feature is implemented in the base kernel and because this method must be relatively generic to allow access to all entities, you can’t bypass it by using the IgnoreQueryFilters() method. This functionality uses direct access to the database by calling DbContext.Database.ExecuteSqlRawAsync() or the DbContext.Database.GetDbConnection().CreateCommand() and requires some SQL knowledge.