.net通用CMS快速開發框架——問題：Dapper通用的多表聯合分頁查詢怎麼破？

最近在弄一個東東，類似那種CMS的後臺管理系統，方便作為其它項目的初始化框架用的。

現在遇到個問題，如標題所示：Dapper通用的多表聯合分頁查詢怎麼破？

單表的話很簡單就可以實現，多表不通用的話也可以很方便的實現，那麼如果多表通用的話，怎麼辦呢？

難道只能通過拼接sql或者使用存儲過程嗎？我先來展示下我的實現方式，希望你有更好的方式，然後同我分享一下，以便解決我的困擾。

因為本來就是做的傳統的CMS類似的項目，所以技術選型也是比較傳統，拋棄了mvvm的js框架、webapi介面、以及nosql緩存。

技術選型：MVC5、Mysql、Dapper、Autofac、Layui、阿裡巴巴矢量庫、T4（後面補上）。

• MVC5：目前.net開發的主流web框架。
• Mysql：輕量免費功能強大的關係型資料庫。
• Dapper：據說是性能最好的.net ORM框架。
• Autofac：據說是性能最好的.net IOC框架。
• Layui：經典的模塊化UI框架，還是很適合我們這樣的後端開發人員的。
• 阿裡巴巴矢量庫：豐富的矢量圖標庫。
• T4：強大的代碼生成模板。

我選擇的都是輕量級比較乾凈的東東來組合的框架。

我選擇由外入內的方式來闡述我現在遇到的問題。以用戶管理界面為例，我講只列出涉及到用戶分頁查詢的代碼，將會省略其它代碼.....

大致上的效果如下圖所示：

經典的多層架構

Global.asax.cs代碼，Dapper自動註入。

  public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            //創建autofac管理註冊類的容器實例
            var builder = new ContainerBuilder();
            SetupResolveRules(builder);
            //使用Autofac提供的RegisterControllers擴展方法來對程式集中所有的Controller一次性的完成註冊 支持屬性註入
            builder.RegisterControllers(Assembly.GetExecutingAssembly()).PropertiesAutowired();        

            // 把容器裝入到微軟預設的依賴註入容器中
            var container = builder.Build();
            DependencyResolver.SetResolver(new AutofacDependencyResolver(container));
        }
        private static void SetupResolveRules(ContainerBuilder builder)
        {
            //WebAPI只用引用services和repository的介面，不用引用實現的dll。
            //如需載入實現的程式集，將dll拷貝到bin目錄下即可，不用引用dll
            var iServices = Assembly.Load("RightControl.IService");
            var services = Assembly.Load("RightControl.Service");
            var iRepository = Assembly.Load("RightControl.IRepository");
            var repository = Assembly.Load("RightControl.Repository");

            //根據名稱約定（服務層的介面和實現均以Services結尾），實現服務介面和服務實現的依賴
            builder.RegisterAssemblyTypes(iServices, services)
              .Where(t => t.Name.EndsWith("Service"))
              .AsImplementedInterfaces().PropertiesAutowired();

            //根據名稱約定（數據訪問層的介面和實現均以Repository結尾），實現數據訪問介面和數據訪問實現的依賴
            builder.RegisterAssemblyTypes(iRepository, repository)
              .Where(t => t.Name.EndsWith("Repository"))
              .AsImplementedInterfaces().PropertiesAutowired();
        }
    }

