.NET SDK N1QL Date Serialization issue with STR_TO_MILLIS

SQL++
1

I have a model that has a date in it. Here is a part of this model:

    public string Version { get; set; }

    [JsonConverter(typeof(UnixDateTimeConverter))]
    public DateTimeOffset LastModified { get; set; }

The last modified is in epoch format and everything serializes and deserializes correctly.
But when I use LINQ to query on this field, the SDK (version 2.7.21) generates the following query:

    var query = _bucketContext.Query<MyEntity>()
           .Where(a => a.Published < to)

    SELECT RAW `Extent1` FROM `mybucket` as `Extent1` 
    WHERE (STR_TO_MILLIS(`Extent1`.`published`) < 1603988145828.31)

The value in the cluster is already an integer and the SDK could infer that by looking at the converter.
How do I enforce the SDK to not add STR_TO_MILLIS on the DateTime fields?

2

I think I figured it out. I had to use UnixMillisecondsConverter instead of my custom epoch converter.
So the query looks good now except the ORDER BY section:

SELECT RAW `Extent1` FROM `mybucket` as `Extent1` 
WHERE (`Extent1`.`published` < 1603996194739.4763)
ORDER BY MILLIS_TO_STR(`Extent1`.`published`) DESC LIMIT 10

Any idea why?

3

Could you please post what is wrong with the query? Did it give wrong results? It should work.

Add order by expression to projection and check what it returns. Make sure field name is right (case sensitive). Check in Web console.

SELECT  e.*,  MILLIS_TO_STR(e.published)  AS publishedmillis  FROM `mybucket` as  e 
WHERE (e.published < 1603996194739.4763)
ORDER BY MILLIS_TO_STR(e.published) DESC LIMIT 10;

If LINQ to N1QL transformation is issue please specify that move post to .NET category.