How to look up Azure table data by ID

Short answer: You can't. So don't try.

Back in 2007, when I wrote a scheduling application for a (still ongoing!) client, Azure was a frustrating research project at Microsoft. Every bit of data the application stored went into SQL Server tables including field-level auditing and event logs.

The application migrated to Azure in August 2012, still logging every audit record and event to SQL tables, which are something like 10x more expensive per byte than Azure Table Storage. Recently, I completed an upgrade to the Inner Drive Extensible Architecture™ so it can now use Azure table storage for both.

The old application knew nothing about this. So upgrading the application with the new IDEA bits worked fine for writing to the audit and event logs, but completely broke reading from them.

Here's the code the app uses displaying a specific audit record so an administrator can see the field-level details:

// Get the repository from Castle IoC:
var repo = ActiveContainer.Instance.Container.Resolve<IAuditRepository>();

// Get the individual audit record by ID:
var audit = repo.Find(id);

That's great if the audit record uses a database identity key. Unfortunately it does not; it uses an Azure partition and row key combination.

I agonized for a couple of days how to fake database identities in Azure, or how to write a mapping table, or do some other code- or data-intensive thing or another. Then this afternoon, epiphany!

The user viewing an audit record is doing it in the context of reviewing a short list of audit headers. They click on one of these headers to pop up the detail box. The detail box uses the audit ID like this: https://scheduler.myclient.com/Auditing/AuditDetailViewer/12345.

It turns out, the only time a user cares about audit details is when she has the audit list right in front of her. So the audit ID is irrelevant. It only has to be unique within the context of the user's experience.

Here's the solution. In the Auditing class, which generates the audit list, I do this:

foreach (var item in orderedAudits)
{
	// Temporal cohesion: Add identity to Audit before using
	AddIdentity(item);
	Cache(item);
	CreateAuditRow(placeHolder, isObjectSpecified, item, timeZone);
	// End temporal cohesion
}

The cache uses least-frequently-used scavenging with a capacity of 512 items. (If the one user who cares about auditing ever needs to see more than 512 audit items in one list, I'll coach him not to do that.) Items live in the cache until it gets full, at which time the least-used ones are removed. This lets me do this in the audit detail view's control code:

var audit = Auditing.Find(id);

The Auditing.Find method simply plucks the item from the cache. If it returns null, oops, the audit details are missing or have expired from the cache, sorry. Just rerun the list of audits that you just clicked on.

I'm going to use a similar approach to the event log.

Related posts