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

Is the parameter that's working the deviceId and the dates are failing? — Nick Chapsas
– Nick Chapsas, Commented Jul 23, 2019 at 7:56
Can you please make a query.ToString() call after your CreateDocumentQuery line? This will show us how your query is formed and executed. I'm suspecting there is a serialization inconsistency — Nick Chapsas
– Nick Chapsas, Commented Jul 23, 2019 at 8:07
Ok and now can you show us some example of the EventProcessedUtcTime field in the database? — Nick Chapsas
– Nick Chapsas, Commented Jul 23, 2019 at 8:18

Nick Chapsas · Accepted Answer · 2019-07-24 08:58:16Z

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

edited Jul 24, 2019 at 8:58

answered Jul 23, 2019 at 9:14

Nick Chapsas

7,2501 gold badge22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Simone Spagna Over a year ago

Using the notation you suggest me I have the same result. Zero items returned

Nick Chapsas Over a year ago

Can you check what the final query looks like?

Simone Spagna Over a year ago

I add it to your post

Simone Spagna Over a year ago

Furthermore, the instruction deviceId.ToString("") generates an exception because the empty string in brackets is not accepted.

Nick Chapsas Over a year ago

@SimoneSpagna Sorry i accidentally added the tostring thing. I tested the code as pasted here and it worked fine.

|

Collectives™ on Stack Overflow

Cosmosdb documentdb Sql query with params returns 0 items

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related