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); }