Simple.Data defines a number of commands for retrieving data from a data store. These can then be daisy-chained in a LINQ-like fashion with further methods to modify the basic query. For example, the Join, On, and OuterJoin commands allow you to join two or more tables together in a query and access the contents of both using a lazy-loading strategy. This means that if you cast the results of a lazily-loaded join query into a POCO, Simple.Data will not ‘gather up’ any 1:n join data into collections for you to query as enumerables. You’ll need to use the eager-loading With or WithOne statements to achieve this.
Natural Joins
Simple.Data allows you to join two or more tables implicitly, rather than use the Join, OuterJoin or With commands, provided the tables have a foreign key constraint set up between them so referential integrity is enforced.
It does this by allowing you to define the chain of tables to be traversed from the ‘start’ table in the query within a column selection. For example, if you were querying the Albums table for an album’s details, and wanted to include the name of the album’s genre instead of its genreid, you could write:
var album = db.Albums.FindAllByGenreId(1) .Select( db.Albums.Title, db.Albums.Genre.Name);
The chain of tables can be as long as required as long as referential integrity is enforced between the tables in the chain.
db.Albums.Genre.Name; db.OrderDetails.Albums.Genre.Name; db.StartTable.Table2.Table3 …. TableN.Field;
You can also use the indexer style for writing the join chain.
db["Albums"]["Genre"]["Name"]; db["OrderDetails"]["Albums"]["Genre"]["Name"]; db["StartTable"]["Table2"]["Table3"] …. ["TableN"]["Field"];
There are two points to note about Natural joins:
- Simple.Data translates natural joins into
LEFT JOIN
statements. - If you cast the results of an natural join query into a POCO, Simple.Data will not ‘gather up’ any 1:n join data into collections for you to query as enumerables. You’ll need to use the
With
orWithOne
statements to achieve this.
Exceptions
Simple.Data may throw one of several exceptions if an aspect of the natural join chain is incorrect.
db.StartTable.Table2.Table3 …. TableN.Field;
UnresolvableObjectException
: TableN doesn’t have a column called ‘Field’.- [AdoAdapter only]
Simple.Data.Ado.SchemaResolutionException
: Table2 and Table3 do not have a foreign key relationship defined in the database. - [AdoAdapter only]
Simple.Data.Ado.AdoAdapterException
: StartTable is not the same table being queried in the main selection command.
Examples
1
To return the titles from the Albums table and Names from the Genres table where GenreId = 1, use the following command:
var albums = db.Albums.FindAllByGenreId(1) .Select( db.Albums.Title, db.Albums.Genre.Name); foreach (var album in albums) { Console.WriteLine(album.Title, album.Name); }
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]) WHERE [dbo].[Albums].[GenreId] = @p1 @p1 (Int32) = 1
2
To return the titles from the Albums table and Names from the Genres table where OrderId = 1 in the OrderDetails table, use the following command:
var orderDetails = db.OrderDetails.FindAllByOrderId(1) .Select( db.OrderDetails.OrderId, db.OrderDetails.Albums.Title, db.OrderDetails.Albums.Genre.Name); foreach (var orderDetail in orderDetails) { Console.WriteLine(orderDetail.OrderId, orderDetail.Title, orderDetail.Name); }
Simple.Data sends the following SQL to the database when orderDetail is evaluated.
select [dbo].[OrderDetails].[OrderId], [dbo].[Albums].[Title], [dbo].[Genres].[Name] from [dbo].[OrderDetails] LEFT JOIN [dbo].[Albums] ON ([dbo].[Albums].[AlbumId] = [dbo].[OrderDetails].[AlbumId]) LEFT JOIN [dbo].[Genres] ON ([dbo].[Genres].[GenreId] = [dbo].[Albums].[GenreId]) WHERE [dbo].[OrderDetails].[OrderId] = @p1 @p1 (Int32) = 1

