Using LLBLGen Pro v3.1 Final (Feb 7, 2011)

Hi,

I'm trying to add some case Order By logic into my query and it's not being generated correctly. I read that I should be calling DbFunctionCall but it seems the expression is still not being applied.

What I'm trying to do here is that if Employee Name = Supervisor Name then it sorts by order number. If it doesn't then it sorts by an alphabetical sort key.

Right now it's just a regular Order by SortKey and OrderNum. How would I go to apply the case?

EntityField2 orderNum = EmployeeSupervisorFields.OrderNum;
EntityField2 sortKey = EmployeeSupervisorFields.SortKey;

sortKey.ExpressionToApply = new DbFunctionCall("CASE WHEN {0} <> {1} THEN {2} END", new object[] { EmployeeSupervisorFields.EmployeeName, EmployeeSupervisorFields.SupervisorName, EmployeeSupervisorFields.SortKey });

orderNum.ExpressionToApply = new DbFunctionCall("CASE WHEN {0} = {1} THEN {2} END", new object[] { EmployeeSupervisorFields.EmployeeName, EmployeeSupervisorFields.SupervisorName, EmployeeSupervisorFields.OrderNum });

ISortExpression sort = new SortExpression(sortKey | SortOperator.Ascending);
sort.Add(orderNum | SortOperator.Ascending);

Thanks, Gilbert

You are almost done. You just need to use the EmitAliasForExpressionAggregateField property. This is an approx code:

EntityField2 orderNum = EmployeeSupervisorFields.OrderNum;
EntityField2 sortKey = EmployeeSupervisorFields.SortKey;

sortKey.ExpressionToApply = new DbFunctionCall("CASE WHEN {0} <> {1} THEN {2} END", new object[] { EmployeeSupervisorFields.EmployeeName, EmployeeSupervisorFields.SupervisorName, EmployeeSupervisorFields.SortKey });

orderNum.ExpressionToApply = new DbFunctionCall("CASE WHEN {0} = {1} THEN {2} END", new object[] { EmployeeSupervisorFields.EmployeeName, EmployeeSupervisorFields.SupervisorName, EmployeeSupervisorFields.OrderNum });

// clause to sort. indicate the EmitAliasForExpressionAggregateField  to sort bases on the expression
ISortClause sortKeySortExp = new SortClause(sortKey , null, SortOperator.Ascending);
sortKeySortExp.EmitAliasForExpressionAggregateField = false;

// clause to sort. indicate the EmitAliasForExpressionAggregateField  to sort bases on the expression
ISortClause orderNumSortExpt = new SortClause(orderNum , null, SortOperator.Ascending);
orderNumSortExpt.EmitAliasForExpressionAggregateField = false;

// build the sort expression
ISortExpression sort = new SortExpression();
sort.Add(sortKeySortExp);
sort.Add(orderNumSortExpt );

Related thread: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=16947&StartAtMessage=0&#94824

Thanks, it's working great now after adding the EmitAliasForExpressionAggregateField.

This is LLBL gold!!

Thanks!

mkucera wrote:

This is LLBL gold!!

Thanks!

Thanks for the feedback!