Recently I’ve been working on an Azure based project.  Luckily for me, I’ve also had the guidance of Azure guru Brent Stineman to help guide me through the Azure bits.  This is my first Azure based project, so there are definitely some learning pains.

One of the pains that I ran into early on had to do with the Azure emulator’s version of table storage.  Apparently it’s stored under the covers as a SQL Server instance.  Seems like a good enough idea.  What’s more interesting, though, is how it’s implemented.  It appears as though each table storage table is stored in a SQL Server table.  Makes enough sense.  However, what’s less obvious is that it seems that the SQL Server table isn’t actually created until the first record is added.  This makes sense too, since how in the world would the Azure emulator know what the SQL Server table should look like, unless it had a sample record available to it?

Without this key bit of information, you may end up writing some code, that looks something like this:

public IEnumerable<PersonModel> GetPeople()
{
	CloudStorageAccount account = CloudStorageAccount.Parse("UseDevelopmentStorage=true");
	DataServiceContext context = new DataServiceContext(account.TableEndpoint.ToString(), account.Credentials);
	DataServiceQuery<PersonModel> query = context.CreateQuery<PersonModel>("Person");

	IEnumerable<PersonModel> people = query.Where(p => p.FirstName == "Jonathan")
					       .AsTableServiceQuery<PersonModel>()
					       .Execute();
	return people;
}

Ok, so you probably wouldn’t write it just like that.  You’d probably use config files and other structures that follow better OOD patterns that make your life easier.  Nonetheless let’s assume your code looks just like this.

So, you fire up your sweet new application.  You don’t have any data in your tables yet, so you expect to see an empty list of people.  Instead, however, you get an exception!  That exception says:

<?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?> <error xmlns=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”> <code>InvalidInput</code> <message xml:lang=”en-US”>One of the request inputs is not valid.</message> </error>

Perfect.  Now what?  The message mentions an invalid request input, but my query parameter is hardcoded, so that can’t be it.  What’s left?  Well, it turns out that if you try and query a table before you add a record to it, the query will fail because the table doesn’t exist.  Since the table doesn’t exist, the column can’t exist.  My query above is trying to compare against a column named ‘FirstName’ that isn’t there.  Bummer.

Now that we know what the issue is, how do we fix it?  Well, I don’t really know what the best practice is.  However, my solution was to create an application that adds a sample record and then immediately deletes it.  This forces the table and columns to be created and allows our query to return successfully.  The code for my application looks something like this:

CloudStorageAccount account = CloudStorageAccount.Parse("UseDevelopmentStorage=true");

CloudTableClient tableClient = account.CreateCloudTableClient();
tableClient.CreateTableIfNotExist("Person");

PersonModel person = new PersonModel();
this.AddObject("Person", person);
this.SaveChanges();
this.DeleteObject(person);
this.SaveChanges();

In our actual implementation, we went a bit beyond this.  Our model classes were already in a separate namespace, so we worked some Reflection magic to pull those classes out of our DAL assembly and perform these same steps dynamically.  We also stored our table names in an external resource file.  Without going into too much detail, it looked something like this:

CloudStorageAccount account = ConfigurationProvider.GetCloudStorageAccount();

CloudTableClient tableClient = account.CreateCloudTableClient();

ResourceSet tables = TableNames.ResourceManager.GetResourceSet(System.Threading.Thread.CurrentThread.CurrentCulture, true, true);

foreach (DictionaryEntry resource in tables)
{
	bool tableCreated = tableClient.CreateTableIfNotExist(resource.Value.ToString());

	//  If the table was not created, then this initialization has already been done.
	if (tableCreated)
	{
		ObjectHandle objectHandle = System.Activator.CreateInstance("Application.DAL", String.Format("Application.DAL.Models.{0}{1}", resource.Value, "Model"));
		object obj = objectHandle.Unwrap();

		this.AddObject(resource.Value.ToString(), obj);
		this.SaveChanges();
		this.DeleteObject(obj);
		this.SaveChanges();
	}
}

This little chunk of code just reads the resource file containing our table names, creates the table if necessary, finds a matching model object, adds the object, and deletes the object.  This pre-initializes all of our tables so that our queries will work as expected.  The other benefit to this code is that when a new table is added, we don’t need to make any code changes.  If using this technique, make sure to have at least one default/parameterless constructor that can be instantiated by Reflection.

I hope this helps with your quest to conquer Azure.  If you have any alternate methods for dealing with this situation, I’d love to hear about them!