使用entity framework 实现分页的小例子

后台代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace LeeCMS.测试
{
    /// <summary>
    /// 返回特定列的概念模型
    /// 返回特定列，减少数据传输量，提高性能。
    /// </summary>
    public class ArticleListModel
    {
        public int ID { get; set; }
        public string Title { get; set; }
        public int? CategoryID { get; set; }
    }

    public partial class _1 : System.Web.UI.Page
    {
        //定义分页所需变量
        public int nPage = 1;           //当前页
        public int nRecordCount = 0;    //记录总数
        public int nPageSize = 10;      //每页记录数
        public int nPageCount = 0;      //总页数
        public string nKeywords = string.Empty; //关键词
        
        protected void Page_Load(object sender, EventArgs e)
        {

            nPage = Common.Utils.ToInt(Request["page"],1); //获取当前页码
            nKeywords = (Request["keyword"] == null) ? string.Empty : Request["keyword"];
            var list = GetInfos(nPage, nPageSize, nKeywords,out nPage, out nPageCount, out nRecordCount);

            Response.Write("请在地址栏中修改page，keyword参数实现分页<br/>");
            Response.Write("当前页码：" + nPage + "；总页数：" + nPageCount + "总记录数：" + nRecordCount + "<br/>");
            foreach (var item in list)
            {
                Response.Write("id:" + item.ID + "_Title" + item.Title + "<br/>");
            }

        }

        /// <summary>
        /// 获取当前页的记录 并输出 当前页，总页数，总记录数
        /// 当linq遇到 ToList() 、 Count() 、 foreach 时才执行sql语句，在此之前可以进行linq拼装。
        /// </summary>
        /// <param name="nPage">当前页</param>
        /// <param name="nPageSize">页大小</param>
        /// <param name="nCurrentPage">当前页</param>
        /// <param name="nPageCount">总页数</param>
        /// <param name="nRecordCount">总记录数</param>
        /// <returns></returns>
        public IList<ArticleListModel> GetInfos(int nPage,int nPageSize,string nKeywords,out int nCurrentPage,out int nPageCount,out int nRecordCount)
        {
            DAL.dbEntities db = new DAL.dbEntities();

            IQueryable<ArticleListModel> a = from b in db.Article
                                             select new ArticleListModel
                                             {
                                                 ID = b.ID,
                                                 Title = b.Title,
                                                 CategoryID = b.CategoryID
                                             };
           
            //根据关键词查找
            if (!string.IsNullOrEmpty(nKeywords))
            {
                a = a.Where(c => c.Title.Contains(nKeywords));
            }
            //根据分类id搜索
            IList<int> cats = new List<int>() { 1, 2 }; //分类id列表
            a = a.Where(c => cats.Contains((int)c.CategoryID));
            //排序
            a = a.OrderByDescending(m => m.ID);
            nRecordCount = a.Count(); //总记录数 --执行sql
            //总页数
            if (nRecordCount % nPageSize == 0)
            {
                nPageCount = nRecordCount / nPageSize;
            }
            else
            {
                nPageCount = (nRecordCount / nPageSize) + 1;
            }
            //当前页
            nCurrentPage = nPage;
            if (nCurrentPage > nPageCount)
            {
                nCurrentPage = nPageCount;
            }
            //获取本页数据
            a = a.Skip((nCurrentPage - 1) * nPageSize).Take(nPageSize);
            var list = a.ToList();  //执行sql
            return list;
        }


    }
}

 

接下来看看entity framework 生成的sql 语句

exec sp_executesql N'SELECT TOP (10) 
[Project1].[ID] AS [ID], 
[Project1].[Title] AS [Title], 
[Project1].[CategoryID] AS [CategoryID]
FROM ( SELECT [Project1].[ID] AS [ID], [Project1].[CategoryID] AS [CategoryID], [Project1].[Title] AS [Title], row_number() OVER (ORDER BY [Project1].[ID] DESC) AS [row_number]
    FROM ( SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[CategoryID] AS [CategoryID], 
        [Extent1].[Title] AS [Title]
        FROM [dbo].[Article] AS [Extent1]
        WHERE ([Extent1].[Title] LIKE @p__linq__0 ESCAPE N''~'') AND ([Extent1].[CategoryID] IN (1,2))
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 10
ORDER BY [Project1].[ID] DESC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'%��%'