Simple.Data

Simple.Data uses a lazy-loading strategy by default to hydrate .NET objects (POCOs) but also has an option to use an eager-loading strategy as well. Here is how they differ and how to use them.

Lazy vs. Eager Loading

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 IList<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,

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. The equivalent Get 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 IList<dynamic>.

Screenshot of the Locals window as eagerDynamicArtist is being accessed. Shows Albums property to have type SimpleList

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 IList<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.

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: