public async Task<Response<JsonObject>> GetByIdAsync(string id)
{
if (string.IsNullOrEmpty(id))
throw new ArgumentNullException("id");
var table = await DefineTableAsync(CreateCloudTable).ConfigureAwait(false);
var query =
new TableQuery<DynamicTableEntity>();
query.Where(TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "PK"),
TableOperators.And,
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, id)));
query.Where(TableQuery.GenerateFilterConditionForBool("sys_deleted", "ne", true));
var items = table.ExecuteQuery(query);
var result = items.ToList();
var json = new JsonObject();
var status = HttpStatusCode.NotFound;
// ReSharper disable UseMethodAny.3
if (result.Count() <= 0) return new Response<JsonObject>(status, json);
// ReSharper restore UseMethodAny.3
json = result.First().ToJsonObject();
status = HttpStatusCode.OK;
return new Response<JsonObject>(status, json);
}
private TableQuery<LogEntity> MapQuery(AzureNLogQueryDefinition originalQuery)
{
var query = new TableQuery<LogEntity>();
var filters = new[]
{
this.GetPartitionKeyFilter(originalQuery), this.GetMinDateFilter(originalQuery),
this.GetMaxDateFilter(originalQuery), this.GetLevelFilter(originalQuery)
}
.Where(f => !string.IsNullOrWhiteSpace(f))
.ToArray();
if (!filters.Any())
{
return query;
}
var querySt = filters[0];
for (var i = 1; i < filters.Length; i++)
{
querySt = TableQuery.CombineFilters(querySt, TableOperators.And, filters[i]);
}
return query.Where(querySt);
}
public IEnumerable<string> GetAllTags(string feed)
{
var query = new TableQuery<FeedEntryEntity>();
query.Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, feed));
var retrievedResults = Table.ExecuteQuery(query);
return retrievedResults.Where(x => x.Tag != null).Select(x => x.Tag).Distinct();
}
public IEnumerable<FeedMetaData> GetFeeds(string username)
{
var query = new TableQuery<FeedMetaData>();
query.Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, username));
var retrievedResults = Table.ExecuteQuery(query);
return retrievedResults;
}
public IEnumerable<FeedEntryEntity> GetPublishFeedEntries(string feed)
{
var query = new TableQuery<FeedEntryEntity>();
query.Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, feed));
var retrievedResults = Table.ExecuteQuery(query);
return retrievedResults.Where(x => x.PublishedAt <= DateTime.Now).OrderByDescending(x => x.PublishedAt);
}
public IEnumerable<FeedEntryEntity> GetAllWithTag(string feedName, string selectedtag)
{
var query = new TableQuery<FeedEntryEntity>();
var generateFilterCondition = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, feedName);
query.Where(generateFilterCondition);
var retrievedResults = Table.ExecuteQuery(query);
var result = retrievedResults.Where(x => x.Tag == selectedtag)
.ToList();
return result;
}
/// <summary>
/// Query Table Storage
/// </summary>
/// <param name="identifier">Identifier</param>
/// <param name="version">Version</param>
/// <param name="fileName">File Name</param>
/// <returns></returns>
public virtual async Task<IEnumerable<IDictionary<string, object>>> Query(Guid? identifier = null, string version = null, string fileName = null)
{
var partitionFilter = identifier.HasValue ? TableQuery.GenerateFilterCondition(TableStorage.PartitionKey, QueryComparisons.Equal, identifier.Value.ToString()) : null;
var rowFilter = !string.IsNullOrWhiteSpace(version) ? TableQuery.GenerateFilterCondition(TableStorage.RowKey, QueryComparisons.Equal, version) : null;
var query = new TableQuery();
if (null != partitionFilter && null != rowFilter)
{
query.Where(TableQuery.CombineFilters(partitionFilter, TableOperators.And, rowFilter));
}
else if (null != partitionFilter)
{
query.Where(partitionFilter);
}
else if (null != rowFilter)
{
query.Where(rowFilter);
}
var images = await this.table.Query(query);
if (null != images)
{
images = images.Where(i => string.IsNullOrWhiteSpace(fileName) || fileName == (string)i["FileName"]);
foreach (var data in images)
{
data.Add("Identifier", data[TableStorage.PartitionKey]);
data.Add("Version", data[TableStorage.RowKey]);
data.Add("CreatedOn", data[TableStorage.Timestamp]);
data.Remove(TableStorage.PartitionKey);
data.Remove(TableStorage.Timestamp);
data.Remove(TableStorage.RowKey);
data.Remove(TableStorage.ETag);
}
}
return images;
}
public IEnumerable<string> GetAllLinks(string feedName)
{
var query = new TableQuery<FeedEntryEntity>();
var generateFilterCondition = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, feedName);
query.Where(generateFilterCondition);
var retrievedResults = Table.ExecuteQuery(query);
var result = retrievedResults
.Where(x => x.Link != null)
.Select(x => x.Link)
.ToList();
return result;
}
// NOTE: we don't have a need (yet?) to query commands, as we don't use them to
// recreate read models, nor we are using it for BI, so we just
// expose events.
public IEnumerable<IEvent> Query(QueryCriteria criteria)
{
var context = this.tableClient;
var query = new TableQuery<MessageLogEntity>()
.Where(x => x.Kind == StandardMetadata.EventKind);
var where = criteria.ToExpression();
if (where != null)
{
query = query.Where(where);
}
var table = context.GetTableReference(this.tableName);
return table.ExecuteQuery(query.AsTableQuery())
.ToList()
.Select(e => this.serializer.Deserialize<IEvent>(e.Payload));
}
private async Task<TableQuerySegment<DynamicTableEntity>> GetData(string tableName, string filter, TableContinuationToken continuationToken)
{
var table = tableClient.GetTableReference(tableName);
if (!table.Exists())
{
throw new ArgumentException("Table does not exist", tableName);
}
var query = new TableQuery<DynamicTableEntity>();
if (!String.IsNullOrEmpty(filter))
{
query = query.Where(filter);
}
if (continuationToken != null)
{
continuationToken.NextTableName = tableName;
}
return await table.ExecuteQuerySegmentedAsync(query, continuationToken);
}
public async Task<List<CheeseReviewEntity>> SearchCheeseReviewsAsync (string emailAddress)
{
List<CheeseReviewEntity> results = new List<CheeseReviewEntity> ();
try {
if (client == null)
await InitializeCloudClientAsync ();
var table = client.GetTableReference (reviewTable);
// Generate a query
var query = new TableQuery<CheeseReviewEntity> ();
// Looking for an exact match
query.Where (TableQuery.GenerateFilterCondition ("PartitionKey", QueryComparisons.Equal, emailAddress));
// This version of the Table Storage NuGet only has the option
// of returning a subset of the entire entity set at a time.
// We could use the TableContinuation token along with a loop to get
// everything out - but we'll just go with the first return
TableContinuationToken continueToken = null;
// Execute the query
var s = await table.ExecuteQuerySegmentedAsync (query, continueToken);
continueToken = s.ContinuationToken;
var searchResults = s.Results;
results.AddRange (s.Results);
} catch (Exception ex) {
var exMsg = ex.ToString ();
}
return results;
}
private IEnumerable<Triple> QueryTriples(string dimension, string pk1)
{
var query = new TableQuery<GraphEntity>();
query.Where(string.Format("PartitionKey gt '{1}~{0}~' and PartitionKey lt '{1}~{0}~~'", KeyEncoder(pk1), dimension));
return table.ExecuteQuery(query).Select(entity => entity.ToTriple());
}
private TableQuery GenerateListTablesQuery(string prefix, int? maxResults)
{
TableQuery query = new TableQuery();
if (!string.IsNullOrEmpty(prefix))
{
// Append Max char to end '{' is 1 + 'z' in AsciiTable
string uppperBound = prefix + '{';
query = query.Where(TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition(TableConstants.TableName, QueryComparisons.GreaterThanOrEqual, prefix),
TableOperators.And,
TableQuery.GenerateFilterCondition(TableConstants.TableName, QueryComparisons.LessThan, uppperBound)));
}
if (maxResults.HasValue)
{
query = query.Take(maxResults.Value);
}
return query;
}
private IEnumerable<Triple> QueryTriples()
{
var query = new TableQuery<GraphEntity>();
query.Where(string.Format("PartitionKey gt '{0}~' and PartitionKey lt '{0}~~'", SUBJECT_VALUE));
return table.ExecuteQuery(query).Select(entity => entity.ToTriple());
}
private IEnumerable<Triple> QueryTriples(string dimension, string pk1, string pk2)
{
var query = new TableQuery<GraphEntity>();
query.Where(TableQuery.GenerateFilterCondition("PartitionKey", "eq", JoinKey(dimension, pk1, pk2)));
return table.ExecuteQuery(query).Select(entity => entity.ToTriple());
}
private bool AppendLogs(CloudStorageAccount storageAccount, string partition, List<QLog> result, QLogBrowserSettings settings)
{
string[] partitionSplit = partition.Split(',');
string tableName = partitionSplit[0];
string tablePartition = partitionSplit[1];
string postfix = StorageConnectionHelper.GetPostfix(settings);
if(!String.IsNullOrWhiteSpace(postfix))
tableName = String.Format("qlog{0}{1}", postfix, tableName);
else
tableName = String.Format("qlog{0}", tableName);
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference(tableName);
if (table.Exists())
{
TableQuery<QLog> logsQuery = new TableQuery<QLog>();
string filter = GetFilters(tablePartition, settings);
logsQuery.Where(filter);
foreach (QLog log in table.ExecuteQuery(logsQuery))
{
if (!String.IsNullOrWhiteSpace(settings.ContainingText))
if(!log.Message.ToLower().Contains(settings.ContainingText.ToLower()))
continue;
log.CreatedOn = log.CreatedOn.ToLocalTime();
result.Add(log);
if (result.Count >= settings.Limit)
return false;
}
return true;
}
else
return true;
}
private void DoEscapeTest(string data, bool useBatch, bool includeKey)
{
DynamicTableEntity ent = new DynamicTableEntity(includeKey ? "temp" + data : "temp", Guid.NewGuid().ToString());
ent.Properties.Add("foo", new EntityProperty(data));
// Insert
if (useBatch)
{
TableBatchOperation batch = new TableBatchOperation();
batch.Insert(ent);
currentTable.ExecuteBatch(batch);
}
else
{
currentTable.Execute(TableOperation.Insert(ent));
}
// Retrieve
TableResult res = null;
if (useBatch)
{
TableBatchOperation batch = new TableBatchOperation();
batch.Retrieve(ent.PartitionKey, ent.RowKey);
res = (currentTable.ExecuteBatch(batch))[0];
}
else
{
res = currentTable.Execute(TableOperation.Retrieve(ent.PartitionKey, ent.RowKey));
}
// Check equality
DynamicTableEntity retrievedEntity = res.Result as DynamicTableEntity;
Assert.IsNotNull(retrievedEntity);
Assert.AreEqual(ent.PartitionKey, retrievedEntity.PartitionKey);
Assert.AreEqual(ent.RowKey, retrievedEntity.RowKey);
Assert.AreEqual(ent.ETag, retrievedEntity.ETag);
Assert.AreEqual(ent.Properties.Count, retrievedEntity.Properties.Count);
Assert.AreEqual(ent.Properties["foo"], retrievedEntity.Properties["foo"]);
// Query using data filter
TableQuery query = new TableQuery();
query.Where(string.Format(
"(PartitionKey eq \'{0}\') and (RowKey eq \'{1}\') and (foo eq \'{2}\')",
ent.PartitionKey,
ent.RowKey,
data.Replace("\'", "\'\'")));
retrievedEntity = currentTable.ExecuteQuery(query).Single();
Assert.IsNotNull(retrievedEntity);
Assert.AreEqual(ent.PartitionKey, retrievedEntity.PartitionKey);
Assert.AreEqual(ent.RowKey, retrievedEntity.RowKey);
Assert.AreEqual(ent.ETag, retrievedEntity.ETag);
Assert.AreEqual(ent.Properties.Count, retrievedEntity.Properties.Count);
Assert.AreEqual(ent.Properties["foo"], retrievedEntity.Properties["foo"]);
// Merge
ent.Properties.Add("foo2", new EntityProperty("bar2"));
if (useBatch)
{
TableBatchOperation batch = new TableBatchOperation();
batch.Merge(ent);
currentTable.ExecuteBatch(batch);
}
else
{
currentTable.Execute(TableOperation.Merge(ent));
}
// Retrieve
if (useBatch)
{
TableBatchOperation batch = new TableBatchOperation();
batch.Retrieve(ent.PartitionKey, ent.RowKey);
res = (currentTable.ExecuteBatch(batch))[0];
}
else
{
res = currentTable.Execute(TableOperation.Retrieve(ent.PartitionKey, ent.RowKey));
}
retrievedEntity = res.Result as DynamicTableEntity;
Assert.AreEqual(ent.PartitionKey, retrievedEntity.PartitionKey);
Assert.AreEqual(ent.RowKey, retrievedEntity.RowKey);
Assert.AreEqual(ent.ETag, retrievedEntity.ETag);
Assert.AreEqual(ent.Properties.Count, retrievedEntity.Properties.Count);
Assert.AreEqual(ent.Properties["foo"], retrievedEntity.Properties["foo"]);
// Replace
ent.Properties.Remove("foo2");
ent.Properties.Add("foo3", new EntityProperty("bar3"));
if (useBatch)
{
TableBatchOperation batch = new TableBatchOperation();
batch.Replace(ent);
currentTable.ExecuteBatch(batch);
}
else
{
currentTable.Execute(TableOperation.Replace(ent));
}
// Retrieve
if (useBatch)
{
TableBatchOperation batch = new TableBatchOperation();
batch.Retrieve(ent.PartitionKey, ent.RowKey);
res = (currentTable.ExecuteBatch(batch))[0];
}
else
{
res = currentTable.Execute(TableOperation.Retrieve(ent.PartitionKey, ent.RowKey));
}
retrievedEntity = res.Result as DynamicTableEntity;
Assert.AreEqual(ent.PartitionKey, retrievedEntity.PartitionKey);
Assert.AreEqual(ent.RowKey, retrievedEntity.RowKey);
Assert.AreEqual(ent.ETag, retrievedEntity.ETag);
Assert.AreEqual(ent.Properties.Count, retrievedEntity.Properties.Count);
Assert.AreEqual(ent.Properties["foo"], retrievedEntity.Properties["foo"]);
}
//************************
//* *
//* ShowTableContainer *
//* *
//************************
// Get and show entities in selected table container. Call from UI thread.
public void ShowTableContainer(String tableName)
{
try
{
this.Cursor = Cursors.Wait;
ContainerDetails.Text = "Loading entity list...";
TableListView.ItemsSource = null;
// Create a temporary copy of the TableColumnNames table and add columns as we encounter them.
// This is done to prume away previously saved colum names that are no longer present in the data.
var tempTableColumnNames = new Dictionary<string, bool>();
TableListViewGridView.Columns.Clear();
AddTableListViewColumn("PartitionKey");
AddTableListViewColumn("RowKey", false);
AddTableListViewColumn("Timestamp");
tempTableColumnNames.Add("PartitionKey", TableColumnNames["PartitionKey"]);
tempTableColumnNames.Add("RowKey", TableColumnNames["RowKey"]);
tempTableColumnNames.Add("Timestamp", TableColumnNames["Timestamp"]);
int containerCount = 0;
_EntityCollection.Clear();
TableListView.Visibility = Visibility.Visible;
EntityToolbarPanel.Visibility = Visibility.Visible;
CloudTable table = tableClient.GetTableReference(tableName);
// Query the table and retrieve a collection of entities.
var query = new TableQuery<ElasticTableEntity>();
// Limit the number of entities returned from the query, defaulting to 500 if not specified.
var take = MaxEntityCountFilter > 0 ? MaxEntityCountFilter : 500;
query.TakeCount = take;
IEnumerable<ElasticTableEntity> entities;
if (EntityQueryEnabled)
{
EntityQuery.IsChecked = true;
string filterCondition = null;
var operation = GetTableQueryOperation(EntityQueryCondition[0]);
if (!string.IsNullOrEmpty(operation))
{
filterCondition = GetQueryFilterCondition(EntityQueryColumnName[0], operation, EntityQueryValue[0]);
}
if (EntityQueryColumnName.Length > 1)
{
operation = GetTableQueryOperation(EntityQueryCondition[1]);
if (!string.IsNullOrEmpty(operation))
{
filterCondition = TableQuery.CombineFilters(
filterCondition,
TableOperators.And,
GetQueryFilterCondition(EntityQueryColumnName[1], operation, EntityQueryValue[1]));
}
}
if (EntityQueryColumnName.Length > 2)
{
operation = GetTableQueryOperation(EntityQueryCondition[2]);
if (!string.IsNullOrEmpty(operation))
{
filterCondition = TableQuery.CombineFilters(
filterCondition,
TableOperators.And,
GetQueryFilterCondition(EntityQueryColumnName[2], operation, EntityQueryValue[2]));
}
}
entities = table.ExecuteQuery(query.Where(filterCondition)).ToList();
}
else
{
EntityQuery.IsChecked = false;
entities = table.ExecuteQuery(query).ToList();
}
if (entities != null)
{
// Iterate through the list of entities.
// Ensure a bound column exists in the list view for each.
// Add a representation of each entity to the items source for the list view.
bool match = false;
foreach (ElasticTableEntity entity in entities)
{
match = false;
if (EntityTextFilter == null) match = true;
if (MaxEntityCountFilter != -1 && containerCount >= MaxEntityCountFilter) break;
foreach (KeyValuePair<String, EntityProperty> prop in entity.Properties)
{
AddTableListViewColumn(prop.Key);
if (!tempTableColumnNames.ContainsKey(prop.Key))
{
tempTableColumnNames.Add(prop.Key, TableColumnNames[prop.Key]);
}
}
EntityItem item = new EntityItem(entity);
if (EntityTextFilter != null)
{
if (entity.RowKey.IndexOf(EntityTextFilter, 0, StringComparison.OrdinalIgnoreCase) != -1 ||
entity.PartitionKey.IndexOf(EntityTextFilter, 0, StringComparison.OrdinalIgnoreCase) != -1)
{
match = true;
}
else
{
foreach(KeyValuePair<String, String> field in item.Fields)
{
if (field.Value.IndexOf(EntityTextFilter, 0, StringComparison.OrdinalIgnoreCase) != -1)
{
match = true;
}
}
}
}
if (match)
{
_EntityCollection.Add(item);
containerCount++;
}
TableColumnNames = tempTableColumnNames;
}
}
if (_EntityCollection != null)
{
foreach (EntityItem entity in _EntityCollection)
{
entity.AddMissingFields(TableColumnNames);
}
}
//SortEntityList();
if (containerCount == 1)
{
ContainerDetails.Text = "(1 entity) as of " + DateTime.Now.ToString();
}
else
{
ContainerDetails.Text = "(" + containerCount.ToString() + " entities) as of " + DateTime.Now.ToString();
}
TableListView.ItemsSource = EntityCollection;
this.Cursor = Cursors.Arrow;
}
catch(Exception ex)
{
this.Cursor = Cursors.Arrow;
ShowError("Error querying table: " + ex.Message);
}
}
private void FillCustomersList(string lastname = null)
{
try
{
var tableClient = InitTableClient();
CloudTable table = tableClient.GetTableReference("customers");
table.CreateIfNotExists();
var query = new TableQuery<CustomerEntity>();
if (!string.IsNullOrEmpty(lastname))
{
query = query.Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, lastname));
}
var result = table.ExecuteQuery(query);
rpAllCustomers.DataSource = result;
rpAllCustomers.DataBind();
}
catch (Exception ex)
{
lblErrorMsg.Text = ex.Message;
}
}
public IEnumerable<SiloMetricsTable> QuerySiloMetrics(string deploymentId)
{
var table = this.TableClient.GetTableReference("OrleansSiloMetrics");
var query = new TableQuery<SiloMetricsTable>();
query.Where(TableQuery.GenerateFilterCondition("PartitionKey", "eq", deploymentId));
return table.ExecuteQuery(query);
}
