Is it possible to use an AggregateFunction in a prefetch path? I would like to filter my prefetch path results by the min value of a field.

thanks.

Yes, use a FieldCompareExpressionPredicate and the expression is a field object, of which you set its AggregateFunctionToUse to AggregateFunction.Min.

Otis wrote:

Yes, use a FieldCompareExpressionPredicate and the expression is a field object, of which you set its AggregateFunctionToUse to AggregateFunction.Min.

Thank you, I'll give that a try.

Otis wrote:

Yes, use a FieldCompareExpressionPredicate and the expression is a field object, of which you set its AggregateFunctionToUse to AggregateFunction.Min.

Otis, Do you have an example you can give of how this is done. I can't figure out how to get this started. It seems that the FieldCompareExpressionPredicate requires a ComparisonOperator which I don't want to use. I am not trying to do any sort of comparison. I just want the query to return a prefetchpath record with the min value of a column.

tprohas wrote:

Otis wrote:

Yes, use a FieldCompareExpressionPredicate and the expression is a field object, of which you set its AggregateFunctionToUse to AggregateFunction.Min.

Otis, Do you have an example you can give of how this is done. I can't figure out how to get this started. It seems that the FieldCompareExpressionPredicate requires a ComparisonOperator which I don't want to use. I am not trying to do any sort of comparison. I just want the query to return a prefetchpath record with the min value of a column.

Aha, you want to sort on the value, ascending and just return the first object?

Otis wrote:

tprohas wrote:

Otis wrote:

Yes, use a FieldCompareExpressionPredicate and the expression is a field object, of which you set its AggregateFunctionToUse to AggregateFunction.Min.

Otis, Do you have an example you can give of how this is done. I can't figure out how to get this started. It seems that the FieldCompareExpressionPredicate requires a ComparisonOperator which I don't want to use. I am not trying to do any sort of comparison. I just want the query to return a prefetchpath record with the min value of a column.

Aha, you want to sort on the value, ascending and just return the first object?

Yeah, I think that would give me what I want.

tprohas wrote:

Otis wrote:

tprohas wrote:

Otis wrote:

Yes, use a FieldCompareExpressionPredicate and the expression is a field object, of which you set its AggregateFunctionToUse to AggregateFunction.Min.

Otis, Do you have an example you can give of how this is done. I can't figure out how to get this started. It seems that the FieldCompareExpressionPredicate requires a ComparisonOperator which I don't want to use. I am not trying to do any sort of comparison. I just want the query to return a prefetchpath record with the min value of a column.

Aha, you want to sort on the value, ascending and just return the first object?

Yeah, I think that would give me what I want.

If you check out this overload of PrefetchPath2.Add:

public IPrefetchPathElement2 Add(
   IPrefetchPathElement2 elementToAdd,
   int maxAmountOfItemsToReturn,
   IPredicateExpression additionalFilter,
   IRelationCollection additionalFilterRelations,
   ISortExpression additionalSorter
);

you'll see that you can specify a sorter and a max amount of items to return. Specify the sorter to sort on your field, descending and 1 to limit the result per element.

Here's an example of a small routine which reads all employees and for each employee the last order they've closed:

DataAccessAdapter adapter = new DataAccessAdapter();
EntityCollection employees = new EntityCollection(new EmployeeEntityFactory());
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(SortClauseFactory.Create(OrderFieldIndex.OrderDate, SortOperator.Descending));
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, null, null, sorter).SubPath.Add(OrderEntity.PrefetchPathOrderDetails);

adapter.FetchEntityCollection(employees, null, prefetchPath);

Otis wrote:

tprohas wrote:

Otis wrote:

tprohas wrote:

Otis wrote:

Yes, use a FieldCompareExpressionPredicate and the expression is a field object, of which you set its AggregateFunctionToUse to AggregateFunction.Min.

