I'm currently struggling with the following problem.

I'd like to create an expression that I can apply dynamically to the column that the gridView is sorted by. In particular I have a gridView for basketball stats that contains a gamesPlayed column and if I would like to sort by a given column (points, fouls, rebounds, etc.), I'd like the following expression to apply:

CASE WHEN gamesPlayed = 0 THEN 0 ELSE [fieldToSortBy] * 1.0 / gamesPlayed END AS _sortField

(the 1.0 is needed cause otherwise the result of [fieldToSortBy] / gamesPlayed will always be rounded to the next integer)

Furthermore, is there a way how to "predefine" the whole expression and then just set the fieldToSortBy so to say as an argument?

I starting working on something, but I didn't get further than this:

IExpression mul = new Expression(field, ExOp.Mul, 1.0);
IExpression exp = new Expression(mul, ExOp.Div, gpField);
IEntityField2 sortField = new EntityField2("_sortField", exp);

This doesn't check for whether gp = 0, and the expression also has to be created once the field is actually known instead of being created once independent of what the field might be.

Hi there,

Your question has two parts: 1. How to construct such sort expression 2. How to handle this sorter dynamically.

So, lets do (1). You have almost there, here is an approximate code:

IExpression mul = new Expression(field, ExOp.Mul, 1.0);
IExpression exp = new Expression(mul, ExOp.Div, gpField);
IExpression caseFunc = new DBFunctionCall("CASE WHEN {0} = 0 THEN 0 ELSE {1} END"
     , new object[]{ TeamFields.GamesPlayed, exp  });

EntityField2 sortField = new EntityField2("sortField", caseFunc);
SortExpression sorter = new SortExpression( sortField | SortOperator.Ascending);

SortClause caseSortClause = new SortClause(sortField, null, SortOperator.Ascending);
// this is important, setting this to false will emit our custom expression at ORDER BY clause
caseSortClause.EmitAliasForExpressionAggregateField = false;
SortExpression sorter = new SortExpression(caseSortClause);

Related thread:[http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14331](http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14331)

You can use such sorter right away. Now lets move to (2). You should do this in your own code, intercepting the sorting action and applying the right sorter to the next fetch. Is that ok for you? If you want something different please show us how your code looks like and what exactly you want

Thank you for the quick help

Can you please elaborate a bit more on (2) though? What precisely do you mean by "intercepting the sorting action"?

If I would "prebuilt" a simple expression, I could just do that:

IExpression exp = new Expression(null, ExOp.Mul, 2.0);

and later on use this (e.g. in another method):

exp.LeftOperator = fieldToMultiplyByTwo;

Is there a way how I can do this with the more complicated expression to set the field that should be divided by gamesPlayed?

I think what David was trying to say, is that you should first handle the Sort event of the GridView, or better use The LLBLGenProDataSource's PerformSelect event in a "LivePersistence = false" mode. To catch the Sorter from the event Arguments, then you can call a method to set the expression to it, based on the field that was used in the Sorter.

well ... I'm using my own GridView class (or an extension thereof), so I'm doing what you mean by "intercepting the sorting" in my base class.

What I'm trying to do though is defining the "dynamic sort expression" on a higher level. Therefore I was wondering if I can define the sort expression once with a "field placeholder" so that when the base class does the sorting on a given field, the field is set into the expression and the expression is evaluated.

From what I understand, I can't do this with a "simple" expression, but would have to write a wrapper around the expression, that when given the field, returns the expression.

Does this make sense?

The field is inside a complex expression inside a sortClause. But you can still access it. Please try using the first Item of the sortExpression which will give you an ISortClause, there you can find the FieldToSortCore (IEntityFieldCore), inwhich you can find the ExpressionToApply....and so on till get your hands on the field.

So build the expression initially with any field, and at runtime try to switch the field as explained above.