Why does RavenDB return a wrong total result count?

ravendb

Solution

You need `Intersect` when using multiple where clauses. To combine `Where` and `Search`, you have to pass `SearchOptions.And` to `Search`:

using (var db = _documentStore.OpenSession()) {
    RavenQueryStatistics stats;
    var query = db.Query<Club>()
        .Customize(_ => _.WaitForNonStaleResultsAsOfLastWrite())
            .Statistics(out stats)
            .Where(club => club.Type == "Amateur")
            .Search(
                club => club.Name,
                "Foo*", 
                escapeQueryOptions: EscapeQueryOptions.AllowPostfixWildcard, 
                options:SearchOptions.And);

    var clubs = query.ToList();

    Assert.AreEqual(2, clubs.Count);
    Assert.AreEqual(2, stats.TotalResults); 
}

Problem

As shown in the following unit test I expect stats.TotalResults to be 2 but is 3. Why is that? ``` [Test] public void RavenQueryStatisticsTotalResultsTest1() { using (var db = _documentStore.OpenSession()) { db.Store(new Club { Name = "Foo1", Type = "Amateur" }); // --> Matches all conditions db.Store(new Club { Name = "Foo2", Type = "Professional" }); db.Store(new Club { Name = "Foo3", Type = "Amateur" }); // --> Matches all conditions db.Store(new Club { Name = "Boo1", Type = "Amateur" }); db.Store(new Club { Name = "Boo2", Type = "Professional" }); db.SaveChanges(); } WaitForIndexing(_documentStore); using (var db = _documentStore.OpenSession()) { RavenQueryStatistics stats; var query = db.Query<Club>() .Statistics(out stats) .Where(club => club.Type == "Amateur") .Intersect() .Search(club => club.Name, "Foo*", escapeQueryOptions: EscapeQueryOptions.AllowPostfixWildcard); var clubs = query.ToList(); Assert.AreEqual(2, clubs.Count); Assert.AreEqual(2, stats.TotalResults); // I expect 2 but was 3! Why is that? } } ```

Original source