Lazily-loaded join query into a POCO

/ Databases / By
Lazily-loaded join query into a POCO

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:

  1. Simple.Data translates natural joins into LEFT JOIN statements.
  2. 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 or WithOne 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
Lazily-loaded join query into a POCO

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 a SimpleExpression or using named parameters. The On 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

targetTableType: ObjectReference
A reference to the table being joined into the query. For example, db.AlbumsjoinExpressionType: 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.

simpleExpressionType: SimpleExpression
A (concatenated sequence of) search criteria. For example, db.targetTable.targetkeyColumn == db.startTable.startKeyColumn.aliasedTargetTableType: 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.

  1. Switch to using the On variant of Join if you weren’t already
  2. Use the As method within the Join to set the table alias.
  3. Add the third out parameter to store a reference to the aliased table
  4. Move the Join method to immediately after the main select method so the out parameter is defined for use by Select and other clauses.
  5. Use the out parameter in the On and Select 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:

  1. Simple.Data translates Join methods into JOIN statements.
  2. You can chain as many Joins to the query as are required.
  3. 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 the On form – it’s just a matter of writing the correct SimpleExpression.
  4. 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 the With or WithOne 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

Related Posts