Are you sure you really will for the pages you often contact

Time:2021-10-15

For coders who have been struggling in the “front line” of crud, they face all kinds of crud code every day. However, after writing so many cruds, are you sure you often have to topage and getpage? Are you sure there is no problem? So today I’ll throw a brick (it’s only valid under SQL server, but it’s the same with other databases by visual inspection)

Generally speaking, everyone will encapsulate a paging method, which is a skill that every developer will have. For EF me today, let’s talk about the correct paging posture and start with the paging code

Generally, we will define a paging return object. Of course, you can also use out to return count

/// 
    ///Paging collection
    /// 
    /// 
    public class PagedResult
    {

        #region Ctor
        /// 
        ///Initialize a new instance of type pagedresult {t}.
        /// 
        ///Total records.
        ///Data for the current page.
        public PagedResult(List data, int total)
        {
            this.Total = total;
            this.Data = data;
        }
        #endregion

        #region Public Properties
        /// 
        ///Gets or sets the total number of records.
        /// 
        public int Total { get; set; }
        /// 
        ///Paging data
        /// 
        public List Data { get; set; }
        #endregion
    }

With this code, we will generally encapsulate the paging method of iqueryable, and the previous simple version

public static async Task> ToPageResultAsync(this IQueryable source, int pageIndex, int pageSize)
        {
            //Set how many pages to get each time
            var take = pageSize <= 0 ? 1 : pageSize;
            //Set current page number to minimum 1
            var index = pageIndex <= 0 ? 1 : pageIndex;
            //How many pages need to be skipped
            var skip = (index - 1) * take;
            //Get total records per
            var count = await source.CountAsync();
            var data = await source.Skip(skip).Take(take).ToListAsync();
            return new PagedResult(data, count);
            
            
        }

In this way, the paging code of the first version is encapsulated, but many people will stop here for this familiar method. After all, over optimization is stupid, but we will find a very important optimization point that many people will ignore is meaningless query, and directly connect to the second version

public static async Task> ToPageResultAsync(this IQueryable source, int pageIndex, int pageSize)
        {
            //Set how many pages to get each time
            var take = pageSize <= 0 ? 1 : pageSize;
            //Set current page number to minimum 1
            var index = pageIndex <= 0 ? 1 : pageIndex;
            //How many pages need to be skipped
            var skip = (index - 1) * take;
            //Get total records per
            var count = await source.CountAsync();
            
            //When the number of databases is less than the number of entries to skip, it means that there is no data and the list is not in the query
            if (count <= skip)
                return new PagedResult(new List(0), count);
            var data = await source.Skip(skip).Take(take).ToListAsync();
            return new PagedResult(data, count);
            
        }

Careful spray friends may find that just one more judgment can reduce the jumping of large pages, but in this case, we will find that if there is a large amount of data, such as millions, a single simple query will make you feel low performance. Why does it take so long to query and return a piece of data, but it becomes faster to return multiple data, In fact, there is a problem here when you

When only one database result is returned, if you use top 2, he will always find the data under count until two are met (personal guess), so let’s optimize the paging code

public static async Task> ToPageResultAsync(this IQueryable source, int pageIndex, int pageSize)
        {
            //Set how many pages to get each time
            var take = pageSize <= 0 ? 1 : pageSize;
            //Set current page number to minimum 1
            var index = pageIndex <= 0 ? 1 : pageIndex;
            //How many pages need to be skipped
            var skip = (index - 1) * take;
            //Get total records per
            var count = await source.CountAsync();
            
            //When the number of databases is less than the number of entries to skip, it means that there is no data and the list is not in the query
            if (count <= skip)
                return new PagedResult(new List(0), count);
            //Get the number of remaining entries
            int remainingCount = count - skip;
            //When the number of remaining pieces is less than the number of takes, the remainingcount is taken
            var realTake = remainingCount < take ? remainingCount : take;
            var data = await source.Skip(skip).Take(realTake).ToListAsync();
            return new PagedResult(data, count);
        }

When the number of remaining entries in the database minus the corresponding number of skips, if the remaining number is not enough for this PageSize, you no longer need to obtain data by PageSize. Therefore, for this query, only realtake is applicable. So far, the correct posture of paging is displayed. If this article is helpful to you, please give me a praise. Thank you