Hi,
I worked on this problem this weekend and I'm trying to implement the equivalent of:
select symbol from stock
where symbol in ('nflx', 'bby', 'conn')
order by
case when symbol='nflx' then '0' when symbol='bby' then '1' when symbol='conn' then '2' end
This works in SQL Management Studio and I see the desired unsorted list.
In code, I have the following:
//...
filter.Add(new FieldCompareRangePredicate(StockFields.Symbol, symbols));
//...
// build the sort expression
StringBuilder sb = new StringBuilder("CASE ");
for (int i = 0; i < symbols.Count; i++) {
sb.AppendFormat("WHEN Symbol = '{0}' THEN '{1}' ", symbols[i], i);
}
sb.Append("END");
IExpression symbolPart = new DbFunctionCall(sb.ToString(), new object[] { StockFields.Symbol });
IEntityField symbolPartField = StockFields.Symbol.SetExpression(symbolPart);
// prepare the sorter
ISortClause symbolPartSortClause = new SortClause(symbolPartField, null, SortOperator.Ascending);
symbolPartSortClause.EmitAliasForExpressionAggregateField = true;
sort = new SortExpression(symbolPartSortClause);
DataTable dt = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dt, 0, sort, filter, relations, false, null, null, 0, 0);
When I use SQL Profiler to check the emitted SQL, it's a long string but it ends with:
( ( [FinDataDB].[dbo].[Stock].[Symbol] IN (@Symbol1, @Symbol2, @Symbol3))) ORDER BY [Symbol] ASC',
N'@Symbol1 nvarchar(16),@Symbol2 nvarchar(16),@Symbol3 nvarchar(16)',@Symbol1=N'NFLX',@Symbol2=N'BBY',@Symbol3=N'CONN'
It looks like the "order by case" isn't being incorporated.
May I please know what I'm doing wrong?
Thank you,
Cheng