Otis, Do you have an example you can give of how this is done. I can't figure out how to get this started. It seems that the FieldCompareExpressionPredicate requires a ComparisonOperator which I don't want to use. I am not trying to do any sort of comparison. I just want the query to return a prefetchpath record with the min value of a column.

Aha, you want to sort on the value, ascending and just return the first object?

Yeah, I think that would give me what I want.

If you check out this overload of PrefetchPath2.Add:

public IPrefetchPathElement2 Add(
   IPrefetchPathElement2 elementToAdd,
   int maxAmountOfItemsToReturn,
   IPredicateExpression additionalFilter,
   IRelationCollection additionalFilterRelations,
   ISortExpression additionalSorter
);

you'll see that you can specify a sorter and a max amount of items to return. Specify the sorter to sort on your field, descending and 1 to limit the result per element.

Here's an example of a small routine which reads all employees and for each employee the last order they've closed:

DataAccessAdapter adapter = new DataAccessAdapter();
EntityCollection employees = new EntityCollection(new EmployeeEntityFactory());
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(SortClauseFactory.Create(OrderFieldIndex.OrderDate, SortOperator.Descending));
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, null, null, sorter).SubPath.Add(OrderEntity.PrefetchPathOrderDetails);

adapter.FetchEntityCollection(employees, null, prefetchPath);

Otis, I just tried the following code and it doesn't work. I can see that the sort is being added to the query, but the query is still returning all the results. The maxAmountOfItemsToReturn parameter is not working. The query is using "DISTINCT ProductVersionID" in the query, but this won't ever work because there is a different version id for each row and this will cause all the rows to show up. What else should I try?

public EntityCollection GetFrameEntityCollection(string styleGeneral, string companyName)
{
    EntityCollection products = new EntityCollection( new ProductEntityFactory() );

    IPrefetchPath2 prefetchPath = new PrefetchPath2( (int)EntityType.ProductEntity );
    prefetchPath.Add( MyProductEntity.PrefetchPathProductType );
    prefetchPath.Add( MyProductEntity.PrefetchPathRidingStyle );
    IPrefetchPathElement2 manufacturerNode = prefetchPath.Add( MyProductEntity.PrefetchPathManufacturer );
    manufacturerNode.SubPath.Add( MyManufacturerEntity.PrefetchPathCompany );
    
    IPrefetchPathElement2 frameNode = prefetchPath.Add( MyProductEntity.PrefetchPathFrame );
    frameNode.SubPath.Add( MyFrameEntity.PrefetchPathFrameSize );
    frameNode.SubPath.Add( MyFrameEntity.PrefetchPathFrameColor );

    ISortExpression sort = new SortExpression();
    sort.Add( SortClauseFactory.Create(ProductVersionFieldIndex.RPPrice, SortOperator.Ascending) );
    prefetchPath.Add( MyProductEntity.PrefetchPathProductVersion, 1, null, null, sort );
    
    RelationPredicateBucket filter = new RelationPredicateBucket();
    filter.Relations.Add( MyProductEntity.Relations.RidingStyleEntityUsingStyleCode );
    filter.Relations.Add( MyProductEntity.Relations.ProductTypeEntityUsingProductTypeID );
    filter.Relations.Add( MyProductEntity.Relations.ManufacturerEntityUsingManufacturerID );
    filter.Relations.Add( MyManufacturerEntity.Relations.CompanyEntityUsingManufacturerID );
    filter.PredicateExpression.Add( PredicateFactory.CompareValue(RidingStyleFieldIndex.StyleGeneral, ComparisonOperator.Equal, styleGeneral) );
    filter.PredicateExpression.Add( PredicateFactory.CompareValue(ProductTypeFieldIndex.TypeName, ComparisonOperator.Equal, "Frame") );
    filter.PredicateExpression.Add( PredicateFactory.CompareValue(CompanyFieldIndex.CompanyName, ComparisonOperator.Equal, companyName) );

    DataAccessAdapter adapter = new DataAccessAdapter();
    adapter.FetchEntityCollection( products, filter, prefetchPath );

    return products;
}

