Hi everybody,

I need to delete records from a few tables with PK - FK relationships. Below you find a code which I wrote to do it. I am wondering is there more efficient way to do it? Thank you very much in advance, Galina.

Dim itm As New WorkOrderEntity(workOrderId) itm.IsNew = False Dim uow As New UnitOfWork2

  Dim fp As New PrefetchPath2(CInt(EntityType.WorkOrderEntity))
  fp.Add(WorkOrderEntity.PrefetchPathEquipmentEvents)
  Dim sp As IPrefetchPathElement2 = fp.Add(WorkOrderEntity.PrefetchPathWorkItems)
  With sp.SubPath.Add(WorkItemEntity.PrefetchPathWorkItemParts)
    .SubPath.Add(WorkItemPartEntity.PrefetchPathInventoryAdjustments)
  End With
  With sp.SubPath.Add(WorkItemEntity.PrefetchPathWorkItemLabor)
  End With
  FetchEntity(itm, fp)

  For Each p As WorkItemEntity In itm.WorkItems
    For Each pp As WorkItemPartEntity In p.WorkItemParts
      For Each ppp As PartInventoryAdjustmentEntity In pp.InventoryAdjustments
        uow.AddForDelete(ppp)
      Next
      uow.AddForDelete(pp)
    Next

    For Each l As WorkItemLaborEntity In p.WorkItemLabor
      uow.AddForDelete(l)
    Next

    uow.AddForDelete(p)
  Next

  For Each p As EquipmentEventEntity In itm.EquipmentEvents
    uow.AddForDelete(p)
  Next

  uow.AddForDelete(itm)
  CommitUnitOfWork(uow)

I can think of two more efficient ways...

1) Enable cascading deletes in the database, and simply delete the parent object - this will remove the parent objects as well...

2) Use the DeleteMulti* methods to delete multiple records in one go - you can run several DeleteMulti statements inside a transaction to ensure that all of your deletes happen or fail together.

Matt

Hi Matt,

Thank you for your response. I would rather not enforce cascading deletes in the database. I use Adapter but not SelfServicing. Does DeleteMulti work for Adapter as well? If it does could you please give me an explicit example how to apply it for at least three tables with PK-FK relationships. For example: Customer -> Work Order -> Work Order Details where only CustomerId is known?

Thanks, Galina.

If using Adapter you need the DeleteEntitiesDirectly method (please always let us know in your first post, it makes our life much easier )

Pseudo Code

Start Transaction

var bucket = new RelationPredicateBucket bucket.Relations.Add(WOD-WO); bucket.Relations.Add(WO-Cust); bucket.PredicateExpression.Add(CustFields.Id = custId) Adapter.DeleteEntitiesDirectly(WOD,bucket)

bucket = new RelationPredicateBucket bucket.Relations.Add(WO-Cust); bucket.PredicateExpression.Add(CustFields.Id = custId) Adapter.DeleteEntitiesDirectly(WO,bucket)

bucket = new RelationPredicateBucket bucket.PredicateExpression.Add(CustFields.Id = custId) Adapter.DeleteEntitiesDirectly(Cust,bucket)

Commit Trans

Hi Matt,

Thank you! It works!

Best, Galina.