Order By : Simple.Data Help

OrderBy(Descending)

Use the OrderBy and OrderByDescending methods to sort the records returns 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`
`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.

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]

ThenBy(Descending)

Use the ThenBy and ThenByDescending methods to specify additional fields on which to sort the records (in an ascending or descending order respectively) following a call to OrderBy and OrderByDescending.

There are two ways to form an ThenBy or an ThenByDescending method.

You can specify the column names as named parameters.
You can specify the column names as part of the method

Syntax

public SimpleQuery ThenBy(
	ObjectReference columnNameReference
)

public SimpleQuery ThenBycolumnName(
)

public SimpleQuery ThenByDescending(
	ObjectReference columnNameReference
)

public SimpleQuery ThenBycolumnNameDescending(
)

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 a ThenBy clause.

Exceptions

Exception	Condition
`InvalidOperationException`	ThenBy has been called without OrderBy in the same daisychain.
`NullReferenceException`	ThenBy 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.
`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`
`Simple.Ado.AdoException`	The column specified in the call to ThenBy is not one of the tables being retrieved in the main query command

Note that issues #277 - #280 are still open with regards to exceptions thrown by ThenBy.

Remarks

ThenBy and ThenByDescending are designed to specify the subsequent fields on which a returned dataset is to be ordered after the first column is specified using OrderBy or OrderByDescending.

ThenBy and ThenByDescending expect only a single column reference so trying to specify two columns using a fluid style will not work. For example, trying to call ThenByArtistIdAndAlbumId will result in Simple.Data trying to order the result set on a column called ArtistIdAndAlbumId. You'll need to use ThenByArtistId().ThenByAlbumId() instead.

Examples

Single Table

The following example retrieves the contents of the Albums table ordered by the AlbumId and then ArtistId fields.

//Fluid style
SimpleQuery albums = Database.Open().Albums.All().OrderByAlbumId().ThenByArtistId();

//Named parameter style
SimpleQuery albums = Database.Open().Albums.All().OrderByAlbumId().ThenBy(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].[AlbumId], [dbo].[Albums].[ArtistId]

The Descending equivalent commands are

//Fluid style 
SimpleQuery albums = Database.Open().Albums.All().OrderByAlbumId().ThenByArtistIdDescending();

//Named parameter style 
SimpleQuery albums = Database.Open().Albums.All().OrderByAlbumId().ThenByDescending(db.Albums.ArtistId);

Joined Tables

The following example queries the album table, left joins the genre table and orders them by two fields 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.AlbumId)
    .ThenBy(db.Albums.Title);    // .ThenByTitle() 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].[AlbumId], [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.Albums.AlbumId)
    .ThenBy(db.Genre.Name);    // .ThenByName() 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 ThenBy 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)
    .OrderBy(db.Albums.AlbumId)
    .ThenByGenreName();

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].[AlbumId], 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 ThenBy 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(db.Albums.AlbumId)
    .ThenBy(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 
   [dbo].[Albums].[AlbumId], [g].[Name]