Simple.Data

Select

By default, the All, Get and Find* commands retrieve every column from each table specified in their invocation. Use the Select method to set explicitly the data fields returned by those commands.

Syntax

public SimpleQuery Select(
		[object[] columnNames]
	)

Parameters

columnNames

Type: object[]
A comma-separated list of fields to be retrieved by the main query command.

Return Value

Type: SimpleQuery
A SimpleQuery object containing a Select clause.

Exceptions

Remarks

The Select method can qualify any query command. It can be daisychained to the target command or called separately.

You can also call it on its own, at which point Simple.Data will imply a call to All.

If you supply no arguments to Select, Simple.Data will return all columns.

Select is not cumulative. Calling it a second time in the same command chain with additional columns to include will throw an InvalidOperationException.

Columns can be identified in any order. Their names will be resolved according to the same rules as those for resolving the names of tables:

1. Find an exact match.
2. Remove all non-whitespace characters from the field names and perform a case-insensitive match.
3. Check if the field name ‘is plural’ and try to match the singular version.
4. Check if the field name ‘is singular’ and try to match the plural version.

You can also use an indexed syntax to identify schemas, tables and columns at runtime rather than the fluid syntax which requires compile time decisions. For example, db.SchemaName.TableName.ColumnName can also be written as db["SchemaName"]["TableName"]["ColumnName"]. See the examples below for comparative code.

Should you reference columns not enumerated in a Select method - for example, trying to access the GenreId column after selecting only AlbumId and Title - throws a Simple.Data.UnresolvableObjectException exception

Retrieving All Columns In A Table Using AllColumns() and Star()

Simple.Data defines two synonymous methods, AllColumns and Star, which when appended to a table reference will return all columns for that table. This comes in handy in join queries where you may want to return all the fields in one table but not in another. See Example 3 for the syntax.

Examples

Using Select With All

To return all the AlbumId and Title fields from the Albums table, use one of the following commands

// Using fluid syntax
var albums = db.Albums.All()
   .Select(
      db.Albums.AlbumId, 
      db.Albums.Title);

var albums = db.Albums.Select(
      db.Albums.AlbumId, 
      db.Albums.Title);

// Using indexed syntax
var albums = db["Albums"].All()
   .Select(
      db["Albums"]["AlbumId"], 
      db["Albums"]["Title"]);

var albums = db["Albums"].Select(
      db["Albums"]["AlbumId"], 
      db["Albums"]["Title"]);

Simple.Data sends the following SQL to the database when albums is evaluated.

select   
   [dbo].[Album].[AlbumId],
   [dbo].[Album].[ArtistId] 
from [dbo].[Album]

Usng Select With FindAllBy

To return all the AlbumId and Title fields from the Albums table where GenreId equals 1, use one of the following commands.

// Using fluid syntax
db.Albums.FindAllByGenreId(1)
    .Select(
        db.Albums.AlbumId,
        db.Albums.Title)
    );
      	
db.Albums.FindAll(db.Albums.GenreId == 1)
    .Select(
        db.Albums.AlbumId,
        db.Albums.Title)
    );

// Using indexed syntax
db["Albums"].FindAllByGenreId(1)
    .Select(
      db["Albums"]["AlbumId"], 
      db["Albums"]["Title"]);
    );
      	
db["Albums"].FindAll(db["Albums"].GenreId == 1)
    .Select(
      db["Albums"]["AlbumId"], 
      db["Albums"]["Title"]);
    );

Either will produce the following SQL

SELECT  
    [dbo].[Album].[AlbumId], 
    [dbo].[Album].[Title] 
from [dbo].[Album]  
where [dbo].[Album].[GenreId] = @p1
@p1 (Int32) = 1

Using AllColumns Or Star

To return all the fields from the Albums table using the AllColumns or Star methods, use the following syntax.

db.Albums.All()
   .Select(db.Albums.Star());

db.Albums.All()
   .Select(db.Albums.AllColumns());

Either will produce the following SQL

SELECT 
  [dbo].[Albums].[AlbumId],
  [dbo].[Albums].[GenreId],
  [dbo].[Albums].[ArtistId],
  [dbo].[Albums].[Title],
  [dbo].[Albums].[Price],
  [dbo].[Albums].[AlbumArtUrl] 
from [dbo].[Albums]