ASP.NET MVC 查询加分页

使用了LinqKit、PagedList.Mvc、EntityFramework 等DLL 直接使用nuget安装即可。

1.表模型：

using System.ComponentModel.DataAnnotations;

namespace StoreDB.Models
{
    public class t_bd_item_info
    {
        [Key] //主键 
        public string item_no { get; set; }

        public string item_name { get; set; }

        public decimal price { get; set; }

        //public DateTime? build_date { get; set; }
    }
}

DbContext：

using StoreDB.Models;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

namespace StoreDB
{
    public class StoreDbContext: DbContext
    {
        public StoreDbContext()
            : base("name=StoreDbContext")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //移除复数表名
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            base.OnModelCreating(modelBuilder);
        }

        public virtual DbSet<t_bd_item_info> t_bd_item_info { get; set; }
    }
}

2.HomeController增加一个SearchIndex Action:

using LinqKit;
using PagedList;
using StoreDB;
using StoreDB.Models;
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Web.Mvc;

namespace WebQueryAndPage.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return Redirect("/Home/SearchIndex");
            //return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }

        public ActionResult SearchIndex(string ItemNo, string ItemName, int page = 1)
        {
            try
            {
                //为true 默认加载所有
                Expression<Func<t_bd_item_info, bool>> where = PredicateBuilder.New<t_bd_item_info>(true);

                //动态拼接查询条件
                if (!string.IsNullOrWhiteSpace(ItemNo))
                {
                    where = where.And(x => x.item_no == ItemNo);
                }
                if (!string.IsNullOrWhiteSpace(ItemName))
                {
                    where = where.And(x => x.item_name.Contains(ItemName));
                }
                StoreDbContext dbContext = new StoreDbContext();

                //直接ToPagedList，不要使用ToList
                var mylist = dbContext.t_bd_item_info.Where(where).OrderBy(x => x.item_no).ToPagedList(page, 10);

                return View(mylist);
            }
            catch (Exception ex)
            {
                return Content(ex.Message);
            }
        }
    }
}

这里使用到了PredicateBuilder （LinqKit），来动态拼接查询条件。ToPagedList （PagedList）来分页。

3.为SearchIndex Action 增加一个视图SearchIndex.cshtml页面：

@*@model IEnumerable<StoreDB.Models.t_bd_item_info>*@

@model IPagedList<StoreDB.Models.t_bd_item_info>
@using PagedList;
@using PagedList.Mvc;
@{
    ViewBag.Title = "SearchIndex";
}

<h2>SearchIndex</h2>

@using (Html.BeginForm("SearchIndex", "Home", FormMethod.Get, new { id = "OrderForm" }))
{
    <p>
        货号： @Html.TextBox("ItemNo")
        商品名称： @Html.TextBox("ItemName")
        <input type="submit" value="查询" />
    </p>
}


<table class="table">
    <tr>
        <th>            
            货号
        </th>
        <th>            
            商品名称
        </th>
        <th>
            @*@Html.DisplayNameFor(model => model.price)*@
            单价
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.item_no)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.item_name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.price)
        </td>
        <td>
            @*@Html.ActionLink("Edit", "Edit", new { id=item.item_no }) |
            @Html.ActionLink("Details", "Details", new { id=item.item_no }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.item_no })*@
        </td>
    </tr>
}

</table>


@Html.PagedListPager(Model, page => Url.Action("SearchIndex", new { page, ItemNo = Request["ItemNo"], ItemName = Request["ItemName"] }))