alex-kulakov
commented
1 year ago Hello @ondrejtucny.
Currently, Bulk operations does not support hierarchies that contains more than one type represented in from of table. This is mostly because DELETE statement is for content of one table and this extension keeps it very simple. I believe there are clever ways to perform such operations, but it will no longer be 1 statement and if it is not than it can be performed partially due to some errors.
Then, it is time-consuming task to develop. Say we have three tables which correspond with entities: a Root, a Middle and a Leaf, where the Root is a hierarchy root entity, the Middle is direct ancestor of the Root and Leaf is direct ancestor of the Middle. Like so...
[HierarchyRoot]
public class Root : Entity
{
[Field, Key]
public long Id { get; private set; }
[Field]
public DateTime CreationDate { get; set; }
[Field]
public string Name { get; set; }
}
public class Middle : Root
{
[Field]
public string Description { get; set; }
[Field]
public double SomeCoefficient {get; set; }
}
public class Leaf : Middle
{
[Field]
public int PriorityIndex{ get; set; }
[Field]
public string PriorityIndexName { get; set; }
}Depending on the Hierarchy's inheritance schema, fields will be spread differently. Lets take the default inheritance schema - ClassTable, and see what happens. In this mode corresponding tables contains fields of PR and the fields that was declared in the type the table represents.
Say the query to delete rows looks like
session.Query.All<Root>().Where(r => r.CreationDate >= DateTime.UtcNow.AddDays(-5)).Delete();
Base query includes all roots, middles, and leaves, so the query in reality would affect three tables. We should also delete rows from Leaf then from Middle and then from Root. So, as I see it, we would need to select all the primary keys that were going to be deleted from Root because the field is in Root table. Then we would need to pass this list of PKs to DELETE statement for Leaf table, then one for the Middle table and finally delete them from Root table. This would be 4 queries.
In general case we would work with thousands or millions of rows so possibly we would need a temporary buffer for millions of PK values. Temporary tables might work as a buffer storage but not all of RDBMSs have this feature, so we would need to load PKs to a local collection or deny the operation.
Ok, what about transactions? this is another condition we need to take into account. What if another transaction changed the CreationDate of a row that out of our selection to a value that fits the condition in the query above between SELECT and final DELETE statement? A row within our selection of PKs could also change CreationDate value and became out of selection so we would false-delete data. What if a row with matching value were inserted?
This is only three entities in linear hierarchy structure, and it raised so many problems to solve, some of them may be unsolvable, tree-like hierarchies will make it even more complicated.
This is a lot of time for development. Unfortunately, I have limited time to spend on tasks, so this is not my priority right now to make BulkOperations support multi-table operations.
The bulk Delete() method fails when deleting members of an EntitySet which contains ancestors of T:
This is the unit test to reproduce the issue: