How to Manage Relationships in Azure Tables that are List Properties in Code

0
votes

I have a question about how my classes should be mapped into Azure Table Storage entities. Say I have two entities, BikeRider and BikeRace. In my C# code, I have 2 classes, and each one has a property that is a collection of the other. So the BikeRider class has a List property, and vice versa. If that's not ideal here, feel free to opine, but that's what I've got today.

How does that map into an Azure Table? I've found questions on SO here that discuss how to store a many to many relationship, but my question is more specifically how a List property should be handled when you save the object containing that property.

thanks!

UPDATE While I was waiting for a reply to this (and thinking I might not get any), I came up with this solution:

The C# BikeRider and BikeRace types both have a List property of the type of the other, as I described above. Each has an instance method to Add an instance of the other to their list (i.e. BikeRace has a method called "AddBikeRider" that adds an instance of BikeRider to the BikeRace.BikeRiders list, and vice versa).

However, when I save to Azure Table Storage, there are 3 tables with the following information:

BikeRider Table

  • PartitionKey, RowKey,

BikeRider Info RiderRace Table

  • PartitionKey: BikeRider RowKey
  • RowKey: BikeRace RowKey
  • Other Info

BikeRace Table

  • PartitionKey, RowKey
  • A separate value for the ID of every BikeRider in the race (the BikeRider RowKey - but could have many).

This way, if you are querying from the BikeRider and trying to get the BikeRaces he's been in, you have the partition key, and all of the rows in that partition are the BikeRaces. If you're querying from the BikeRace, and trying to get all the BikeRiders in a race, you have the BikeRider ID's to use in querying the BikeRider table. You're not duplicating data beyond storing those keys in multiple places, so you don't have to multiple storage locations if your data changes.

The challenge here is what Ming has posted - if the Table Service save method throws an exception for a type with a List property, can you create a simpler interface with its own preliminary save method that handles the list type and turns it into a save-able data type first, to persist it to the Azure Tables in the way I described? Is this too complicated, or too brittle?

1
c#nosqlazure-table-storage

1 Answers

1
votes

this is not supported. Table storage does not support list properties. If you use a list property in your CLR data model, WCF Data Services will try to serialize it, and result in an exception.

To simulate a relationship, you can create a property on the BikeRace entity, such as BikeRiderID.

If you need a read only collection, you can create a method (instead of a property) in the BikeRider class, such as GetBikeRaces. Inside the implementation, you query the BikeRace table to find all entities whose BikeRiderID is the current bike rider's ID. If you want to update the list of bike races, you also need to update the BikeRider table using a separate request.

You can refer to http://blog.smarx.com/posts/one-to-many-relationships-in-windows-azure-tables for a sample.