C# Table.TableQuery類代碼示例

        public List<ICatalogJob> GetAllCatalogJob(string organization)
        {
            string tableName = NameHelper.GetCatalogJobTableName(organization);
            tableName = TableDataAccess.ValidateTableName(tableName);
            TableDataAccess tableDataAccess = new TableDataAccess(TableClient);
            CloudTable table = tableDataAccess.GetTable(tableName);
            if (table == null)
                return null;

            TableQuery<CatalogEntity> query = new TableQuery<CatalogEntity>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, organization));

            query.TakeCount = 100;
            TableRequestOptions a = new TableRequestOptions();
            OperationContext c = new OperationContext();

            var queryResult = table.ExecuteQuery(query);

            List<ICatalogJob> result = new List<ICatalogJob>(queryResult.Count());
            foreach (CatalogEntity entity in queryResult)
            {
                CatalogEntity.SetOtherByPartitionRowKeys(entity);
                result.Add(entity);
            }
            return result;
        }

        private List<ConnectionEntity> SendMessageTo(String who, String message)
        {
            //var name = Context.User.Identity.Name;
            var name = this.GetConnectionUser();

            if (!String.IsNullOrEmpty(name))
            {
                var table = GetConnectionTable();

                // Notice that the partition keys are stored in azure storage as lower case
                var query = new TableQuery<ConnectionEntity>()
                    .Where(TableQuery.GenerateFilterCondition(
                    "PartitionKey",
                    QueryComparisons.Equal,
                    who.ToLowerInvariant()));

                var queryResult = table.ExecuteQuery(query).ToList();
                if (queryResult.Count == 0)
                {
                    Clients.Caller.showErrorMessage("The user is no longer connected.");
                }
                else
                {
                    // Load only once the host application connections to display the data there
                    if(queryResult.Count(o=>o.PartitionKey.Equals(Constants.SignalR_HostApplicationUserName.ToLowerInvariant())) <= 0)
                        queryResult.AddRange(this.SendMessageTo(Constants.SignalR_HostApplicationUserName, message));

                    return queryResult;
                }
            }

            return new List<ConnectionEntity>();
        }

        internal ISet<string> GetAllItemsFromParttion(string partitionKey)
        {
            var returnSet = new HashSet<string>();

            try
            {
                var tableQuery =
                    new TableQuery<ToBeIndexedEntity>().Where(TableQuery.GenerateFilterCondition("PartitionKey",
                                                                                                 QueryComparisons.Equal,
                                                                                                 partitionKey));
                var exec = _table.ExecuteQuery(tableQuery);

                foreach (var toBeIndexedEntity in exec)
                {
                    returnSet.Add(toBeIndexedEntity.EntityId);
                }

            }
            catch (Exception ex)
            {
                Trace.TraceError("Error in getting all entities for parition {0}, exception {1}", partitionKey, ex);
            }

            return returnSet;
        }

        public TableBinding(ScriptHostConfiguration config, TableBindingMetadata metadata, FileAccess access) 
            : base(config, metadata, access)
        {
            if (string.IsNullOrEmpty(metadata.TableName))
            {
                throw new ArgumentException("The table name cannot be null or empty.");
            }

            TableName = metadata.TableName;

            PartitionKey = metadata.PartitionKey;
            if (!string.IsNullOrEmpty(PartitionKey))
            {
                _partitionKeyBindingTemplate = BindingTemplate.FromString(PartitionKey);
            }

            RowKey = metadata.RowKey;
            if (!string.IsNullOrEmpty(RowKey))
            {
                _rowKeyBindingTemplate = BindingTemplate.FromString(RowKey);
            }

            _tableQuery = new TableQuery
            {
                TakeCount = metadata.Take ?? 50,
                FilterString = metadata.Filter
            };
        }

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

        public List<Good> GetByCategoryId(string categoryId,
			bool? isApprover = null)
        {
            string filter;
            if (isApprover.HasValue)
            {
                filter = TableQuery.CombineFilters(
                    TableQuery.GenerateFilterCondition(
                        "PartitionKey",
                        QueryComparisons.Equal,
                        categoryId),
                    TableOperators.And,
                    TableQuery.GenerateFilterConditionForBool(
                        "IsApproved",
                        QueryComparisons.Equal,
                        isApprover.Value)
                    );
            }
            else
            {
                filter = TableQuery.GenerateFilterCondition(
                    "PartitionKey",
                    QueryComparisons.Equal,
                    categoryId);
            }

            var query = new TableQuery<Good>()
                .Where(filter);
            return Table.ExecuteQuery(query).ToList()
                .OrderBy(i => i.Title).ToList();
        }

 public ActionResult DeleteConfirmed(string partitionKey)
 {
     // Delete all rows for this mailing list, that is,
     // Subscriber rows as well as MailingList rows.
     // Therefore, no need to specify row key.
     var query = new TableQuery<MailingList>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, partitionKey));
     var listRows = mailingListTable.ExecuteQuery(query).ToList();
     var batchOperation = new TableBatchOperation();
     int itemsInBatch = 0;
     foreach (MailingList listRow in listRows)
     {
         batchOperation.Delete(listRow);
         itemsInBatch++;
         if (itemsInBatch == 100)
         {
             mailingListTable.ExecuteBatch(batchOperation);
             itemsInBatch = 0;
             batchOperation = new TableBatchOperation();
         }
     }
     if (itemsInBatch > 0)
     {
         mailingListTable.ExecuteBatch(batchOperation);
     }
     return RedirectToAction("Index");
 }

        public IEnumerable<SymmetricKey> GetAllKeys()
        {
            // Create the CloudTable object that represents the "people" table.
            var table = tableClient.GetTableReference(keyTableName);

            // Create a retrieve operation that takes a customer entity.
            var query = new TableQuery<SymmetricKey>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "SymmetricKey"));

            try
            {
                return table.ExecuteQuery(query).ToList();
            }
            catch (DataServiceQueryException dsq)
            {
                throw new AzureCryptoException("Failed to load encryption keys from storage", dsq);
            }
            catch (DataServiceClientException dsce)
            {
                throw new AzureCryptoException("Failed to load encryption keys from storage", dsce);
            }
            catch (Exception ex)
            {
                throw new AzureCryptoException("Could not load encryption keys table", ex);
            }
        }

        public ActionResult Index(string id, string listName)
        {
            if (string.IsNullOrEmpty(id) == true || string.IsNullOrEmpty(listName))
            {
                ViewBag.errorMessage = "Empty subscriber ID or list name.";
                return View("Error");
            }
            string filter = TableQuery.CombineFilters(
                TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, listName),
                TableOperators.And,
                TableQuery.GenerateFilterCondition("SubscriberGUID", QueryComparisons.Equal, id));
            var query = new TableQuery<Subscriber>().Where(filter);
            var subscriber = mailingListTable.ExecuteQuery(query).ToList().Single();

            if (subscriber == null)
            {
                ViewBag.Message = "You are already unsubscribed";
                return View("Message");
            }

            var unsubscribeVM = new UnsubscribeVM();
            unsubscribeVM.EmailAddress = MaskEmail(subscriber.EmailAddress);
            unsubscribeVM.ListDescription = FindRow(subscriber.ListName, "mailinglist").Description;
            unsubscribeVM.SubscriberGUID = id;
            unsubscribeVM.Confirmed = null;
            return View(unsubscribeVM);
        }

 private List<LogEntity> GetLogEntities()
 {
     // Construct the query operation for all customer entities where PartitionKey="Smith".
     var query = new TableQuery<LogEntity>();
     var entities = _cloudTable.ExecuteQuery(query);
     return entities.ToList();
 }

        /// <summary>
        /// Demonstrate a partition scan whereby we are searching for all the entities within a partition. Note this is not as efficient 
        /// as a range scan - but definitely more efficient than a full table scan. The async API's require the user to implement 
        /// paging themselves using continuation tokens. 
        /// </summary>
        /// <param name="partitionKey">The partition within which to search</param>
        public static async Task<List<AuditModel>> PartitionScanAsync(string partitionKey)
        {
            // Retrieve the storage account from the connection string.
            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
                ConfigurationManager.AppSettings["StorageConnectionString"]);
            // Create the table client.
            CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

            // Create the CloudTable object that represents the "people" table.
            CloudTable table = tableClient.GetTableReference("audit");

            TableQuery<AuditModel> partitionScanQuery = new TableQuery<AuditModel>().Where
                (TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, partitionKey));
            var toReturn = new List<AuditModel>();
            TableContinuationToken token = null;
            // Page through the results
            do
            {
                TableQuerySegment<AuditModel> segment = await table.ExecuteQuerySegmentedAsync(partitionScanQuery, token);
                token = segment.ContinuationToken;
                foreach (AuditModel entity in segment)
                {
                    toReturn.Add(entity);
                }
            }
            while (token != null);

            return toReturn.OrderByDescending(a=> a.Timestamp).ToList();
        }

        public List<Reply> GetReplyNotif(string userid)
        {
            TableQuery<ReplyNotificationEntifity> query = new TableQuery<ReplyNotificationEntifity>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, userid));

            List<Reply> replies = new List<Reply>();
            TableBatchOperation batchOperation = new TableBatchOperation();
            int count = 0;

            // Print the fields for each customer.
            foreach (ReplyNotificationEntifity entity in _replyNotification.ExecuteQuery(query))
            {
                replies.Add(JsonConvert.DeserializeObject<Reply>(entity.Content));

                batchOperation.Add(TableOperation.Delete(entity));
                count++;

                if((count % 100) == 0){
                    _replyNotification.ExecuteBatch(batchOperation);
                    batchOperation = new TableBatchOperation();
                    count = 0;
                }
            }

            if (count > 0)
            {
                _replyNotification.ExecuteBatch(batchOperation);
            }

            return replies;
        }

        /*
        public IEnumerable<SensorLog> Get(string device_id)
        {
            var queryPairs = this.Request.GetQueryNameValuePairs();
            string dateLong = queryPairs.FirstOrDefault(q => q.Key == "date").Value;

            string storeCS = CloudConfigurationManager.GetSetting("StorageConnectionString");
            CloudStorageAccount storageAccound = CloudStorageAccount.Parse(storeCS);
            CloudTableClient tableClient = storageAccound.CreateCloudTableClient();
            CloudTable sensorLogTable = tableClient.GetTableReference("SensorLog");

            TableQuery<SensorLog> query = null;
            if (dateLong == null)
            {
                query = new TableQuery<SensorLog>().Where(
                    TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, device_id));
            } else
            {
                long dateBinary = long.Parse(dateLong);
                DateTime data = DateTime.FromBinary(dateBinary);
                query = new TableQuery<SensorLog>().Where(
                    TableQuery.CombineFilters(
                    TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, device_id),
                    TableOperators.And,
                    TableQuery.GenerateFilterConditionForDate("UploadTime", QueryComparisons.GreaterThan, data)));
            }
            var results = sensorLogTable.ExecuteQuery(query);
            var sorted = from t in results orderby t.UploadTime select t;
            return sorted.Select(ent => (SensorLog)ent).ToList();
        }*/
        public IEnumerable<SensorLog> Get(string device_id, int len)
        {
            var queryPairs = this.Request.GetQueryNameValuePairs();
            string dateLong = queryPairs.FirstOrDefault(q => q.Key == "date").Value;

            string storeCS = CloudConfigurationManager.GetSetting("StorageConnectionString");
            CloudStorageAccount storageAccound = CloudStorageAccount.Parse(storeCS);
            CloudTableClient tableClient = storageAccound.CreateCloudTableClient();
            tableClient.DefaultRequestOptions = new TableRequestOptions()
            {
                PayloadFormat = TablePayloadFormat.JsonNoMetadata
            };
            CloudTable sensorLogTable = tableClient.GetTableReference("SensorLog");

            TableQuery<SensorLog> query = null;
            if (dateLong == null)
            {
                query = new TableQuery<SensorLog>().Where(
                    TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, device_id));
            }
            else
            {
                long dateBinary = long.Parse(dateLong);
                DateTime data = DateTime.FromBinary(dateBinary);
                query = new TableQuery<SensorLog>().Where(
                    TableQuery.CombineFilters(
                    TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, device_id),
                    TableOperators.And,
                    TableQuery.GenerateFilterConditionForDate("UploadTime", QueryComparisons.GreaterThan, data)));
            }
            var results = sensorLogTable.ExecuteQuery(query);
            var sorted = from t in results orderby t.UploadTime select t;
            var list = sorted.Select(ent => (SensorLog)ent).ToList();
            return list.GetRange(list.Count - len, len);
        }

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

        //
        // GET: /Subscription/
        //
        // Note: This way of handling may not scale and may need to use continuation tokens later
        //
        public ActionResult Index()
        {
            TableRequestOptions reqOptions = new TableRequestOptions()
            {
                MaximumExecutionTime = TimeSpan.FromSeconds(10),
                RetryPolicy = new LinearRetry(TimeSpan.FromSeconds(3), 3)
            };

            List<Subscription> subscribers;
            try
            {
                var query = new TableQuery<Subscription>().Select(new string[] {
                    "PartitionKey",
                    "RowKey",
                    "Description",
                    "Verified"
                });

                subscribers = subscribersTable.ExecuteQuery(query, reqOptions).ToList();
            }
            catch (StorageException se)
            {
                ViewBag.errorMessage = "Timeout error, try again.";
                Trace.TraceError(se.Message);
                return View("Error: " + se.Message);
            }

            return View(subscribers);
        }