Querying azure table storage for null values

I just had this problem and found a nice little ninja-trick to actually test for nulls. Although I'm using the Azure Storage interface directly, I'm 90% sure it will work for LINQ too if you do the same.

Here's what I did to check if Price (Int32?) is null:

not (Price lt 0 or Price gt 0)

I'm guessing in your case you can do the same in LINQ by testing if fooBar.Termination_Date is less or greater than DateTime.UtcNow for example. Something like this:

where fooBar.PartitionKey == kPartitionID
  && fooBar.Code == kfooBarCode
  && fooBar.Effective_Date <= kFooBarDate.ToUniversalTime()
  && (fooBar.Termination_Date > kFooBarDate.ToUniversalTime()
  || (not (fooBar.Termination_Date < DateTime.UtcNow 
            or fooBar.Termination_Date > DateTime.UtcNow))
select fooBar;

The problem is that because azure table storage does not have a schema, the null column actually doesn't exist. This is why your query is not valid. there is no such thing as a null column in table storage. You could do something like store an empty string if you really have to. Really though the fundamental issue here is that Azure table storage really is not built to be queried by any columns other than partition key and row key. Every time you make a query on one of these non-standard columns you are doing a table scan. If you start to get lots of data you are going to have a very high rate of query time outs. I would suggest setting up a manual index for these types of queries. For example, you could store the same data in the same table but with different values for the Row key. Ultimately, if your are app is not getting crazy high usage I would just use SQL Azure as it will be much more flexible for the types of queries you are doing.

Update: Azure has a great guide on table storage design that I would recommend reading. http://azure.microsoft.com/en-us/documentation/articles/storage-table-design-guide/