Hi, This is what I want to achieve...

SELECT ProperFromDay = CONVERT(DateTIME, SUBSTRING(FromDay, 4, LEN(FromDay))) FROM myTABLE ORDER BY ProperFromDay

--where FromDay is a VARCHAR with values like "Wed 17 Feb 2010", "Sat 01 Mar 2008", "Wed 17 Mar 2010", "Sat 27 Feb 2010"

I was hoping for something like ...

myTABLECollection myCol = new myTABLECollection(); myCol.GetMulti(null); myCol.Sort([the alias column]); //etc

I know the above is not exactly the correct syntax but I am sure there must be a way to sort using an alias (or any other way to trim the "Sat", "Wed" from the varchar to make it possible to convert)

Thanking you in advance

There is - you need to investigate 2 things.

http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_expressionsaggregates.htm

Sort clauses can work on expressions (ie calculated fields) as well as plain fields

http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_dbfunctioncall.htm#functionsinexpressions

DBFunctionCalls, which allow you to call native sql functions

Hope this gets you started in the right direction, feel free to come back to us if you have any further questions.

Matt

Hi, Thanks, the links did me some good but am still not able to get the desired results. I guess my query was not clear enough so here are the details:

The collection I talked about is "already" retrieved and am actually trying to "sort the collection" itself i.e.

OrderEntity ordEntity = new OrderEntity(1234);

so when I say ordEntity.OrderChildren I mean the collection of OrderChildren (which is a child table)

what I was using

ordEntity.OrderChildren.Sort((int)OrderChildrenFieldIndex.FromDay, System.ComponentModel.ListSortDirection.Ascending);

but since FromDay is a VARCHAR and I want to sort it in the form of DATETIME, I am facing this issue (which is hanled in SQL as under):

SELECT
ProperFromDay = CONVERT(DateTIME, SUBSTRING(FromDay, 4, LEN(FromDay))) 
FROM OrderChildren
ORDER BY ProperFromDay

and what I am hoping for is like (using above)

ordEntity.OrderChildren.Sort(SUBSTRING((int)OrderChildrenFieldIndex.FromDay, 0, 4), System.ComponentModel.ListSortDirection.Ascending);

some code examples will help, thanks.

The Sort() method, sorts at client side, not on the database side. So maybe you want to cinsider sorting the collection when fetching it..i.e. on the database side.

The Sort() method, sorts at client side, not on the database side.

That is exactly what I wanted, to sort at the client side using some kind of technique to "Substring" the already retrieved FromDay rows and sort on it... but I guess if that is not possible I will have to retrieve the data in a "sorted" form.

Thanks for your support, I appreciate it...

Okay, now when I am dealing with "Sort" during the retrieval process...what exactly is wrong here:

OrderChildrenCollection opCol = new OrderChildrenCollection();
ISortExpression sorter = new SortExpression();

sorter.Add(new SortClause(new EntityField("FromDay", new Expression(new DbFunctionCall("SUBSTRING({0}, 4, LEN({0}))", new object[] {OrderChildrenFields.FromDay}))), SortOperator.Ascending));
            
            
opCol.GetMulti(OrderChildrenFields.OrderId == 12345, 0, sorter);

You can view the original query from the earlier post. Thanks

sorter.Add(new SortClause(new EntityField("FromDay",

it could be because there is already a field called "FromDay" - try using a different alias for the sort expression.

On the SortClause, you have to set the EmitAliasForExpressionAggregateField = false. A similar example can be found here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=16947

Thanks for all your help and here is what I applied

DataTable dtResults = new DataTable();
            IPredicateExpression filter = new PredicateExpression();

            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(OrderChildrenFields.OrderChildrenId, 0);
            fields.DefineField(OrderChildrenFields.OrderId, 1);
            fields.DefineField(OrderChildrenFields.FromDay, 2, "ProperFromDay");
            fields[2].ExpressionToApply = new DbFunctionCall("CONVERT(DATETIME, SUBSTRING({0}, 4, LEN({0})))", new object[] {OrderChildrenFields.FromDay});

            ISortExpression sorter = new SortExpression();

            sorter.Add(new SortClause(OrderChildrenFields.FromDay.SetFieldAlias("ProperFromDay"), SortOperator.Ascending));

            filter.Add(OrderChildrenFields.OrderId == orderID);
            
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dtResults, 0, sorter, filter, null, true, null, null, 0, 0);

BUT ... the resultant query still doesn't use the alias field...i.e. (the rest is perfect)

...
ORDER BY 
[myDB].[dbo].[OrderChildren].[FromDay] ASC',N'@OrderId1 int',@OrderId1=7070

How can I modify the above code to make query as:

...
ORDER BY 
ProperFromDay ASC',N'@OrderId1 int',@OrderId1=7070

Thanking you again

In that case you should set EmitAliasForExpressionAggregateField to true.

In the first case, I thought you wanted to fetch an EntityCollection, and in this case, no expression or alias would be in the select list, that's why you should output the expression as is in the Order clause.

Now that you have the expression and alias in the select list, then you should just use the alias in the Order by clause.

You mean this:

ISortExpression sorter = new SortExpression();

sorter.Add(new SortClause(OrderChildrenFields.FromDay.SetFieldAlias("ProperFromDay"), SortOperator.Ascending));

cause its not working...

...you should just use the alias in the Order by clause...

• Can you provide the syntax. Thanks

            DataTable dtResults = new DataTable();
            IPredicateExpression filter = new PredicateExpression();

            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(OrderChildrenFields.OrderChildrenId, 0);
            fields.DefineField(OrderChildrenFields.OrderId, 1);
            fields.DefineField(OrderChildrenFields.FromDay, 2, "ProperFromDay");
            fields[2].ExpressionToApply = new DbFunctionCall("CONVERT(DATETIME, SUBSTRING({0}, 4, LEN({0})))", new object[] {OrderChildrenFields.FromDay});

            SortClause sortClause = new SortClause(fields[2], null, SortOperator.Ascending);
            sortClause.EmitAliasForExpressionAggregateField = true;
            SortExpression sorter = new SortExpression(sortClause);

            filter.Add(OrderChildrenFields.OrderId == orderID);
            
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dtResults, 0, sorter, filter, null, true, null, null, 0, 0)

;

Thanks alot man...that finally did it. Am grateful to all who helped.