• Compsition in Windows Azure Table Storage: choosing the row key and simulating StartsWith

    Published by on April 22nd, 2010 12:35 pm under azure, Emerging Technology

    1 Comment

    For a couple of months I’ve been working with Eugenio Pace, Scott Densmore and Matias Woloski between others in creating the Windows Azure Application Guidance.

    The business stories of the project are detailed in Eugenio’s blog so, if want to get familiarized with it, I’d recommend starting with this post.

    The motivation for this post started when we were leaving behind the relational databases and started using Windows Azure Storage (Replacing the data tier with Azure Table Storage.)

    The model

    As you can see in this simple model, there is a composition relationship (1 to n) between Expense (1) and ExpenseItem (n). Note that the relationship is not aggregation because the expense items make no sense unless the expense referencing them exists.

    image

    In a relational database is easy: Foreign keys can be used. In Azure Table Storage you have to find a way of implementing this yourself with the row keys in both tables.

    Querying the repository

    Now let’s take a look at how we implemented the repository method GetExpenseById:

    1. Get the expense by its Id
    2. Get the expense items with the expense Id

    #1 is easy: Get the expense by its Id query:

    var expenseQuery =

    (from e in context.Expenses
    where e.RowKey == expenseId.ToString()
    select e).AsTableServiceQuery();

    #2 is more tricky: Get the expense items with the expense Id query:

    char charAfterSeparator = Convert.ToChar((Convert.ToInt32(‘_’) + 1));
    var nextId = expenseId.ToString() + charAfterSeparator;
    var expenseItemQuery =

    (from expenseItem in context.ExpenseItem
    where
    expenseItem.RowKey.CompareTo(expenseId.ToString()) >= 0 &&
    expenseItem.RowKey.CompareTo(nextId) < 0 &&

    expenseItem.PartitionKey.CompareTo(expenseRow.PartitionKey) == 0
    select expenseItem).AsTableServiceQuery();

    How the entities are stored

    To understand who this works, we need to first understand how the expense and the expense items are being stored.

    1. Storing the Expense row:
      1. Expense Id = Expense row key = new Guid (in the real implementation we are using an inverted timestamp)
    2. Storing the Expense item row
      1. Expense item Id = new Guid
      2. Expense item row key = (Expense Id + “_” + Expense item Id)

    Simulating RowKey.StartsWith

    Now we can understand how the nextId is calculated in the get expense item query with the lines:

    char charAfterSeparator = Convert.ToChar((Convert.ToInt32(‘_’) + 1));
    var nextId = expenseId.ToString() + charAfterSeparator;

    As you can see, the query is simulating what would be a more natural approach, which is still not supported in Azure Table Storage:

    where
    expenseItem.RowKey.StartsWith(expenseId.ToString())

    As an example, only the highlighted rows would be returned from the Expense Item table when calling GetExpenseById(“A059D3C0-608A-45f7-B2CF-000000000000″):

    Expenses

    image

    Expense item

    image

    The source code

    Get the latest version of this code at: http://wag.codeplex.com/releases/.