Check if container / collection exists before running select

Question

I have a multi tenant cosmos db. Each tenant has their own container. I am new to cosmos so if any of my terms are in correct please feel free to correct me.

var collection = $"{tenant}.expense";

I am trying to query data in this collection.

var documentRep = new DocumentDbRepository(_storageInfo);
var collection = $"{tenant}.expense";

var sql = $"SELECT VALUE COUNT(1) FROM (SELECT DISTINCT c.UserName FROM c WHERE((c.Created >= '{startDate:yyyy-MM-dd}T00:00:00' or c.Modified >= '{startDate:yyyy-MM-dd}T00:00:00') and (c.Created <= '{endDate:yyyy-MM-dd}T23:59:00' or c.Modified <= '{endDate:yyyy-MM-dd}T23:59:00'))) AS x";

var cnt = await documentRep.QueryCountAsync(collection, sql);

But if the tenant doesn't actually exist cosmos is returning a 404. Is there a way to check if the container actually exists in the document rep before running the select?

DocumentDbRepository.cs

The error is being thrown in this method. Its getting caught in the genral exception. not in the DocumentclientException as i would have thought.

Having no results over a period of dates i would expect to result in 0 but thats not the issue here because the tenant being sent is actually invalid for this database it doesn't exist.

public class DocumentDbRepository
    {
        private readonly Database _cosmosDatabase;

        public DocumentDbRepository(StorageInfo storageInfo)
        {
            var databaseId = storageInfo.AzureCosmosDb.Collection;
            var endpoint = storageInfo.AzureCosmosDb.Uri;
            var key = storageInfo.AzureCosmosDb.Key;

            var cosmosClient = new CosmosClient(endpoint, key);
            _cosmosDatabase = cosmosClient.GetDatabase(databaseId);
        }

        public async Task<int> QueryCountAsync(string collectionId, string sql)
        {
            try
            {
                var container = _cosmosDatabase.GetContainer(collectionId);

                var queryDefinition = new QueryDefinition(sql);

                var queryResultSetIterator = container.GetItemQueryIterator<int>(queryDefinition);

                var currentResultSet = await queryResultSetIterator.ReadNextAsync();

                return currentResultSet.FirstOrDefault();
            }
            catch (DocumentClientException e)
            {
            //NOTE: the error does not catch here
            if (e.StatusCode == HttpStatusCode.NotFound)
             {
              return 0;  // 
              }
              else
              {
                  throw;
               }
              }
            catch (Exception e)
            {
                // Error caught here.
                Console.WriteLine(e);
                throw;
            }
        }
    }
}

full error

Response status code does not indicate success: 404 Substatus: 0 Reason: (Message: {"Errors":["Resource Not Found"]} ActivityId: 18569a9-b155-44e1-9248-be86c291b9b5, Request URI: /apps/4867e06-ea60-4691-bcbc-6f423aac702d/services/829b605-5f0c-4728-bbfe-e4696e42f253/partitions/1249694-b944-486b-b3a4-db72b8534085/replicas/32156515793694366s, RequestStats: RequestStartTime: 2019-10-16T06:44:51.7346321Z, RequestEndTime: 2019-10-16T06:44:51.7346321Z, Number of regions attempted:1 ResponseTime: 2019-10-16T06:44:51.7346321Z, StoreResult: StorePhysicalAddress: rntbd://10.0.0.26/apps/4867e06-ea60-4691-bcbc-6f423aac702d/services/0829b605-5f0c-4728-bbfe-e4696e42f253/partitions/1249694-b944-486b-b3a4-db72b8534085/replicas/32156515793694366s, LSN: 40, GlobalCommittedLsn: 40, PartitionKeyRangeId: , IsValid: True, StatusCode: 404, SubStatusCode: 0, RequestCharge: 1, ItemLSN: -1, SessionToken: -1#40, UsingLocalLSN: False, TransportException: null, ResourceType: Collection, OperationType: Read ResponseTime: 2019-10-16T06:44:51.7346321Z, StoreResult: StorePhysicalAddress: rntbd://10.0.0.24/apps/67e06-ea60-4691-bcbc-6f423aac702d/services/0829b605-5f0c-4728-bbfe-e4696e42f253/partitions/d1249694-b944-486b-b3a4-db72b8534085/replicas/32156612013279478s, LSN: 40, GlobalCommittedLsn: 40, PartitionKeyRangeId: , IsValid: True, StatusCode: 404, SubStatusCode: 0, RequestCharge: 1, ItemLSN: -1, SessionToken: -1#40, UsingLocalLSN: False, TransportException: null, ResourceType: Collection, OperationType: Read , SDK: Microsoft.Azure.Documents.Common/2.7.0, Windows/10.0.18362 cosmos-netstandard-sdk/3.3.0).

from cosmos

I need to be able to check that tem does not have a .expense container / collection. Not exactly sure what the term is i am very new to cosmos

The following does appear to set up a container but the thing is i know for a fact that it doesn't exists. There must be a way of checking before i call the request. Or at least catching that the issue is that the collection id is invalid.

var container = _cosmosDatabase.GetContainer(collectionId);

Sajeetharan Sajeetharan · Accepted Answer · 2019-10-16T06:56:22

You can make use of the following methods if you are using the SDK

DocumentClient.CreateDatabaseIfNotExistsAsync DocumentClient.CreateDocumentCollectionIfNotExistsAsync

EDIT

you can take all Container informations in a list, by query with (and without) condition.

var client = new DocumentClient(new Uri("yourEndPointUrl"), "yourAuthKey");
var list = client.CreateDatabaseQuery().AsEnumerable().ToList();

then you can verify if it exists or not, there is no direct method available

You can get collections with the following method,

DocumentClient client = new DocumentClient(new Uri(endpoint), authKey);

Database database = client.CreateDatabaseQuery("SELECT * FROM d WHERE d.id = \"[YOUR_DATABASE_ID]\"").AsEnumerable().First();

List<DocumentCollection> collections = client.CreateDocumentCollectionQuery((String)database.SelfLink).ToList();

Check if container / collection exists before running select

DocumentDbRepository.cs

full error

from cosmos

1 Answers