This is the second of a number of posts derived from the documentation for Simple.Data that I’m compiling and writing at the moment. The code here is part of the Simple.Data.Sample project on github.

Simple.Data is bloody clever at times but quite easy to get confused by. The most common confusion is between using commands that return multiple results as a SimpleQuery object and those that return a single results as a SimpleRecord object. With the return type hidden under the veil of ‘dynamic’ it’s easy to lose track and try to call a method on one that only works on the other. It’s the main reason for Mark to deprecate the FindBy method for v1.0. The second most common is around the subject of lazy\eager-loaded JOINs and what that means in your code.

[more]

Let’s take an example and suppose we have two tables in our database, Artists and Albums. The simplest way to display an artist’s details and album titles would be this.

// Retrieve artist with PK value of 22
var lazyDynamicArtist = db.Artists.Get(22);
Console.WriteLine("Artist {0} ({1})", lazyDynamicArtist.ArtistId, lazyDynamicArtist.Name);

foreach (var album in lazyDynamicArtist.Albums)

Console.WriteLine("\t{0}", album.Title); 
}

Simple.Data adopts a lazy evaluation strategy so lazyDynamicArtist is not evaluated until accessed for the first time in the first call to Console.WriteLine. Then it sends the following SQL to the database.

SELECT TOP 1 
[dbo].[Artists].[ArtistId],
[dbo].[Artists].[Name]
from
[dbo].[Artists]
where
[ArtistId] = @p1
@p1 (Int32) = 22

This explains the existence of the ArtistId and Name properties for lazyDynamicArtist, but where has the Albums property come from? Answer: Simple.Data has inferred the hierarchy of tables / existence of the Albums property from the foreign key relationship between Artists and Albums table. Once again, the Albums collection is evaluated only when accessed for the first time (here, as part of the foreach loop). When that occurs, another query is sent to the database and silently returns another dynamic object to iterate through.

select
[dbo].[Albums].[AlbumId],
[dbo].[Albums].[GenreId],
[dbo].[Albums].[ArtistId],
[dbo].[Albums].[Title],
[dbo].[Albums].[Price],
[dbo].[Albums].[AlbumArtUrl]
from
[dbo].[Albums]
WHERE
[dbo].[Albums].[ArtistId] = @p1
@p1 (Int32) = 22
Note that despite Simple.Data’s name resolution tricks when parsing table and column names in query functions, accessing artist.Album will throw an exception. Simple.Data does not use any pluralisation rules at this point. You must use the exact table name as a property to access to the inferred hierarchy.

This lazy evaluation strategy does have its drawbacks however. The most obvious is the number of SQL commands you can end up sending to the database. In this example, with only two tables needing to be accessed for a single artist, only two statements are sent. In general, n+1 statements will be sent: one for the list of artists and one per artist (n) for each list of albums to retrieve. Add in more tables, and the number of SQL commands sent to the database is quickly far more than your DBA would prefer you make

Another less immediately obvious down side to this strategy comes via Simple.Data’s ability to cast the results of a query into a statically typed object (POCO) on the fly. Let’s say we have an Artist class defined like this:

public class Artist
{
public int ArtistId { get; set; }
public string Name { get; set; }
public IEnumerable<Album> Albums { get; set; }
}
If we try the following code, we hit a snag:
// Cast result of query to Artist type
Artist lazyPocoArtist = db.Artists.Get(22);

Console.WriteLine("Artist {0} {1}", lazyPocoArtist.ArtistId, lazyPocoArtist.Name);

foreach (var album in lazyPocoArtist.Albums)
{
Console.WriteLine("\t{0}", album.Title);
}

The first two lines of code work fine, but lazyPocoArtist.Albums now returns null. The inferred hierarchy is lost as a result of casting the dynamic (SimpleRecord) object to the static Artist type, no evaluation of Albums occurs and a System.NullReferenceException is thrown.

Recap

In brief then,

  • Access the artist information as a dynamic (SimpleRecord or item in a SimpleQuery) object and the Albums property will be evaluated when it is accessed for the first time. It will become available as another dynamic (SimpleQuery) object to iterate through. Note that even if the artist in question has no albums attached to it in the database, a SimpleQuery object is still created but with nothing in it to enumerate.
  • Access the artist information as a POCO and the Albums property will return null.

Eager Evaluation

It’s fairly obvious that the lazyPocoArtist variable returns null for its Albums property because Simple.Data didn’t actually retrieve any Album information for the artist before it was cast into the Artist type. It was lazy. Fortunately, we can change the initial query command and turn Simple.Data into a pre-emptive, eager data retrieval machine. All we need to do is add a With statement like so.

