mongo range-based pagination with linq

Question

It is possible to do range based pagination with mongo linq or I'm better off with filter route. The following is my case at hand:

I store and generate by Id's as mongo ObjectIds, but treat them as strings in my domain:

    BsonIgnoreIfDefault]
    [BsonRepresentation(BsonType.ObjectId)]
    [BsonId(IdGenerator = typeof(StringObjectIdGenerator))]
    public string Id { get; set; }

and I'm trying

var result = await _collection.AsQueryable()
         .Where(m => m.Id > afterId) // '>' illegal with strings
         .OrderBy(m => m.Id)
         .ToListAsync();

Error CS0019 Operator '>' cannot be applied to operands of type 'string' and 'string'.

Another option. My Id's are mongo-generated as ObjectId and I compare them in my filter:

var idFilter = Builders<T>.Filter.Gt(m => m.Id, afterId);
result = await _collection.Find(idFilter).ToListAsync();

Note : If you want to know if some object has been generated *after* another, you should **NOT** rely on objectId being "greater" (in lexicographic order) than another. See : https://stackoverflow.com/questions/31057827/is-mongodb-id-objectid-generated-in-an-ascending-order — Pac0, Feb 28 '18 at 09:30
now, concerning your precise question, in C# to see if a string is "greater" than another, you can use String.Compare . `String.Compare(a, b)` will give -1 if a is less than b, 0 if equal, 1 if a is greater than b . — Pac0, Feb 28 '18 at 09:32
Hi @Pac0 thanks for the comments.I do not quite care which object was generated after which object, as long as they would not affect my paging. My main concern is just to `deep` page through the results without repetition. — rethabile, Feb 28 '18 at 09:46
I think that the string comparison won't be handled with linq (though you can try : `.Where(m => String.Compare(m.Id, afterId) > 1)` ). To do a range-pagination, I don't have a solution. The linq `Take` and `Skip` are supported, though, so you could do some size-based pagination. But it doesn't seem to be what you want. — Pac0, Feb 28 '18 at 10:28
If it is an option, you could add another field numeric id to your documents, then your first query could work — Pac0, Feb 28 '18 at 10:40
thanks. I'll try `.where(m => string.compare...` with `.Take` and see how it pans out. — rethabile, Feb 28 '18 at 11:35
That didn't work for me :(. `Compare({document}{_id}, "5a7314c61bb1534b24998221") is not supported.` — rethabile, Feb 28 '18 at 12:39
Yes, in docs String.Compare didn't look supported, I wrote an answer to correctly summarize my comments. — Pac0, Feb 28 '18 at 12:41

Pac0 · Answer 1 · 2018-02-28T14:53:44.270

1

If you wish to do a string comparison the comparison 'string1 > string2' would be written in C# String.Compare(string1, string2) == 1 .

However, reading the the docs on C# driver, it doesn't seem that the Linq adapter for mongodb can translate this yet, so a .Where(m => String.Compare(m.Id, afterId) == 1) is likely to be simply ignored / fail . (EDIT : as per your comment, it gives an error message)

As an alternative, you can :

Add a different numerical id field (unique and indexed) to allow sorting via Linq (a bit ugly and overkill, but might be a possibility)
paginate by sized chunks instead of id ranges with Take and Skip, which are already supported, like that :

/// take the results 2001-3000 in the list ordered by id.
var result = await _collection.AsQueryable()
     .OrderBy(m => m.Id)
     .Skip(2000)
     .Take(1000)
     .ToListAsync();

edited Feb 28 '18 at 14:53

answered Feb 28 '18 at 12:40

Pac0

21,465
8
65
74

I wanted to avoid `skip` as much as i can as I read somewhere that it's the one that hit the performance. Since my use case is just to load all the records (strictly for my api internal use), I'm thinking of exposing `IAsyncCursor` via `FindAsync` or `IQueryable` via `AsQueryable` as above. And then resort to the mongodb default sort for sorting. and then just `foreach` where i use the records.Does that sounds like a good idea? – rethabile Feb 28 '18 at 14:27
Not an expert with using IAsyncCursor, and not sure if I understand the implications very well. If memory is not an issue in your case, you could load all the records, then on the actual C# list of records already loaded, you will be able to use `.Where(m => String.Compare(m.Id, afterId) == 1)` without any issue, as this will be Linq to Object and not Linq to mongodb query anymore – Pac0 Feb 28 '18 at 14:48
also, I suggest you to *try* using `Skip` to see if there is *actually* a performance problem in your case. – Pac0 Feb 28 '18 at 14:50

mongo range-based pagination with linq

1 Answers1