Querying azure table storage for null values

Question

Does anyone know the proper way to query azure table storage for a null value. From what I've read, it's possible (although there is a bug which prevents it on development storage). However, I keep getting the following error when I do so on the live cloud storage:

One of the request inputs is not valid.

This is a dumbed down version of the LINQ query that I've put together.

var query = from fooBar in fooBarSVC.CreateQuery<FooBar>("FooBars")
        where fooBar.PartitionKey == kPartitionID
            && fooBar.Code == kfooBarCode
            && fooBar.Effective_Date <= kFooBarDate.ToUniversalTime()
            && (fooBar.Termination_Date > kFooBarDate.ToUniversalTime() || fooBar.Termination_Date == null)
        select fooBar;

If I run the query without checking for null, it works fine. I know a possible solution would be to run a second query on the collection that this query brings back. I don't mind doing that if I need to, but would like to know if I can get this approach to work first.

Anyone see anything obvious I'm doing wrong?

Neocene

Answer 1

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/

Kimbro

Answer 2

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;

Susi

Thanks @HansOlav for saving my day. I have tried more than 2 hours for this, and tried few alternatives also but nothing seems to work except this. – Petrochemistry

Answer 3

For a string column called MyColumn I was able to type: not(MyColumn gt '')

Mike S answer above put me on the right path.

Haihaida

This should be the accepted answer as it worked flawlessly, thanks! – Bromberg

Answer 4

For strings, we can compare to empty string.

IsNotBlank(value) 

Can be:

(Value gt '') 

Alonzoaloof

Answer 5

Using the Azure Tables client library for .NET. to query for null Guid values.

In the sample code, the property's name is MyColumn.

var filter = Azure.Data.Tables.TableClient
      .CreateQueryFilter($"not(MyColumn gt {Guid.Empty})");

The TableClient.CreateQueryFilter method will create the filter:

not(MyColumn gt guid'00000000-0000-0000-0000-000000000000')

Gravel

Answer 6

I ran across this problem today while I was trying to get values that were known from Azure table storage. Based on the answers above, I learned that using "less than" actually gives me the values that I was looking for.

So in other words if you're trying to grab things that are null (field does not exist in that row) from Azure table storage, using table storage queries, then the equivalent looks like

Looking for NULL VALUES

not (FieldName lt '')

If you WANT the null values

(FieldName lt '')

The above words directly in Azure when using advanced querying -- FYI

Ukulele