var eagerDynamicArtist = db.Artists.FindAllByArtistId(22).WithAlbums().FirstOrDefault();

Console.WriteLine("Artist {0} {1}", eagerDynamicArtist.ArtistId, eagerDynamicArtist.Name);

foreach (var album in eagerDynamicArtist.Albums)
{
Console.WriteLine("\t{0}", album.Title);
}

Note that I’ve switched from using Get to FindAllBy.FirstOrDefault to return a SimpleRecord object.

  • There’s currently a bug using Get with a With statement in that it only returns the first row in the JOINed table rather than them all.
  • Once the bug is fixed, the equivalent statement would be var eagerDynamicArtist = db.Artists.WithAlbums().Get(22); which looks odd, but needs to be this way around or Simple.Data will throw an exception.

As with the lazy loaded sample earlier, eagerDynamicArtist is not evaluated until accessed for the first time in the first call to Console.WriteLine at which point it sends the following SQL to the database.

select 
[dbo].[Artists].[ArtistId],
[dbo].[Artists].[Name],
[dbo].[Albums].[AlbumId] AS [__withn__Albums__AlbumId],
[dbo].[Albums].[GenreId] AS [__withn__Albums__GenreId],
[dbo].[Albums].[ArtistId] AS [__withn__Albums__ArtistId],
[dbo].[Albums].[Title] AS [__withn__Albums__Title],
[dbo].[Albums].[Price] AS [__withn__Albums__Price],
[dbo].[Albums].[AlbumArtUrl] AS [__withn__Albums__AlbumArtUrl]
from [dbo].[Artists]
LEFT JOIN [dbo].[Albums] ON ([dbo].[Artists].[ArtistId] = [dbo].[Albums].[ArtistId])
WHERE
[dbo].[Artists].[ArtistId] = @p1
@p1 (Int32) = 22

The With statement is fluid like FindAllBy, so by adding WithAlbums into the command chain, it knows to include the contents of the Albums table in its query to the database and that it must then collate the results into one row of information about an artist. It does this with a LEFT JOIN statement. The net result is that eagerDynamicArtist is created with an Albums property already populated with all the albums in a SimpleList object - another IEnumerable<dynamic>.

As with lazyDynamicArtist, you can just iterate over a SimpleList to access each item it contains so the rest of the example code here remains the same. The main difference is that there are no additional SQL statements sent to the database on the fly, so your DBAs will like you a lot more, except if there are no items in the Albums table matching the artistId. More on that in a minute.

So how does this look when casting eagerDynamicArtist to a POCO? It works very well.

Artist eagerPocoArtist = db.Artists.FindAllByArtistId(22).WithAlbums().FirstOrDefault();

Console.WriteLine("Artist {0} {1}", eagerPocoArtist.ArtistId, eagerPocoArtist.Name);

foreach (var album in eagerPocoArtist.Albums)
{
Console.WriteLine("\t{0}", album.Title);
}

eagerPocoArtist.Albums is populated and cast correctly from a SimpleList into an IEnumerable<Album> (or whatever subtype you prefer) for your code to iterate through. Hurrah! But with a proviso....

When there are no matching items in the JOINed table

What happens in an eager-loading scenario if the Artists table contains a row with no matching rows in the Albums table? Answer: the LEFT JOIN generated by WithAlbums returns null for all the Albums table fields.

  • If the result is accessed as a dynamic object, eagerDynamicArtist will not have an Albums property pre-generated at all. However, because Simple.Data still infers a hierarchy via foreign keys, it evaluates the Albums collection again when accessed for the first time (here, as part of the foreach loop), sending another query to the database which again returns no results as a dynamic (SimpleQuery) object. (But not a null one)
  • If the result is cast first to a POCO, eagerPocoArtist.Albums will be set to null, so it will have to be tested for null before being iterated over.

In a nutshell then, if the JOINed table has no associated rows, Simple.Data drops back to behaving as if it were lazy-loading data rather than eager-loading it. Coder beware.

Wrapping Up

The examples above are all predicated on the following assumptions:

  • The code is using the Simple.Data.SqlServer provider to access the database. YMMV if you are using a different Simple.Data.ADO-based provider
  • The two tables have a foreign key relationship between them and referential integrity is being maintained. Without it, the inferred hierarchy does not exist and the Albums property only exists if you eager-load it using the WithMany method rather than With. The former (and its corresponding WithOne method) allows you to specify the relationship between two tables while the former bases it on existing FK relationships.

You can find more about joining tables in the Simple.Data documentation.