Problem in DateRange query in C#

Hi there! im very new to Elastic Search and just need some help.

Im trying to performe an complex query in c# with my own list of "Indexes", "Dateranges" and "Terms" as well but with out success..

result = client.Search<Event>(e => e.Index(customIndex)
                                                .Query(q => 
                                                    q.DateRange(dr => dr
                                                                .Boost(1.1)
                                                                .Field("DateTime")
                                                                .GreaterThanOrEquals(dateFrom)
                                                                .LessThanOrEquals(dateTo)
                                                                .Format("dd/MM/yyyy")
                                                            ) &&
                                                    q.Terms(mt => mt.
                                                    Field("RuleID")
                                                    .Terms(legacyIDs)
                                                    )
                                                )
                                                .From(pageSize * page)
                                                .Size(pageSize)
                                                .Sort(ss => ss.Descending("Date")
                                                .Field(fie => fie.UnmappedType(FieldType.Date)))
                                                );

Do i Missed something?
The NEST Client it says everythings went well, but the result its different.

Its my approach correct? i think the problem it is in the daterange :frowning:

thanks in advance

pageSize && page are passed correctly, so i thinks theres no problem at all :slight_smile:
Im just trying to focus on the dateRange if its something wrong

Not sure how it works in Nest but I think you should put your queries within a bool query with filter or must clauses.

1 Like

Thanks for your help. now im working with something like that...

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "DateTime": {
              "gte": "2018-06-09T00:00:00",
              "format": "yyyymmdd"
            }
          }
        }
      ]
    }
  }
}

but im not sure at all...

gte and format don't match here. Which looks strange to me.

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

1 Like

Thanks again for your reply.
What im trying to performed its very simple, just querying from my "Event" Documents that match my needs which are:

.Index('custom_Index_array_of_strings_to_look_for')
.Query(
           Events.DateField  ->GreaterThan ("DateToPrivided")
           Events.Datefield -> LowerThanOrEqueals ("DateFromProvided")
&&
            Events.Rule that match with my custom "LegacysIdsProvided" inside a Terms Query
)

and pretty much thats it.

now im digging with this link: https://www.elastic.co/guide/en/elasticsearch/reference/6.x/query-filter-context.html and i think thats the buttom key of the all problem, what do you think?

I'been reading the all "n00bies" section in order to help myself to write properly my query to everybody knows what i am trying to do.

GET index/_search
result = client.Search<Event>(s => s
                            .Query(q => q
                                .Bool(b => b
                                    .Filter(f => f
                                        .Terms(t => t
                                            .Field("RuleID")
                                            .Terms(legacyIDs)
                                            )
                                        &&
                                        f.DateRange(dr => dr
                                            .Field("DateTime")
                                            .GreaterThanOrEquals(new DateTime().AddDays(-1))
                                        )
                                    )
                                )
                            )
                        );

Reading the differences between "Context Querys" and "Filter Querys" allow me to understand what i was doing wrong, but unfortunately theres somethings missing up there with my query.
Now i think its quite clear, but if you dont understand it please let me know :slight_smile:

If I understand correctly, you're looking to have both the terms query and range query executed in the context of filter clauses of a bool query? If that's the case, your query is almost correct, but

  1. .Filter(...) takes a collection of lambda expressions to express the queries
  2. new DateTime().AddDays(-1) is an unrepresentable date; it is one day less than the smallest representable date in .NET (1/01/0001 12:00:00 AM). I think you want DateTime.Now.AddDays(-1) here
var legacyIDs = new [] { "1", "2" };

var result = client.Search<Event>(s => s
    .Query(q => q
        .Bool(b => b
            .Filter(f => f
                .Terms(t => t
                    .Field("RuleID")
                    .Terms(legacyIDs)
                ), f => f               
                .DateRange(dr => dr
                    .Field("DateTime")
                    .GreaterThanOrEquals(DateTime.Now.AddDays(-1))
                )
            )
        )
    )
);

this produces the query

{
  "query": {
    "bool": {
      "filter": [
        {
          "terms": {
            "RuleID": [
              "1",
              "2"
            ]
          }
        },
        {
          "range": {
            "DateTime": {
              "gte": "2018-06-13T10:29:29.6995906+10:00"
            }
          }
        }
      ]
    }
  }
}

With operator overloading, you can create bool queries even more succinctly. Here's the same query but with more concise syntax

var result = client.Search<Event>(s => s
    .Query(q => +q
        .Terms(t => t
            .Field("RuleID")
            .Terms(legacyIDs)
        ) && +q          
        .DateRange(dr => dr
            .Field("DateTime")
            .GreaterThanOrEquals(DateTime.Now.AddDays(-1))
        )
    )
);
1 Like

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.