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