Entity Framework paging, that is to say Skip/Take, doesn't work for large tables

[ststeiger]

The definiton of skip and take doesn't allow for more than 2.147483648 billion rows

Int32.MaxValue = 2'147'483'648

public static IQueryable<TSource> Skip<TSource>(this IQueryable<TSource> source, int count);
public static IQueryable<TSource> Take<TSource>(this IQueryable<TSource> source, int count);

Theoretically, you could skip more than that:

// Some init
List<Person> persons = new List<Person>();
List<Person> resultList = persons;
long bigNumber = 3 * (long)int.MaxValue + 12;

while (bigNumber > int.MaxValue)
{
    resultList = resultList.Skip(int.MaxValue).ToList();
    bigNumber -= int.MaxValue;
}
resultList = resultList.Skip(int.MaxValue).ToList();
// Then what do what you want with this result list

But that doesn't work either because EF internally tracks the skip count as an int.
Why even int and not uint ? Can't skip or take less than zero.

https://stackoverflow.com/questions/32309807/how-use-long-type-for-skip-in-linq

        protected int CalculatePages(int totalCount, int pageSize)
        {

            return ((int)(totalCount / pageSize)) + ((totalCount % pageSize) > 0 ? 1 : 0);
        }

        protected long CalculatePages(long totalCount, long pageSize)
        {
            return ((long)(totalCount / pageSize)) + ((totalCount % pageSize) > 0 ? 1L : 0L);
        }


        //public int GetNumPages<TEntity>(int pageSize) where TEntity : class
        //{
        //    int totalCount = this.m_ctx.Set<TEntity>().Count();
        //    return CalculatePages(totalCount, pageSize);
        //}


        public long GetNumPages<TEntity>(long pageSize) where TEntity : class
        {
            // int totalCount = this.m_ctx.Set<TEntity>().Count();
            long totalCount = this.m_ctx.Set<TEntity>().LongCount();
            return CalculatePages(totalCount, pageSize);
        }

[ajcvickers]

@ststeiger EF Core doesn't define the Skip and Take methods--these are defined in the BCL as part of LINQ.

That being said, using Skip/Take for paging this amount of data will likely perform poorly. See #9115 and the links it contains for a different approach that we would like to support in some way in a future release of EF Core.

I'm curious if you have tried the Skip/Take approach with close to int.Max values and, if so, what the performance is like?

Leaving this open for now to discuss whether we should update the internal code to store the skip as a long.

[smitpatel]

Leaving this open for now to discuss whether we should update the internal code to store the skip as a long.

We don't store them. We always parametrize arg of skip/take. Internally SQL tree contains Expression to represent value of skip/take. Which are not tied to any type let alone int.

[ajcvickers]

Triage: we will investigate any issues around multiple Skip calls and then decide what to do.