tprohas wrote:

Otis wrote:

tprohas wrote:

Otis wrote:

tprohas wrote:

Otis wrote:

Yes, use a FieldCompareExpressionPredicate and the expression is a field object, of which you set its AggregateFunctionToUse to AggregateFunction.Min.

Otis, Do you have an example you can give of how this is done. I can't figure out how to get this started. It seems that the FieldCompareExpressionPredicate requires a ComparisonOperator which I don't want to use. I am not trying to do any sort of comparison. I just want the query to return a prefetchpath record with the min value of a column.

Aha, you want to sort on the value, ascending and just return the first object?

Yeah, I think that would give me what I want.

If you check out this overload of PrefetchPath2.Add:

public IPrefetchPathElement2 Add(
   IPrefetchPathElement2 elementToAdd,
   int maxAmountOfItemsToReturn,
   IPredicateExpression additionalFilter,
   IRelationCollection additionalFilterRelations,
   ISortExpression additionalSorter
);

you'll see that you can specify a sorter and a max amount of items to return. Specify the sorter to sort on your field, descending and 1 to limit the result per element.

Here's an example of a small routine which reads all employees and for each employee the last order they've closed:

DataAccessAdapter adapter = new DataAccessAdapter();
EntityCollection employees = new EntityCollection(new EmployeeEntityFactory());
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(SortClauseFactory.Create(OrderFieldIndex.OrderDate, SortOperator.Descending));
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, null, null, sorter).SubPath.Add(OrderEntity.PrefetchPathOrderDetails);

adapter.FetchEntityCollection(employees, null, prefetchPath);

Otis, I just tried the following code and it doesn't work. I can see that the sort is being added to the query, but the query is still returning all the results. The maxAmountOfItemsToReturn parameter is not working. The query is using "DISTINCT ProductVersionID" in the query, but this won't ever work because there is a different version id for each row and this will cause all the rows to show up. What else should I try?

As you use a prefetch path, multiple queries are executed, so I don't really know what DISTINCT ProductVersionID means in this context.

The sorter is applied, and indeed all results are returned though as you've specified just 1 element should be returned, just 1 is merged with the parent. The rest is discarded. But it merges all of the entities with the parent?

Otis,

What do you mean when you say all the entities are merged with the parent? I have the following code for the fetch.

public EntityCollection GetFrameEntityCollection(string styleGeneral, string companyName)
        {
            EntityCollection products = new EntityCollection( new ProductEntityFactory() );

            IPrefetchPath2 prefetchPath = new PrefetchPath2( (int)EntityType.ProductEntity );
            prefetchPath.Add( MyProductEntity.PrefetchPathProductType );
            prefetchPath.Add( MyProductEntity.PrefetchPathRidingStyle );
            IPrefetchPathElement2 manufacturerNode = prefetchPath.Add( MyProductEntity.PrefetchPathManufacturer );
            manufacturerNode.SubPath.Add( MyManufacturerEntity.PrefetchPathCompany );
            
            IPrefetchPathElement2 frameNode = prefetchPath.Add( MyProductEntity.PrefetchPathFrame );
            frameNode.SubPath.Add( MyFrameEntity.PrefetchPathFrameSize );
            frameNode.SubPath.Add( MyFrameEntity.PrefetchPathFrameColor );

            IRelationCollection filterVersion = new RelationCollection();
            filterVersion.Add( MyProductVersionEntity.Relations.ProductEntityUsingProductID );
            ISortExpression sort = new SortExpression();
            sort.Add( SortClauseFactory.Create(ProductVersionFieldIndex.RPPrice, SortOperator.Ascending) );
            prefetchPath.Add( MyProductEntity.PrefetchPathProductVersion, 1, null, filterVersion, sort );
            
            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add( MyProductEntity.Relations.RidingStyleEntityUsingStyleCode );
            filter.Relations.Add( MyProductEntity.Relations.ProductTypeEntityUsingProductTypeID );
            filter.Relations.Add( MyProductEntity.Relations.ManufacturerEntityUsingManufacturerID );
            filter.Relations.Add( MyManufacturerEntity.Relations.CompanyEntityUsingManufacturerID );
            filter.PredicateExpression.Add( PredicateFactory.CompareValue(RidingStyleFieldIndex.StyleGeneral, ComparisonOperator.Equal, styleGeneral) );
            filter.PredicateExpression.Add( PredicateFactory.CompareValue(ProductTypeFieldIndex.TypeName, ComparisonOperator.Equal, "Frame") );
            filter.PredicateExpression.Add( PredicateFactory.CompareValue(CompanyFieldIndex.CompanyName, ComparisonOperator.Equal, companyName) );

            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection( products, filter, prefetchPath );

            return products;
        }

