Hi!

I've tried to figure out how to use a dynamic relation with a prefetch path. A get a runtime error:

"System.InvalidCastException: Unable to cast object of type 'LogRetention.LLBL.RelationClasses.DynamicRelation' to type 'SD.LLBLGen.Pro.ORMSupportClasses.EntityRelation'.\r\n at SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2.Add(IPrefetchPathElement2 elementToAdd, Int32 maxAmountOfItemsToReturn, IPredicateExpression additionalFilter, IRelationCollection additionalFilterRelations, ISortExpression additionalSorter, IEntityFactory2 entityFactoryToUse, ExcludeIncludeFieldsList excludedIncludedFields)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2.Add(IPrefetchPathElement2 elementToAdd, Int32 maxAmountOfItemsToReturn, IPredicateExpression additionalFilter, IRelationCollection additionalFilterRelations)\r\n at LogRetention.BusinessFacade.SystemInformation.FetchWithExportHistory() in C:\\Projects\\LogRetention\\BusinessFacade\\SystemInformation.cs:line 124\r\n at LogRetention.BusinessFacade.LogRetention.FetchSystemInformationsAndExportHistory() in C:\Projects\LogRetention\BusinessFacade\LogRetentio n.cs:line 169"

The code:

            EntityCollection<SystemInformationEntity> collectionToReturn = new EntityCollection<SystemInformationEntity>();

            ResultsetFields derivedTableFields = new ResultsetFields(2);
            derivedTableFields.DefineField(ExportHistoryFields.FkStatusCode, 0);
            derivedTableFields.DefineField(ExportHistoryFields.StartTime, 1);
            derivedTableFields[1].SetAggregateFunction(AggregateFunction.Max);
            GroupByCollection groupBy = new GroupByCollection(derivedTableFields[0]);
            DerivedTableDefinition derivedTable = new DerivedTableDefinition(derivedTableFields, "MaxStatusAndStartTime", null, groupBy);

            IRelationCollection relations = new RelationCollection();
            relations.Add(new DynamicRelation(
                                                derivedTable,
                                                JoinHint.Inner,
                                                EntityType.ExportHistoryEntity,
                                                "History",
                                                (
                                                    (new EntityField2(
                                                                        ExportHistoryFields.FkStatusCode.Name,
                                                                        "MaxStatusAndStartTime",
                                                                        typeof(int)
                                                                       ) == ExportHistoryFields.FkStatusCode.SetObjectAlias("History")
                                                    )
                                                    &
                                                    (new EntityField2(
                                                                        ExportHistoryFields.StartTime.Name,
                                                                        "MaxStatusAndStartTime",
                                                                        typeof(DateTime)
                                                                      ) == ExportHistoryFields.StartTime.SetObjectAlias("History")
                                                    )
                                                )
                                            )
                            );
            relations.SelectListAlias = "History";

            
            IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.SystemInformationEntity);
            prefetchPath.Add(SystemInformationEntity.PrefetchPathExportHistory, 0, null, relations);

            IRelationPredicateBucket bucket = new RelationPredicateBucket(SystemInformationFields.Host == Environment.MachineName);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{                   
    adapter.FetchEntityCollection(collectionToReturn, bucket, prefetchPath);
}

And the SQL that I'm hoping to be able to produce for the prefetch query is:

SELECT [pkExportHistoryId]
      ,[fkSystemInformationId]
      ,[ExportHistory].[fkStatusCode]
      ,[ExportHistory].[startTime]
      ,[endTime]
      ,[message]
      ,[exportDate]
      ,[exportDateAsFloat]
      ,[exportRowCount]
  FROM [LogRetention].[dbo].[ExportHistory]
INNER JOIN 
    (
        SELECT fkStatusCode, MAX(startTime) AS startTime
        FROM dbo.ExportHistory
        GROUP BY fkStatusCode
    ) maxstatus ON dbo.ExportHistory.fkStatusCode = maxstatus.fkStatusCode 
        AND dbo.ExportHistory.startTime = maxstatus.startTime

It seems to me that the relation collection passed to the Add method of the prefetch path object cannot contain any DynamicRelation instances. Is this true?

Other related info: ORMSupportClasses version: 2.6.10.0421 Designer version: 2.6 .NET Framework: 2

Best regards,

Anders

Indeed, that is not supported. Will your query work if it would be rewritten like this?:

SELECT [pkExportHistoryId]
     ,[fkSystemInformationId]
     ,[ExportHistory].[fkStatusCode]
     ,[ExportHistory].[startTime]
     ,[endTime]
     ,[message]
     ,[exportDate]
     ,[exportDateAsFloat]
     ,[exportRowCount]