BaseController：

   public class BaseController : Controller
    {// GET: Base
        public virtual ActionResult Index()
        {
            return View();
        }

UserController:

    public class UserController : BaseController
    {
        private IUserService service;

        public UserController(IUserService _service)
        {
            service = _service;
        }
        /// <summary>
        /// 載入數據列表
        /// </summary>
        /// <param name="pageInfo">頁面實體信息</param>
        /// <param name="filter">查詢條件</param>
        /// <returns></returns>
        [HttpGet]
        public JsonResult List(PageInfo pageInfo, UserModel filter)
        {
            var result = service.GetListByFilter(filter, pageInfo);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

PageInfo：

    public class PageInfo
    {
        public int page { get; set; }
        public int limit { get; set; }
        /// <summary>
        /// 排序欄位 CreateOn
        /// </summary>
        public string field { get; set; }
        /// <summary>
        /// 排序方式 asc desc
        /// </summary>
        public string order { get; set; }
        /// <summary>
        /// 返回欄位逗號分隔
        /// </summary>
        public string returnFields { get; set; }
        public string prefix { get; set; }
    }

UserModel：

using DapperExtensions;
using System;
using System.ComponentModel.DataAnnotations;

namespace RightControl.Model
{
    [Table("t_User")]
    public class UserModel:Entity
    {
        /// <summary>
        /// 用戶名
        /// </summary>
        [Display(Name = "用戶名")]
        public string UserName { get; set; }
        /// <summary>
        /// 真實名稱
        /// </summary>
        [Display(Name = "真實名稱")]
        public string RealName { get; set; }
        /// <summary>
        /// 密碼
        /// </summary>
        public string PassWord { get; set; }
        /// <summary>
        /// 創建者
        /// </summary>
        public int CreateBy { get; set; }
        /// <summary>
        /// 角色ID
        /// </summary>
        public int RoleId { get; set; }
        /// <summary>
        /// 更新時間
        /// </summary>
        [Display(Name = "更新時間")]
        public DateTime UpdateOn { get; set; }
        [Computed]
        public string RoleName { get; set; }
    }
}

Entity：

    public class Entity
    {
        [DapperExtensions.Key(true)]
        public virtual int Id { get; set; }
        /// <summary>
        /// 創建時間
        /// </summary>
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd HH:mm:ss}")]
        [Display(Name = "創建時間")]
        public DateTime CreateOn { get; set; }
        /// <summary>
        /// 菜單狀態(1：啟用，0：禁用）
        /// </summary>
        public bool Status { get; set; }
        #region 查詢條件
        [Computed]
        public string StartEndDate { get; set; }
        #endregion
    }

IBaseService：

    public interface IBaseService<T> where T : class, new()
    {
        dynamic GetListByFilter(T filter, PageInfo pageInfo);
    }

IUserService：

    public interface IUserService : IBaseService<UserModel>
    {
    ...
    }

BaseService:

    public abstract class BaseService<T> where T : class, new()
    {
        public IBaseRepository<T> baseRepository{get; set;}
public dynamic GetPageUnite(IBaseRepository<T> repository, PageInfo pageInfo, string where, object filter)
        {
            string _orderBy = string.Empty;
            if (!string.IsNullOrEmpty(pageInfo.field))
            {
                _orderBy = string.Format(" ORDER BY {0} {1}", pageInfo.prefix+pageInfo.field, pageInfo.order);
            }
            else
            {
                _orderBy = string.Format(" ORDER BY {0}CreateOn desc",pageInfo.prefix);
            }
            long total = 0;
            var list = repository.GetByPageUnite(new SearchFilter { pageIndex = pageInfo.page, pageSize = pageInfo.limit, returnFields = pageInfo.returnFields, param = filter, where = where, orderBy = _orderBy }, out total);

            return Pager.Paging(list, total);
        }
        protected  string CreateWhereStr(Entity filter, string _where)
        {
            if (!string.IsNullOrEmpty(filter.StartEndDate) && filter.StartEndDate != " ~ ")
            {
                var dts = filter.StartEndDate.Trim().Split('~');
                var start = dts[0].Trim();
                var end = dts[1].Trim();
                if (!string.IsNullOrEmpty(start))
                {
                    _where += string.Format(" and CreateOn>='{0}'", start + " 00:00");
                }
                if (!string.IsNullOrEmpty(end))
                {
                    _where += string.Format(" and CreateOn<='{0}'", end + " 59:59");
                }
            }

            return _where;
        }
    }

UserService:

    public class UserService: BaseService<UserModel>, IUserService
    {
        public IUserRepository repository { get; set; }//屬性註入
        public dynamic GetListByFilter(UserModel filter, PageInfo pageInfo)
        {
            pageInfo.prefix = "u.";
            string _where = " t_User u INNER JOIN t_role r on u.RoleId=r.Id";
            if (!string.IsNullOrEmpty(filter.UserName))
            {
                _where += string.Format(" and {0}UserName=@UserName",pageInfo.prefix);
            }
            if (!string.IsNullOrEmpty(pageInfo.order))
            {
                pageInfo.order = pageInfo.prefix + pageInfo.order;
            }
            pageInfo.returnFields = string.Format("{0}Id,{0}UserName,{0}RealName,{0}CreateOn,{0}`PassWord`,{0}`Status`,{0}RoleId,r.RoleName",pageInfo.prefix);
            return GetPageUnite(baseRepository, pageInfo, _where, filter);
        }

IBaseRepository:

 public interface IBaseRepository<T> where T : class, new()
    {
        IEnumerable<T> GetByPageUnite(SearchFilter filter, out long total);
    }

IUserRepository:

    public interface IUserRepository : IBaseRepository<UserModel>
    {

    }

BaseRepository:

    public class BaseRepository<T>: IBaseRepository<T> where T :class, new()
    {
public IEnumerable<T> GetByPageUnite(SearchFilter filter, out long total)
        {
            using (var conn = MySqlHelper.GetConnection())
            {
                return conn.GetByPageUnite<T>(filter.pageIndex, filter.pageSize, out total, filter.returnFields, filter.where, filter.param, filter.orderBy, filter.transaction, filter.commandTimeout);
            }
        }

    }

UserRepository:

    public class UserRepository : BaseRepository<UserModel>, IUserRepository
    {
    }

最後的分頁代碼：

        /// <summary>
        /// 獲取分頁數據
        /// </summary>
        public static IEnumerable<T> GetByPageUnite<T>(this IDbConnection conn, int pageIndex, int pageSize, out long total, string returnFields = null, string where = null, object param = null,
 string orderBy = null, IDbTransaction transaction = null, int? commandTimeout = null)
        {        
            int skip = 0;
            if (pageIndex > 0)
            {
                skip = (pageIndex - 1) * pageSize;
            }

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM {0};",  where);
            sb.AppendFormat("SELECT {0} FROM {1} {2}  LIMIT {3},{4}", returnFields, where, orderBy, skip, pageSize);
            using (var reader = conn.QueryMultiple(sb.ToString(), param, transaction, commandTimeout))
            {
                total = reader.ReadFirst<long>();
                return reader.Read<T>();
            }
        }

Index視圖：

@{
    Layout = "~/Views/Shared/_LayoutBase.cshtml";
}

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Table</title>
</head>

<body>
    <div class="admin-main">
            <blockquote class="layui-elem-quote p10">
                <div class="layui-form-item" style="margin-bottom:0px;">
                    <label class="layui-form-label">用戶名稱：</label>
                    <div class="layui-input-inline">
                        <input name="UserName" id="UserName" lay-verify="url" autocomplete="off" class="layui-input">
                    </div>
                    <a href="javascript:;" class="layui-btn layui-btn-sm" id="btnSearch"  data-type="reload">
                        <i class="layui-icon">&#xe615;</i> 搜索
                    </a>
                    <div style="float:right;">
                        <a href="javascript:;" class="layui-btn layui-btn-sm" id="btnAdd">
                            <i class="layui-icon">&#xe608;</i> 添加用戶
                        </a>
                    </div>
                </div>
            </blockquote>
            <div class="layui-field-box">
                <table id="defaultTable" lay-filter="defaultruv"></table>
                <script type="text/html" id="switchTpl">
                    {{#  if(d.Status == 1){ }}
                    <i class="layui-icon green">&#xe618;</i>啟用
                        {{#  } else { }}
                    <i class="layui-icon red" style="font-size:18px;">&#x1006;</i> 停用
                        {{#  } }}
                        <!-- 這裡的 checked 的狀態只是演示 -->
                        @*<input type="checkbox" name="Status"  value="{{d.Id}}"  lay-skin="switch" lay-text="開啟|禁用" lay-filter="statusSwitch" {{ d.Status == 1 ? 'checked' : '' }}>*@
</script>
                <script type="text/html" id="bar">
                    <a class="layui-btn layui-btn-normal layui-btn-xs" lay-event="detail">查看</a>
                    <a class="layui-btn layui-btn-xs" lay-event="edit">編輯</a>
                    <a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">刪除</a>
                </script>
            </div>
    </div>
    <script>
            layui.config({
                base: '/plugins/app/'
            });

            layui.use(['table', 'common', 'form'], function () {
                var table = layui.table,
                    form = layui.form,
                    common = layui.common;
                //表格
                table.render({
                    id: 'defaultReload'
                   , elem: '#defaultTable'
                   , height: 'full-112' //高度最大化減去差值
                   , url: '/Permissions/User/List' //數據介面
                   , page: true //開啟分頁
                   , cols: [[ //表頭
                     { checkbox: true, fixed: true },
                     { field: 'Id', title: 'Id', width: 80, fixed: 'left' }
                     , { field: 'UserName', title: '用戶名稱', sort: true }
                     , { field: 'RealName', title: '真實姓名' }
                     , { field: 'RoleName', title: '角色名稱' }
                     , { field: 'Status', title: '狀態', templet: '#switchTpl', unresize: true, width: 100, align:'center' }
                     , { field: 'CreateOn', title: '創建時間', sort: true, templet: '<div>{{showDate(d.CreateOn)}}</div>' }
                     , { field: '', title: '操作', toolbar: "#bar" }
                   ]]
                });
                var $ = layui.$, active = {
                    reload: function () {
                        //執行重載
                        table.reload('defaultReload', {
                            page: {
                                curr: 1 //重新從第 1 頁開始
                            }
                          , where: {
                              UserName: $("#UserName