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);
}
}
/// <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);
ItemResponse <DeviceInformationItem> createResponse = await container.CreateItemAsync <DeviceInformationItem>(
partitionKey : PartitionKey.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");
FeedIterator <DeviceInformationItem> setIterator = container.CreateItemQuery <DeviceInformationItem>(sql, partitionKey: PartitionKey.NonePartitionKeyValue, maxItemCount: 2);
while (setIterator.HasMoreResults)
{
FeedResponse <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.
ItemResponse <DeviceInformationItem> createResponseWithPk = await container.CreateItemAsync <DeviceInformationItem>(
partitionKey : new PartitionKey(item.PartitionKey),
item : item);
// Deleting item from fixed container with CosmosContainerSettings.NonePartitionKeyValue.
ItemResponse <DeviceInformationItem> deleteResponseWithoutPk = await container.DeleteItemAsync <DeviceInformationItem>(
partitionKey : PartitionKey.NonePartitionKeyValue,
id : item.Id);
}
}
}
}
private static async Task QueryWithSqlParameters(CosmosContainer container)
{
// Query using two properties within each item. WHERE Id == "" AND Address.City == ""
// notice here how we are doing an equality comparison on the string value of City
CosmosSqlQueryDefinition query = new CosmosSqlQueryDefinition("SELECT * FROM Families f WHERE f.id = @id AND f.Address.City = @city")
.UseParameter("@id", "AndersonFamily")
.UseParameter("@city", "Seattle");
List <Family> results = new List <Family>();
FeedIterator <Family> resultSetIterator = container.CreateItemQuery <Family>(query, partitionKey: new PartitionKey("Anderson"));
while (resultSetIterator.HasMoreResults)
{
results.AddRange((await resultSetIterator.FetchNextSetAsync()));
}
Assert("Expected only 1 family", results.Count == 1);
}
private static async Task QueryPartitionedContainerInParallelAsync(CosmosContainer container)
{
List <Family> familiesSerial = new List <Family>();
string queryText = "SELECT * FROM Families";
// 0 maximum parallel tasks, effectively serial execution
QueryRequestOptions options = new QueryRequestOptions()
{
MaxBufferedItemCount = 100
};
FeedIterator <Family> query = container.CreateItemQuery <Family>(
queryText,
maxConcurrency: 0,
requestOptions: options);
while (query.HasMoreResults)
{
foreach (Family family in await query.FetchNextSetAsync())
{
familiesSerial.Add(family);
}
}
Assert("Parallel Query expected two families", familiesSerial.ToList().Count == 2);
// 1 maximum parallel tasks, 1 dedicated asynchronous task to continuously make REST calls
List <Family> familiesParallel1 = new List <Family>();
query = container.CreateItemQuery <Family>(
queryText,
maxConcurrency: 1,
requestOptions: options);
while (query.HasMoreResults)
{
foreach (Family family in await query.FetchNextSetAsync())
{
familiesParallel1.Add(family);
}
}
Assert("Parallel Query expected two families", familiesParallel1.ToList().Count == 2);
AssertSequenceEqual("Parallel query returns result out of order compared to serial execution", familiesSerial, familiesParallel1);
// 10 maximum parallel tasks, a maximum of 10 dedicated asynchronous tasks to continuously make REST calls
List <Family> familiesParallel10 = new List <Family>();
query = container.CreateItemQuery <Family>(
queryText,
maxConcurrency: 10,
requestOptions: options);
while (query.HasMoreResults)
{
foreach (Family family in await query.FetchNextSetAsync())
{
familiesParallel10.Add(family);
}
}
Assert("Parallel Query expected two families", familiesParallel10.ToList().Count == 2);
AssertSequenceEqual("Parallel query returns result out of order compared to serial execution", familiesSerial, familiesParallel10);
}
private static async Task 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.
//******************************************************************************************************************
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");
FeedIterator <SalesOrder> resultSet = container.CreateItemQuery <SalesOrder>(
query,
partitionKey: new PartitionKey("Account1"),
maxItemCount: 1);
List <SalesOrder> allSalesForAccount1 = new List <SalesOrder>();
while (resultSet.HasMoreResults)
{
SalesOrder sale = (await resultSet.FetchNextSetAsync()).First();
Console.WriteLine($"\n1.4.1 Account Number: {sale.AccountNumber}; Id: {sale.Id} ");
allSalesForAccount1.Add(sale);
}
Console.WriteLine($"\n1.4.2 Query found {allSalesForAccount1.Count} items.");
// Use the same query as before but get the cosmos response message to access the stream directly
FeedIterator streamResultSet = container.CreateItemQueryStream(
query,
maxConcurrency: 1,
partitionKey: new PartitionKey("Account1"),
maxItemCount: 10);
List <SalesOrder> allSalesForAccount1FromStream = new List <SalesOrder>();
while (streamResultSet.HasMoreResults)
{
using (CosmosResponseMessage responseMessage = await streamResultSet.FetchNextSetAsync())
{
// Item stream operations do not throw exceptions for better performance
if (responseMessage.IsSuccessStatusCode)
{
dynamic streamResponse = FromStream <dynamic>(responseMessage.Content);
List <SalesOrder> salesOrders = streamResponse.Documents.ToObject <List <SalesOrder> >();
Console.WriteLine($"\n1.4.3 - Item Query via stream {salesOrders.Count}");
allSalesForAccount1FromStream.AddRange(salesOrders);
}
else
{
Console.WriteLine($"Query item from stream failed. Status code: {responseMessage.StatusCode} Message: {responseMessage.ErrorMessage}");
}
}
}
Console.WriteLine($"\n1.4.4 Query found {allSalesForAccount1FromStream.Count} items.");
if (allSalesForAccount1.Count != allSalesForAccount1FromStream.Count)
{
throw new InvalidDataException($"Both query operations should return the same list");
}
}
public async Task TestJsonSerializerSettings(bool useGateway)
{
CosmosClient cosmosClient = TestCommon.CreateCosmosClient((cosmosClientBuilder) => {
if (useGateway)
{
cosmosClientBuilder.WithCustomJsonSerializer(new CustomJsonSerializer(CustomSerializationTests.GetSerializerWithCustomConverterAndBinder())).WithConnectionModeGateway();
}
else
{
cosmosClientBuilder.WithCustomJsonSerializer(new CustomJsonSerializer(CustomSerializationTests.GetSerializerWithCustomConverterAndBinder())).WithConnectionModeDirect();
}
});
CosmosContainer container = cosmosClient.Databases[databaseName].Containers[partitionedCollectionName];
var rnd = new Random();
var bytes = new byte[100];
rnd.NextBytes(bytes);
var testDocument = new TestDocument(new KerberosTicketHashKey(bytes));
//create and read
ItemResponse <TestDocument> createResponse = await container.CreateItemAsync <TestDocument>(testDocument.Name, testDocument);
ItemResponse <TestDocument> readResponse = await container.ReadItemAsync <TestDocument>(testDocument.Name, testDocument.Id);
AssertEqual(testDocument, readResponse.Resource);
AssertEqual(testDocument, createResponse.Resource);
// upsert
ItemResponse <TestDocument> upsertResponse = await container.UpsertItemAsync <TestDocument>(testDocument.Name, testDocument);
readResponse = await container.ReadItemAsync <TestDocument>(testDocument.Name, testDocument.Id);
AssertEqual(testDocument, readResponse.Resource);
AssertEqual(testDocument, upsertResponse.Resource);
// replace
ItemResponse <TestDocument> replacedResponse = await container.ReplaceItemAsync <TestDocument>(testDocument.Name, testDocument.Id, testDocument);
readResponse = await container.ReadItemAsync <TestDocument>(testDocument.Name, testDocument.Id);
AssertEqual(testDocument, readResponse.Resource);
AssertEqual(testDocument, replacedResponse.Resource);
CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("select * from r");
FeedIterator <TestDocument> feedIterator =
container.CreateItemQuery <TestDocument>(sqlQueryDefinition: sql, partitionKey: testDocument.Name, maxItemCount: 1);
FeedResponse <TestDocument> queryResponse = await feedIterator.FetchNextSetAsync();
AssertEqual(testDocument, queryResponse.First());
//Will add LINQ test once it is available with new V3 OM
// // LINQ Lambda
// var query1 = client.CreateDocumentQuery<TestDocument>(partitionedCollectionUri, options)
// .Where(_ => _.Id.CompareTo(String.Empty) > 0)
// .Select(_ => _.Id);
// string query1Str = query1.ToString();
// var result = query1.ToList();
// Assert.AreEqual(1, result.Count);
// Assert.AreEqual(testDocument.Id, result[0]);
// // LINQ Query
// var query2 =
// from f in client.CreateDocumentQuery<TestDocument>(partitionedCollectionUri, options)
// where f.Id.CompareTo(String.Empty) > 0
// select f.Id;
// string query2Str = query2.ToString();
// var result2 = query2.ToList();
// Assert.AreEqual(1, result2.Count);
// Assert.AreEqual(testDocument.Id, result2[0]);
}