Query Entities with a Filter in Entity Group Transactions

Question

Hi Everyone,

I wanted to check if anyone has some experience with performing Entity Queries via an Entity Group transaction.

I am able to perform requests via the transaction when using the following resource request format:

http://myaccount.table.core.windows.net/Blogs(PartitionKey='Channel_19',RowKey='2')

But if I use:

http://myaccount.table.core.windows.net/Blogs()?$filter=PartitionKey%20eq%20'Channel_19'%20and%20RowKey%20eq%20'2'

I get the following error:

HTTP/1.1 400 Bad Request
DataServiceVersion: 1.0;
Content-Type: application/xml

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
  <code>InvalidInput</code>
  <message xml:lang="en-US">0:One of the request inputs is not valid.
RequestId:27bb25b0-85ab-4fd9-9f2d-505e49fb76d9
Time:2012-04-25T15:06:43.6439345Z</message>
</error>

I am trying to get multiple random access records from one table/partition in a single round trip, any suggestions would be appreciated. 

Thanks in advance, if I can get this working it will really optimise some read heavy situations!

Answer 1

http:// myaccount.table.core.windows.net/Blogs()?PartitionKey%20eq%20'Channel_19'%20and%20RowKey%20eq%20'2'

Maybe it still needs to be between brackets like

http:// myaccount.table.core.windows.net/Blogs(?PartitionKey%20eq%20'Channel_19'%20and%20RowKey%20eq%20'2')

---

Microsoft Platform Developer Cloudikka blog

Answer 2

Hi Petr, 

Thanks for your answer but unfortunately not, my objective here is to use the filter mechanism, the reason I am putting it in a entity group transaction is to avoid having the filter truncated by using a GET. In the example there is no risk of this happening, but when I add additional keys to the ()$filter= then this will be possible. 

The higher level objective is to get many random keys from the one partition in a single roundtrip to table services, I would like to avoid multiple requests where possible for better throughput. Basically by using $filter I am trying to do the equivalent of WHERE rowkey IN ('key1', 'key2' .....)

There is more information about how these requests are meant to work at: 

http://msdn.microsoft.com/en-us/library/windowsazure/dd179421.aspx

http://msdn.microsoft.com/en-us/library/windowsazure/dd894031.aspx

http://msdn.microsoft.com/en-us/library/windowsazure/dd894038.aspx

Answer 3

James, I double checked you request format and it appears to be syntactically correct and should work. (I validated by looking at the request made by my table storage client, www.lucifure.com, in case you are interested in some of the abstractions provided by it.).

Can you try using ‘+’ instead of ‘%20’ in case that is getting inadvertently escaped out?

Also, looking at the actual request made using Fiddler may give better clues.

Answer 4

Hi Lucifure, 

Thanks for your answer, I've now tried it with the + signs to no avail, I'm pretty sure the request format is correct, as if I use the exact same request as a GET request there are no problems. There are only issues when I attempt to make it part of an entity group transaction, I was kind of hoping someone from the Azure Storage team would offer some insights........

(PS: Fiddler is awesome, but I don't need it as I am using a hand rolled rest client and I'm acutely aware of the raw requests been made) 

Thanks Again,
James

Answer 5

Hi James,

I am assuming that you are formatting your payload as specified here http://msdn.microsoft.com/en-us/library/windowsazure/dd894038.aspx.

Entity group transactions are much more involved; given that I have written an alternate table storage client, is it easy to make an error elsewhere.

The link specifies that only a single query is supported in the batch. Is it possible that you have more than a single query in your batch? If so, you can format your query like PartitionKey=’X’ and (RowKey=”Y1” or RowKey=”Y2” …) and not use the batch at all.

Answer 6

Hi Lucifure, 

Sorry for the delay, didn't get the email alert for some reason. Thanks for your help, I really appriciate having second person look into the logic. Definitely just the one query in the batch, and the batch works if I use the non-filter syntax (I've really tested / exhausted all other items around this, and am rather sure it's the syntax).

My motivation for using the post/batch method is to avoid limitations in the URL length of GET requests, which I hit really quickly with the ID's our system uses.

I have a strong suspicion that the post/batch/transaction method doesn't support using $filter=

That said I've made stupid mistakes before, is there any chance you can try a filter query in your library?

Regards,
James

Answer 7

Hi James,

You are correct that there are multiple limitations and I understand your attempts to circumvent them. However the batch method only supports a single queue per batch.

So another approach for your needs, as I am sure you have considered, is to build multiple queries with as much filter as each can hold and execute them in parallel.

Best!