I'm using LLBLGen v2.6 and I have the following problem:

I have a column of type varchar that contains numbers in the format 'yy-00000', for example '11-01234'. Because of a problem validating the user input our database now contains a number of records without the leading 0 after the dash (ie: '11-1234').

The problem is that this messes up our sorting when presenting a list to the user. At the moment I don't have the option of fixing this number, so my idea is to solve this using the following sort expression:

ORDER BY CAST(SUBSTRING(BatchNummer, 4, 5) AS int) DESC

I have tried it using the following code, but it doesn't work:

        
DbFunctionCall castCall = new DbFunctionCall("cast(substring(batchnummer, 4,5) as int)", 
                new object[] {});

EntityField sortField = new EntityField("NumberOnly", castCall);
SortClause numberSortClause = new SortClause(sortField, null, SortOperator.Descending);
numberSortClause.EmitAliasForExpressionAggregateField = false;

batchCollection.GetMulti(null, 0, new SortExpression(numberSortClause));

The resulting query now ends in

ORDER BY cast(substring(batchnummer, 4,5) as int)() DESC

which obviously throws an exception. What is the best way to construct this sort expression?

You are almost there. Use your field instead of "batchnummer" string:

DbFunctionCall castCall = new DbFunctionCall("cast(substring({0}, 4,5) as int)", 
                new object[] {YourEntityFields.BatchNummer});

See this example: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=16947&StartAtMessage=0&#94824

Thanks, it works perfectly now!