Azure table storage - pattern for parent-child (self referencing schema)

Using Windows Azure Table Storage (WATS) and trying to update the app to use Azure. I've read many articles, and am not sure on the best approach for this, that is parent to child in a self referencing model.

ie a single p开发者_JAVA百科arent message could have many child sub-messages. In a DB model, it would be a self referencing table.

How would I best structure this for WATS so that when I make a query "Give me 10 parent records", it will also return all the child-messages belonging to the parent...

The entity of the message / submessage as below. I've tried to define the PK and RK as below:

public class TextCacheEntity : AzureTableEntity // custom table inherits AzureTableEntity
{
    public override void GenerateKeys()
    {
        PartitionKey = string.Format("{0}_{1}_{2}", MessageType, AccountId.PadThis(), ParentMessageId );
        RowKey = string.Format("{0}_{1}", DateOfMessage.Ticks.ReverseTicks(), MessageId);
    }
    public string MessageType { get; set; }
    public int AccountId { get; set; }
    public DateTime DateOfMessage { get; set; }
    public string MessageId { get; set; }
    public string ParentMessageId { get; set; }
    // other properties...
}

I thought of an implementation so the child messages store the parentMessagesId, and the parent parentMessageId would be empty.

The pattern would then be

1. Get the parent messages

   .Where(o => o.ParititionKey == "Parent_000000000000001_").Take(10)
   

2. Get the child messages. Iterate through all the parent messages and using a parallel for loop

   .Where(o => o.ParititionKey == "Child_000000000000001_" + parentMessageId)
   

But the problem is that this will result in 11 queries !

---

See this example by Scott Densmore:

http://scottdensmore.typepad.com/blog/2011/04/multi-entity-schema-tables-in-windows-azure.html

---

You can do this by using the same PK for both. There are a couple reasons to do this, but one good one is that you can then also issue batch commands for parent and children at once and achieve a type of consistent transaction. Also, when they share the same PK within the same table, it means they are going to be colocated together and served from the same partition. You are less likely to continuation tokens (but you should still expect them). To differentiate between parent and children you can either add an attribute or use the RowKey perhaps.

The only trick to this (and the model you already ahve), is that if the parent and children are not the same CLR type, you will have issues with serialization in WCF DataServices. You can fix this of course by creating an uber-CLR type that has both child and parent properties or you can override serialization with the ReadingEntity event and handle it yourself.

Anyhow, use the same PK for both children and parent. Then when you search PK ranges you will always get parents and children returned at once (you can discriminate with a Where clause predicate if you wish).