MongoDB.Driver C# SortBy alphabetical, then skip and take get the result is the wrong position."

Question

I am using mongodb and MongoDB.Driver library version 2.19.1 in my C# project. I'm facing a problem that I can't find the cause of right now. This is the model design :

public class UserFriend
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }

    public List<FriendInfo> Friends { get; set; }

    public List<FriendInvite> FriendInvites { get; set; }
}

public class FriendInfo
{
    public string UserId { get; set; }

    public string FirstCharName { get; set; }

    public string Email { get; set; }

    public string FullName { get; set; }

    public string Avatar { get; set; }

    public DateTime FriendDay { get; set; }
}

public class FriendInvite
{
    public string Sender { get; set; }

    public string Email { get; set; }

    public string Introduction { get; set; }

    public string FullName { get; set; }

    public string CharName { get; set; }

    public string Avatar { get; set; }

    public DateTime Time { get; set; }
}

And I have a query to get all the user's friends sorted by FirstCharName with userId passed in:

var listall = await _userFriendsCollection.Aggregate()
                                .Match(x => x.Id == userId)
                                .Unwind<UserFriend>(new StringFieldDefinition<UserFriend>("Friends"))
                                .ReplaceRoot<FriendInfo>(new BsonValueAggregateExpressionDefinition<UserFriend, FriendInfo>(@"$Friends"))
                                .SortBy(o => o.FirstCharName)
                                .ToListAsync();

The query above returns me a result set : enter image description here but the problem here is when I use skip and limit . Specifically here, skip is 10 and limit is 10 :

var limit = await _userFriendsCollection.Aggregate()
                                            .Match(x => x.Id == userId)
                                            .Unwind<UserFriend>(new StringFieldDefinition<UserFriend>("Friends"))
                                            .ReplaceRoot<FriendInfo>(new BsonValueAggregateExpressionDefinition<UserFriend, FriendInfo>(@"$Friends"))
                                            .SortBy(o => o.FirstCharName)
                                            .Skip(skip)
                                            .Limit(take)
                                            .ToListAsync();

According to the list of all above, it should have taken record number 20 at the end of the list. but here record number 21 is in the returned result set : enter image description here

I don't know if there is a problem with my code or if it is due to the arrangement of alpha beta characters

Answer 1

SortBy is not a stable sort.

So rows with the same FirstCharName are not guaranteed to appear in the same order each time you run the query. This applies to the group of three 'V' records spanning the upper bound of your selection.

A quick fix would be to add .ThenBy(o => o.UserId) underneath the SortBy line to specify the ordering within the groups.