Explicit Joins
Simple.Data allows you to join two or more tables explicitly using one of three methods: Join, LeftJoin, and OuterJoin. These will create (inner) JOIN
, LEFT JOIN
and LEFT OUTER JOIN
statements respectively. You can use them all to connect two tables that do not have a foreign key constraint set up between them. They all operate in the same way, so for clarity, the Join
methods is used for description. The Examples section demonstrates LeftJoin
and OuterJoin
methods further.
[ADO Adapter Only] Note that as LEFT JOIN
and LEFT OUTER JOIN
are synonymous in T-SQL, the OuterJoin
method as implemented by the Simple.Data.Ado adaptor creates a LEFT JOIN
statement.
There are two ways to form a Join
method:
- You can specify the related column names as named parameters
- You can use the
On
method to specify the related column names and their relationship using aSimpleExpression
or using named parameters. TheOn
method also has an overload to let you specify an alias for the joined table.
Syntax
// Named parameters, no On method public SimpleQuery Join( ObjectReference targetTable, JoinExpression joinExpression ) // Named parameters, with On method public SimpleQuery Join( ObjectReference targetTable [, out dynamic aliasedTargetTable] ).On( JoinExpression joinExpression ) // Simple Expression, with On method public SimpleQuery Join( ObjectReference targetTable [, out dynamic aliasedTargetTable] ).On( SimpleExpression simpleExpression )
The call to On
must be immediately preceded by a call to Join
.
Parameters
targetTable
Type: ObjectReference
A reference to the table being joined into the query. For example, db.AlbumsjoinExpression
Type: JoinExpression
A JoinExpression uses named parameters to idetify the join condition between the two tables. It has the form targetkeyColumn : db.startTable.startKeyColumn where
- targetKeyColumn is the name of the key column in the target table being joined into the query.
- startTable is the name of a table already referenced in the query, either in the initial command or in a previous join.
- startKeyColumn is the name of the key column in the startTable which forms the basis of the join for that table.
simpleExpression
Type: SimpleExpression
A (concatenated sequence of) search criteria. For example, db.targetTable.targetkeyColumn == db.startTable.startKeyColumn
.aliasedTargetTable
Type: dynamic
An out parameter which allows you to alias tables in the join and reuse them in later joins.
For example, if you were querying the Albums table for an album’s details, and wanted to include the name of the album’s genre instead of its genreId, you could use either form:
db.Albums.FindAllByGenreId(1) .Select( db.Albums.Title, db.Genre.Name) .Join(db.Genre, GenreId: db.Albums.GenreId); db.Albums.FindAllByGenreId(1) .Select( db.Albums.Title, db.Genre.Name) .Join(db.Genre).On(GenreId: db.Albums.GenreId); db.Albums.FindAllByGenreId(1) .Select( db.Albums.Title, db.Genre.Name) .Join(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId);
You can also use indexer style in Joins.
db.Albums.FindAllByGenreId(1) .Select( db["Albums"]["Title"], db["Genre"]["Name"]) .Join(db["Genre"], GenreId: db["Albums"]["GenreId"]); db.Albums.FindAllByGenreId(1) .Select( db["Albums"]["Title"], db["Genre"]["Name"]) .Join(db["Genre"]).On(GenreId: db["Albums"]["GenreId"]); db.Albums.FindAllByGenreId(1) .Select( db["Albums"]["Title"], db["Genre"]["Name"]) .Join(db["Genre"]).On(db["Genre"]["GenreId"] == db["Albums"]["GenreId"]);
All six examples above produce the same SQL statement to be set to the database.
select [dbo].[Albums].[Title], [dbo].[Genres].[Name] from [dbo].[Albums] JOIN [dbo].[Genres] ON ([dbo].[Genres].[GenreId] = [dbo].[Albums].[GenreId]) WHERE [dbo].[Albums].[GenreId] = @p1 @p1 (Int32) = 1
The columns referenced in the Select statement need only identify their parent table. (Compare this to using ‘natural’ joins). However, if you decide to give an alias to a table in a join, you’ll need to make a few changes.
- Switch to using the
On
variant ofJoin
if you weren’t already - Use the
As
method within the Join to set the table alias. - Add the third
out
parameter to store a reference to the aliased table - Move the
Join
method to immediately after the main select method so theout
parameter is defined for use bySelect
and other clauses. - Use the
out
parameter in theOn
andSelect
statements to identify columns in that table.
For example,
dynamic GenreAlias; var albums = db.Albums.FindAllByGenreId(1) .Join(db.Genre.As("g"), out GenreAlias).On(GenreAlias.GenreId == db.Albums.GenreId) .Select( db.Albums.Title, GenreAlias.Name); dynamic GenreAlias; var albums = db.Albums.FindAllByGenreId(1) .Join(db.Genre.As("g"), out GenreAlias).On(GenreId: db.Albums.GenreId) .Select( db.Albums.Title, GenreAlias.Name);
Both examples produce the same SQL statement to be set to the database:
select [dbo].[Albums].[Title], [g].[Name] from [dbo].[Albums] JOIN [dbo].[Genres] [g] ON ([g].[GenreId] = [dbo].[Albums].[GenreId]) WHERE [dbo].[Albums].[GenreId] = @p1 @p1 (Int32) = 1
There are several points to note about the Join statement:
- Simple.Data translates
Join
methods into JOIN statements. - You can chain as many
Join
s to the query as are required. - The named parameter form of
Join
can only set column relationships based on a single equality. You can create column relationships based on any arithmetical operator using theOn
form – it’s just a matter of writing the correctSimpleExpression
. - If you cast the results of a
Join
query into a POCO, Simple.Data will not ‘gather up’ any 1:n join data into collections for you to query as enumerables. You’ll need to use theWith
orWithOne
statements to achieve this.
Exceptions
To Be Confirmed
Examples
1
The following example uses the LeftJoin
method to join the OrderDetails, Album and Genre tables where OrderDetails.OrderId = 1 and sends the Album title and Genre name out to the screen.
var orderDetails = db.OrderDetails.FindAllByOrderId(1) .Select( db.OrderDetails.OrderId, db.OrderDetails.Albums.Title, db.OrderDetails.Albums.Genre.Name) .LeftJoin(db.Albums, AlbumId: db.OrderDetails.AlbumId) .LeftJoin(db.Genre, GenreId: db.Albums.GenreId); foreach (var od in orderDetails) { Console.WriteLine(od.OrderId, od.Title, od.Name); }
The same can be achieved using the On
form of LeftJoin
.
var orderDetails = db.OrderDetails.FindAllByOrderId(1) .Select( db.OrderDetails.OrderId, db.OrderDetails.Albums.Title, db.OrderDetails.Albums.Genre.Name) .LeftJoin(db.Albums).On(db.Albums.AlbumId == db.OrderDetails.AlbumId) .LeftJoin(db.Genre).(db.Genre.GenreId == db.Albums.GenreId); foreach (var od in orderDetails) { Console.WriteLine(od.OrderId, od.Title, od.Name); }
The following SQL is sent to the server when orderDetails is evaluated in both versions of the code.
select [dbo].[OrderDetails].[OrderId], [dbo].[Albums].[Title], [dbo].[Genres].[Name] from [dbo].[OrderDetails] LEFT JOIN [dbo].[Albums] ON ([dbo].[Albums].[AlbumId] = [dbo].[OrderDetails].[AlbumId]) LEFT JOIN [dbo].[Genres] ON ([dbo].[Genres].[GenreId] = [dbo].[Albums].[GenreId]) WHERE [dbo].[OrderDetails].[OrderId] = @p1 @p1 (Int32) = 1
2
The following example uses the OuterJoin
method to join the OrderDetails, Album and Genre tables where OrderDetails.OrderId = 1 and sends the Album title and Genre name out to the screen. The album and genre tables are given aliases.
dynamic GenreAlias; dynamic AlbumsAlias; var orderDetails = db.OrderDetails.FindAllByOrderId(1) .OuterJoin(db.Albums.As("a"), out AlbumsAlias).On(AlbumsAlias.AlbumId == db.OrderDetails.AlbumId) .OuterJoin(db.Genre.As("g"), out GenreAlias).On(GenreAlias.GenreId == AlbumsAlias.GenreId) .Select( db.OrderDetails.OrderId, AlbumsAlias.Title, GenreAlias.Name); foreach (var od in orderDetails) { Console.WriteLine(od.OrderId, od.Title, od.Name); }
The following SQL is sent to the server when orderDetails is evaluated
select [dbo].[OrderDetails].[OrderId], [a].[Title], [g].[Name] from [dbo].[OrderDetails] LEFT JOIN [dbo].[Albums] [a] ON ([a].[AlbumId] = [dbo].[OrderDetails].[AlbumId]) LEFT JOIN [dbo].[Genres] [g] ON ([g].[GenreId] = [a].[GenreId]) WHERE [dbo].[OrderDetails].[OrderId] = @p1 @p1 (Int32) = 1