Hi,

This is a request for help with a specific type of query. I just can't see how to translate it for LLBLGen Pro.

The SQL I need to emulate is this:

SELECT * FROM Categories ORDER BY dbo.GetFullSequenceNumber(ID, '')

and the data looks like this:

ID____Title____SequenceNumber____ParentID 1 Cat1 1 0 2 Cat2 1 1 3 Cat3 2 0

As I hope you can see, this allows a hierarchy of categories to be created. The function GetFullSequenceNumber allows us to order our results as a flattened view of the tree. It returns the following for IDs 1, 2 and 3:

1: 1. 2: 1.1 3: 2.

So, that's the background, the question is how do I do the order by bit in an Adapter-type world?

Never mind, I solved it!

For anyone wishing to do this, the trick is outlined below:

SortExpression se = new SortExpression();

// set my dbfunction at the sorter clause
var exp = new DbFunctionCall("dbo.GetFullSequenceNumber", new object[] { CategoryFields.ID, "" });
var field = new EntityField2("fullseq", exp);
SortClause sc1 = new SortClause(field, new FieldPersistenceInfo() ,SortOperator.Ascending);
sc1.EmitAliasForExpressionAggregateField = false;
se.Add(sc1);

The only bit I'm not sure about is my empty FieldPersistenceInfo (I've no idea what that does), but the code seems to work great!

orinoco77 wrote:

The only bit I'm not sure about is my empty FieldPersistenceInfo (I've no idea what that does), but the code seems to work great!

Don't worry about FieldPersistenceInfo. You can pass null into that parameter. Thanks for sharing