Cosmosdb documentdb Sql query with params returns 0 items

Question

I have a problem with a query. When I pass a single parameter (deviceId) the query works normally. If I add the other two parameters, no items are returned to me even though the collections are correctly populated and have items that satisfy the required conditions. I enclose the code c # which does the above described.

 public static async Task<IEnumerable<T>> GetItemsCompact(string deviceId,DateTime startdate,DateTime enddate)
        {
            string collectionToUse;
            SqlQuerySpec sqlQuerySpec = new SqlQuerySpec();

            if (typeof(T).ToString().IndexOf("Telemetry") != -1)
            {
                DocumentDBRepository<EBB.Web.Telemerty.Models.CosmosDBTelemetry>.Initialize();
                collectionToUse = AppSettings.Collection;

                sqlQuerySpec.QueryText = "SELECT c.messageUID as messageUID, ";
                sqlQuerySpec.QueryText += "c.deviceId as deviceId, ";
                sqlQuerySpec.QueryText += "udf.UDF_VIn(c.VIn) as VIn, ";
                sqlQuerySpec.QueryText += "udf.UDF_AIn(c.AIn) as AIn, ";
                sqlQuerySpec.QueryText += "udf.UDF_W(c.W) as W, ";
                sqlQuerySpec.QueryText += "udf.UDF_Var(c.Var) as Var, ";
                sqlQuerySpec.QueryText += "c.EventProcessedUtcTime as EventProcessedUtcTime ";
                sqlQuerySpec.QueryText += "from Telemetry c ";
                sqlQuerySpec.QueryText += "where c.deviceId = @deviceId and ";
                sqlQuerySpec.QueryText += "c.EventProcessedUtcTime >= @startdate and ";
                sqlQuerySpec.QueryText += "c.EventProcessedUtcTime <= @enddate";
                sqlQuerySpec.Parameters = new SqlParameterCollection()
                    {
                          new SqlParameter("@deviceId", deviceId),
                          new SqlParameter("@startdate",startdate),
                          new SqlParameter("@enddate",enddate)
                    };

            }
            else
            {
                DocumentDBRepository<EBB.Web.Telemerty.Models.CosmosDBEvents>.Initialize();
                collectionToUse = AppSettings.Collection2;
                sqlQuerySpec.QueryText = "select doc.uid as uid, ";
                sqlQuerySpec.QueryText += "doc.deviceId as deviceId, ";
                sqlQuerySpec.QueryText += "doc.eventId as eventId, ";
                sqlQuerySpec.QueryText += "doc.ts as ts, ";
                sqlQuerySpec.QueryText += "doc.startTS as startTS, ";
                sqlQuerySpec.QueryText += "doc.endTS as endTS, ";
                sqlQuerySpec.QueryText += "doc.no as no, ";
                sqlQuerySpec.QueryText += "doc.params as params, ";
                sqlQuerySpec.QueryText += "doc.tags as tags ";
                sqlQuerySpec.QueryText += "from Events doc ";
                sqlQuerySpec.QueryText += "where doc.deviceId = @deviceId and ";
                sqlQuerySpec.QueryText += "doc.EventProcessedUtcTime >= @startdate and ";
                sqlQuerySpec.QueryText += "doc.EventProcessedUtcTime <= @enddate";
                sqlQuerySpec.Parameters = new SqlParameterCollection()
                    {
                          new SqlParameter("@deviceId", deviceId),
                          new SqlParameter("@startdate",startdate),
                          new SqlParameter("@enddate",enddate)
                    };
            }

            FeedOptions opt = new FeedOptions
            {
                EnableCrossPartitionQuery = true,
                MaxItemCount = -1
            };

            IDocumentQuery<T> query = client.CreateDocumentQuery<T>(
                    UriFactory.CreateDocumentCollectionUri(AppSettings.Database, collectionToUse), sqlQuerySpec,
                    opt).AsDocumentQuery();

            List<T> results = new List<T>();

            while (query.HasMoreResults)
            {
                results.AddRange(await query.ExecuteNextAsync<T>());
            }

            return results;
         }

query.toSting() in Telemetry case is equal to :

    "{\"query\":\"SELECT c.messageUID as messageUID, c.deviceId as deviceId, udf.UDF_VIn(c.VIn) as VIn, udf.UDF_AIn(c.AIn) as AIn, udf.UDF_W(c.W) as W, udf.UDF_Var(c.Var) as Var, c.EventProcessedUtcTime as EventProcessedUtcTime from Telemetry c where c.deviceId = @deviceId and c.EventProcessedUtcTime >= @startdate and c.EventProcessedUtcTime <= @enddate\",\"parameters\":[{\"name\":\"@deviceId\",\"value\":\"where c.deviceId = \\\"EBBBrain1874008291\\\"\"},{\"name\":\"@startdate\",\"value\":\"2019-06-01T00:00:00\"},{\"name\":\"@enddate\",\"value\":\"2019-06-30T00:00:00\"}]}"

Can someone help me? Thanks in advance. Simone

Answer 1

The problem is caused because of the serialization of the property in the parameters. When you're creating the document you are using UTC but when you're querying, the SDK thinks the DateTimeKind of the DateTime is Unspecified (which is valid) so it's converting it in this unspecified format 2019-06-30T00:00:00 instead of this UTC 2019-05-31T14:58:15.3238226Z one. If you used something like DateTime.Now it would use the local format which looks like this 2009-06-15T13:45:30.0000000-07:00. The reason why you don't get any results back is because the date range check is ultimately a string comparison which is enabled by the ISO formatting.

To get that working you need need to use the DateTime.SpecifyKind method. Here is how you parameters clause should look like:

sqlQuerySpec.Parameters = new SqlParameterCollection()
{
      new SqlParameter("@deviceId", deviceId),
      new SqlParameter("@startdate", DateTime.SpecifyKind(startdate, DateTimeKind.Utc)),
      new SqlParameter("@enddate", DateTime.SpecifyKind(enddate, DateTimeKind.Utc))
};