About Links Archives Search Feed Albums of Note

Inferred Hierarchies and Evaluation Strategies in Simple.Data

This is the second of a number of posts derived from the documentation for Simple.Data [http://simplefx.org/simpledata/docs/] that I’m compiling and writing at the moment. The code here is part of the Simple.Data.Sample project on github [https://github.com/markrendle/Simple.Data.Sample].

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 [http://blog.markrendle.net/2012/10/03/simple-data-change-findby-is-deprecated-for-1-0/] . The second most common is around the subject of lazy-loaded JOINs and what that means in your code.


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(“”, 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 [http://blog.hmobius.com/post/2012/06/23/Name-Resolution-in-SimpleData.aspx], 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 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(“”, 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(“”, album.Title); }

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

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 [http://simplefx.org/simpledata/docs/pages/Start/NamingConventions.html] 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.

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(“”, album.Title); }

eagerPocoArtist.Albums is populated and cast correctly from a SimpleList into an IEnumerable (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:

You can find more about joining tables in the Simple.Data documentation [http://simplefx.org/simpledata/docs/index.html].

Posted on October 14, 2012   #Geek Stuff  

← Next post    ·    Previous post →