Cascading bulk delete not supported?

[Bernd]

Hi .

I try to make use of the DeleteAll method with cascading deletes. But only the first level is deleted. Is this supported and I configured something wrong or is it not meant to work in combination?

Kind regards

Bernd

[Doroteya]

Hi Bernd,

The outcome you experienced on your side is expected. Such behaviour is by design, because if we were to implement it like this, the risk of accidentally deleting large amounts of data was huge. The proper configuration in this case is to explicitly set the cascade rule on a given constraint and to use the DeleteAll method only on the parent entity.

I hope this helps.

Regards,
Doroteya
Telerik by Progress

 

[Bernd]

Hi Doroteya.

Thank you for your explanation. Hopefully you can show me where I went wrong.

I configured the cascading rules. WorkOrder is the top level class. Deleting a single WorkOrder, deletes all depending objects. So far so good. DeleteAll deletes only the WorkOrders, not the depending objects. Replacing the call with a loop does the job. But it seems not very elegant.

IQueryable<WorkOrder> query = dbContext.WorkOrders.Where(wo => aukzs.Contains(wo.MasterSystemId.Substring(0, 1)) && !liste.Contains(wo.MasterSystemHierarchy + wo.MasterSystemId));
int deleted = query.Count();/*.DeleteAll(); doesn't support cascading delete */
if (deleted > 0) {
    foreach (var item in query) dbContext.Delete(item);
    dbContext.SaveChanges();
}

Maybe there is something wrong or missing in my configuration. Sample of the configuration:

01.public MappingConfiguration<WorkOrder> GetWorkOrderClassConfiguration() {
02.    MappingConfiguration<WorkOrder> configuration = new MappingConfiguration<WorkOrder>();
03.    configuration.MapType(x => new { }).WithDataAccessKind(DataAccessKind.Default).WithCascadeDelete(CascadeDeleteOption.Yes);
04.
05.    return configuration;
06.}
07.
08.public void PrepareWorkOrderAssociationConfigurations(MappingConfiguration<WorkOrder> configuration) {
09.    configuration.HasAssociation(x => x.Operations).WithOpposite(x => x.WorkOrder).HasConstraint((m, d)=>d.WorkOrderId == m.Id).IsManaged().IsDependent();
10.    configuration.HasAssociation(x => x.MaterialComponents).WithOpposite(x => x.WorkOrder).HasConstraint((m, d)=>d.WorkOrderId == m.Id).IsManaged().IsDependent();
11.    configuration.HasAssociation(x => x.Transactions).WithOpposite(x => x.WorkOrder).HasConstraint((m, d)=>d.WorkOrderId == m.Id).IsManaged().IsDependent();
12.    configuration.HasAssociation(x => x.Customer).WithOpposite(x => x.WorkOrders).HasConstraint((d, m)=>d.CustomerId == m.Id);
13.    configuration.HasAssociation(x => x.Endproduct).WithOpposite(x => x.WorkOrders).HasConstraint((d, m)=>d.EndproductId == m.Id).IsManaged();
14.    configuration.HasAssociation(x => x.OrderType).WithOpposite(x => x.WorkOrders).HasConstraint((d, m)=>d.OrderTypeId == m.Id);
15.    configuration.HasAssociation(x => x.UnitOfMeasure).HasConstraint((d, m)=>d.EndProductUomId == m.Id);
16.}
17.
18.public MappingConfiguration<Operation> GetOperationClassConfiguration() {
19.    MappingConfiguration<Operation> configuration = new MappingConfiguration<Operation>();
20.    configuration.MapType(x => new { }).WithDataAccessKind(DataAccessKind.Default).WithCascadeDelete(CascadeDeleteOption.Yes);
21.
22.    return configuration;
23.}
24.
25.public void PrepareOperationAssociationConfigurations(MappingConfiguration<Operation> configuration) {
26.    configuration.HasAssociation(x => x.MaterialComponents).WithOpposite(x => x.Operation).HasConstraint((m, d)=>d.OperationId == m.Id).IsManaged().IsDependent();
27.    configuration.HasAssociation(x => x.Transactions).WithOpposite(x => x.Operation).HasConstraint((m, d)=>d.OperationId == m.Id).IsDependent();
28.    configuration.HasAssociation(x => x.MessageEvents).WithOpposite(x => x.Operation).HasConstraint((m, d)=>d.OperationId == m.Id).IsDependent();
29.    configuration.HasAssociation(x => x.CalendarEvents).WithOpposite(x => x.Operation).HasConstraint((m, d)=>d.OperationId == m.Id).IsManaged().IsDependent();
30.    configuration.HasAssociation(x => x.OperatingResource).WithOpposite(x => x.OperationsAsOpRes).HasConstraint((d, m)=>d.OperatingResourceId == m.Id);
31.    configuration.HasAssociation(x => x.CycleUoM).WithOpposite(x => x.Operations).HasConstraint((d, m)=>d.CycleTimeUoM  == m.Id).IsManaged();
32.    configuration.HasAssociation(x => x.Workplace).WithOpposite(x => x.Operations).HasConstraint((d, m)=>d.WorkplaceId == m.Id);
33.    configuration.HasAssociation(x => x.WorkOrder).WithOpposite(x => x.Operations).HasConstraint((d, m)=>d.WorkOrderId == m.Id).IsManaged();
34.}

Kind regards

Bernd

[Doroteya]

Hi Bernd,

Thank you for getting back to me and I am sorry that my response was misleading.

In order to implement the needed behaviour you need to modify the database constraints between:
- WorkOrders and Operations,
- WorkOrders and MaterialComponents, and
- WorkOrders and Transactions.

Furthermore, based on the provided code snippets, you will have to modify the database constraints between:
- Operations and MaterialComponents,
- Operations and Transactions,
- Operations and MessageEvents, and
- Operations and CalendarEvents as well.

To illustrate better the necessary modifications, I am attaching to this message a short video based on the SofiaCarRental database. In the video I am implementing a workflow that consumes the DeleteAll() method with database constraints that support cascade delete.

As for the WithCascadeDelete() method it is designed to support properly the scenarios, which involve multi-table entities and vertical inheritance.

I hope this helps.

Regards,
Doroteya
Telerik by Progress

 

[Bernd]

Hi Doroteya.

Thank you very much for taking the time helping me and recording the video.

I thought DataAccess would take care of the contraints and indexing during creating/updating the schema. Obviously not.

What is the recommended way to add creation of such contraints, views etc in the migration process? Hopefuly not a manual execution. :)

Kind regards

Bernd

[Doroteya]

Hello Bernd,

Currently, the Fluent API does not include a method which supports such configuration. Therefore, you won't be able to modify the model and persist the changes through the schema migration process. As a workaround, you can modify the existing constraints through SQL statements executed by Data Access. The following code snippet illustrates the idea:

using (SofiaCarRentalContext dbContext = new SofiaCarRentalContext())
{
   var SqlQuery = @"ALTER TABLE[dbo].[RentalRates] DROP CONSTRAINT [FK_RentalRateCategory] "
            + @"ALTER TABLE [dbo].[RentalRates]  WITH CHECK ADD  CONSTRAINT [FK_RentalRateCategory] FOREIGN KEY([CategoryID]) "
            + @"REFERENCES[dbo].[Categories]([CategoryID]) "
            + @"ON DELETE CASCADE "
            + @"ALTER TABLE[dbo].[RentalRates] CHECK CONSTRAINT [FK_RentalRateCategory] ";

    dbContext.ExecuteNonQuery(SqlQuery, System.Data.CommandType.Text, null);
    dbContext.SaveChanges();
}

I hope this helps.


Regards,
Doroteya
Telerik by Progress

 

[Bernd]

It helps.

Thank you