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 IteratorTest() { string sprocBody = "function() { { var x = 42; } }"; int numberOfSprocs = 3; string[] sprocIds = new string[numberOfSprocs]; for (int i = 0; i < numberOfSprocs; i++) { string sprocId = Guid.NewGuid().ToString(); sprocIds[i] = sprocId; CosmosStoredProcedureResponse storedProcedureResponse = await this.container.StoredProcedures.CreateStoredProceducreAsync(sprocId, sprocBody); Assert.AreEqual(HttpStatusCode.Created, storedProcedureResponse.StatusCode); } List <string> readSprocIds = new List <string>(); CosmosResultSetIterator <CosmosStoredProcedureSettings> iter = this.container.StoredProcedures.GetStoredProcedureIterator(); while (iter.HasMoreResults) { CosmosQueryResponse <CosmosStoredProcedureSettings> currentResultSet = await iter.FetchNextSetAsync(); { foreach (CosmosStoredProcedureSettings storedProcedureSettingsEntry in currentResultSet) { readSprocIds.Add(storedProcedureSettingsEntry.Id); } } } CollectionAssert.AreEquivalent(sprocIds, readSprocIds); }
private static async Task QueryItemsInPartitionAsStreams(CosmosContainer container) { // SQL CosmosResultSetIterator setIterator = container.Items.CreateItemQueryAsStream( "SELECT F.id, F.LastName, F.IsRegistered FROM Families F", partitionKey: "Anderson", maxConcurrency: 1, maxItemCount: 1); int count = 0; while (setIterator.HasMoreResults) { using (CosmosQueryResponse response = await setIterator.FetchNextSetAsync()) { Assert("Response failed", response.IsSuccess); count++; using (StreamReader sr = new StreamReader(response.Content)) using (JsonTextReader jtr = new JsonTextReader(sr)) { JsonSerializer jsonSerializer = new JsonSerializer(); dynamic items = jsonSerializer.Deserialize <dynamic>(jtr); Assert("Expected one family", items.Count == 1); dynamic item = items[0]; Assert($"Expected LastName: Anderson Actual: {item.LastName}", string.Equals("Anderson", item.LastName.ToString(), StringComparison.InvariantCulture)); } } } Assert("Expected 1 family", count == 1); }
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); } }
private static CosmosContainer GetMockedContainer(string containerName = "myColl") { Mock <CosmosResultSetIterator <DocumentServiceLeaseCore> > mockedQuery = new Mock <CosmosResultSetIterator <DocumentServiceLeaseCore> >(); mockedQuery.Setup(q => q.FetchNextSetAsync(It.IsAny <CancellationToken>())) .ReturnsAsync(() => CosmosQueryResponse <DocumentServiceLeaseCore> .CreateResponse(DocumentServiceLeaseContainerCosmosTests.allLeases, string.Empty, false)); mockedQuery.SetupSequence(q => q.HasMoreResults) .Returns(true) .Returns(false); Mock <CosmosItems> mockedItems = new Mock <CosmosItems>(); mockedItems.Setup(i => i.CreateItemQuery <DocumentServiceLeaseCore>( // To make sure the SQL Query gets correctly created It.Is <string>(value => ("SELECT * FROM c WHERE STARTSWITH(c.id, '" + DocumentServiceLeaseContainerCosmosTests.leaseStoreManagerSettings.GetPartitionLeasePrefix() + "')").Equals(value)), It.IsAny <int>(), It.IsAny <int?>(), It.IsAny <string>(), It.IsAny <CosmosQueryRequestOptions>())) .Returns(() => { return(mockedQuery.Object); }); Mock <CosmosContainer> mockedContainer = new Mock <CosmosContainer>(); //mockedContainer.Setup(c => c.LinkUri).Returns(new Uri("/dbs/myDb/colls/" + containerName, UriKind.Relative)); //mockedContainer.Setup(c => c.Client).Returns(DocumentServiceLeaseContainerCosmosTests.GetMockedClient()); mockedContainer.Setup(c => c.Items).Returns(mockedItems.Object); return(mockedContainer.Object); }
private async Task <CosmosQueryResponse> ExecuteNextQueryStreamPrivateAsync(CancellationToken cancellationToken) { if (this.queryExecutionContext == null) { this.queryExecutionContext = await this.CreateDocumentQueryExecutionContextAsync(true, cancellationToken); } else if (this.queryExecutionContext.IsDone) { this.queryExecutionContext.Dispose(); this.queryExecutionContext = await this.CreateDocumentQueryExecutionContextAsync(true, cancellationToken); } FeedResponse <CosmosElement> response = await this.queryExecutionContext.ExecuteNextAsync(cancellationToken); CosmosQueryResponse typedFeedResponse = FeedResponseBinder.ConvertToCosmosQueryResponse( response, this.feedOptions.CosmosSerializationOptions); if (!this.HasMoreResults && !tracedLastExecution) { DefaultTrace.TraceInformation( string.Format( CultureInfo.InvariantCulture, "{0}, CorrelatedActivityId: {1} | Last ExecuteNextAsync with ExecuteNextAsyncMetrics: [{2}]", DateTime.UtcNow.ToString("o", CultureInfo.InvariantCulture), this.CorrelatedActivityId, this.executeNextAysncMetrics)); tracedLastExecution = true; } return(typedFeedResponse); }
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); }
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(); } } }
/// <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 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); } }
/// <summary> /// Get Items by SqlQuery /// </summary> /// <typeparam name="TItem"></typeparam> /// <param name="query"></param> /// <param name="maxConcurrency"></param> /// <param name="maxItemCount"></param> /// <returns></returns> protected virtual async Task <IEnumerable <TItem> > GetItemsAsync <TItem>(CosmosSqlQueryDefinition query, int maxConcurrency = 1, int maxItemCount = 10) { // TODO: method argument's default value's definition....move to appsettings? var set = _context.Containers[ContainerId].Items.CreateItemQuery <TItem>(query, maxConcurrency, maxItemCount); var items = new List <TItem>(); while (set.HasMoreResults) { CosmosQueryResponse <TItem> response = await set.FetchNextSetAsync(); items.AddRange(response); } // TODO: logging total Request Charge.There is this issue: https://github.com/Azure/azure-cosmos-dotnet-v3/issues/21 // 現状だと、responseの中の Non-public Fieldにいる。。。 return(items); }
public async Task VerifyCosmosDefaultResultSetStreamIteratorOperationType() { CosmosClient mockClient = MockCosmosUtil.CreateMockCosmosClient( (cosmosClientBuilder) => cosmosClientBuilder.UseConnectionModeDirect()); CosmosContainer container = mockClient.Databases["database"].Containers["container"]; CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("select * from r"); CosmosResultSetIterator setIterator = container.Items.CreateItemQueryAsStream( sqlQueryDefinition: sql, maxConcurrency: 1, partitionKey: "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; CosmosQueryResponse response = await setIterator.FetchNextSetAsync(); //Test gateway mode mockClient = MockCosmosUtil.CreateMockCosmosClient( (cosmosClientBuilder) => cosmosClientBuilder.UseConnectionModeGateway()); container = mockClient.Databases["database"].Containers["container"]; setIterator = container.Items.CreateItemQueryAsStream( sqlQueryDefinition: sql, maxConcurrency: 1, partitionKey: "pk", requestOptions: new CosmosQueryRequestOptions()); 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; response = await setIterator.FetchNextSetAsync(); }
internal static CosmosQueryResponse <TInput> CreateCosmosQueryFeedResponse <TInput>( CosmosResponseMessage cosmosResponseMessage, CosmosJsonSerializer jsonSerializer) { using (cosmosResponseMessage) { // Throw the exception if the query failed. cosmosResponseMessage.EnsureSuccessStatusCode(); string continuationToken = ChangeFeedResultSetStreamIterator.GetContinuationToken(cosmosResponseMessage); bool hasMoreResults = ChangeFeedResultSetStreamIterator.GetHasMoreResults(continuationToken, cosmosResponseMessage.Headers.ContentLengthAsLong); return(CosmosQueryResponse <TInput> .CreateResponse <TInput>( stream : cosmosResponseMessage.Content, jsonSerializer : jsonSerializer, continuationToken : continuationToken, hasMoreResults : hasMoreResults)); } }
public async Task <IList <Ad> > GetAdsAsync() { List <Ad> ads = new List <Ad>(); try { var response = await cache.StringGetAsync("1").ConfigureAwait(false); if (String.IsNullOrEmpty(response)) { var sqlQueryText = "SELECT * FROM c WHERE c.MessageType = 'AD'"; var partitionKeyValue = "AD"; // Message type CosmosSqlQueryDefinition queryDefinition = new CosmosSqlQueryDefinition(sqlQueryText); CosmosResultSetIterator <Ad> queryResultSetIterator = container.Items.CreateItemQuery <Ad>(queryDefinition, partitionKeyValue); while (queryResultSetIterator.HasMoreResults) { CosmosQueryResponse <Ad> currentResultSet = await queryResultSetIterator.FetchNextSetAsync(); ads.AddRange(currentResultSet); } await cache.StringSetAsync("1", JsonConvert.SerializeObject(ads.First()), TimeSpan.FromMinutes(10)); } else { ads.Add(JsonConvert.DeserializeObject <Ad>(response)); } } catch (Exception ex) when(ex is RedisConnectionException || ex is RedisException || ex is RedisCommandException || ex is RedisServerException || ex is RedisTimeoutException || ex is CosmosException || ex is TimeoutException) { throw new AdRepositoryException("Repository Connection Exception", ex); } return(ads); }
public async Task DatabaseIterator() { List <CosmosDatabase> deleteList = new List <CosmosDatabase>(); HashSet <string> databaseIds = new HashSet <string>(); try { for (int i = 0; i < 3; i++) { CosmosDatabaseResponse createResponse = await this.CreateDatabaseHelper(); deleteList.Add(createResponse.Database); databaseIds.Add(createResponse.Resource.Id); } CosmosResultSetIterator <CosmosDatabaseSettings> setIterator = this.cosmosClient.Databases.GetDatabaseIterator(); while (setIterator.HasMoreResults) { CosmosQueryResponse <CosmosDatabaseSettings> iterator = await setIterator.FetchNextSetAsync(this.cancellationToken); foreach (CosmosDatabaseSettings databaseSettings in iterator) { if (databaseIds.Contains(databaseSettings.Id)) { databaseIds.Remove(databaseSettings.Id); } } } } finally { foreach (CosmosDatabase database in deleteList) { await database.DeleteAsync(cancellationToken : this.cancellationToken); } } Assert.AreEqual(0, databaseIds.Count); }
public async Task ValidateMaxItemCountOnItemQuery() { IList <ToDoActivity> deleteList = new List <ToDoActivity>(); HashSet <string> itemIds = new HashSet <string>(); try { deleteList = await CreateRandomItems(6, randomPartitionKey : false); CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("select * from toDoActivity t where t.taskNum = @task").UseParameter("@task", deleteList.First().taskNum); // Test max size at 1 CosmosResultSetIterator <ToDoActivity> setIterator = this.Container.Items.CreateItemQuery <ToDoActivity>(sql, "TBD", 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, "TBD", maxItemCount: 2); while (setIteratorMax2.HasMoreResults) { CosmosQueryResponse <ToDoActivity> iter = await setIteratorMax2.FetchNextSetAsync(); Assert.AreEqual(2, iter.Count()); } } finally { foreach (ToDoActivity delete in deleteList) { CosmosResponseMessage deleteResponse = await this.Container.Items.DeleteItemStreamAsync(delete.status, delete.id); deleteResponse.Dispose(); } } }
/* * Run a query (using Azure Cosmos DB SQL syntax) against the container */ private async Task RunQuery() { var sqlQueryText = "SELECT * FROM c WHERE c.LastName = 'Andersen'"; var partitionKeyValue = "Andersen"; Console.WriteLine("Running query: {0}\n", sqlQueryText); CosmosSqlQueryDefinition queryDefinition = new CosmosSqlQueryDefinition(sqlQueryText); CosmosResultSetIterator <Family> queryResultSetIterator = this.container.Items.CreateItemQuery <Family>(queryDefinition, partitionKeyValue); List <Family> families = new List <Family>(); while (queryResultSetIterator.HasMoreResults) { CosmosQueryResponse <Family> currentResultSet = await queryResultSetIterator.FetchNextSetAsync(); foreach (Family family in currentResultSet) { families.Add(family); Console.WriteLine("\tRead {0}\n", family); } } }
public async Task <GenresModel> GetGenre(string id) { var sqlQueryText = ("SELECT * FROM c WHERE c._id = {0}", id).ToString(); var partitionKeyValue = id; CosmosSqlQueryDefinition queryDefinition = new CosmosSqlQueryDefinition(sqlQueryText); CosmosResultSetIterator <GenresModel> queryResultSetIterator = this.genreContainer.Items.CreateItemQuery <GenresModel>(queryDefinition, partitionKeyValue); List <GenresModel> genres = new List <GenresModel>(); while (queryResultSetIterator.HasMoreResults) { CosmosQueryResponse <GenresModel> currentResultSet = await queryResultSetIterator.FetchNextSetAsync(); foreach (GenresModel genre in currentResultSet) { genres.Add(genre); } } return(genres.FirstOrDefault()); }
public async Task <MoviesModel> GetMovie(string id) { var sqlQueryText = "SELECT * FROM c WHERE c._id = '" + id + "'"; var partitionKeyValue = id; CosmosSqlQueryDefinition queryDefinition = new CosmosSqlQueryDefinition(sqlQueryText); CosmosResultSetIterator <MoviesModel> queryResultSetIterator = this.movieContainer.Items.CreateItemQuery <MoviesModel>(queryDefinition, partitionKeyValue); List <MoviesModel> movies = new List <MoviesModel>(); while (queryResultSetIterator.HasMoreResults) { CosmosQueryResponse <MoviesModel> currentResultSet = await queryResultSetIterator.FetchNextSetAsync(); foreach (MoviesModel movie in currentResultSet) { movies.Add(movie); } } return(movies.FirstOrDefault()); }
public async Task ItemMultiplePartitionQueryStream() { IList <ToDoActivity> deleteList = await this.CreateRandomItems(101, randomPartitionKey : true); CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("SELECT * FROM toDoActivity t"); List <ToDoActivity> resultList = new List <ToDoActivity>(); double totalRequstCharge = 0; CosmosResultSetIterator setIterator = this.Container.Items.CreateItemQueryAsStream(sql, maxConcurrency: 5, maxItemCount: 5); 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); resultList.AddRange(response); } } Assert.AreEqual(deleteList.Count, resultList.Count); Assert.IsTrue(totalRequstCharge > 0); List <ToDoActivity> verifiedOrderBy = deleteList.OrderBy(x => x.taskNum).ToList(); resultList = resultList.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); } }
[Ignore] //Temporary ignore till we fix emulator issue public async Task ReadNonPartitionItemAsync() { try { await this.CreateNonPartitionContainerItem(); await this.CreateUndefinedPartitionItem(); fixedContainer = this.database.Containers[nonPartitionContainerId]; CosmosContainerResponse containerResponse = await fixedContainer.ReadAsync(); Assert.IsTrue(containerResponse.Resource.PartitionKey.Paths.Count > 0); Assert.AreEqual(PartitionKey.SystemKeyPath, containerResponse.Resource.PartitionKey.Paths[0]); //Reading item from fixed container with CosmosContainerSettings.NonePartitionKeyValue. CosmosItemResponse <ToDoActivity> response = await fixedContainer.Items.ReadItemAsync <ToDoActivity>( partitionKey : CosmosContainerSettings.NonePartitionKeyValue, id : nonPartitionItemId); Assert.IsNotNull(response.Resource); Assert.AreEqual(HttpStatusCode.OK, response.StatusCode); Assert.AreEqual(nonPartitionItemId, response.Resource.id); //Adding item to fixed container with CosmosContainerSettings.NonePartitionKeyValue. ToDoActivity itemWithoutPK = CreateRandomToDoActivity(); CosmosItemResponse <ToDoActivity> createResponseWithoutPk = await fixedContainer.Items.CreateItemAsync <ToDoActivity>( partitionKey : CosmosContainerSettings.NonePartitionKeyValue, item : itemWithoutPK); Assert.IsNotNull(createResponseWithoutPk.Resource); Assert.AreEqual(HttpStatusCode.Created, createResponseWithoutPk.StatusCode); Assert.AreEqual(itemWithoutPK.id, createResponseWithoutPk.Resource.id); //Updating item on fixed container with CosmosContainerSettings.NonePartitionKeyValue. itemWithoutPK.status = "updatedStatus"; CosmosItemResponse <ToDoActivity> updateResponseWithoutPk = await fixedContainer.Items.ReplaceItemAsync <ToDoActivity>( partitionKey : CosmosContainerSettings.NonePartitionKeyValue, id : itemWithoutPK.id, item : itemWithoutPK); Assert.IsNotNull(updateResponseWithoutPk.Resource); Assert.AreEqual(HttpStatusCode.OK, updateResponseWithoutPk.StatusCode); Assert.AreEqual(itemWithoutPK.id, updateResponseWithoutPk.Resource.id); //Adding item to fixed container with non-none PK. ToDoActivityAfterMigration itemWithPK = CreateRandomToDoActivityAfterMigration("TestPk"); CosmosItemResponse <ToDoActivityAfterMigration> createResponseWithPk = await fixedContainer.Items.CreateItemAsync <ToDoActivityAfterMigration>( partitionKey : itemWithPK.status, item : itemWithPK); Assert.IsNotNull(createResponseWithPk.Resource); Assert.AreEqual(HttpStatusCode.Created, createResponseWithPk.StatusCode); Assert.AreEqual(itemWithPK.id, createResponseWithPk.Resource.id); //Quering items on fixed container with cross partition enabled. CosmosSqlQueryDefinition sql = new CosmosSqlQueryDefinition("select * from r"); CosmosResultSetIterator <dynamic> setIterator = fixedContainer.Items .CreateItemQuery <dynamic>(sql, maxConcurrency: 1, maxItemCount: 10, requestOptions: new CosmosQueryRequestOptions { EnableCrossPartitionQuery = true }); while (setIterator.HasMoreResults) { CosmosQueryResponse <dynamic> queryResponse = await setIterator.FetchNextSetAsync(); Assert.AreEqual(3, queryResponse.Count()); } //Reading all items on fixed container. setIterator = fixedContainer.Items .GetItemIterator <dynamic>(maxItemCount: 10); while (setIterator.HasMoreResults) { CosmosQueryResponse <dynamic> queryResponse = await setIterator.FetchNextSetAsync(); Assert.AreEqual(3, queryResponse.Count()); } //Quering items on fixed container with CosmosContainerSettings.NonePartitionKeyValue. setIterator = fixedContainer.Items .CreateItemQuery <dynamic>(sql, partitionKey: CosmosContainerSettings.NonePartitionKeyValue, maxItemCount: 10); while (setIterator.HasMoreResults) { CosmosQueryResponse <dynamic> queryResponse = await setIterator.FetchNextSetAsync(); Assert.AreEqual(2, queryResponse.Count()); } //Quering items on fixed container with non-none PK. setIterator = fixedContainer.Items .CreateItemQuery <dynamic>(sql, partitionKey: itemWithPK.status, maxItemCount: 10); while (setIterator.HasMoreResults) { CosmosQueryResponse <dynamic> queryResponse = await setIterator.FetchNextSetAsync(); Assert.AreEqual(1, queryResponse.Count()); } //Deleting item from fixed container with CosmosContainerSettings.NonePartitionKeyValue. CosmosItemResponse <ToDoActivity> deleteResponseWithoutPk = await fixedContainer.Items.DeleteItemAsync <ToDoActivity>( partitionKey : CosmosContainerSettings.NonePartitionKeyValue, id : itemWithoutPK.id); Assert.IsNull(deleteResponseWithoutPk.Resource); Assert.AreEqual(HttpStatusCode.NoContent, deleteResponseWithoutPk.StatusCode); //Deleting item from fixed container with non-none PK. CosmosItemResponse <ToDoActivityAfterMigration> deleteResponseWithPk = await fixedContainer.Items.DeleteItemAsync <ToDoActivityAfterMigration>( partitionKey : itemWithPK.status, id : itemWithPK.id); Assert.IsNull(deleteResponseWithPk.Resource); Assert.AreEqual(HttpStatusCode.NoContent, deleteResponseWithPk.StatusCode); //Reading item from partitioned container with CosmosContainerSettings.NonePartitionKeyValue. CosmosItemResponse <ToDoActivity> undefinedItemResponse = await Container.Items.ReadItemAsync <ToDoActivity>( partitionKey : CosmosContainerSettings.NonePartitionKeyValue, id : undefinedPartitionItemId); Assert.IsNotNull(undefinedItemResponse.Resource); Assert.AreEqual(HttpStatusCode.OK, undefinedItemResponse.StatusCode); Assert.AreEqual(undefinedPartitionItemId, undefinedItemResponse.Resource.id); } finally { if (fixedContainer != null) { await fixedContainer.DeleteAsync(); } } }
/// <summary> /// Import many documents using stored procedure. /// </summary> private static async Task RunBulkImport(CosmosContainer container) { string inputDirectory = @".\Data\"; string inputFileMask = "*.json"; int maxFiles = 2000; int maxScriptSize = 50000; // 1. Get the files. string[] fileNames = Directory.GetFiles(inputDirectory, inputFileMask); DirectoryInfo di = new DirectoryInfo(inputDirectory); FileInfo[] fileInfos = di.GetFiles(inputFileMask); // 2. Prepare for import. int currentCount = 0; int fileCount = maxFiles != 0 ? Math.Min(maxFiles, fileNames.Length) : fileNames.Length; // 3. Create stored procedure for this script. string scriptId = "BulkImport"; string body = File.ReadAllText(@".\JS\BulkImport.js"); await TryDeleteStoredProcedure(container, scriptId); CosmosStoredProcedure sproc = await container.StoredProcedures.CreateStoredProceducreAsync(scriptId, body); // 4. Create a batch of docs (MAX is limited by request size (2M) and to script for execution. // We send batches of documents to create to script. // Each batch size is determined by MaxScriptSize. // MaxScriptSize should be so that: // -- it fits into one request (MAX request size is 16Kb). // -- it doesn't cause the script to time out. // -- it is possible to experiment with MaxScriptSize to get best performance given number of throttles, etc. while (currentCount < fileCount) { // 5. Create args for current batch. // Note that we could send a string with serialized JSON and JSON.parse it on the script side, // but that would cause script to run longer. Since script has timeout, unload the script as much // as we can and do the parsing by client and framework. The script will get JavaScript objects. string argsJson = CreateBulkInsertScriptArguments(fileNames, currentCount, fileCount, maxScriptSize); var args = new dynamic[] { JsonConvert.DeserializeObject <dynamic>(argsJson) }; // 6. execute the batch. CosmosItemResponse <int> scriptResult = await container.StoredProcedures[scriptId].ExecuteAsync <dynamic, int>("Andersen", args); // 7. Prepare for next batch. int currentlyInserted = scriptResult.Resource; currentCount += currentlyInserted; } // 8. Validate int numDocs = 0; CosmosResultSetIterator <dynamic> setIterator = container.Items.GetItemIterator <dynamic>(); while (setIterator.HasMoreResults) { CosmosQueryResponse <dynamic> response = await setIterator.FetchNextSetAsync(); numDocs += response.Count(); } Console.WriteLine("Found {0} documents in the collection. There were originally {1} files in the Data directory\r\n", numDocs, fileCount); }
// Async main requires c# 7.1 which is set in the csproj with the LangVersion attribute public static async Task Main(string[] args) { IConfigurationRoot configuration = new ConfigurationBuilder() .AddJsonFile("appSettings.json") .Build(); string endpoint = configuration["EndPointUrl"]; if (string.IsNullOrEmpty(endpoint)) { throw new ArgumentNullException("Please specify a valid endpoint in the appSettings.json"); } string authKey = configuration["AuthorizationKey"]; if (string.IsNullOrEmpty(authKey) || string.Equals(authKey, "Super secret key")) { throw new ArgumentException("Please specify a valid AuthorizationKey in the appSettings.json"); } // Connecting to Emulator. Change if you want a live account CosmosConfiguration cosmosConfiguration = new CosmosConfiguration(endpoint, authKey); cosmosConfiguration.AddCustomHandlers( new LoggingHandler(), new ConcurrencyHandler(), new ThrottlingHandler() ); CosmosClient client = new CosmosClient(cosmosConfiguration); CosmosDatabaseResponse databaseResponse = await client.Databases.CreateDatabaseIfNotExistsAsync("mydb"); CosmosDatabase database = databaseResponse.Database; CosmosContainerResponse containerResponse = await database.Containers.CreateContainerIfNotExistsAsync("mycoll", "/id"); CosmosContainer container = containerResponse.Container; Item item = new Item() { Id = Guid.NewGuid().ToString(), Name = "Test Item", Description = "Some random test item", Completed = false }; // Create await container.Items.CreateItemAsync <Item>(item.Id, item); item.Completed = true; // Replace await container.Items.ReplaceItemAsync <Item>(item.Id, item.Id, item); // Querying CosmosResultSetIterator <Item> query = container.Items.CreateItemQuery <Item>(new CosmosSqlQueryDefinition("SELECT * FROM c"), maxConcurrency: 1); List <Item> results = new List <Item>(); while (query.HasMoreResults) { CosmosQueryResponse <Item> response = await query.FetchNextSetAsync(); results.AddRange(response.ToList()); } // Read Item CosmosItemResponse <Item> cosmosItemResponse = await container.Items.ReadItemAsync <Item>(item.Id, item.Id); AccessCondition accessCondition = new AccessCondition { Condition = cosmosItemResponse.ETag, Type = AccessConditionType.IfMatch }; // Concurrency List <Task <CosmosItemResponse <Item> > > tasks = new List <Task <CosmosItemResponse <Item> > >(); tasks.Add(UpdateItemForConcurrency(container, accessCondition, item)); tasks.Add(UpdateItemForConcurrency(container, accessCondition, item)); try { await Task.WhenAll(tasks); } catch (CosmosException ex) { // Verify that our custom handler catched the scenario Debug.Assert(999.Equals(ex.SubStatusCode)); } // Delete await container.Items.DeleteItemAsync <Item>(item.Id, item.Id); }
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"); CosmosResultSetIterator <SalesOrder> resultSet = container.Items.CreateItemQuery <SalesOrder>( query, 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 CosmosResultSetIterator streamResultSet = container.Items.CreateItemQueryAsStream( query, maxConcurrency: 1, partitionKey: "Account1", maxItemCount: 10); List <SalesOrder> allSalesForAccount1FromStream = new List <SalesOrder>(); while (streamResultSet.HasMoreResults) { using (CosmosQueryResponse responseMessage = await streamResultSet.FetchNextSetAsync()) { // Item stream operations do not throw exceptions for better performance if (responseMessage.IsSuccess) { dynamic streamResponse = FromStream <dynamic>(responseMessage.Content); List <SalesOrder> salesOrders = streamResponse.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"); } }