Now I am seeing the product versions being fetched using SQL Profiler. I am trying to use this code to get the one returned instance of ProductVersionEntity and its throwing the error "Object reference not set to an instance of an object". So even though there are 65 rows being returned from the query the frame.ProductVersion property is still an empty instance of a collection.

ProductVersionEntity frameVersion = (ProductVersionEntity)frame.ProductVersion[0];

Can you tell me what I am going wrong?

I'm not sure why you're doing this: IRelationCollection filterVersion = new RelationCollection(); filterVersion.Add( MyProductVersionEntity.Relations.ProductEntityUsingProductID );

as the sort is applied on the ProductVersion entities already. Could you try without that line, please?

Otis wrote:

I'm not sure why you're doing this: IRelationCollection filterVersion = new RelationCollection(); filterVersion.Add( MyProductVersionEntity.Relations.ProductEntityUsingProductID );

as the sort is applied on the ProductVersion entities already. Could you try without that line, please?

I've commented out these two lines and removed the filterVersion from the prefetch path so the code now looks like this. I'm still not getting any results in the product.ProductVersion collection even though I can see that the query is returning results using the SQL Profiler. What's next?

//          IRelationCollection filterVersion = new RelationCollection();
//          filterVersion.Add( MyProductVersionEntity.Relations.ProductEntityUsingProductID );
            ISortExpression sort = new SortExpression();
            sort.Add( SortClauseFactory.Create(ProductVersionFieldIndex.RPPrice, SortOperator.Ascending) );
            prefetchPath.Add( MyProductEntity.PrefetchPathProductVersion, 1, null, null, sort );

tprohas wrote:

Otis wrote:

I'm not sure why you're doing this: IRelationCollection filterVersion = new RelationCollection(); filterVersion.Add( MyProductVersionEntity.Relations.ProductEntityUsingProductID );

as the sort is applied on the ProductVersion entities already. Could you try without that line, please?

I've commented out these two lines and removed the filterVersion from the prefetch path so the code now looks like this. I'm still not getting any results in the product.ProductVersion collection even though I can see that the query is returning results using the SQL Profiler. What's next?

Hmm. After re-checking your code more carefully, I saw that you added the relation to sort on a related field. The relation though is the wrong one I think (you should use the opposite relation). You use ProductVersion -> Product but you're sorting on ProductVersion's field RPPrice, though you should use: Product -> ProductVersion. I'm not sure if this would make a difference in your case, but it could.

I've checked the code and it does pass on the sorter and relations set for a node to the fetcher, so the data indeed should be loaded. I'll see if I can write up a reasonable testcase here which represents exactly what you're doing. I don't think the other prefetch path nodes are influencing the resultset.

I'm sorry this is somewhat trial/error.

Otis wrote:

tprohas wrote:

Otis wrote:

I'm not sure why you're doing this: IRelationCollection filterVersion = new RelationCollection(); filterVersion.Add( MyProductVersionEntity.Relations.ProductEntityUsingProductID );

as the sort is applied on the ProductVersion entities already. Could you try without that line, please?

