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!