Greetings, I am trying to build a generic Delete routine so that an Entity would delete all its related (the FK side of its relations) before the entity itself is deleted. To delete an Order entity, I need to know about its related OrderDetails (even though they are NOT loaded in the current entity) so that I would delete them (using DeleteEntitiesDirectly) before the Order record. My problems: 1- What about related entities to OrderDetails and so on... 2- How can I do this generically (not being specific to Order-OrderDetails). What I am trying to do is to simulate a Cascade-Delete functionality in LLBL code

Are you using SQL server? If so doing it in the database would be your best bet. Are there any drawbacks to this approach for your solution?

What I want is a way to generically build a filter for all related (OneToMany and OneToOne) entites so that I can build a DeleteEntitiesDirectly command for all such entities. For example, To delete an Order and its related OrderDetails (related by OrderId) I would build a filterBucket for OrderDetails (OrderDetails.OrderId = value of Order.OrderId) and execute both the Order's DeleteEntity and the OrderDetails' DeleteEntititiesDirectly in a UnitOfWork. The problem here is, can this be done (or generated) generically so that: 1- An entity (Order) can discover what are its related (OneToOne and OneToMany) entities 2- For each of these related entites, discover what are the FK fields so to be able to build the filterBucket 3- For each of these related entites, build a DeleteEntitiesDirectly that needs each related entiy's name and the filter bucket. I want to be able to do this either in the GENERATED CODE or in the TDL template so that it would generate the code to do the above.

I haven't tried it but I think with adapter it's very simple. The beauty is that these filters are already available. In 'Order', you have a method called GetRelationInfoOrderDetails(), which specifies the relations and filter for the orderdetail entities related to the order entity you call it on.

This same filter can be used to call DeleteEntitiesDirectly.

Great Tip Frans... Thanks This is how I am using the code now (inside the Orders BL class)

adapter = BL.DataAccessAdapterFactory.CreateDataAccessadapter(True)
adapter.StartTransaction(IsolationLevel.RepeatableRead, "DeleteOrdersEntity " & Me.ToString)
Dim uow As UnitOfWork2 = New UnitOfWork2
uow.AddDeleteEntitiesDirectlyCall("OrderDetailsEntity", Me.GetRelationInfoOrderDetailsCollection())
uow.AddForDelete(Me)
uow.Commit(adapter, True)

1- The code executes succesfully with no errors. When I check the database, I find that the related OrderDetails records did get deleted BUT the Order entity itself did NOT get deleted. 2- Is there any build in mechanism to obtain the EntityName for the AddDeleteEntitiesDirectlyCall() function?

I've removed your previous post as this second post contains all the info of that previous post -- Otis

omar wrote:

Great Tip Frans... Thanks This is how I am using the code now (inside the Orders BL class)

adapter = BL.DataAccessAdapterFactory.CreateDataAccessadapter(True)
adapter.StartTransaction(IsolationLevel.RepeatableRead, "DeleteOrdersEntity " & Me.ToString)
Dim uow As UnitOfWork2 = New UnitOfWork2
uow.AddDeleteEntitiesDirectlyCall("OrderDetailsEntity", Me.GetRelationInfoOrderDetailsCollection())
uow.AddForDelete(Me)
uow.Commit(adapter, True)

1- The code executes succesfully with no errors. When I check the database, I find that the related OrderDetails records did get deleted BUT the Order entity itself did NOT get deleted.

Is there a delete query generated for the order entity? It should be deleted. Or is it a new entity? (e.g. IsNew is true)

2- Is there any build in mechanism to obtain the EntityName for the AddDeleteEntitiesDirectlyCall() function?

You mean that 'OrderDetailsEntity' name?

Otis wrote:

Is there a delete query generated for the order entity? It should be deleted. Or is it a new entity? (e.g. IsNew is true)

Bulls-eye. It was actually a new instance of the Orders object.. Thanks man

Otis wrote:

You mean that 'OrderDetailsEntity' name?

Yes. It came to me that I can write my TDL template to generate this code for me.

OK.. seems I spoke too soon. Now I am getting the following exception

An exception was caught during the execution of an action query: DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Orders'. The conflict occurred in database 'NorthwindJCL', table 'Order Details', column 'OrderID'.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Parameters Values:
@OrderID1=11080


    Query: DELETE FROM [NorthwindJCL].[dbo].[Orders] WHERE ( [NorthwindJCL].[dbo].[Orders].[OrderID] = @OrderID1)
    Parameter: @OrderID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11080.

When I turned tracing ON, the generated SQL query showed

Generated Sql query: 
    Query: DELETE FROM [NorthwindJCL].[dbo].[Orders] WHERE ( [NorthwindJCL].[dbo].[Orders].[OrderID] = @OrderID1)
    Parameter: @OrderID1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 11080.

I don't know why now it generates SQL query for the primary entity (Orders) and does NOT generate any SQL query for the related entity (OrdersDetails). This is the code being executed (in the Orders class)

            adapter = BL.DataAccessAdapterFactory.CreateDataAccessadapter(True)
            adapter.StartTransaction(IsolationLevel.RepeatableRead, "DeleteOrdersEntity " & Me.ToString)

            Me.IsNew = False
            Me.OrderID = crit.OrderID  'get PK field value

            Dim uow As UnitOfWork2 = New UnitOfWork2

            uow.AddDeleteEntitiesDirectlyCall("OrderDetailsEntity", Me.GetRelationInfoOrderDetailsCollection())

            uow.AddForDelete(Me)  'delete the Order entity

            uow.Commit(adapter, True)

            adapter.CloseConnection()

After further testing I found out the following (Me=Orders) If the Me.IsNew=True The UOW code generates SQL to delete OrdersDetails and not Orders (which is expected) but when Me.IsNew=False The same UOW code generates SQL to delete Orders and does NOT generate SQL to Delete OrdersDetails. Does this have to do with the fact I am deleting OrdersDetails using AddDeleteEntitiesDirectlyCall while I use AddForDelete for the Orders entity. ??

Delete entities directly calls are executed after the delete actions on entities added with AddForDelete. So if you add 'Order' with AddForDelete, and orderdetails deletes through AddDeleteEntitiesDirectly, the order is first deleted but fails with an exception, as there are orderdetail rows still pointing at the order.

Solution: add the order delete to the uow with DeleteEntitiesDirectly as well. First the orderdetails delete, then the order delete.