I'm using this code to select the row with the Min ProductVersionID and have the problem that the column from the select statement is missing the column name. I tried changing the Alias to be "VersionID" instead of "ProductVersionID" and it works, is it not possible to use the same name as the original column?

public ProductColorsTypedList GetProductColors(int productID)
{
    using(WSDataAccessAdapter adapter = new WSDataAccessAdapter(DBUtil.ConnectionString))
    {
        ProductColorsTypedList colorsList = new ProductColorsTypedList();
        IRelationPredicateBucket filter = (IRelationPredicateBucket)colorsList.GetRelationInfo();
        filter.PredicateExpression.Add( PredicateFactory.CompareValue(ProductVersionFieldIndex.ActiveState, ComparisonOperator.Equal, ActiveState.Web ) );
        //filter.PredicateExpression.Add( PredicateFactory.CompareValue(ProductFieldIndex.ActiveState, ComparisonOperator.Equal, ActiveState.Web) );
        filter.PredicateExpression.Add( PredicateFactory.CompareValue(ProductVersionFieldIndex.ProductID, ComparisonOperator.Equal, productID ) );
    
        // Create Group By Clause
        ResultsetFields fields = (ResultsetFields)colorsList.GetFieldsInfo();
        fields["ProductVersionID"].AggregateFunctionToApply = AggregateFunction.Min;
        fields["ProductVersionID"].Alias = "VersionID";
        IGroupByCollection  groupBy = new GroupByCollection();
        for(int i = 0; i < fields.Count; i++)
        {
            IEntityField2 field = fields[i] as IEntityField2;
            // Add all the fields to the group by clause except for
            // fields that have an aggregate function applied.
            if ( field.AggregateFunctionToApply == AggregateFunction.None )
                groupBy.Add(field);
        }

        adapter.FetchTypedList(colorsList.GetFieldsInfo(), colorsList, filter, 0, null, false, groupBy);
        return colorsList;
    }
}

Hi, Question: Is the database column called "ProductVersionID" or "VersionID"

This is a typical behaviour. the same happens when you have the same query run on the Query Analyzer, yet you can have the same column name if you specified it as the Alias.

sparmar2000 wrote:

Hi, Question: Is the database column called "ProductVersionID" or "VersionID"

The database column name is "ProductVersionID". I used "VersionID" as the alias value because using something different then the actual column name had to be used to make the alias get used in the generated sql. I want the actual column name to be the alias, if I wrote my sql by hand I could do this, but the generated code won't.

Now I see what you mean.

May I suggest a workaround: to create a sql view that returns the products colors do it all in the view (grouping, aliassing...) then create a typedview mapped on it.

I always tend to do it that way.

You have to specify an alias, because the sql will be MIN(columname), but the code itself doesn't know if there's an expression as well. So it also might have been: MIN(col1 + col2). This means that you need to specify an alias for that field.