Date value not received

[andrejK (Andrej)]

Hi everyone, not sure if this is the correct forum, but I will try.

I have C# console application where I use NEST to index data and search in ElasticSearch.
Versions:

• ElasticSearch 7.5.2
• .NET 4.7.2
• NEST 7.6.1

When I use NEST for search, everything is ok. But in some special cases, I would like to use NEST's low level search (because I can use sql command). But in low level search Timestamp is not obtained. Here is code sample:

class EsObject
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Timestamp { get; set; }

    public override string ToString()
    {
        return $"Id = {Id}, Name = {Name}, Timestamp = {Timestamp.ToString("dd.MM.yyyy HH:mm:ss")}";
    }
}

class Program
{
    static void Main(string[] args)
    {
        var indexName = "es_objects";
        //
        // Create data objects
        //
        var obj = new EsObject()
        {
            Id = 1,
            Name = "Object_1",
            Timestamp = new DateTime(2020, 2, 12, 4, 55, 19)
        };
        Console.WriteLine($"Object created {obj}");

        //
        // Connect to ElasticSearch
        //
        Console.Write("Connecting to ElasticSearch... ");
        var node = new Uri("http://localhost:9200");
        var settings = new ConnectionSettings(node)
            .DefaultIndex(indexName)
            .DefaultMappingFor<EsObject>(x => x.IdProperty("Id"))
            .DefaultFieldNameInferrer(s => s.ToLower()); ;

        var esClient = new ElasticClient(settings);
        Console.WriteLine("done");

        //
        // Index data
        //
        Console.Write("Indexing data... ");
        var idxResp = esClient.IndexDocument(obj);
        Console.WriteLine("done");

        //
        // Searching using NEST
        //
        Console.Write("Searching data using NEST ... ");
        var result = esClient.Search<EsObject>(s => s
                        .Query(q => q
                            .Match(m => m
                                .Field(f => f.Name)
                                    .Query(obj.Name))))
                        .Documents
                        .ToList();

        Console.WriteLine("done");
        Console.WriteLine($"Results: found {result.Count} items");
        if (result.Count > 0)
            Console.WriteLine($"Found item: {result[0]}");

        //
        // Searching using SQL
        //
        Console.Write("Searching data using low level ... ");
        var request = new TranslateSqlRequest
        {
            Query = $"select * from {indexName} where name='{obj.Name}'"
        };

        var sqlResponse = esClient.LowLevel.Sql.Translate<StringResponse>(PostData.Serializable(request));
        var resp = esClient.LowLevel.Search<SearchResponse<EsObject>>(indexName, sqlResponse.Body).Documents.ToList();
        Console.WriteLine("done");
        Console.WriteLine($"Results: found {resp.Count} items");
        if (resp.Count > 0)
            Console.WriteLine($"Found item: {resp[0]}");

        Console.ReadKey();
    }
}

When I run program, I get following results:

Object created Id = 1, Name = Object_1, Timestamp = 12.02.2020 04:55:19
Connecting to ElasticSearch... done
Indexing data... done
Searching data using NEST ... done
Results: found 1 items
Found item: Id = 1, Name = Object_1, Timestamp = 12.02.2020 04:55:19
Searching data using low level ... done
Results: found 1 items
Found item: Id = 1, Name = Object_1, Timestamp = 01.01.0001 00:00:00

From the results you can see that first search returned 12.02.2020 04:55:19, but the second 01.01.0001 00:00:00.

Any idea how can I get proper date value when using low level search?

[andrejK (Andrej)]

Question answered by Russ Cam on stack overflow.

var resp = client.LowLevel.Search<SearchResponse<EsObject>>(indexName, sqlResponse.Body);

var documents = resp.Hits
    .Select(h => {
        h.Source.Id = h.Fields.ValueOf<EsObject, int>(f => f.Id);   
        h.Source.Timestamp = DateTimeOffset.FromUnixTimeMilliseconds(long.Parse(h.Fields.Value<string>("timestamp"))).DateTime;
        return h.Source;
    })
    .ToList();