No results using Linq Where() with List.Count condition

[ulja-dd]

Hi,

I'm using CouchCB.NET 3.1.0 via nuget.org

The db query is supposed to return all User documents where the List property RoleIds is not empty. I'm using the following Linq statement to do so:

var usersWithRoles = await context.Users
   .Where(u => u.RoleIds.Count > 0)
   .Take(int.MaxValue)
   .ToListAsync();

The expected result is to get some users. The Users database has users where the condition is true, however the usersWithRoles is always empty.

Is there something I'm doing wrong or is this a bug?

To replicate this issue, you may use the following programm:

using CouchDB.Driver;
using CouchDB.Driver.Extensions;
using CouchDB.Driver.Options;
using CouchDB.Driver.Types;
using Flurl.Http;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace CountIssue
{
    class Program
    {
        static async Task Main(string[] args)
        {
            await using var context = new Context();

            await InsertUsersAsync(context);

            await CountQueryAsync(context);
        }

        private static async Task CountQueryAsync(Context context)
        {
            var usersWithRoles = await context.Users
                .Where(u => u.RoleIds.Count > 0)
                .Take(int.MaxValue)
                .ToListAsync();

            Console.WriteLine($"\n\nusersWithRoles: {usersWithRoles.Count}");
        }

        private static async Task InsertUsersAsync(Context context)
        {
            var user1 = new User()
            {
                Id = Guid.NewGuid().ToString(),
                Name = "user 1",
                RoleIds = new List<string>()
                {
                    "r1"
                }
            };
            var user2 = new User()
            {
                Id = Guid.NewGuid().ToString(),
                Name = "user 2",
                RoleIds = new List<string>()
            };
            var user3 = new User()
            {
                Id = Guid.NewGuid().ToString(),
                Name = "user 3",
                RoleIds = new List<string>()
                {
                    "r1",
                    "r2"
                }
            };

            await context.Users.AddAsync(user1);
            await context.Users.AddAsync(user2);
            await context.Users.AddAsync(user3);
        }
    }

    public class Context : CouchContext
    {
        public CouchDatabase<User> Users { get; set; }

        protected override void OnConfiguring(CouchOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseEndpoint("http://192.168.56.56:5984")
                .EnsureDatabaseExists()
                .UseBasicAuthentication(username: "developer", password: "***")
                .ConfigureFlurlClient(settings =>
                {
                    settings.AfterCall = OnAfterCall;
                });
        }

        private void OnAfterCall(FlurlCall obj)
        {
            Console.WriteLine($"--- {obj.Request.Verb} {obj.Request.Url}\n{obj.RequestBody}");
        }

        protected override void OnDatabaseCreating(CouchDatabaseBuilder databaseBuilder)
        {
            databaseBuilder.Document<User>()
                .ToDatabase($"count_issue_users");
        }
    }

    public class User : CouchDocument
    {
        public string Name { get; set; }
        public List<string> RoleIds { get; set; }
    }
}

The output is similar to:

--- PUT http://192.168.56.56:5984/count_issue_users

--- PUT http://192.168.56.56:5984/count_issue_users/2e871230-8438-4bcc-8a1a-0d465896db5d
{"_conflicts":[],"name":"user 1","roleIds":["r1"],"_id":"2e871230-8438-4bcc-8a1a-0d465896db5d"}
--- PUT http://192.168.56.56:5984/count_issue_users/9069a548-b550-410a-b1c8-2acd5d420616
{"_conflicts":[],"name":"user 2","roleIds":[],"_id":"9069a548-b550-410a-b1c8-2acd5d420616"}
--- PUT http://192.168.56.56:5984/count_issue_users/16cba9e3-c5dd-4a29-838d-9d3996255880
{"_conflicts":[],"name":"user 3","roleIds":["r1","r2"],"_id":"16cba9e3-c5dd-4a29-838d-9d3996255880"}
--- POST http://192.168.56.56:5984/count_issue_users/_find
{"selector":{"roleIds.count":{"$gt":0}},"limit":2147483647}


usersWithRoles: 0

The expected result of usersWithRoles would be a list with the two users "user 1" and "user 3".

[AlexandrSHad]

As far as I see that is not supported by CouchDB itself.
You only can query data like this:

{
   "selector": {
      "$and": [
         {
            "roleIds": {
               "$exists": true
            }
         },
         {
            "roleIds": {
               "$ne": null
            }
         },
         {
            "roleIds": {
               "$not": {
                  "$size": 0
               }
            }
         }
      ]
   }
}

But that's a bad idea. Maybe a better solution would be to create a dedicated view.

[ulja-dd]

At the moment my workaround is to fetch all data from the database and doing the filtering in memory.

I'm not familliar yet with the capabilities of CouchDB and the details of Mango queries, since I just started working with CouchDB some weeks ago.

I see two options to solve this issue, in case CouchDB really isn't capable of counting elements of an array property:

1. Throw a NotSupportedException in case Count is used within Linq or
2. Provide an extension with a specialized Count method that is able to translate into a generic Mango query like the one above.

Returning false data (since no data is not correct) dosn't seam to be a good idea.

[matteobortolazzo]

@ulja-dd both options are valid

@AlexandrSHad why do you think it's a bad idea?

I agree on the use of views, but would it hurt to implement it?

[AlexandrSHad]

My concern is this wouldn't work fast on a big amount of data as we have no dedicated index. You might say that any query wouldn't work fast without required indices, but in this case, I am not aware if indexing by array length is even possible. The only way is to use partial index along with UseIndex() method. And this is a fine tunning CouchDB.NET is not allowed to do on its own as CouchDB can't detect partial indices automatically. In addition, the partial index covers only one case. If you were to query data with a different condition (Count = 1 or Count <= 3) it wouldn't fit.
Therefore I supposed it would be better to use a view with array length included in a composed key.
emit([doc.Id, doc.RoleIds.length], null)
Of course, if you implement translating Count clause to the respective selector it should do its task. But I do not know how many hassles will it take and how will it work along with other potential conditions.
Also, you have to keep in mind the client can query data like this:
.Where(u => u.RoleIds.Any())
Or even
.Where(u => u.RoleIds.Count.Any(id => id >= 1000))
And the last thought. If you take it into development it would be great to provide information to a client about missing indices. For instance log respective message with warning level (after you implement built-in logging).
So that's not so easy and should be well thought out.
But I am not against this feature as a whole)

[matteobortolazzo]

Hey, couldnt work on the project in the last months. I agree supporting that is bad. I will add an exception