- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Using DynamicRelation with a prefetch
Joined: 19-Mar-2007
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.
Joined: 19-Mar-2007
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);
Joined: 19-Mar-2007
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;
}