If I create Cascade Deletes (and/or Delete Triggers) in a Target Per Entity Hierarchy, will this cause problems for LLBLGEN?

My guess is that the 'parent' DELETES issued by LLBLGEN to remove the rows up the hierarchy will become NOPs but otherwise be safe.

As an example, if I have these hierarchies: AnalysisItem<-SaleAnalysisItem<-CommissionItem AccountsTransaction<-CommissionStatement

and CommissionStatement has a 1:m relationship with CommissionItem

I add the following cascade deletes:- CommissionItem - all deleted when the parent CommissionStatement is deleted

and I now add for the hierarchies (not strictly needed for LLBLGEN use) AccountsTransaction - deleted when the corresponding CommissionStatement is deleted SaleAnalysisItem - deleted when the corresponding CommissionItem is deleted AnalysisItem - deleted when the corresponding SaleAnalysisItem is deleted

In code, I can delete a commission statement with var entity = new CommissionStatementEntity(123); adapter.DeleteEntity(entity);

But I should now be able to do the same thing in the database with DELETE FROM CommissionStatement WHERE ID = 123

and achieve identical results.

Can anyone foresee any problems with this?

Cheers Simon

Hi Simon,

I cannot see a problem with that. Just have in mind that if you map a subtype (entity is a subtype in your model) the parent is deleted as well, this shouldn't be a problem in your trigger, but just take that into account.

daelmo wrote:

Hi Simon,

I cannot see a problem with that. Just have in mind that if you map a subtype (entity is a subtype in your model) the parent is deleted as well, this shouldn't be a problem in your trigger, but just take that into account.

That mainly the point, LLBLGen is clever enough to do that which is great from a code point of view, but sometimes, you need to be able to do it by 'finger-poking' the database.

I now have DELETE Triggers on all my inherited entities which will delete their parent (recursively) which works great from the database side - I can just delete the one subtype and the delete can go both up (via the triggers) and down (via Cascade Deletes) all in one hit.

The only thing I can think of is that if you delete an entity, it will fail because the delete query of the supertype fails as the trigger already removed it: the batch query will fail in total if 1 of the queries for the supertypes fails (returns 0 rows affected).

The only thing you gain is when you delete entities directly, as that's not supported in the ORM for these entities. The runtime will always run the queries for the supertype rows as well, so I'm not sure whether it will gain you much in the end.

So not sure why it works, perhaps the delete queries for the supertype rows still see the rows already removed by the trigger on the subtype, but that's just guessing.

Otis wrote:

The only thing I can think of is that if you delete an entity, it will fail because the delete query of the supertype fails as the trigger already removed it: the batch query will fail in total if 1 of the queries for the supertypes fails (returns 0 rows affected).

The only thing you gain is when you delete entities directly, as that's not supported in the ORM for these entities. The runtime will always run the queries for the supertype rows as well, so I'm not sure whether it will gain you much in the end.

So not sure why it works, perhaps the delete queries for the supertype rows still see the rows already removed by the trigger on the subtype, but that's just guessing.

That's worrying. I'd better make what I am doing will be OK.

This is a test I'm running:-

        [Test]
        public void TestCreateAndAllocateCommissionStatementThenDeleteAllocation()
        {
            PerformTestInTransactionWithRollback(adapter =>
                {
                    var info = DoCreateCreateAndAllocateCommissionStatement(adapter);

                    adapter.DeleteEntity(info.Allocation);

                    Assert.AreEqual(0, adapter.GetDbCount<AllocationEntity>()); Assert.AreEqual(0, adapter.GetDbCount<AllocationItemEntity>());
                });
        }

And the SQL generated by the DeleteEntity method is this:-

--BatchAction Query (Count=2):

--Action Query #1:
DECLARE @p1 int; SET @p1='6'

DELETE 
FROM
  [Allocation] 
WHERE
  ( [Allocation].[ID] = @p1)

--Action Query #2:
DECLARE @p2 int; SET @p2='6'

DELETE 
FROM
  [AccountsTransaction] 
WHERE
  ( [AccountsTransaction].[ID] = @p2)

Now I just recreated this and ran the above manually so that I could see the output:-

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

It gets a bit difficult to work out which result goes with which command/trigger but I know the last two non-zeros would be for NominalTransaction/NominalItem that have a Cascade deleteo.

