Pagination with C# Driver not working as expected

[kyi87_N_A]

Hello,
i have big problems with pagination with the C# Driver (Driver Version 2.18.0 MongoDb Version 5.0.5). I tried a way with the build in Linq (MongoDB.Driver.Linq) and the way over the Fluent-Framework.

The Problem is, after some invokes of my pagination method (see below) with the MongoDb-Linq, i got some duplicated values back. The duplication is not in the database itself, and also does not occure in a single invoke of the method. Before i invoke the pagination-method i do some sorting and filtering.

It seems the Problem lies in the Take Method, skip works as expected. I tried the examples also with the fluent-framework and got exact the same strange behaviour (limit instead of take, makes the problem).

Example Output without pagination and ordering and filtering (var result = await query.ToListAsync();):

    SP500;US23331A1097;D.R. HORTON
    DowJones;US4592001014;IBM
    SP500;US0200021014;ALLSTATE
    SP500;US4165151048;HARTFORD FINANCIAL SERVICES GROUP
    SP500;US42824C1099;HEWLETT PACKARD ENTERPRISE
    SP500;US5260571048;LENNAR
    SP500;US9113631090;UNITED RENTALS
    Nikkei225;JP3726800000;JAPAN TOBACCO
    Nikkei225;JP3942800008;YAMAHA MOTOR
    Nasdaq100;US3755581036;GILEAD SCIENCES
    Nasdaq100;US60770K1079;MODERNA
    SP500;US0010551028;AFLAC
    SP500;US03076C1062;AMERIPRISE FINANCIAL
    SP500;US09062X1037;BIOGEN
    SP500;US3024913036;FMC
    SP500;US3755581036;GILEAD SCIENCES
    SP500;US6703461052;NUCOR
    SP500;US74834L1008;QUEST DIAGNOSTICS
    SP500;US8760301072;TAPESTRY
    SP500;US9139031002;UNIVERSAL HEALTH SERVICES

MongoDB-Driver LINQ: → Works NOT as expected

        private async Task<List<T>> Paginate<T>(IMongoQueryable<T> query, PaginationData paginationData )
        {
            var list = await query.Skip(paginationData .PageNumber * paginationData .PageSize)
                .Take(paginationData .PageSize)
                .ToListAsync();
            return list;
        }

Pagination Output: → BIOGEN is duplicated and the order is wrong compared to output above

  ####### Pagenumber 0; Pagesize: 10
  SP500;US23331A1097;D.R. HORTON
  Nikkei225;JP3726800000;JAPAN TOBACCO
  DowJones;US4592001014;IBM
  SP500;US5260571048;LENNAR
  SP500;US0200021014;ALLSTATE
  SP500;US9113631090;UNITED RENTALS
  Nikkei225;JP3942800008;YAMAHA MOTOR
  SP500;US42824C1099;HEWLETT PACKARD ENTERPRISE
  SP500;US4165151048;HARTFORD FINANCIAL SERVICES GROUP
  **SP500;US09062X1037;BIOGEN**
  ####### Pagenumber 1; Pagesize: 10
  SP500;US6703461052;NUCOR
  Nasdaq100;US60770K1079;MODERNA
  SP500;US0010551028;AFLAC
  SP500;US8760301072;TAPESTRY
  SP500;US9139031002;UNIVERSAL HEALTH SERVICES
  SP500;US3024913036;FMC
  **SP500;US09062X1037;BIOGEN**
  Nikkei225;JP3436100006;SOFTBANK
  Nasdaq100;US3755581036;GILEAD SCIENCES
  SP500;US3755581036;GILEAD SCIENCES

My Workaround, which is pretty slow but gives the right results:

Standard LINQ: → Works as expected

        private async Task<List<T>> Paginate<T>(IMongoQueryable<T> query, PaginationData paginationData )
        {
            var result = await query.ToListAsync();
            var list = result.Skip(paginationData .PageNumber * paginationData .PageSize)
                .Take(paginationData .PageSize)
                .ToList();
            return list;
        }

Pagination Output: → No Duplicated Values → Output is like Output above.

  ####### Pagenumber 0; Pagesize: 10
  SP500;US23331A1097;D.R. HORTON
  DowJones;US4592001014;IBM
  SP500;US0200021014;ALLSTATE
  SP500;US4165151048;HARTFORD FINANCIAL SERVICES GROUP
  SP500;US42824C1099;HEWLETT PACKARD ENTERPRISE
  SP500;US5260571048;LENNAR
  SP500;US9113631090;UNITED RENTALS
  Nikkei225;JP3726800000;JAPAN TOBACCO
  Nikkei225;JP3942800008;YAMAHA MOTOR
  Nasdaq100;US3755581036;GILEAD SCIENCES
  ####### Pagenumber 1; Pagesize: 10
  Nasdaq100;US60770K1079;MODERNA
  SP500;US0010551028;AFLAC
  SP500;US03076C1062;AMERIPRISE FINANCIAL
  SP500;US09062X1037;BIOGEN
  SP500;US3024913036;FMC
  SP500;US3755581036;GILEAD SCIENCES
  SP500;US6703461052;NUCOR
  SP500;US74834L1008;QUEST DIAGNOSTICS
  SP500;US8760301072;TAPESTRY
  SP500;US9139031002;UNIVERSAL HEALTH SERVICES

Does anyone have an idea what the problem here is? Is it a bug in the driver?

Thanks in advance!

[kyi87_N_A]

I logged the queries to see what really goes to MongoDB.

This is the query which gives the right order:

{
    "aggregate": "LevermannScoreCollection",
    "pipeline": [
        {
            "$match": {
                "Date": "ISODate('2022-12-22T00:00:00Z')"
            }
        },
        {
            "$project": {
                "_v": "$Score",
                "_id": 0
            }
        },
        {
            "$unwind": "$_v"
        },
        {
            "$project": {
                "Isin": "$_v.Isin",
                "IndexType": "$_v.StockIndexType",
                "Result": "$$ROOT",
                "NameLower": {
                    "$toLower": "$_v.StockInformation.Name"
                },
                "_id": 0
            }
        },
        {
            "$sort": {
                "Result._v.TotalScore": -1
            }                                
        }
    ],
    "cursor": {},
    "$db": "StockAnalysisDB",
    "lsid": {
        "id": "CSUUID('2624c3dd-f150-4b71-bfad-8853aef347c4')"
    }
}

This is the query with pagination which gives the incorrect element order back:

{
    "aggregate": "LevermannScoreCollection",
    "pipeline": [
        {
            "$match": {
                "Date": "ISODate('2022-12-22T00:00:00Z')"
            }
        },
        {
            "$project": {
                "_v": "$Score",
                "_id": 0
            }
        },
        {
            "$unwind": "$_v"
        },
        {
            "$project": {
                "Isin": "$_v.Isin",
                "IndexType": "$_v.StockIndexType",
                "Result": "$$ROOT",
                "NameLower": {
                    "$toLower": "$_v.StockInformation.Name"
                },
                "_id": 0
            }
        },
        {
            "$sort": {
                "Result._v.TotalScore": -1
            }
        },
        {
            "$skip": "NumberLong(0)"
        },
        {
            "$limit": "NumberLong(10)"
        }
    ],
    "cursor": {},
    "$db": "StockAnalysisDB",
    "lsid": {
        "id": "CSUUID('2624c3dd-f150-4b71-bfad-8853aef347c4')"
     
}

I dont get it… Seems okay