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