This is the output with Tracing switched to Verbose

Method Enter: DataAccessAdapterBase.DeleteEntity(2)
Active Entity Description: 
    Entity: TIPS.DAL.EntityClasses.AllocationEntity. ObjectID: 36b05e8b-dab9-496e-93e3-5c9fb6075ccf
    PrimaryKey field: ID. Type: System.Int32. Value: 12
    PrimaryKey field: ID. Type: System.Int32. Value: 12



--BatchAction Query (Count=2):

--Action Query #1:
DECLARE @p1 int; SET @p1='12'

DELETE 
FROM
  [Allocation] 
WHERE
  ( [Allocation].[ID] = @p1)

--Action Query #2:
DECLARE @p2 int; SET @p2='12'

DELETE 
FROM
  [AccountsTransaction] 
WHERE
  ( [AccountsTransaction].[ID] = @p2)

Method Enter: DataAccessAdapterBase.ExecuteActionQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: Query.ReflectOutputValuesInRelatedFields
Method Exit: Query.ReflectOutputValuesInRelatedFields: no parameter relations.
Executed Sql Query: 
    Query: DELETE FROM [TIPS].[dbo].[Allocation] WHERE ( [TIPS].[dbo].[Allocation].[ID] = @p1)
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.


Executed Sql Query: 
    Query: DELETE FROM [TIPS].[dbo].[Allocation] WHERE ( [TIPS].[dbo].[Allocation].[ID] = @p1)
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.

Executed Sql Query: 
    Query: DELETE FROM [TIPS].[dbo].[AccountsTransaction] WHERE ( [TIPS].[dbo].[AccountsTransaction].[ID] = @p2)
    Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.


Executed Sql Query: 
    Query: DELETE FROM [TIPS].[dbo].[AccountsTransaction] WHERE ( [TIPS].[dbo].[AccountsTransaction].[ID] = @p2)
    Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.

Method Exit: DataAccessAdapterBase.ExecuteActionQuery
Method Exit: DataAccessAdapterBase.DeleteEntity(2)
Method Enter: DataAccessAdapterBase.Rollback
Method Enter: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Rollback

If I run this through SQL Debugger, I can see @@ROWCOUNT=1 after the first delete and then @@ROWCOUNT=0 after the second delete which is exactly what you expected.

Does LLBLGen get @@ROWCOUNT back or some other value?

I get a bit lost because I can't step into LLBLGen code properly (I can see stuff in the Local window but the source code stepping seems to be random - probably because it is a Resharper reverse compile) but I'm guessing there are two ActionQuery objects, both deletes, wrapped up in a BatchActionTransaction.

However, the BatchActionTransaction has its _quitOnPartyFailure set to False and so will work correctly based on the first delete returning 1.

You set _quitOnPartyFailure to false? As that's the reason it works for you. The code should set it to true by default, causing the problems I described (which you don't run into).

Otis wrote:

You set _quitOnPartyFailure to false? As that's the reason it works for you. The code should set it to true by default, causing the problems I described (which you don't run into).

I didn't set it.

LLBLGen doesn't seem to be setting it which is great for me in this scenario but will mess me up if you fix it to be as you expected.

huh? It is set, Line 490, DynamicQueryEngineBase... very strange. It should be true, so if it's not, we'll have to check it and it will be set to true (so your code will fail, but it should, as the runtime should fail if one of the queries fails, as it's a major error otherwise because the whole transaction should roll back. )

(edit) Oh wait, this is v3.5, right? Or v4? In v4 it IS set, in v3.5 it might not be, due to the bug you reported with the # of queries added to batchquery.

(edit2) never mind, it is indeed not set in v4. I simply don't understand. Looking into it.

It's not set at two places. It's not set because the query created at these places (a delete query and an update query) are for a single entity and no additional filters/joins are in play, so the entity is assumed to be there in full.

The flag is set when there is an additional filter in play as these can fail the query. So the flag is set at the right place and not set when it's not necessarily needed.

We'll leave it as-is so your code and other code in play at other projects doesn't fail when we make this change, as it's not necessary.

I said in my previous post to 'fix it' because I thought it was not set in the situation when it IS needed, namely the situation with additional filters/joins. This isn't the case: it works there.

So all is well. Carry on!