I've commented out these two lines and removed the filterVersion from the prefetch path so the code now looks like this. I'm still not getting any results in the product.ProductVersion collection even though I can see that the query is returning results using the SQL Profiler. What's next?

Hmm. After re-checking your code more carefully, I saw that you added the relation to sort on a related field. The relation though is the wrong one I think (you should use the opposite relation). You use ProductVersion -> Product but you're sorting on ProductVersion's field RPPrice, though you should use: Product -> ProductVersion. I'm not sure if this would make a difference in your case, but it could.

I've checked the code and it does pass on the sorter and relations set for a node to the fetcher, so the data indeed should be loaded. I'll see if I can write up a reasonable testcase here which represents exactly what you're doing. I don't think the other prefetch path nodes are influencing the resultset.

I'm sorry this is somewhat trial/error.

Otis,

Again thank you for the help. I think your right about the relation direction. I changed my code to the following and it fixed the problem. You can see this change on line 25 where I added "filter.Relations.Add( MyProductEntity.Relations.ProductVersionEntityUsingProductID );". This is now working and I understand why its working. Enjoy your day!

        public EntityCollection GetFrameForWebUI(string styleGeneral, string companyName)
        {
            EntityCollection products = new EntityCollection( new ProductEntityFactory() );

            IPrefetchPath2 prefetchPath = new PrefetchPath2( (int)EntityType.ProductEntity );
            prefetchPath.Add( MyProductEntity.PrefetchPathProductType );
            prefetchPath.Add( MyProductEntity.PrefetchPathRidingStyle );
            IPrefetchPathElement2 manufacturerNode = prefetchPath.Add( MyProductEntity.PrefetchPathManufacturer );
            manufacturerNode.SubPath.Add( MyManufacturerEntity.PrefetchPathCompany );
            
            IPrefetchPathElement2 frameNode = prefetchPath.Add( MyProductEntity.PrefetchPathFrame );
            frameNode.SubPath.Add( MyFrameEntity.PrefetchPathFrameSize );
            frameNode.SubPath.Add( MyFrameEntity.PrefetchPathFrameColor );

//          IRelationCollection filterVersion = new RelationCollection();
//          filterVersion.Add( MyProductVersionEntity.Relations.ProductEntityUsingProductID );
            ISortExpression sort = new SortExpression();
            sort.Add( SortClauseFactory.Create(ProductVersionFieldIndex.RPPrice, SortOperator.Ascending) );
            prefetchPath.Add( MyProductEntity.PrefetchPathProductVersion, 1, null, null, sort );
            
            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.Relations.Add( MyProductEntity.Relations.RidingStyleEntityUsingStyleCode );
            filter.Relations.Add( MyProductEntity.Relations.ProductTypeEntityUsingProductTypeID );
            filter.Relations.Add( MyProductEntity.Relations.ManufacturerEntityUsingManufacturerID );
            filter.Relations.Add( MyProductEntity.Relations.ProductVersionEntityUsingProductID );
            filter.Relations.Add( MyManufacturerEntity.Relations.CompanyEntityUsingManufacturerID );
            filter.PredicateExpression.Add( PredicateFactory.CompareValue(ProductVersionFieldIndex.ActiveState, ComparisonOperator.Equal, ProductManager.ACTIVE_STATE_ACTIVE) );
            filter.PredicateExpression.Add( PredicateFactory.CompareValue(RidingStyleFieldIndex.StyleGeneral, ComparisonOperator.Equal, styleGeneral) );
            filter.PredicateExpression.Add( PredicateFactory.CompareValue(ProductTypeFieldIndex.TypeName, ComparisonOperator.Equal, "Frame") );
            filter.PredicateExpression.Add( PredicateFactory.CompareValue(CompanyFieldIndex.CompanyName, ComparisonOperator.Equal, companyName) );

            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection( products, filter, prefetchPath );

            return products;
        }

Great it's finally solved!