order by not working

Question

I am using an objectdatasource with a gridview to get data from my orm class, but I cannot get it to order by properly. I am using the code below but it does not come up in descending order like I have specified below. What am I missing? Using subsonic 2.1

<DataObjectMethod(DataObjectMethodType.Select, True)> Public Function FetchByPatID(ByVal PatientID As Object) As VisitCollection

    Dim coll As VisitCollection = New VisitCollection().Where("PatientID", PatientID).Load()
    **OrderBy.Desc(Visit.DosColumn)**
    Return coll

End Function

Answer 1

The order by is executed against the database as part of your query. It needs to be added before the .Load() method is called.

Dim coll As VisitCollection = New VisitCollection().Where("PatientID", PatientID).OrderByDesc(Visit.Columns.DosColumn).Load()

Answer 2

Ranomore is correct. You must specify the OrderBy before you actually execute the command as the ordering is done on the database.

If you are using SubSonic 2.1 I much prefer the new syntax that has been added which makes the Query much more readable

SubSonic.Select.AllColumnsFrom(Of Visit)().Where(Visit.PatientIDColumn).isEqualTo(PatientID).OrderByDesc(Visit.DosColumn.ColumnName).ExecuteAsCollection(Of VisitCollection)()