EF调用存储过程实现分页

1、通用分页代码

public static List<T> SelectPageList<T>(string sqlstr, int pageIndex, int pagesize, string orderByField, ref int totalCount) where T : class

    {

           SqlParameter[] spm = new SqlParameter[5];

           spm[0] = new SqlParameter("@Sql", sqlstr);

           spm[1] = new SqlParameter("@PageIndex", pageIndex);

           spm[2] = new SqlParameter("@PageSize", pagesize);

           spm[3] = new SqlParameter("@OrderByField", orderByField);

           spm[4] = new SqlParameter("@TotalRecord", totalCount);

           spm[4].Direction = ParameterDirection.Output;          

         var data = db.Database.SqlQuery<T>("exec Pro_PageProcedure @Sql,@PageIndex,@PageSize,@OrderByField,@TotalRecord out", spm).ToList();

           totalCount = Convert.ToInt32(spm[4].Value.ToString());           

return data;

     }

2、通用的SQL分页存储过程

CREATE PROCEDURE [dbo].[Pro_PageProcedure]

 @Sql nvarchar(max),        --表名

 @PageIndex int = 1 ,            --指定当前为第几页

 @PageSize int,                    --每页多少条记录

 @OrderByField nvarchar(1000),        --row_number需要的排序字段

 @TotalRecord int output           --返回总页数 

as

    Declare @_sql nvarchar(max);    

    --计算总记录数         

    set @_sql = 'select @TotalRecord = count(*) from (' + @Sql + ') a'

 

   EXEC sp_executesql @_sql,N'@TotalRecord int OUTPUT',@TotalRecord OUTPUT--计算总记录数           

    

    Declare @StartRecord int

    Declare @EndRecord int

    

    set @StartRecord = (@pageIndex-1)*@PageSize + 1

    set @EndRecord = @StartRecord + @pageSize - 1

    

   set @_sql ='select * from ( select ROW_NUMBER() Over(' + @OrderByField + ') as _ttRowId,* from (' +  @Sql + ') _tt0 ) _tt1 '

   set @_sql = @_sql + 'where _ttRowId between ' + CAST(@StartRecord as nvarchar) + ' and ' + CAST(@EndRecord as nvarchar)

        

    Exec(@_sql)

3、调用示例

/// <summary>

        /// 获取分页列表

        /// </summary>

        /// <returns></returns>

        public List<FixedAnswer> GetPageList(int pageIndex, int pageSize, ref int totalCount)

        {            

            string strSql = "select * from FixedAnswer";

            string orderfied = "order by id desc";

            return SelectPageList<FixedAnswer>(strSql, pageIndex, pageSize, orderfied, ref totalCount);

        }