In this video, use the table API to retrieve an entity from Azure storage tables by using a query encompassing the PartitionKey and RowKey.
- [Instructor] Let's see how to get some data out of Azure Table storage. I'll copy and paste the file from before, drag and drop onto the project, hold down Control, notice the plus symbol, let go of the mouse button. Click the modified file, the copied file, rename it. And this one is now for queries. So we'll open the file itself, rename the class.
And scroll down. So here we have the context, we'll set up the Client and then we'll use that to get the table. I'll delete the test methods. So we should end up with an empty class, that has an empty test method. So in the class initialize, the context object is provided, we use it to read key, the name of our storage account, create the credentials object, cloud storage account, and then from there we get the Client.
So let's query that table we've created, we know it's called Customers. Take a look at storage explorer, we know it's still there. There's the test new entity table and it has 100 entries. We will ask the Client object to give us a reference to that table. Test new entity is its name, here at the bottom left. And then I'll just make sure the table as it exists returns a boolean if we await.
And we'll just check NFluent here, check that tableExists isTrue. If that's the case, we're connected and we can query. I'll just build, Control + Shift + B, run the test to make sure we're in a good state. Right click, run selected tests. And we're not. Now back to storage explorer, testNewEntity, new with a W, new entity.
Save, run the test again. And my typo has been corrected. Okay, let's get into something useful, let's create a query. Well a query is going to come from a new TableQuery, and in this case we know what we're getting. So we will actually say you know what? We're going to get a CustomerEntity out of this, so the TableQuery object will give us a query defined specifically for CustomerEntity, we're going to be expecting to get that back.
Now notice what's going to happen, I'm going to hit the enter key here, so I'm still in the same statement. Say .where. So I'm going to build up a where clause, notice it's a string, so it's just a query, a where expression that's going to be submitted through the Rest API to give us a list of customers back. Now within this, let's put this on the next line to help. There is a helper called TableQuery.GenerateFilterCondition.
And it simply takes three parameter strings again. What do you want, is it equal to, less than, greater than, et cetera. Some value. And so the first thing we want to look at is going to be the partition key, and then we'll use a query comparison operator, in this case an equal sign. Yes, it's going to generate a string there for us. And then the second one will be Delsink, the name that we're looking for in the partition key. Now in this case, we should get duplicates, remember we have the partition key, combined with the row key for unique values.
And so that's our query object that we want to submit through the table reference to generate some results for us. So to do that we can await the table, not executeAsync just as it is, but we need to distinguish that we're going to execute a query, not an insert, not a batch. This is specialized to a query. And notice that it is segmented, so it will give us portions of our query result, and we have to check to see if there's more. So it's essentially going to page the results as the become available, now after passing the query itself and a table continuation token, if I can pronounce that tongue twister.
So a TCT for short, table continuation token. Close the bracket, semicolon, so let's get those in place. The query we created up here, this is a TableQuery object, it's already ready to go. And the table continuation token, TCT, will start out as null. So it's this object that keeps track of whether there is more data to be had. So we have to run out method more than once, we have to execute the query until table continuation token is null again.
So what does this mean? It starts out null, we call this method, so being null it knows it's the first time it called this. And then the result, what comes back to us will tell us if there's another page, so whether there is a new ContinuationToken for us to use. So the query result coming back will have a ContinuationToken for us. .ContinuationToken, and of course it will have actual results as well. So this query result is the result of this call, but it's not the result that we're looking for, we're looking for customer entities.
We'll create our own collection for that. It's no real surprise, we can add to that. And look at how convenient this is, the query result from that method has a results method, and we were specific about what our query was about. And so the result is in fact a CustomerEntity collection, a list of CustomerEntity. And there you have it, that is how we get through all of these calls until there are no more pages in terms of results and we just take each page of results and add it to our collection before we process.
So let's check that the collection has a lot of data in it. Customers.Count IsGreaterThan zero, I hope. Control + Shift + B to build. Right click, run our query, and it passes. Now we know there's 100 results in there at the moment. Let's see if that passes. Right click, run.
And it does not succeed. Notice the expected value that we specified was 100, and the checked value, the value of the actual result was one. Well why? Notice up here, Delsink with a capital D, go to storage explorer, all there is except the first one, lower case D. So it is case sensitive when it's doing that search, so be a little bit careful with that, just like the names of tables, it is fussy, it is case sensitive when we're searching. Let's do something just slightly more elaborate.
Copy and paste this method, Control + C, scroll to the bottom, Control + V. So this will be number 32, and now I just want to show you a slightly more elaborate query. So this should start feeling like a language integrated query, but we don't actually have that in the .NET core 2 library. You have to use the full Windows client for Azure Table storage if you still want link support. And the classes have been marked as deprecated, so there are changes afoot. So here we have the TableQuery, and we say where, and notice this guy over here.
We have TableQuery.CombineFilters. So open that bracket, close it down here. And so now this GenerateFolderCondition is going to be one of two. So put it all on one line. So we'll combine the filters, this one, and we'll add a second. So pardon me, just clearing out brackets here. Comma, let's make this full screen so you can see the whole statement.
So we're going to combine this filter with two more. And so the next one is TableOperators and And, and then another Table Query, and we'll GenerateFilterCondition again. And so in this case we have PartitionKey, and I'll put in RowKey and then the actual value.
Now in this case remember these two combined make for a unique key. And so we will not be getting a collection back, and our test here at the bottom should be one to be exact, same as before. The difference though, please note this guy over here, TableOperators with an And, combine TableQuery after TableQuery. There are more options of course. So and, or, equals, not equals. But this is something to get used to, the TableQuery and TableOperators to combine into an actual query.
Remember to give Microsoft feedback. Yes, we all want language and integrated query here as well, especially with the latest version of the API and especially in the .NET core library. So let's do one more query, something a little bit more flexible. I'll copy this last method, go to the end, paste. And so now 33. Save all, build, yeah we're still good. Okay, so previously we had a query that's built up from query operators and all these different query parts.
So we're going to do things a little bit differently now. We're going to select from the table and we're not going to select everything. So previously we got all of the properties for that object. Now I'm going to say I want less. So the TableQuery will not be of CustomerEntity. But instead, we will select specific columns. Look at the arguments there. I list the string, so please name the columns that you would like to retrieve. So the bigger your entities are and the more data you have, the bigger the network traffic will be, the more bandwidth you will consume.
So here we have the option to query and then select only the columns that's relevant to our query. So I'll just say you know what? I only want the email address column, maybe that's enough for what I'm doing. So we have the where clause, yes that's valid, and now we have the select where we can say, you know what, please project on the server side, project only the columns I want into an object that I would use here on the client side. This does mean that my result will not be customers any more, but rather a list of something new.
A DynamicTableEntity. And a DynamicTableEntity is one that doesn't insist on having certain properties, certain columns. And so when we run our query, same as before, run this query, we have our ContinuationToken, and by the end we will have more than one result. Greater than zero, let's take a look inside. ForEach, DynamicTableEntity, in the results.
Take a look, please Check.That e.Properties, we don't have the strict typing here, so we don't actually have an EmailAddress on this object. But we do have a properties collection, and it will check. Does the properties collection have TryGetValue a property by the name EmailAddress? It better. And if it does, it'll give us an entity, property for that particular value, for that particular property.
And we're going to insist that this is true. So this method here, TryGetValue will return true if EmailAddress exists, and if it exists it'll actually give us the EmailAddress. So this whole thing, if not true, we were given an entity without the EmailAddress property. And so we're just going to check that everything that comes back in fact does have EmailAddresses. Control + Shift + B to build, and just remember what we did differently on the TableQuery. We used select to project only the properties, only the columns, the fields we wanted on these entities server side onto our result, and so that's all we got.
Going back to test explorer, this is number 33 by now, run. And success. It's worth putting a breakpoint down here and inspecting what E looks like. It is a dynamic table entity, so it could have many properties, but at this point we're saying we're not very specific, we're not very particular about it.
- Creating a Blob storage account
- Stored access policies for granting privileges
- Shared access signatures
- Encrypting data at rest
- Connecting to and creating a table
- Creating entities
- Querying tables
- Performance constraints of Azure Storage tables