Hello,

I am receiving an error when trying to delete a set of entities directly in the database, without loading them first. I am using LL 2.6, adapter templates, and the PostgreSQL driver.

This delete requires the use of an IN predicate. My first implementation using Linq works fine, but I would like to eliminate the need to read the IN set into memory before composing the Delete predicate:

                            var query =
                                    (from archivedStoreTemplate in metaData.MerchantArchivedStoreTemplates
                                     where archivedStoreTemplate.MerchantStoreId == merchantStoreTemplate.MerchantStoreId
                                     orderby archivedStoreTemplate.CreatedAt
                                     select archivedStoreTemplate.Id).Take(countArchivedTemplates - MaxArchiveCount);

                            dataAccessAdapter.DeleteEntitiesDirectly(typeof(MerchantArchivedStoreTemplatesEntity),
                                new RelationPredicateBucket(new FieldCompareRangePredicate(MerchantArchivedStoreTemplatesFields.Id, null,
                                    query.ToArray())));

I am reproducing this query:

      DELETE FROM 
            merchant_archived_store_templates 
        WHERE 
            id IN (SELECT id FROM merchant_archived_store_templates 
                   WHERE merchant_store_id = merchant_store_id_in 
                   ORDER BY created_at ASC LIMIT archive_count - max_archive_count_in);

Using the native LLBLGen api, I tried forming a FieldCompareSet predicate:

                        
                            IPredicate deleteFilter = new FieldCompareSetPredicate(
                                MerchantArchivedStoreTemplatesFields.Id, null, MerchantArchivedStoreTemplatesFields.Id, null, SetOperator.In,
                                (MerchantArchivedStoreTemplatesFields.MerchantStoreId == merchantStoreTemplate.MerchantStoreId), null, null,
                                5, new SortExpression(MerchantArchivedStoreTemplatesFields.Id | SortOperator.Ascending));

                           dataAccessAdapter.DeleteEntitiesDirectly(typeof(MerchantArchivedStoreTemplatesEntity), new RelationPredicateBucket(deleteFilter));

This is resulting in the following error and I can't figure out why?

{"Object reference not set to an instance of an object."}
[System.NullReferenceException]: {"Object reference not set to an instance of an object."}
Data: {System.Collections.ListDictionaryInternal}
HelpLink: null
InnerException: null
Message: "Object reference not set to an instance of an object."
Source: "SD.LLBLGen.Pro.DQE.PostgreSql.NET20"
StackTrace: "   at SD.LLBLGen.Pro.DQE.PostgreSql.PostgreSqlSpecificCreator.CreateFieldName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Int32& uniqueMarker, Boolean applyAggregateFunction)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareSetPredicate.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker)\r\n
at SD.LLBLGen.Pro.DQE.PostgreSql.DynamicQueryEngine.CreateSingleTargetDeleteDQ(IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate deleteFilter)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateDeleteDQ(IFieldPersistenceInfo[] fieldsPersistenceInfo, 
IDbConnection connectionToUse, List`1 pkFilters, IPredicate additionalDeleteFilter, IRelationCollection relationsToWalk)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateDeleteDQ(IFieldPersistenceInfo[] fieldsPersistenceInfo, List`1 pkFilters, IPredicate additionalDeleteFilter, IRelationCollection relationsToWalk)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.DeleteEntitiesDirectly(String entityName, IRelationPredicateBucket filterBucket)\r\n   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.DeleteEntitiesDirectly(Type typeOfEntity, IRelationPredicateBucket filterBucket)\r\n
at OneShop.SecureCart.Business.StoreHelper.SaveMerchantStoreTemplate(MerchantStoreTemplatesEntity merchantStoreTemplate, Boolean makeBackup) in C:\\SVN\\OneShop\\OneShop.SecureCart\\OneShop.SecureCart.Business\\LLBLGenBusinessClasses\\StoreHelper.cs:line 1314"
TargetSite: {System.String CreateFieldName(SD.LLBLGen.Pro.ORMSupportClasses.IEntityFieldCore, SD.LLBLGen.Pro.ORMSupportClasses.IFieldPersistenceInfo, System.String, System.String, Int32 ByRef, Boolean)}

I am hoping someone might be able to put me on the right tracks here?

Thanks.

Can1

In the predicate

MerchantArchivedStoreTemplatesFields.MerchantStoreId == merchantStoreTemplate.MerchantStoreId

where is merchantStoreTemplate added to the query - I can't see it ?

Do you need to add this relation as well ?

Matt

It is a method parameter:

public static MerchantStoreTemplatesEntity SaveMerchantStoreTemplate(MerchantStoreTemplatesEntity merchantStoreTemplate, bool makeBackup)

and merchantStoreTemplate.MerchantStoreId does have a value when the query tries to execute.

No need for a relation, I just need to identify a subset of MerchantStoreTemplatesEntities to delete.

Can1

Mmm. That should work indeed. What LLBLGen runtime library are you using? (if possible, update to the latest build an try again)

Those results are with the most recent version, running version 2.6.8.1114 of the ORMSupportClass.NET20 assembly and version 2.6.8.819 of the DQE.PostgreSQL.NET20 assembly.

Can1

Will look into it. There involved entity is nothing special? I mean: not involved in inheritance?

No inheritance Frans, just a basic entity.

The crash issue is due to the fact you pass 'null' as alias string. You should pass string.Empty. So change

IPredicate deleteFilter = new FieldCompareSetPredicate(
                                MerchantArchivedStoreTemplatesFields.Id, null, MerchantArchivedStoreTemplatesFields.Id, null, SetOperator.In,
                                (MerchantArchivedStoreTemplatesFields.MerchantStoreId == merchantStoreTemplate.MerchantStoreId), null, null,
                                5, new SortExpression(MerchantArchivedStoreTemplatesFields.Id | SortOperator.Ascending));

into

IPredicate deleteFilter = new FieldCompareSetPredicate(
                                MerchantArchivedStoreTemplatesFields.Id, null, MerchantArchivedStoreTemplatesFields.Id, null, SetOperator.In,
                                (MerchantArchivedStoreTemplatesFields.MerchantStoreId == merchantStoreTemplate.MerchantStoreId), null, string.Empty,
                                5, new SortExpression(MerchantArchivedStoreTemplatesFields.Id | SortOperator.Ascending));

In my test I see that there's no limit applied. Will look into that.

(edit) there appears to be a small glitch in the DQEs: it doesn't properly determine the uniqueness of rows if a PK field is in the projection and there are no relations. You run into the problem where you sort on a field that's not in the selectlist. This makes it impossible to emit DISTINCT. Distinct is required for proper limit usage, unless the full pk of the entity is in the projection and there are no relations, as that will make the resultset have unique rows (without the full pk, this isn't certain).

However, there's currently no way to signal the routine which produces the query that the fetch contains the full PK. Changing that is a breaking change. This pops up only in cases where distinct can't be emitted in the subquery for these cases which is only in the case where distinct violating types are found, or relations are present, or sorting takes place on fields not in the projection).

So we can't fix this issue unless we introduce a breaking change, which we won't do, so you've to revert to the workaround you have now.