No sorting desired for TypedListDAO.GetMultiAsDataTable

Posts   
 
    
pepipepi
User
Posts: 5
Joined: 11-May-2014
# Posted on: 04-Jun-2014 19:53:29   

Hi,

I have some code that does the following:

...

List<string> symbols;

...

IPredicateExpression filter = new PredicateExpression(); filter.Add(new FieldCompareRangePredicate(StockFields.Symbol, symbols));

...

DataTable dt = new DataTable(); TypedListDAO dao = new TypedListDAO(); dao.GetMultiAsDataTable(fields, dt, MaxResults, sort, filter, relations, false, null, null, 0, 0);

My problem has to do with the sort order. Under some circumstances, I would like the order of the rows in the data table to be the same as the order of the symbols that are passed in to the FieldCompareRangePredicate.

I've not been able to achieve this as it looks like the GetMultiAsDataTable will sort the rows even if null is passed in for the sort parameter.

Is there a way to achieve "no sort"?

Thank you, Cheng

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 05-Jun-2014 08:27:07   

Hi Cheng,

If no sort is specified, the data is retrieved as your DB server returns it. So, How would you do it in a SQL query? That might give us a hint so we can give you an advise on how to achieve that SQL query.

David Elizondo | LLBLGen Support Team
pepipepi
User
Posts: 5
Joined: 11-May-2014
# Posted on: 05-Jun-2014 16:54:55   

Hi David,

Thanks for your reply. I did a query in SQL Server Management Studio and it is indeed the server that does the sorting.

I found a post on http://bytes.com/topic/sql-server/answers/516983-ordering-results-order-clause that discusses how to work around this and I'll look to do that.

Thanks for your help.

Cheng

pepipepi
User
Posts: 5
Joined: 11-May-2014
# Posted on: 16-Jun-2014 16:21:07   

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

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 17-Jun-2014 08:05:31   

Use:

symbolPartSortClause.EmitAliasForExpressionAggregateField = false;
David Elizondo | LLBLGen Support Team
pepipepi
User
Posts: 5
Joined: 11-May-2014
# Posted on: 17-Jun-2014 15:45:38   

Hi David,

Thanks for this. The Order By Case is emitted now, but I'm getting syntax errors with the generated SQL.

With:

IExpression symbolPart = new DbFunctionCall(sb.ToString(), new object[] { StockFields.Symbol });

The generated SQL ends with:

ORDER BY CASE WHEN Symbol = ''NFLX'' THEN ''0'' WHEN Symbol = ''BBY'' THEN ''1'' WHEN Symbol = ''CONN'' THEN ''2'' END ([FinDataDB].[dbo].[Stock].[Symbol]) ASC',N'@Symbol1 nvarchar(16),@Symbol2 nvarchar(16),@Symbol3 nvarchar(16)',@Symbol1=N'NFLX',@Symbol2=N'BBY',@Symbol3=N'CONN'

The DB server complains about the bolded part with a "Incorrect syntax near 'FinDataDB'" error.

If I change the code to either:

IExpression symbolPart = new DbFunctionCall(sb.ToString(), new object[] {});

or:

IExpression symbolPart = new DbFunctionCall(sb.ToString(), null);

The generated SQL ends with:

ORDER BY CASE WHEN Symbol = ''NFLX'' THEN ''0'' WHEN Symbol = ''BBY'' THEN ''1'' WHEN Symbol = ''CONN'' THEN ''2'' END () ASC',N'@Symbol1 nvarchar(16),@Symbol2 nvarchar(16),@Symbol3 nvarchar(16)',@Symbol1=N'NFLX',@Symbol2=N'BBY',@Symbol3=N'CONN'

The DB server complains about the parentheses (bolded) with a "Incorrect syntax near ')'" error.

Is there a way to suppress the parameter portion of DbFunctionCall?

Thank you, Cheng

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Jun-2014 10:48:29   

The string you pass to the DbFunctionCall should look like: CASE WHEN {0} = ''NFLX'' THEN ''0'' WHEN {0} = ''BBY'' THEN ''1'' WHEN {0} = ''CONN'' THEN ''2'' END

so the field, which you pass as parameter is then filled in at the {0} spots.

To do so, you should change:

sb.AppendFormat("WHEN Symbol = '{0}' THEN '{1}' ", symbols[i], i);

into

sb.AppendFormat("WHEN {{0}} = '{0}' THEN '{1}' ", symbols[i], i);

Hope this helps simple_smile

Frans Bouma | Lead developer LLBLGen Pro
pepipepi
User
Posts: 5
Joined: 11-May-2014
# Posted on: 18-Jun-2014 17:04:37   

Hi Frans,

Thanks a lot, that fixed it! simple_smile