public void ValidateCreateQueryDefinition()
{
string query = "select * from s where s.Account = @account";
string paramName = "@account";
string paramValue = "12345";
CosmosSqlQueryDefinition sqlQueryDefinition = new CosmosSqlQueryDefinition(query)
.UseParameter(paramName, paramValue);
SqlQuerySpec sqlQuerySpec = sqlQueryDefinition.ToSqlQuerySpec();
Assert.AreEqual(query, sqlQuerySpec.QueryText);
Assert.AreEqual(1, sqlQuerySpec.Parameters.Count);
SqlParameter sqlParameter = sqlQuerySpec.Parameters.First();
Assert.AreEqual(paramName, sqlParameter.Name);
Assert.AreEqual(paramValue, sqlParameter.Value);
string newParamValue = "9001";
sqlQueryDefinition.UseParameter(paramName, newParamValue);
sqlQuerySpec = sqlQueryDefinition.ToSqlQuerySpec();
Assert.AreEqual(query, sqlQuerySpec.QueryText);
Assert.AreEqual(1, sqlQuerySpec.Parameters.Count);
sqlParameter = sqlQuerySpec.Parameters.First();
Assert.AreEqual(paramName, sqlParameter.Name);
Assert.AreEqual(newParamValue, sqlParameter.Value);
}
public async Task <IEnumerable <HeroDocument> > ListAsync()
{
var heroContainer =
_cosmosDatabase.Containers[_containerId];
var sqlQuery =
"SELECT * FROM t1 WHERE t1.object = @object";
var sqlQueryDefinition =
new CosmosSqlQueryDefinition(sqlQuery);
sqlQueryDefinition.UseParameter(
"@object", "Hero");
var heroDocumentIterator =
heroContainer.Items.CreateItemQuery <HeroDocument>(
sqlQueryDefinition,
maxConcurrency: MAXIMUM_CONCURRENCY);
var heroDocumentList = new List <HeroDocument>();
while (heroDocumentIterator.HasMoreResults)
{
heroDocumentList.AddRange(
await heroDocumentIterator.FetchNextSetAsync());
}
;
return(heroDocumentList);
}
public async Task <IEnumerable <Order> > FetchListAsync(
Guid?itemId)
{
var orderContainer =
_cosmosDatabase.Containers["orders"];
var query =
$"SELECT * FROM o";
if (itemId.HasValue)
{
query += $" WHERE ARRAY_CONTAINS(o.items, {{ \"id\": \"{itemId}\" }}, true)";
}
var queryDefinition =
new CosmosSqlQueryDefinition(query);
var orders =
orderContainer.Items.CreateItemQuery <Order>(queryDefinition, maxConcurrency: 2);
var orderList = new List <Order>();
while (orders.HasMoreResults)
{
orderList.AddRange(
await orders.FetchNextSetAsync());
}
;
return(orderList);
}
public async Task <Event> GetEventAsync(string id)
{
try
{
using (CosmosClient cosmosClient = new CosmosClient(accountEndpoint, accountKey))
{
var databaseResponse = await cosmosClient.Databases.CreateDatabaseIfNotExistsAsync(databaseName);
var containerResponse = await databaseResponse.Database.Containers.CreateContainerIfNotExistsAsync(eventsContainerName, "/id");
var query = $"SELECT * FROM Events e WHERE e.id = '{id}'";
var queryDefinition = new CosmosSqlQueryDefinition(query);
var iterator = containerResponse.Container.Items.CreateItemQuery <Event>(queryDefinition, maxConcurrency: 1);
var ticketEvents = new List <Event>();
while (iterator.HasMoreResults)
{
var queryResponse = await iterator.FetchNextSetAsync();
foreach (var item in queryResponse)
{
ticketEvents.Add(item);
}
}
return(ticketEvents.FirstOrDefault());
}
}
catch (Exception ex)
{
logger?.LogError(ex.Message);
throw new Exception(ex.Message);
}
}
public async Task QuerySinglePartitionItemStreamTest(int perPKItemCount, int maxItemCount)
{
IList <ToDoActivity> deleteList = deleteList = await this.CreateRandomItems(pkCount : 3, perPKItemCount : perPKItemCount, randomPartitionKey : true);
ToDoActivity find = deleteList.First();
CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("select * from r");
int iterationCount = 0;
int totalReadItem = 0;
int expectedIterationCount = perPKItemCount / maxItemCount;
string lastContinuationToken = null;
do
{
iterationCount++;
CosmosResultSetIterator setIterator = this.Container.Items
.CreateItemQueryAsStream(sql, 1, find.status,
maxItemCount: maxItemCount,
continuationToken: lastContinuationToken,
requestOptions: new CosmosQueryRequestOptions());
using (CosmosQueryResponse response = await setIterator.FetchNextSetAsync())
{
lastContinuationToken = response.ContinuationToken;
Trace.TraceInformation($"ContinuationToken: {lastContinuationToken}");
JsonSerializer serializer = new JsonSerializer();
using (StreamReader sr = new StreamReader(response.Content))
using (JsonTextReader jtr = new JsonTextReader(sr))
{
ToDoActivity[] results = serializer.Deserialize <ToDoActivity[]>(jtr);
ToDoActivity[] readTodoActivities = results.OrderBy(e => e.id)
.ToArray();
ToDoActivity[] expectedTodoActivities = deleteList
.Where(e => e.status == find.status)
.Where(e => readTodoActivities.Any(e1 => e1.id == e.id))
.OrderBy(e => e.id)
.ToArray();
totalReadItem += expectedTodoActivities.Length;
string expectedSerialized = JsonConvert.SerializeObject(expectedTodoActivities);
string readSerialized = JsonConvert.SerializeObject(readTodoActivities);
Trace.TraceInformation($"Expected: {Environment.NewLine} {expectedSerialized}");
Trace.TraceInformation($"Read: {Environment.NewLine} {readSerialized}");
int count = results.Length;
Assert.AreEqual(maxItemCount, count);
Assert.AreEqual(expectedSerialized, readSerialized);
Assert.AreEqual(maxItemCount, expectedTodoActivities.Length);
}
}
}while (lastContinuationToken != null);
Assert.AreEqual(expectedIterationCount, iterationCount);
Assert.AreEqual(perPKItemCount, totalReadItem);
}
private static async Task <SalesOrder> QueryItems()
{
//******************************************************************************************************************
// 1.4 - Query for items by a property other than Id
//
// NOTE: Operations like AsEnumerable(), ToList(), ToArray() will make as many trips to the database
// as required to fetch the entire result-set. Even if you set MaxItemCount to a smaller number.
// MaxItemCount just controls how many results to fetch each trip.
// If you don't want to fetch the full set of results, then use CreateItemQuery().AsItemQuery()
// For more on this please refer to the Queries project.
//
// NOTE: If you want to get the RU charge for a query you also need to use CreateItemQuery().AsItemQuery()
// and check the RequestCharge property of this IQueryable response
// Once again, refer to the Queries project for more information and examples of this
//******************************************************************************************************************
Console.WriteLine("\n1.4 - Querying for a item using its AccountNumber property");
CosmosSqlQueryDefinition query = new CosmosSqlQueryDefinition(
"select * from sales s where s.AccountNumber = @AccountInput ")
.UseParameter("@AccountInput", "Account1");
CosmosResultSetIterator <SalesOrder> resultSet = container.Items.CreateItemQuery <SalesOrder>(
query, maxConcurrency: 1,
maxItemCount: 1);
while (resultSet.HasMoreResults)
{
SalesOrder sale = (await resultSet.FetchNextSetAsync()).First();
Console.WriteLine($"Account Number: {sale.AccountNumber}; Id: {sale.Id} ");
return(sale);
}
throw new ArgumentNullException("No Sale order found by query");
}
public async Task <IEnumerable <LeaderBoard> > FetchListAsync()
{
var leaderBoardContainer =
_cosmosDatabase.Containers["leaderboards"];
var query =
$"SELECT * FROM lb ORDER BY lb.name";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
var leaderBoards =
leaderBoardContainer.Items.CreateItemQuery <LeaderBoard>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var leaderBoardList = new List <LeaderBoard>();
while (leaderBoards.HasMoreResults)
{
leaderBoardList.AddRange(
await leaderBoards.FetchNextSetAsync());
}
;
return(leaderBoardList);
}
public async Task <Learner> FetchByUserNameAsync(
string userName)
{
var learnerContainer =
_cosmosDatabase.Containers["learners"];
var query =
$"SELECT * FROM l where l.userName = @userName";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
queryDefinition.UseParameter(
"@userName", userName);
var learners =
learnerContainer.Items.CreateItemQuery <Learner>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var learnerList = new List <Learner>();
while (learners.HasMoreResults)
{
learnerList.AddRange(
await learners.FetchNextSetAsync());
}
;
var learner =
learnerList.FirstOrDefault();
return(learner);
}
public async Task <ICollection <Order> > GetUserOrdersAsync(string username)
{
try
{
using (CosmosClient cosmosClient = new CosmosClient(accountEndpoint, accountKey))
{
var databaseResponse = await cosmosClient.Databases.CreateDatabaseIfNotExistsAsync(databaseName);
var containerResponse = await databaseResponse.Database.Containers.CreateContainerIfNotExistsAsync(ordersContainerName, "/EventId");
var query = $"SELECT * FROM Orders o WHERE o.UserName = '******'";
var queryDefinition = new CosmosSqlQueryDefinition(query);
var iterator = containerResponse.Container.Items.CreateItemQuery <Order>(queryDefinition, maxConcurrency: 1);
var ticketOrders = new List <Order>();
while (iterator.HasMoreResults)
{
var queryResponse = await iterator.FetchNextSetAsync();
foreach (var item in queryResponse)
{
ticketOrders.Add(item);
}
}
return(ticketOrders.OrderByDescending(o => o.OrderDate).ToList());
}
}
catch (Exception ex)
{
logger?.LogError(ex.Message);
throw new Exception(ex.Message);
}
}
public async Task <IEnumerable <Learner> > FetchListAsync(
string[] names)
{
var learnerContainer =
_cosmosDatabase.Containers["learners"];
var query =
$"SELECT * FROM l";
query += " WHERE ARRAY_CONTAINS(@names, l.userName)";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
queryDefinition.UseParameter("@names", names);
var learners =
learnerContainer.Items.CreateItemQuery <Learner>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var learnerList = new List <Learner>();
while (learners.HasMoreResults)
{
learnerList.AddRange(
await learners.FetchNextSetAsync());
}
;
return(learnerList);
}
public async Task ValidateMaxItemCountOnItemQuery()
{
IList <ToDoActivity> deleteList = await this.CreateRandomItems(6, randomPartitionKey : false);
ToDoActivity toDoActivity = deleteList.First();
CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition(
"select * from toDoActivity t where t.status = @status")
.UseParameter("@status", toDoActivity.status);
// Test max size at 1
CosmosResultSetIterator <ToDoActivity> setIterator =
this.Container.Items.CreateItemQuery <ToDoActivity>(sql, toDoActivity.status, maxItemCount: 1);
while (setIterator.HasMoreResults)
{
CosmosQueryResponse <ToDoActivity> iter = await setIterator.FetchNextSetAsync();
Assert.AreEqual(1, iter.Count());
}
// Test max size at 2
CosmosResultSetIterator <ToDoActivity> setIteratorMax2 =
this.Container.Items.CreateItemQuery <ToDoActivity>(sql, toDoActivity.status, maxItemCount: 2);
while (setIteratorMax2.HasMoreResults)
{
CosmosQueryResponse <ToDoActivity> iter = await setIteratorMax2.FetchNextSetAsync();
Assert.AreEqual(2, iter.Count());
}
}
public async Task <IEnumerable <ContestLearner> > FetchListAsync(
Guid contestId)
{
var contestLearnerContainer =
_cosmosDatabase.Containers["contestlearners"];
var query =
$"SELECT * FROM cl";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
var contestLearners =
contestLearnerContainer.Items.CreateItemQuery <ContestLearner>(
queryDefinition,
contestId.ToString());
var contestLearnerList = new List <ContestLearner>();
while (contestLearners.HasMoreResults)
{
contestLearnerList.AddRange(
await contestLearners.FetchNextSetAsync());
}
;
return(contestLearnerList);
}
public async Task <ContestLearner> FetchByUserNameAsync(
Guid contestId,
string userName)
{
var contestLearnerContainer =
_cosmosDatabase.Containers["contestlearners"];
var query =
$"SELECT * FROM cl WHERE cl.userName = @userName";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
queryDefinition.UseParameter("@userName", userName.ToLower());
var contestLearners =
contestLearnerContainer.Items.CreateItemQuery <ContestLearner>(
queryDefinition,
contestId.ToString());
var contestLearnerList = new List <ContestLearner>();
while (contestLearners.HasMoreResults)
{
contestLearnerList.AddRange(
await contestLearners.FetchNextSetAsync());
}
;
var contestLearner =
contestLearnerList.FirstOrDefault();
return(contestLearner);
}
public async Task <IEnumerable <OrderItem> > FetchListAsync(
Guid orderId)
{
var orderContainer =
_cosmosDatabase.Containers["orders"];
var query =
$"SELECT i.id, i.name, i.quantity, i.isTaxable FROM o JOIN i IN o.items";
// Since the partition is based on the orderid we do not really need to query for the orderid
// var query =
// $"SELECT i.id, i.name, i.quantity, i.isTaxable FROM o JOIN i IN o.items WHERE o.id = \"{orderId}\"";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
var orderItems =
orderContainer.Items.CreateItemQuery <OrderItem>(queryDefinition, orderId.ToString());
var orderItemList = new List <OrderItem>();
while (orderItems.HasMoreResults)
{
orderItemList.AddRange(
await orderItems.FetchNextSetAsync());
}
;
return(orderItemList);
}
public async Task <IEnumerable <Course> > FetchListAsync(
Guid[] ids)
{
var courseContainer =
_cosmosDatabase.Containers["courses"];
var query =
$"SELECT * FROM l";
query += " WHERE ARRAY_CONTAINS(@ids, l.id)";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
queryDefinition.UseParameter("@ids", ids);
var courses =
courseContainer.Items.CreateItemQuery <Course>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var courseList = new List <Course>();
while (courses.HasMoreResults)
{
courseList.AddRange(
await courses.FetchNextSetAsync());
}
;
return(courseList);
}
public async Task <IEnumerable <Car> > GetCarsByCarCategoryAsync(string carCategory)
{
var query = new CosmosSqlQueryDefinition("select * from cars c where c.carCategory = @carCategory ")
.UseParameter("@carCategory", carCategory);
return(await GetItemsAsync <Car>(query));
}
public async Task ItemMultiplePartitionQuery()
{
IList <ToDoActivity> deleteList = await this.CreateRandomItems(3, randomPartitionKey : true);
ToDoActivity find = deleteList.First();
CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("select * from toDoActivity t where t.id = '" + find.id + "'");
CosmosQueryRequestOptions requestOptions = new CosmosQueryRequestOptions()
{
MaxBufferedItemCount = 10,
ResponseContinuationTokenLimitInKb = 500
};
CosmosResultSetIterator <ToDoActivity> setIterator =
this.Container.Items.CreateItemQuery <ToDoActivity>(sql, maxConcurrency: 1, maxItemCount: 1, requestOptions: requestOptions);
while (setIterator.HasMoreResults)
{
CosmosQueryResponse <ToDoActivity> iter = await setIterator.FetchNextSetAsync();
Assert.AreEqual(1, iter.Count());
ToDoActivity response = iter.First();
Assert.AreEqual(find.id, response.id);
}
}
public async Task <IEnumerable <Learner> > FetchListByLeaderBoardIdAsync(
Guid leaderBoardId)
{
var learnerContainer =
_cosmosDatabase.Containers["learners"];
var query =
$"SELECT * FROM l";
query += $" WHERE ARRAY_CONTAINS(l.leaderBoards, {{\"id\": \"{leaderBoardId}\"}}, true)";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
var learners =
learnerContainer.Items.CreateItemQuery <Learner>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var learnerList = new List <Learner>();
while (learners.HasMoreResults)
{
learnerList.AddRange(
await learners.FetchNextSetAsync());
}
;
learnerList =
learnerList.Where(x => x.Status != LearnerStatus.Failed)
.ToList();
return(learnerList);
}
public async Task VerifyCosmosDefaultResultSetStreamIteratorOperationType()
{
CosmosClient mockClient = MockDocumentClient.CreateMockCosmosClient();
CosmosDatabase database = new CosmosDatabase(mockClient, "database");
CosmosContainer container = new CosmosContainer(database, "container");
CosmosItems item = new CosmosItems(container);
CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("select * from r");
CosmosResultSetIterator setIterator = item
.CreateItemQueryAsStream(sql, "pk", requestOptions: new CosmosQueryRequestOptions());
TestHandler testHandler = new TestHandler((request, cancellationToken) => {
Assert.AreEqual(
15, //OperationType.SqlQuery
(int)request.GetType().GetProperty("OperationType", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).GetValue(request, null)
);
return(TestHandler.ReturnSuccess());
});
mockClient.RequestHandler.InnerHandler = testHandler;
mockClient.CosmosConfiguration.UseConnectionModeDirect();
CosmosResponseMessage response = await setIterator.FetchNextSetAsync();
testHandler = new TestHandler((request, cancellationToken) => {
Assert.AreEqual(
14, //OperationType.Query
(int)request.GetType().GetProperty("OperationType", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).GetValue(request, null)
);
return(TestHandler.ReturnSuccess());
});
mockClient.RequestHandler.InnerHandler = testHandler;
mockClient.CosmosConfiguration.UseConnectionModeGateway();
response = await setIterator.FetchNextSetAsync();
}
public async Task <IEnumerable <Item> > GetItemAllAsync()
{
var query = new CosmosSqlQueryDefinition("select * from t ");
//.UseParameter("@account", "12345");
return(await GetItemsAsync <Item>(query));
}
private async Task TestOrderyByQueryAsync()
{
var jsonSerializerSettings = new JsonSerializerSettings
{
ConstructorHandling = ConstructorHandling.AllowNonPublicDefaultConstructor,
Converters =
{
new ObjectStringJsonConverter <SerializedObject>(_ => _.Name, _ => SerializedObject.Parse(_))
}
};
CosmosClient cosmosClient = TestCommon.CreateCosmosClient((cosmosClientBuilder) => cosmosClientBuilder.WithCustomJsonSerializer(new CustomJsonSerializer(jsonSerializerSettings)));
CosmosContainer container = cosmosClient.Databases[databaseName].Containers[partitionedCollectionName];
// Create a few test documents
int documentCount = 3;
var numberFieldName = "NumberField";
for (int i = 0; i < documentCount; ++i)
{
var newDocument = new MyObject(i);
var createdDocument = await container.CreateItemAsync <MyObject>(newDocument.pk, newDocument);
}
CosmosSqlQueryDefinition cosmosSqlQueryDefinition1 = new CosmosSqlQueryDefinition("SELECT * FROM root");
FeedIterator <MyObject> setIterator1 = container.CreateItemQuery <MyObject>(cosmosSqlQueryDefinition1, maxConcurrency: -1, maxItemCount: -1);
CosmosSqlQueryDefinition cosmosSqlQueryDefinition2 = new CosmosSqlQueryDefinition("SELECT * FROM root ORDER BY root[\"" + numberFieldName + "\"] DESC");
FeedIterator <MyObject> setIterator2 = container.CreateItemQuery <MyObject>(cosmosSqlQueryDefinition2, maxConcurrency: -1, maxItemCount: -1);
List <MyObject> list1 = new List <MyObject>();
List <MyObject> list2 = new List <MyObject>();
while (setIterator1.HasMoreResults)
{
foreach (MyObject obj in await setIterator1.FetchNextSetAsync())
{
list1.Add(obj);
}
}
while (setIterator2.HasMoreResults)
{
foreach (MyObject obj in await setIterator2.FetchNextSetAsync())
{
list2.Add(obj);
}
}
Assert.AreEqual(documentCount, list1.Count);
Assert.AreEqual(documentCount, list2.Count);
for (int i = 0; i < documentCount; ++i)
{
Assert.AreEqual("Name: " + (documentCount - i - 1), list2[i].SerializedObject.Name);
}
}
public async Task ItemMultiplePartitionOrderByQueryStream()
{
IList <ToDoActivity> deleteList = new List <ToDoActivity>();
try
{
deleteList = await CreateRandomItems(300, randomPartitionKey : true);
CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("SELECT * FROM toDoActivity t ORDER BY t.taskNum ");
CosmosQueryRequestOptions requestOptions = new CosmosQueryRequestOptions()
{
MaxBufferedItemCount = 10,
ResponseContinuationTokenLimitInKb = 500
};
List <ToDoActivity> resultList = new List <ToDoActivity>();
double totalRequstCharge = 0;
CosmosResultSetIterator setIterator =
this.Container.Items.CreateItemQueryAsStream(sql, maxConcurrency: 5, maxItemCount: 1, requestOptions: requestOptions);
while (setIterator.HasMoreResults)
{
using (CosmosQueryResponse iter = await setIterator.FetchNextSetAsync())
{
Assert.IsTrue(iter.IsSuccess);
Assert.IsNull(iter.ErrorMessage);
Assert.IsTrue(iter.Count <= 5);
totalRequstCharge += iter.RequestCharge;
ToDoActivity response = this.jsonSerializer.FromStream <ToDoActivity[]>(iter.Content).First();
resultList.Add(response);
}
}
Assert.AreEqual(deleteList.Count, resultList.Count);
Assert.IsTrue(totalRequstCharge > 0);
List <ToDoActivity> verifiedOrderBy = deleteList.OrderBy(x => x.taskNum).ToList();
for (int i = 0; i < verifiedOrderBy.Count(); i++)
{
Assert.AreEqual(verifiedOrderBy[i].taskNum, resultList[i].taskNum);
Assert.AreEqual(verifiedOrderBy[i].id, resultList[i].id);
}
}
finally
{
foreach (ToDoActivity delete in deleteList)
{
CosmosResponseMessage deleteResponse = await this.Container.Items.DeleteItemStreamAsync(delete.status, delete.id);
deleteResponse.Dispose();
}
}
}
public async Task <T> GetAsync(string id)
{
var query = new CosmosSqlQueryDefinition("SELECT * from c where c.id=@id").UseParameter("@id", id);
IEnumerable <T> response = await GetItemsAsync(query, maxItemCount : 1);
if (response == null || !response.Any())
{
return(default(T));
}
return(response.First());
}
/// <summary>
/// The function demonstrates migrating documents that were inserted without a value for partition key, and those inserted
/// pre-migration to other logical partitions, those with a value for partition key.
/// </summary>
private static async Task MigratedItemsFromNonePartitionKeyToValidPartitionKeyValue(CosmosContainer container)
{
// Pre-create a few items in the container to demo the migration
const int ItemsToCreate = 4;
// Insert a few items with no Partition Key
for (int i = 0; i < ItemsToCreate; i++)
{
string itemid = Guid.NewGuid().ToString();
DeviceInformationItem itemWithoutPK = GetDeviceWithNoPartitionKey(itemid);
CosmosItemResponse <DeviceInformationItem> createResponse = await container.Items.CreateItemAsync <DeviceInformationItem>(
partitionKey : CosmosContainerSettings.NonePartitionKeyValue,
item : itemWithoutPK);
}
// Query items on the container that have no partition key value by supplying NonePartitionKeyValue
// The operation is made in batches to not lose work in case of partial execution
int resultsFetched = 0;
CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("select * from r");
CosmosResultSetIterator <DeviceInformationItem> setIterator = container.Items
.CreateItemQuery <DeviceInformationItem>(sql, partitionKey: CosmosContainerSettings.NonePartitionKeyValue, maxItemCount: 2);
while (setIterator.HasMoreResults)
{
CosmosQueryResponse <DeviceInformationItem> queryResponse = await setIterator.FetchNextSetAsync();
resultsFetched += queryResponse.Count();
// For the items returned with NonePartitionKeyValue
IEnumerator <DeviceInformationItem> iter = queryResponse.GetEnumerator();
while (iter.MoveNext())
{
DeviceInformationItem item = iter.Current;
if (item.DeviceId != null)
{
// Using existing deviceID for partition key
item.PartitionKey = item.DeviceId;
Console.WriteLine("Migrating item {0} to Partition {1}", item.Id, item.DeviceId);
// Re-Insert into container with a partition key
// This could result in exception if the same item was inserted in a previous run of the program on existing container
// and the program stopped before the delete.
CosmosItemResponse <DeviceInformationItem> createResponseWithPk = await container.Items.CreateItemAsync <DeviceInformationItem>(
partitionKey : item.PartitionKey,
item : item);
// Deleting item from fixed container with CosmosContainerSettings.NonePartitionKeyValue.
CosmosItemResponse <DeviceInformationItem> deleteResponseWithoutPk = await container.Items.DeleteItemAsync <DeviceInformationItem>(
partitionKey : CosmosContainerSettings.NonePartitionKeyValue,
id : item.Id);
}
}
}
}
public async Task <IEnumerable <Learner> > FetchListAsync(
string status = null,
int?maximumRecords = null,
string orderBy = "l.displayName")
{
var learnerContainer =
_cosmosDatabase.Containers["learners"];
var query =
$"SELECT";
if (maximumRecords.HasValue)
{
query += $" TOP {maximumRecords.Value}";
}
query += " * FROM l";
if (!string.IsNullOrWhiteSpace(status))
{
query += " WHERE l.status = @status";
}
if (!string.IsNullOrWhiteSpace(orderBy))
{
query += $" ORDER BY {orderBy}";
}
var queryDefinition =
new CosmosSqlQueryDefinition(query);
if (!string.IsNullOrWhiteSpace(status))
{
queryDefinition.UseParameter("@status", status);
}
var learners =
learnerContainer.Items.CreateItemQuery <Learner>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var learnerList = new List <Learner>();
while (learners.HasMoreResults)
{
learnerList.AddRange(
await learners.FetchNextSetAsync());
}
;
return(learnerList);
}
public async Task <IEnumerable <Todo> > QueryWithUserNameAsync(string userName)
{
var query = new CosmosSqlQueryDefinition("SELECT * FROM Todo t WHERE t.UserName = @userName")
.UseParameter("@userName", userName);
var iterator = ContainerResponse.Container.Items.CreateItemQuery <Todo>(query, partitionKey: userName);
var result = new List <Todo>();
while (iterator.HasMoreResults)
{
result.AddRange((await iterator.FetchNextSetAsync()));
}
return(result);
}
public async Task ValidateUserDefinedFunctionsTest()
{
// Prevent failures if previous test did not clean up correctly
await this.scripts.DeleteUserDefinedFunctionAsync("calculateTax");
ToDoActivity item = new ToDoActivity()
{
id = Guid.NewGuid().ToString(),
cost = 9001,
description = "udf_test_item",
status = "Done",
taskNum = 1
};
await this.container.CreateItemAsync <ToDoActivity>(item.status, item);
CosmosUserDefinedFunctionSettings cosmosUserDefinedFunction = await this.scripts.CreateUserDefinedFunctionAsync(
new CosmosUserDefinedFunctionSettings
{
Id = "calculateTax",
Body = @"function(amt) { return amt * 0.05; }"
});
CosmosSqlQueryDefinition sqlQuery = new CosmosSqlQueryDefinition(
"SELECT t.id, t.status, t.cost, udf.calculateTax(t.cost) as total FROM toDoActivity t where t.cost > @expensive and t.status = @status")
.UseParameter("@expensive", 9000)
.UseParameter("@status", "Done");
FeedIterator <dynamic> feedIterator = this.container.CreateItemQuery <dynamic>(
sqlQueryDefinition: sqlQuery,
partitionKey: "Done");
HashSet <string> iterIds = new HashSet <string>();
while (feedIterator.HasMoreResults)
{
foreach (var response in await feedIterator.FetchNextSetAsync())
{
Assert.IsTrue(response.cost > 9000);
Assert.AreEqual(response.cost * .05, response.total);
iterIds.Add(response.id.Value);
}
}
Assert.IsTrue(iterIds.Count > 0);
Assert.IsTrue(iterIds.Contains(item.id));
// Delete existing user defined functions.
await this.scripts.DeleteUserDefinedFunctionAsync(cosmosUserDefinedFunction.Id);
}
public async Task ItemQueryStreamSerializationSetting()
{
IList <ToDoActivity> deleteList = await this.CreateRandomItems(101, randomPartitionKey : true);
CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("SELECT * FROM toDoActivity t ORDER BY t.taskNum");
CosmosSerializationOptions options = new CosmosSerializationOptions(
ContentSerializationFormat.CosmosBinary.ToString(),
(content) => JsonNavigator.Create(content),
() => JsonWriter.Create(JsonSerializationFormat.Binary));
CosmosQueryRequestOptions requestOptions = new CosmosQueryRequestOptions()
{
CosmosSerializationOptions = options
};
List <ToDoActivity> resultList = new List <ToDoActivity>();
double totalRequstCharge = 0;
CosmosResultSetIterator setIterator =
this.Container.Items.CreateItemQueryAsStream(sql, maxConcurrency: 5, maxItemCount: 5, requestOptions: requestOptions);
while (setIterator.HasMoreResults)
{
using (CosmosQueryResponse iter = await setIterator.FetchNextSetAsync())
{
Assert.IsTrue(iter.IsSuccess);
Assert.IsNull(iter.ErrorMessage);
Assert.IsTrue(iter.Count <= 5);
totalRequstCharge += iter.RequestCharge;
IJsonReader reader = JsonReader.Create(iter.Content);
IJsonWriter textWriter = JsonWriter.Create(JsonSerializationFormat.Text);
textWriter.WriteAll(reader);
string json = Encoding.UTF8.GetString(textWriter.GetResult());
Assert.IsNotNull(json);
ToDoActivity[] responseActivities = JsonConvert.DeserializeObject <ToDoActivity[]>(json);
resultList.AddRange(responseActivities);
}
}
Assert.AreEqual(deleteList.Count, resultList.Count);
Assert.IsTrue(totalRequstCharge > 0);
List <ToDoActivity> verifiedOrderBy = deleteList.OrderBy(x => x.taskNum).ToList();
for (int i = 0; i < verifiedOrderBy.Count(); i++)
{
Assert.AreEqual(verifiedOrderBy[i].taskNum, resultList[i].taskNum);
Assert.AreEqual(verifiedOrderBy[i].id, resultList[i].id);
}
}
public async Task <List <TicketOrderStats> > GetOrderStats()
{
var error = string.Empty;
var handler = HandlersFactory.GetProfilerHandler(settingService, logger);
handler.Start(LOG_TAG, "GetOrderStats", null);
try
{
using (CosmosClient cosmosClient = new CosmosClient(endpointUri, primaryKey))
{
var databaseResponse = await cosmosClient.Databases.CreateDatabaseIfNotExistsAsync(databaseName);
var containerResponse = await databaseResponse.Database.Containers.CreateContainerIfNotExistsAsync(ordersContainerName, "/EventId");
var query = $"SELECT * FROM Orders o WHERE IS_NULL(o.FulfillDate)=false";
var queryDefinition = new CosmosSqlQueryDefinition(query);
var iterator = containerResponse.Container.Items.CreateItemQuery <TicketOrder>(queryDefinition, maxConcurrency: 1);
var ticketOrders = new List <TicketOrder>();
while (iterator.HasMoreResults)
{
var queryResponse = await iterator.FetchNextSetAsync();
foreach (var item in queryResponse)
{
ticketOrders.Add(item);
}
}
return(ticketOrders.GroupBy(o => new { ID = o.Tag }).Select(g => new TicketOrderStats {
Tag = g.Key.ID, Count = g.Count(), SumSeconds = g.Sum(x => ((DateTime)x.FulfillDate - x.OrderDate).TotalSeconds), AverageSeconds = g.Average(x => ((DateTime)x.FulfillDate - x.OrderDate).TotalSeconds)
}).ToList());
}
}
catch (Exception ex)
{
error = ex.Message;
throw new Exception(error);
}
finally
{
handler.Stop(error);
}
}
// Return List of Feedback from Cosmos DB
private async Task <List <Feedback> > GetCosmosItems()
{
// Read a single query page from Azure cosmos DB as stream
var myQueryDef = new CosmosSqlQueryDefinition($"Select * from f where f.Complete != true");
var myQuery = _cosmos.Items.CreateItemQuery <Feedback>(myQueryDef, maxConcurrency: 4);
List <Feedback> myData = new List <Feedback>();
while (myQuery.HasMoreResults)
{
var set = await myQuery.FetchNextSetAsync();
myData.AddRange(set);
}
return(myData);
}
