OrderBy(Descending)
Use the OrderBy
and OrderByDescending
methods to sort the records returned by a query command on a named column in an ascending or descending order respectively.
There are two ways to form an OrderBy
or an OrderByDescending
method.
- You can specify the column names as named parameters.
- You can specify the column names as part of the method
Syntax
public SimpleQuery OrderBy( ObjectReference columnNameReference ) public SimpleQuery OrderBycolumnName( ) public SimpleQuery OrderByDescending( ObjectReference columnNameReference ) public SimpleQuery OrderBycolumnNameDescending( )
Parameters
columnNameReference
- Type:
ObjectReference
A reference to the column being sorted against written using either dot notation (db.Table.Column) or index notation (db["Table"]["Column"]). columnName
- Type:
string
The name or alias of the column being sorted against
Return Value
Type: SimpleQuery
A SimpleQuery
object containing an OrderBy clause.
Exceptions
Exception | Condition |
---|---|
NullReferenceException |
OrderBy has been called directly on a table reference without a base command (All, FindAllBy etc) |
ArgumentException |
columnNameReference is not a valid, single column reference- or - Both columnNameReference and columnName are used in the same call.- or - OrderBy has been called twice or more in the same daisychain of commands instead of once followed by many calls to ThenBy. |
UnresolvableObjectException |
You have attempted to order results by a column in a secondary joined table using a fluid style columnName rather than the named parameter style columnNameReference |
[ADO Adapter Only] Simple.Ado.AdoException |
The column specified in the call to OrderBy is not in one of the tables being retrieved in the main query command |
Note that issues #269 - #272 are still open with regards to exceptions thrown by OrderBy.
Remarks
OrderBy
and OrderByDescending
are designed to specify the first field on which a returned dataset is to be ordered. Any further ordering should be specified using ThenBy
and ThenByDescending
.
OrderBy
and OrderByDescending
expect only a single column reference so trying to specify two columns using a fluid style will not work. For example, trying to call OrderByArtistIdAndAlbumId
will result in Simple.Data trying to order the result set on a column called ArtistIdAndAlbumId. You’ll need to use OrderByArtistId().ThenByAlbumId()
instead.
Similarly, if you try and specify two columns to either OrderBy
and OrderByDescending
using a hybrid approach of fluid and named parameters - for example OrderByGenreId(db.Albums.Title)
, the named parameter is ingored and OrderByGenreId()
is applied to the SimpleQuery
Examples
Single Table
The following example retrieves the contents of the Albums table ordered by the ArtistId
field.
//Fluid style SimpleQuery albums = Database.Open().Albums.All().OrderByArtistId(); //Named parameter style SimpleQuery albums = Database.Open().Albums.All().OrderBy(db.Albums.ArtistId);
Simple.Data sends the following SQL to the database when albums
is evaluated.
select [dbo].[Albums].[AlbumId], [dbo].[Albums].[GenreId], [dbo].[Albums].[ArtistId], [dbo].[Albums].[Title], [dbo].[Albums].[Price], [dbo].[Albums].[AlbumArtUrl] from [dbo].[Albums] ORDER BY [dbo].[Albums].[ArtistId]
The Descending equivalent commands are
//Fluid style SimpleQuery albums = Database.Open().Albums.All().OrderByArtistIdDescending(); //Named parameter style SimpleQuery albums = Database.Open().Albums.All().OrderByDescending(db.Albums.ArtistId);
Joined Tables
The following example queries the album table, left joins the genre table and orders them by a field in the (primary) album table.
SimpleQuery albums = Database.Open().Albums.All() .Select( db.Albums.Title, db.Genre.Name) .LeftJoin(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId) .OrderBy(db.Albums.Title); // .OrderByTitle() also works
Simple.Data sends the following SQL to the database when albums
is evaluated.
select [dbo].[Albums].[Title], [dbo].[Genres].[Name] from [dbo].[Albums] LEFT JOIN [dbo].[Genres] ON ([dbo].[Genres].[GenreId] = [dbo].[Albums].[GenreId]) ORDER BY [dbo].[Albums].[Title]
You can also order this query by a field in the joined table but only using the named parameter style of call.
SimpleQuery albums = Database.Open().Albums.All() .Select( db.Albums.Title, db.Genre.Name) .LeftJoin(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId) .OrderBy(db.Genre.Name); // .OrderByName() throws an UnresolvableObjectException
Using Column Aliases
If a column has been given a unique column alias using the As
method, you can use the fluid style of calling OrderBy to reference the alias.
SimpleQuery albums = Database.Open().Albums.All() .Select( db.Albums.Title, db.Genre.Name.As("GenreName")) .LeftJoin(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId) .OrderByGenreName();
Simple.Data sends the following SQL to the database when albums
is evaluated.
select [dbo].[Albums].[Title], [dbo].[Genres].[Name] from [dbo].[Albums] LEFT JOIN [dbo].[Genres] ON ([dbo].[Genres].[GenreId] = [dbo].[Albums].[GenreId]) ORDER BY GenreName
Using Table Aliases
If a table has been given an alias using the As
method, you can use the named parameter style of calling OrderBy to reference it.
dynamic genreAlias; SimpleQuery albums = Database.Open().Albums.All() .LeftJoin(db.Genre.As("g"), out genreAlias).On(genreAlias.GenreId == db.Albums.GenreId) .Select( db.Albums.Title, genreAlias.Name) .OrderBy(genreAlias.Name);
Simple.Data sends the following SQL to the database when albums
is evaluated.
select [dbo].[Albums].[Title], [g].[Name] from [dbo].[Albums] LEFT JOIN [dbo].[Genres] [g] ON ([g].[GenreId] = [dbo].[Albums].[GenreId]) ORDER BY [g].[Name]