I would like to generate a SortExpression/SortClause that is based on an expression, e.g.

SORT BY (colA + colB) ASC

I have done some reading here in the forum and I'm not quite sure where the limitations are (what can be done, what not). Especially that some of these threads are a few years old: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14331 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14725 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4801 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=13532 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=13313

The very optimal solution would be that one could sort dynamically with the same simple expression that DataTableColumn.Expression offers ... ?

You can use an expression in a sort clause - please see the documentation here

Beyond that, I'm not quite sure what you're asking - perhaps you could give it a go, and then come back to us with any more specific questions...?

Matt

MTrinder wrote:

You can use an expression in a sort clause - please see the documentation here

Beyond that, I'm not quite sure what you're asking - perhaps you could give it a go, and then come back to us with any more specific questions...?

Matt

I looked through the part of the docu that you suggested and I could do something like this:

EntityField2 sortField = new EntityField2("sortField", new Expression(MyEntityFields.ColA, ExOp.Add, MyEntityFields.ColB));
SortClause sorter = new SortClause(sortField, null, SortOperator.Ascending);

But is there a way that I can just generate a SortClause from an expression given in a string? Meaning something along the lines of:

EntityField2 sortField = new EntityField2("sortField", "ColA + ColB");
SortClause sorter = new SortClause(sortField, null, SortOperator.Ascending);

What I am trying to achieve is an universal concept of creating GridView fields that will make me able to a) calculate values of cells based on values of other cells in the same row, b) sort the underlying LGP data source through a sort clause c) and sort the column of the GridView based on the expression

The problem with the Expression is that even if I am able to calculate the values of the left and right operand (assuming that they are EntityFields), I am not really able to calculate the result of the expression in a reasonable way since the operator is not really universal (can only be used to generate a query text).

protected double getFieldValue(DataRowView dataItem)
{
    // get the expression from the entity field associated grid view field
    IExpression exp = this.DbField.ExpressionToApply;

    // get the values of the left and right operand of the expression
    double leftValue = getOperandFieldValue(dataItem, exp.LeftOperand); // e.g. = 2
    double rightOperand = getOperandFieldValue(dataItem, exp.RightOperand); // e.g. = 3

    ExOp operator = exp.Operator; // e.g. ExOp.Add
    
    double result = operator.Evaluate(leftValue, rightValue); // e.g. 2 + 3 = 5

    return result;
}

I'm not even able to figure out how to transform the expression in a string using .ToQueryText(). This way I could possible try using this for some functionalities offered by the DataTable class.

I think you don't need DataTable funcionallity. The fisrt link you post should explain how to do it. Or... we dont undertand you welll, please be more specific (some example or code snppet should be helpful).

daelmo wrote:

I think you don't need DataTable funcionallity. The fisrt link you post should explain how to do it. Or... we dont undertand you welll, please be more specific (some example or code snppet should be helpful).

hmmm ... it's really difficult to post some code that would actually help without posting half of the project here ... so I will post you a link to the current solution that we have:

http://webcast.crezbasketball.com/pbl/ -> select either team by clicking at the logo at the top (this functionality is based on a .xml file structure and I am now in the process of redesigning it based on a database, with LGP of course)

If you look at the columns of the teams "Stats by game" table, you will see that some columns are "dependent on others" (= values are expressions of other columns), e.g. Reb (T) = Reb (D) + Reb (O). There is for example also functionality the creates the footer sums, or columns that are combining two columns into one, e.g. click "View Totals" and then take a look at the FG2 (M-A) column.

Beside that there are also more complicated calculations involved, e.g. calculation of the percentage is currently solved like this:

// this = DataTable
this.Columns["fg2Perc"].Expression = "IIF(fga2 = 0, 0, 100 * fgm2 / fga2)";

Does this give you a better idea about my headaches?

I know, I could solve the "expression stuff" on database level. In particular I could just add another column in the view and calculate the value with sql expressions. But I've read a view articles that suggest that in terms of performance it is better to calculate dynamic columns like on the fly in memory.

If you already define a field in the dynamicList which has the required permission and all you need is to sort by this field.

Then all you should do is pass that field to the SortClause. And set its EmitAliasForExpressionAggregateField property to false.