Style: Adapter LLBLGen Version: 5.11.4 ASP.Net 4.7.2 Error: None StackTrace: None Database: MS SQL 2019

I've written some code that's meant to migrate a SupplierCategory record to a different record when it's deleted. Each Supplier can have many SupplierCategory records linked by a SupplierCategoryJoin table.

My process was going to be to:

• Run adapter.UpdateEntitiesDirectly to set all existing records with the deleting categoryId to the migrationId (i.e. the replacement)

• Run adapter.DeleteEntitiesDirectly to delete the record that's being deleted

However, I kept getting FK constraint issues on deletion saying my ID was still in use after writing this code. I checked the DB and the values hadn't changed as expected:

int? migrationId = 9;
int categoryId = 4;

SupplierCategoryJoinEntity entity = new() { CategoryId = migrationId.Value };

IRelationPredicateBucket bucket = new RelationPredicateBucket();

bucket.Relations.Add(new DynamicRelation(EntityType.SupplierCategoryJoinEntity, JoinHint.Left, EntityType.SupplierCategoryJoinEntity, string.Empty, "B",
    SupplierCategoryJoinFields.SupplierId == SupplierCategoryJoinFields.SupplierId.SetObjectAlias("B")
    & SupplierCategoryJoinFields.CategoryId.SetObjectAlias("B") == migrationId.Value));

bucket.PredicateExpression.Add(SupplierCategoryJoinFields.CategoryId == categoryId &
                                SupplierCategoryJoinFields.CategoryId.SetObjectAlias("B") == DBNull.Value);

adapter.UpdateEntitiesDirectly(entity, bucket);

I inspected the SQL output by overriding ExecuteActionQuery in the DataAccessAdapter and the "IActionQuery queryToExecute" object shows a blank query, i.e. it's not generating any update statement.

I thought it was a problem with the self reference (which is required to ensure we only set values that don't already have that category which would lead to a duplicate primary key exception), but I also tried just removing all the relations and the predicates. I then added a new Predicate of the explicit record to update and it still didn't generate any query text:

SupplierCategoryJoinEntity entity = new() { CategoryId = migrationId.Value };
bucket.PredicateExpression.Add(SupplierCategoryJoinFields.CategoryId == categoryId);
adapter.UpdateEntitiesDirectly(entity, bucket);

My SupplierCategoryJoinEntity is a simple table with two fields (both primary keys): SupplierId PK int NOT NULL, CategoryId PK int NOT NULL.

In the end I've used the same bucket above to just load the data into an EntityCollection, modify the in-memory values, then save, which works as expected:

IRelationPredicateBucket bucket = new RelationPredicateBucket();

bucket.Relations.Add(new DynamicRelation(EntityType.SupplierCategoryJoinEntity, JoinHint.Left, EntityType.SupplierCategoryJoinEntity, string.Empty, "B",
    SupplierCategoryJoinFields.SupplierId == SupplierCategoryJoinFields.SupplierId.SetObjectAlias("B")
    & SupplierCategoryJoinFields.CategoryId.SetObjectAlias("B") == migrationId.Value));

bucket.PredicateExpression.Add(SupplierCategoryJoinFields.CategoryId == categoryId &
                                SupplierCategoryJoinFields.CategoryId.SetObjectAlias("B") == DBNull.Value);

EntityCollection<SupplierCategoryJoinEntity> collection = new EntityCollection<SupplierCategoryJoinEntity>();
adapter.FetchEntityCollection(collection, bucket);

foreach (SupplierCategoryJoinEntity entity in collection)
{
    entity.CategoryId = migrationId.Value;
}

adapter.SaveEntityCollection(collection, false, false);

However, that's fetching data unnecessarily now which I'd rather not when I should be able to update the records directly.

So why can't I use adapter.UpdateEntitiesDirectly in this scenario to update the CategoryId? Is it because it's a PK? Thanks in advance!

That's by design. PK can't be updated directly.

Only by entity saves, as you have done later.

Is there an alternative or than just executing raw SQL? In some scenarios that means fetching 10,000 records, just to set the primary key and then SaveEntityCollection will send 10,000 update queries back.

Where as simple SQL in the above example would be a single query without the need to fetch the data first:

UPDATE A SET A.CategoryId = 100 FROM SupplierCategory A LEFT JOIN SupplierCategoryB ON B.SupplierId = A.SupplierId AND B.CategoryId = 100 WHERE A.CategoryId = 999 AND B.CategoryId IS NULL

Changing the PK means you effectively change the entity's identity, i.o.w.: they become different entity instances. We only allow that in-memory and not in bulk. I'm curious what is the use case that you have to change 10K entity's identity?

We have options on various things to migrate to a different value when you delete an existing value. So in this example, you delete a supplier category and the user is given the option to migration to a new value. They can select that new value to replace it with; that's our common use case.

That effectively comes down to insert 10,000 new rows and delete the old ones. You bypass the extra work by updating the PK of existing rows to something else. I understand the use case, tho our framework doesn't offer the ability to do what you want; it skips PK fields for generated sql for bulk operations. This was a deliberate choice: primary key fields are static, they don't change. If there's a chance they need to change value, then the fields shouldn't be part of the PK but different fields should be. If there aren't fields in the entity which have that characteristic, an artificial PK field should be added.

If the raw sql route isn't an option for you, you could model it differently by adding a unique constraint to the 2 fk fields and add a new identity field as PK or use a unique constraint and no PK on the materialized relationship table. After all; you use the PK really as a unique constraint, not a primary key (as there are no non-pk attributes/fields and you change the pk fields' values). Our framework requires a PK for CUD operations on an entity so it requires that you then add a artificial PK field (which can be an identity Pk field). The UC on the 2 fields make sure there's no duplicate, bulk operations will work and you don't need to fall back to raw sql. If that's not possible, then I'm afraid you're out of options, there's no option to remove this restriction on bulk operations. (other than altering the runtime framework's source code and remove the restriction, which is at DynamicQueryEngineBase.cs, line 1884)

OK - I think we'll just use a stored procedure to eliminate the performance overhead and then call them using ActionProcedures.

That is indeed a way to deal with this, and not change the model. I'm sorry for the restriction, but it's what it is...

I Just thought of an alternative, which is less performant however: use InsertEntitiesDirectly, (https://www.llblgen.com/Documentation/5.12/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_usingentityclasses_creating.htm#inserting-entities-based-on-a-query-insertentitiesdirectly and then delete the old rows. This basically inserts the new rows with the new PKs and you can then delete the old ones afterwards.