【ASP.NET】多条件查询

在网页中数据都是进行分页获取，同时包含字段的模糊搜索等，这就多条件查询

首先在业务逻辑中添加方法，由于查询的参数可能不固定，我们可以先定义个参数类

创建一个参数基类

public class BaseParam
{
    public int PageIndex { get; set; }
    public int PageSize { get; set; }
    public int Total { get; set; }
}

用户查询参数类

public class UserQueryParam : BaseParam
{
    public string SchName { get; set; }
    public string SchRemark { get; set; }
}

业务逻辑中

接口

public partial interface IUserInfoService : IBaseService<UserInfo>
{
    IQueryable<UserInfo> LoadPageData(Model.Param.UserQueryParam userQueryParam);
}

用户数据查询

public partial class UserInfoService : BaseService<UserInfo>, IUserInfoService
{
    public IQueryable<UserInfo> LoadPageData(UserQueryParam userQueryParam)
    {
        short normalFlag = (short)Model.DelFlagEnum.Normal;
        var temp = DbSession.UserInfoDal.GetEntities(u => u.DelFlag == normalFlag);

        //过滤
        if(string.IsNullOrEmpty(userQueryParam.SchName))
        {
            temp = temp.Where(u => u.Name.Contains(userQueryParam.SchName)).AsQueryable();
        }

        if (string.IsNullOrEmpty(userQueryParam.SchRemark))
        {
            temp = temp.Where(u => u.Name.Contains(userQueryParam.SchRemark)).AsQueryable();
        }

        userQueryParam.Total = temp.Count();
        
        //分页
        return temp.OrderBy(u => u.Id)
            .Skip(userQueryParam.PageSize * (userQueryParam.PageIndex - 1))
            .Take(userQueryParam.PageSize).AsQueryable();
    }
}

在Controller中

public ActionResult GetAllUserInfo()
{
    int pageSize = 10;
    int pageIndex = 1;
    int total = 0;

    string schName = Request["schName"];
    string schRemark = Request["schRemark"];

    UserQueryParam userQueryParam = new UserQueryParam
    {
        PageIndex = pageIndex,
        PageSize = pageSize,
        Total = total,
        SchName = schName,
        SchRemark = schRemark
    };

    var pageData = UserInfoService.LoadPageData(userQueryParam)
         .Select(u => new { u.Id, u.Name, u.Pwd, u.Remark, u.ShowName, u.ModfiedOn, u.SubTime });

    var data = new { total = userQueryParam.Total, rows = pageData.ToList() };

    return Json(data, JsonRequestBehavior.AllowGet);
}