YesSql query using the same index twice

[kevinasdzine]

I thought I had a solution for this before but I'm not finding a reference to it now. I have a content type with two date fields, "Start" and "End". I want to query for content items where Start is < DateTime.today and End > DateTime.today. However, if I include two .With<DateFieldIndex> clauses, the resulting query only joins with DateFieldIndex table once and I get no results. Can someone clue me in?

var contentItem = await _session.Query<ContentItem>()
    .With<DateFieldIndex>(index => index.ContentType == "QuickSurvey" && index.ContentPart == "QuickSurvey" && index.ContentField == "Start" && index.Date <= ((DateTime?)DateTime.Today))
    .With<DateFieldIndex>(index => index.ContentType == "QuickSurvey" && index.ContentPart == "QuickSurvey" && index.ContentField == "End" && index.Date >= ((DateTime?)DateTime.Today))
    .FirstOrDefaultAsync();

[hishamco]

At first glance, this condition should return no results, could you try to split your query into two queries instead

[kevinasdzine]

I suppose I could do one query greater or equal, a second less than or equal, and then use a Linq expression to select the result(s) that have the same ContentItemId but it seems something of a hack and it's surprising to me that YesSql doesn't have a way to query for multiple fields of the same field type.

[hishamco]

This could happen if you have a strongly type that you query on it, @sebastienros is there a way in YesSQL to do it in one query?

[lampersky]

in yessql there are "Any" and "All" methods, you could use "All" against your index with multiple predicates, an example from YesSql repo:

                var query = session.Query<Article>()
                    .Any(
                        x => x.With<ArticlesByDay>(x => x.DayOfYear == 305),
                        x => x.With<ArticlesByDay>(x => x.DayOfYear == 306)
                    );

[kevinasdzine]

Thanks. I knew I had used something like this before but I changed jobs and didn't have access to my old source code.

[hishamco]

How did the above query know the field name?

[kevinasdzine]

The corrected code would read more like:

var contentItem = await _session.Query<ContentItem>()
    .All(
    field => field.With<DateFieldIndex>(index => index.ContentType == "QuickSurvey" && index.ContentPart == "QuickSurvey" && index.ContentField == "Start" && index.Date <= ((DateTime?)DateTime.Today)),
    field => field.With<DateFieldIndex>(index => index.ContentType == "QuickSurvey" && index.ContentPart == "QuickSurvey" && index.ContentField == "End" && index.Date >= ((DateTime?)DateTime.Today))
    )
    .FirstOrDefaultAsync();

[sebastienros]

In your example it's the same field/property (date) so could it be with a single statement but "Date <= x && Date >= y" ?

If these were two date fields in the same index, then you would need All