FROM [LogRetention].[dbo].[ExportHistory] xx
WHERE fkStatusCode  IN
    (
        SELECT fkStatusCode
        FROM dbo.ExportHistory
        GROUP BY fkStatusCode
        HAVING MAX(startTime) = [LogRetention].[dbo].[ExportHistory]
    ) 

If so, you can use a FieldCompareSetPredicate on your PrefetchPath.

Unfortunatelly that is not satisfactory. The sql enclosed however, can do the trick. I'll try to convert it into LLBL code.

SELECT [pkExportHistoryId]
      ,[fkSystemInformationId]
      ,[fkStatusCode]
      ,[startTime]
      ,[endTime]
      ,[message]
      ,[exportDate]
      ,[exportDateAsFloat]
      ,[exportRowCount]
  FROM [LogRetention].[dbo].[ExportHistory] eh1
WHERE EXISTS(
        SELECT NULL AS tmp 
        FROM dbo.ExportHistory eh2
        GROUP BY fkStatusCode
        HAVING eh2.fkStatusCode = eh1.fkStatusCode
        AND MAX(eh2.startTime) = eh1.startTime
    )

Thanks for the tip.

Edited:

Can this be done with the FieldCompareSetPredicate? I had a look and can't see how to do it.

Yes it does. FieldCompareSetPredicate also allows you to define EXISTS () queries. It is then not necessary to specify an IEntityField2 object with the predicate's constructor (specify null / nothing) as it is ignored when building the SQL. Keep in mind that EXISTS() queries are semantically the same as IN queries and IN queries are often simpler to formulate. This is an approximate code:

// define the groupBy, you just have to set the alias in the subquery
GroupByCollection grouper = new GroupByCollection();
grouper.Add(ExportHistoryFields.StatusCode.SetObjectAlias("eh2"));

IPredicateExpression havingClause = new PredicateExpression();
havingClause.Add(PredicateExpression(ExportHistoryFields.StatusCode == ExportHistoryFields.StatusCode.SetObjectAlias("eh2"));
havingClause.Add(ExportHistoryFields.StartTime ==   ExportHistoryFields.StartTime.SetObjectAlias("eh2").SetAggregateFunction(AggregateFunction.Max));
grouper.HavingClause = havingClause;

// exist predicate
FieldCompareSetPredicate existFilter = new  FieldCompareSetPredicate(
                null, null, ExportHistoryFields.StatusCode, null, SetOperator.Exist, null,
                null, "eh2", 0, null, false, grouper);

// the filter of the prefetchPath
IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.SystemInformationEntity);
            prefetchPath.Add(SystemInformationEntity.PrefetchPathExportHistory, 0, pathFilter);

Many thanks!

That helped!

I ended up with the following code:

            EntityCollection<SystemInformationEntity> collectionToReturn = new EntityCollection<SystemInformationEntity>();

            PredicateExpression maxFilter = BuildExportHistoryLastKnownStatusPrefetchFilter();

            IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.SystemInformationEntity);
            prefetchPath.Add(SystemInformationEntity.PrefetchPathExportHistory, 0, maxFilter);

            IRelationPredicateBucket bucket = new RelationPredicateBucket(SystemInformationFields.Host == Environment.MachineName);

            try
            {
                using (DataAccessAdapter adapter = new DataAccessAdapter())
                {                   
                    adapter.FetchEntityCollection(collectionToReturn, bucket, prefetchPath);
                }
            }

and method for building the exists-clause:

        private static PredicateExpression BuildExportHistoryLastKnownStatusPrefetchFilter()
        {
            var maxHavingClause = new PredicateExpression(ExportHistoryFields.FkStatusCode.SetObjectAlias("t2") == ExportHistoryFields.FkStatusCode
                & ExportHistoryFields.StartTime.SetObjectAlias("t2").SetAggregateFunction(AggregateFunction.Max) == ExportHistoryFields.StartTime);
            var maxGroupBy = new GroupByCollection(ExportHistoryFields.FkStatusCode.SetObjectAlias("t2"));
            maxGroupBy.HavingClause = maxHavingClause;

            PredicateExpression maxFilter = new PredicateExpression();
            maxFilter.Add(new FieldCompareSetPredicate(
                                                        null,
                                                        null,
                                                        ExportHistoryFields.FkStatusCode.SetObjectAlias("t2"),
                                                        null,
                                                        SetOperator.Exist,
                                                        null,
                                                        null,
                                                        "t2",
                                                        0,
                                                        null,
                                                        false,
                                                        maxGroupBy));
            return maxFilter;
        }