How to customize the way LlblGen deletes an entity

Posts   
 
    
Vitaly L
User
Posts: 6
Joined: 22-Jul-2020
# Posted on: 22-Jul-2020 12:43:05   

Hello,

I know that I want to do something strange simple_smile So let me give you some background.

We are developing a system for last ~6y using LlblGen + MSSQL. Now we decided to switch compatibility level of our DB to 130 (aka MS SQL 2016). AFter that I faced an issue with cascade delete (see details here: https://forrestmcdaniel.com/2018/11/02/cascading-delete-troubles/).

Now I'm considering options how to deal with this issue. One of the options is to force legacy cardinality estimator on delete operations. But I was unable to find any way to customize the way LlblGen generates delete query. Is there any? Or may be I could delete entity instance from DB via stored procedure, but I have to somehow tell ORM "hey, this one is already deleted". Haven't found a way to do that also cry

Most likely I'll have to get rid of cascade deletes on DB level and perform it manually on application level. But I stil hope that I missed smth simple_smile

Environment: * LlblGen v 5.6 + adapter * MS Sql Server 2016

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 23-Jul-2020 00:34:47   

may be I could delete entity instance from DB via stored procedure, but I have to somehow tell ORM "hey, this one is already deleted". Haven't found a way to do that also

That's the case with Cascade Deletes as well. Well if you have entities in Collections, you can just remove them from the collection. Otherwise a refetch after a delete operation will always bring the only existing database entities, and never the deleted ones.

But I was unable to find any way to customize the way LlblGen generates delete query. Is there any?

Could you please give an example what kind of customization do you need?

Vitaly L
User
Posts: 6
Joined: 22-Jul-2020
# Posted on: 23-Jul-2020 11:56:02   

Walaa wrote:

Could you please give an example what kind of customization do you need?

I'd like to add some hints to the generated SQL like

DELETE FROM Table WHERE RecordID = @SomeValue 
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

or

DELETE FROM Table WHERE RecordID = @SomeValue 
OPTION (OPTIMIZE FOR UNKNOWN);
Vitaly L
User
Posts: 6
Joined: 22-Jul-2020
# Posted on: 23-Jul-2020 19:17:26   

I've tried folowing and it looks like kind of working solution. Not sure if it is correct, but anyway.

I'm using UnitOfWork2. I have UnitOfWork2Adapter - own class that wraps UnitOfWork2 and exposes part of its interface.

My UnitOfWork2Adapter have AddForDelete method that is basically calls UnitOfWork2.AddForDelete.

I modified it in a following manner:


public void AddForDelete(IEntity2 entity)
{
    if (entity is ActivityEntity activity)
    {
        _activitiesToDelete.Add(activity);
    }
    else
    {
        _unitOfWork2.AddForDelete(entity);
    }
}

As you may notice, I introduced separate queue for deleting activity entities (the one that I want to delete in a custom way).

I used UnitOfWork2 callbacks to delete entities from DB:


_unitOfWork2.AddCallBack((Action<IDataAccessAdapter>)DeleteActivitiesFromDbViaStoredProcedureCall, UnitOfWorkCallBackScheduleSlot.PostEntityDelete, true);

And there is an implementation of DeleteActivitiesFromDbViaStoredProcedureCall:


private void DeleteActivitiesFromDbViaStoredProcedureCall(IDataAccessAdapter dataAccessAdapter)
{
    foreach (var activity in _activitiesToDelete)
    {
        ActionProcedures.PDeleteFromActivity(activity.ActivityID, dataAccessAdapter);
    }

    _activitiesToDelete.Clear();
}

In order to make LlblGen not to delete these entities, I've used UnitOfWork2.RemoveFromUoW:


_unitOfWork2.AddCallBack((Action)RemoveDeletingActivitiesFromUow, UnitOfWorkCallBackScheduleSlot.PreEntityInsert, false);

...

private void RemoveDeletingActivitiesFromUow()
{
    foreach (var activity in _activitiesToDelete)
    {
        UnitOfWork2.RemoveFromUoW(activity);
    }
}

Now I'm testing this solution. It looks a bit... custom. Not sure if I'll go with it into production.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Jul-2020 09:53:58   

Another way to do this is the following: in a partial class of DataAccessAdapter, override OnDeleteEntity(deleteQuery, entityToDelete)

Implement it like so:

public override void OnDeleteEntity(IActionQuery deleteQuery, IEntity2 entityToDelete)
{
    if (entity is ActivityEntity activity)
    {
        // append hint to query
        deleteQuery.Command.CommandText+="OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));"
    }
}

It will now append the hint string to the query to execute for that specific type. You can add more logic there to determine the entities to append the hint to, e.g. if they have a specific attribute assigned to them, or other ways.

Frans Bouma | Lead developer LLBLGen Pro
Vitaly L
User
Posts: 6
Joined: 22-Jul-2020
# Posted on: 24-Jul-2020 11:19:44   

Otis wrote:

Another way to do this is the following: in a partial class of DataAccessAdapter, override OnDeleteEntity(deleteQuery, entityToDelete)

Implement it like so:

public override void OnDeleteEntity(IActionQuery deleteQuery, IEntity2 entityToDelete)
{
    if (entity is ActivityEntity activity)
    {
        // append hint to query
        deleteQuery.Command.CommandText+="OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));"
    }
}

It will now append the hint string to the query to execute for that specific type. You can add more logic there to determine the entities to append the hint to, e.g. if they have a specific attribute assigned to them, or other ways.

Wow! Thanks a lot!