Git.Framework 框架隨手記--ORM查詢資料集合二

賀臣發表於2014-05-15

　　一. 分組查詢

　　　　在SQL中使用Group By 來對資料分組,在實際中分組中一般與聚合函式一併使用。在Git.Framework中提供了相應的分組方法

DataTable Group(T entity);
DataTable Group(T entity, bool isOpenTrans);
IEnumerable<System.Linq.IGrouping<TKey, T>> Group<TKey>(T entity, Func<T, TKey> keySelector);

　　　　對某張表進行分組查詢需要使用Group方法, 在分組的時候必須指定分組的欄位。在Entity類中有一個方法Group方法，這個方法就是用於指定分組的欄位.

AdminEntity entity = new AdminEntity();
entity.Group(a => a.CreateUser);
DataTable table = this.Admin.Group(entity);

　　　　上面這段程式碼是使用CreateUser欄位對錶進行分組,返回兩個欄位: 一個CreateUser,一個是分組的行數

AdminEntity entity = new AdminEntity();
entity.Group(a => new { a.CreateUser,a.LoginCount});
DataTable table = this.Admin.Group(entity);

　　　　上面這段程式碼是根據兩個欄位分組,DataTable中返回三個欄位,由上可知.

IEnumerable<System.Linq.IGrouping<TKey, T>> Group<TKey>(T entity, Func<T, TKey> keySelector);

　　　　這是一個高大上的方法,上面返回DataTable實屬無奈，技藝當時還不精，所以只能硬生生的這麼返回值。這個泛型的方法有點意思，也有點高階了，做Linq 的都知道。沒錯這個和Linq to SQL中的效果是一樣的。返回一個Key--集合的模式.(對於動態型別目前還是升級中)

var query= this.Admin.Group(entity, a => new {a.UserCode });
foreach (var item in query)
{
       string key=item.Key.UserCode;
       int count = item.Count();
}

　　二. 連線方法

　　　　在SQL Server中使用Left join, Right join 等關鍵字用於來連線查詢,這個對於一個碼農來說已經簡單的不能再簡單了的事情. 下面先看看一個做連結查詢的規則問題

SELECT t0.[ID],t0.[UserName],t0.[PassWord],t0.[UserCode],t0.[RealName],t0.[Email],t0.[Mobile],t0.[Phone],t0.[CreateTime],t0.[CreateIp],t0.[CreateUser],t0.[LoginCount],t0.[Picture],t0.[UpdateTime],t0.[IsDelete],t0.[Status],t0.[DepartNum],t0.[ParentCode],t0.[RoleNum],t0.[Remark],t1.[RoleName] AS RoleName 
FROM [dbo].[Admin] AS t0  
LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum]

　　　使用關鍵字LEFT JOIN [Table] ON 條件

　　　在Entity中提供了幾個用於連線查詢的方法.

public void Inner<T>(T entity, params Git.Framework.DataTypes.Params<string, string>[] param) where T : BaseEntity;
public void Left<T>(T entity, params Git.Framework.DataTypes.Params<string, string>[] param) where T : BaseEntity;
public void Right<T>(T entity, params Git.Framework.DataTypes.Params<string, string>[] param) where T : BaseEntity;

　　　　看到上面幾個方法可能有點發暈,先來解釋一下具體是怎麼回事。

　　　　T 是一個實體對映物件,就是資料庫中對映的實體類, T entity 相當於Left Join中的右表

　　　　params Git.Framework.DataTypes.Params<string, string>[] param 這個又有點奇怪了,這個是用於指定左右表的連線欄位，不明白先看看下面這個類

[Serializable]
    public class Params<T1>
    {
        public T1 Item1 { get; set; }

        public Params()
        {

        }
    }

    [Serializable]
    public class Params<T1, T2>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }

        public Params()
        {

        }
    }

    [Serializable]
    public class Params<T1, T2, T3>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }

        public Params()
        {

        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }

        public Params()
        {
        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4, T5>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }
        public T5 Item5 { get; set; }

        public Params()
        {
        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4, T5,T6>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }
        public T5 Item5 { get; set; }
        public T6 Item6 { get; set; }

        public Params()
        {
        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4, T5, T6,T7>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }
        public T5 Item5 { get; set; }
        public T6 Item6 { get; set; }
        public T7 Item7 { get; set; }

        public Params()
        {
        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4, T5, T6,T7,T8>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }
        public T5 Item5 { get; set; }
        public T6 Item6 { get; set; }
        public T7 Item7 { get; set; }
        public T8 Item8 { get; set; }

        public Params()
        {
        }
    }

Params<> 泛型類

　　　　很熟悉是不是,.NET4.0中有一個一樣的類,沒錯是一樣的。只不過在寫這個框架的時候還沒有這個版本,所以現在就一直保留了。

　　　　Params<string, string> 說明有兩個位string 型別的欄位，一個用於指明左表的欄位名第二個用於指定右表的欄位名

　　　　params Git.Framework.DataTypes.Params<string, string>[] param 而這個是陣列說明可以自定多個關聯欄位也就是on後面的條件

　　　　params 的作用是幹什麼的，不懂，自己去查。

AdminEntity entity = new AdminEntity();
entity.IncludeAll();
entity.Where(a => a.UserName == userName).And(a => a.PassWord == passWord);
SysRoleEntity roleEntity = new SysRoleEntity();
roleEntity.Include("RoleName", "RoleName");
entity.Left<SysRoleEntity>(roleEntity, new Params<string, string>() { Item1 = "RoleNum", Item2 = "RoleNum" });
entity = this.Admin.GetSingle(entity);

　　　　上面的程式碼就是一個的做連結查詢案例，分別從表Admin中查詢了所有欄位，在SysRole表中查詢了欄位RoleName,並且將此欄位重新命名為RoleName,和原欄位名一樣。

SELECT t0.[ID],t0.[UserName],t0.[PassWord],t0.[UserCode],t0.[RealName],t0.[Email],t0.[Mobile],t0.[Phone],t0.[CreateTime],t0.[CreateIp],t0.[CreateUser],t0.[LoginCount],t0.[Picture],t0.[UpdateTime],t0.[IsDelete],t0.[Status],t0.[DepartNum],t0.[ParentCode],t0.[RoleNum],t0.[Remark],t1.[RoleName] AS RoleName FROM [dbo].[Admin] AS t0  LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum]  WHERE t0.[UserName]=@0_t0_UserName AND t0.[PassWord]=@1_t0_PassWord

　　　　重新命名的部分請檢視: t1.[RoleName] AS RoleName

LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum]

　　　　做連結查詢某張表,並且使用欄位RoleNum 關聯,在Admin表中存在一個欄位RoleNum 在SysRole表中存在一個RoleNum欄位,主外來鍵關係。

new Params<string, string>() { Item1 = "RoleNum", Item2 = "RoleNum" }

　　　　這一句程式碼是連線關聯欄位的核心，指定連線查詢指定的管理欄位，如果這個傳入一個陣列那麼on 的後面將用and連線兩個表示式

　　　　上面的三個方法大同小異用法都一樣，但是要注意以下包含查詢的欄位不能重複，如果有重複欄位那麼一定要指定別名以示區分

　　三. 連線查詢的實質

　　　　上面專門講到了分頁查詢，上面的連線查詢都可以使用到分頁查詢，在某種程度上這種抽象還是值得稱讚的，對於物件操作層的資料和實際的資料查詢是完全隔離的，都是通過內聯的方式轉化的。

public List<AdminEntity> GetList(AdminEntity entity, ref PageInfo pageInfo)
{
     entity.IncludeAll();
     entity.OrderBy(a => a.ID, EOrderBy.DESC);
     entity.Where(a => a.IsDelete == (int)EIsDelete.NotDelete);
     SysRoleEntity roleEntity = new SysRoleEntity();
     roleEntity.Include("RoleName", "RoleName");
     entity.Left<SysRoleEntity>(roleEntity, new Params<string, string>() { Item1 = "RoleNum", Item2 = "RoleNum" });
     SysDepartEntity departEntity = new SysDepartEntity();
     departEntity.Include("DepartName", "DepartName");
     entity.Left<SysDepartEntity>(departEntity, new Params<string, string>() { Item1 = "DepartNum", Item2 = "DepartNum" });
     int rowCount = 0;
     List<AdminEntity> listResult = this.Admin.GetList(entity, pageInfo.PageSize, pageInfo.PageIndex, out rowCount);
     pageInfo.RowCount = rowCount;
     return listResult;
}

　　　　程式碼看起來有點煩，但是相比ADO.NET 好像又簡化了很多。上面是一個連線查詢並且分頁的過程，看看那具體的SQL程式碼

DECLARE @StartIndex INT SET @StartIndex = 1 SELECT @RecordCount=COUNT(*) FROM [dbo].[Admin] AS t0  LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum] LEFT JOIN [dbo].[SysDepart] AS t2 ON  t0.[DepartNum]=t2.[DepartNum]  WHERE t0.[IsDelete]=@0_t0_IsDelete IF (@PageIndex<=1) BEGIN SET @PageIndex=1 END SET @StartIndex = ( @PageIndex - 1 ) * @PageSize + 1 ; WITH TempTable AS( SELECT ROW_NUMBER() OVER (ORDER BY t0.[ID] DESC) RowNumber,t0.[ID],t0.[UserName],t0.[PassWord],t0.[UserCode],t0.[RealName],t0.[Email],t0.[Mobile],t0.[Phone],t0.[CreateTime],t0.[CreateIp],t0.[CreateUser],t0.[LoginCount],t0.[Picture],t0.[UpdateTime],t0.[IsDelete],t0.[Status],t0.[DepartNum],t0.[ParentCode],t0.[RoleNum],t0.[Remark],t1.[RoleName] AS RoleName,t2.[DepartName] AS DepartName FROM [dbo].[Admin] AS t0  LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum] LEFT JOIN [dbo].[SysDepart] AS t2 ON  t0.[DepartNum]=t2.[DepartNum]  WHERE t0.[IsDelete]=@0_t0_IsDelete ) SELECT * FROM TempTable WHERE RowNumber BETWEEN (@StartIndex) AND (@PageIndex * @PageSize)

　　　　貌似還可以哦,完成了我們想要的東西，返回了總行數並且返回了當前頁的資料。

　　　　看一個超級複雜的連線分頁查詢。

Stopwatch watch = new Stopwatch();
                watch.Reset();
                watch.Start();
                T_EXECUTEEntity entity = new T_EXECUTEEntity();
                entity.IncludeAll();
                int rowCount = 0;
                //計劃
                T_PLANEntity plan = new T_PLANEntity();
                plan.Include(a => new { PlanNumber = a.plan_number, PlanFinishQty = a.plan_finish_qty, OrderDetailID = a.order_detail_id, PlanQty = a.plan_qty });
                if (!planNumber.IsEmpty())
                {
                    plan.Where("plan_number", ECondition.Like, "%" + planNumber + "%");
                }
                entity.Left<T_PLANEntity>(plan, new Params<string, string>() { Item1 = "plan_id", Item2 = "plan_id" });
                //產品
                T_PARTEntity part = new T_PARTEntity();
                part.Include(a => new { PartName = a.part_name, PartSpecification = a.part_specification, PartDisplay = a.part_display });
                if (!PartName.IsEmpty())
                {
                    part.Where("part_name", ECondition.Like, "%" + PartName + "%");
                }
                entity.Left<T_PARTEntity>(part, new Params<string, string>() { Item1 = "part_id", Item2 = "part_id" });

                //裝置 可以不連線
                
                T_DEVICEEntity device = new T_DEVICEEntity();
                device.Include(a => new { DeviceName = a.device_name, DeviceNumber = a.device_number });
                //entity.Left<T_DEVICEEntity>(device, new Params<string, string>() { Item1 = "device_id", Item2 = "device_id" });
                entity.Left<T_DEVICEEntity>(device, new Params<string, string>() { Item1 = "device_number", Item2 = "device_number" });


                //工序 可以不連線
                T_PROCEDUREEntity procedure = new T_PROCEDUREEntity();
                procedure.Include(a => new { ProcedureName = a.procedure_name });
                entity.Left<T_PROCEDUREEntity>(procedure, new Params<string, string>() { Item1 = "procedure_id", Item2 = "procedure_id" });


                //作業時間
                T_EXECUTE_TIMEEntity entityTime = new T_EXECUTE_TIMEEntity();
                entityTime.Include(a => new { ETHours = a.et_hours });
                entity.Left<T_EXECUTE_TIMEEntity>(entityTime, new Params<string, string>() { Item1 = "execute_id", Item2 = "execute_id" });
                
                
                if (strStatus == 0)
                {
                    entity.Where("execute_end_time", ECondition.Is, null);
                    if (!WorkGroup.IsEmpty())
                    {
                        entity.And("creator", ECondition.Like, "%" + WorkGroup + "%");
                    }
                }
                else if (strStatus == 1)
                {
                    entity.Where("execute_end_time", ECondition.IsNot, null);
                    if (!WorkGroup.IsEmpty())
                    {
                        entity.And("creator", ECondition.Like, "%" + WorkGroup + "%");
                    }
                }
                else
                {
                    if (!WorkGroup.IsEmpty())
                    {
                        entity.Where("creator", ECondition.Like, "%" + WorkGroup + "%");
                    }
                }
                if (!beginTime.IsEmpty() && !endTime.IsEmpty())
                {
                    DateTime begin = ConvertHelper.ToType<DateTime>(beginTime);
                    DateTime end = ConvertHelper.ToType<DateTime>(endTime);
                    entity.Where<T_EXECUTEEntity>("execute_start_time", ECondition.Between, begin, end);

                }

                T_ORDER_DETAILEntity orderDetail = new T_ORDER_DETAILEntity();
                plan.Left<T_ORDER_DETAILEntity>(orderDetail, new Params<string, string>() { Item1 = "order_detail_id", Item2 = "order_detail_id" });
                orderDetail.OrderBy(a => a.order_detail_id, EOrderBy.DESC);

                T_PARTEntity par = new T_PARTEntity();
                par.Include(a => new { PartPictureNum = a.part_picture_number });
                orderDetail.Left<T_PARTEntity>(par, new Params<string, string>() { Item1 = "part_id", Item2 = "part_id" });
                if (!PicNum.IsEmpty())
                {
                    par.Where("part_picture_number", ECondition.Like, "%" + PicNum + "%");
                }

                entity.OrderBy(a=>a.plan_id,EOrderBy.DESC);
                List<T_EXECUTEEntity> listResult = this.T_EXECUTE.GetList(entity, pageInfo.PageSize, pageInfo.PageIndex, out rowCount);
                pageInfo.RowCount = rowCount;

                watch.Stop();
                log.Info("**************************************"+watch.ElapsedMilliseconds.ToString());

                return listResult;

超級複雜的連線查詢分頁

　　　　如果能夠理解上面這段程式碼，那麼你使用這個連線查詢絕對沒有問題，下面是生成的SQL

DECLARE @StartIndex INT SET @StartIndex = 1 SELECT @RecordCount=COUNT(*) FROM [dbo].[T_EXECUTE] AS t0  LEFT JOIN [dbo].[T_PLAN] AS t1 ON  t0.[plan_id]=t1.[plan_id] LEFT JOIN [dbo].[T_ORDER_DETAIL] AS t2 ON  t1.[order_detail_id]=t2.[order_detail_id] LEFT JOIN [dbo].[T_PART] AS t3 ON  t2.[part_id]=t3.[part_id] LEFT JOIN [dbo].[T_PART] AS t4 ON  t0.[part_id]=t4.[part_id] LEFT JOIN [dbo].[T_DEVICE] AS t5 ON  t0.[device_number]=t5.[device_number] LEFT JOIN [dbo].[T_PROCEDURE] AS t6 ON  t0.[procedure_id]=t6.[procedure_id] LEFT JOIN [dbo].[T_EXECUTE_TIME] AS t7 ON  t0.[execute_id]=t7.[execute_id]  WHERE t0.[execute_start_time] BETWEEN @0_t0_Begin_execute_start_time AND @0_t0_End_execute_start_time IF (@PageIndex<=1) BEGIN SET @PageIndex=1 END SET @StartIndex = ( @PageIndex - 1 ) * @PageSize + 1 ; WITH TempTable AS( SELECT ROW_NUMBER() OVER (ORDER BY t0.[plan_id] DESC) RowNumber,t0.[execute_id],t0.[plan_id],t0.[part_id],t0.[procedure_id],t0.[device_id],t0.[device_number],t0.[execute_start_time],t0.[execute_end_time],t0.[execute_plan_qty],t0.[execute_qty],t0.[execute_bad_qty],t0.[stuff_number],t0.[execute_state],t0.[execute_remark1],t0.[execute_remark2],t0.[creator],t0.[create_time],t0.[modifier],t0.[modify_time],t0.[execute_storage_qty],t0.[stuff_number2],t0.[stuff_number3],t0.[stuff_number4],t0.[stuff_number5],t0.[wt_code],t0.[execute_bad_qty1],t0.[execute_bad_qty2],t0.[execute_bad_qty3],t0.[execute_bad_qty4],t0.[execute_work_state],t0.[execute_restart_time],t1.[plan_number] AS PlanNumber,t1.[plan_finish_qty] AS PlanFinishQty,t1.[order_detail_id] AS OrderDetailID,t1.[plan_qty] AS PlanQty,t3.[part_picture_number] AS PartPictureNum,t4.[part_name] AS PartName,t4.[part_specification] AS PartSpecification,t4.[part_display] AS PartDisplay,t5.[device_name] AS DeviceName,t5.[device_number] AS DeviceNumber,t6.[procedure_name] AS ProcedureName,t7.[et_hours] AS ETHours FROM [dbo].[T_EXECUTE] AS t0  LEFT JOIN [dbo].[T_PLAN] AS t1 ON  t0.[plan_id]=t1.[plan_id] LEFT JOIN [dbo].[T_ORDER_DETAIL] AS t2 ON  t1.[order_detail_id]=t2.[order_detail_id] LEFT JOIN [dbo].[T_PART] AS t3 ON  t2.[part_id]=t3.[part_id] LEFT JOIN [dbo].[T_PART] AS t4 ON  t0.[part_id]=t4.[part_id] LEFT JOIN [dbo].[T_DEVICE] AS t5 ON  t0.[device_number]=t5.[device_number] LEFT JOIN [dbo].[T_PROCEDURE] AS t6 ON  t0.[procedure_id]=t6.[procedure_id] LEFT JOIN [dbo].[T_EXECUTE_TIME] AS t7 ON  t0.[execute_id]=t7.[execute_id]  WHERE t0.[execute_start_time] BETWEEN @0_t0_Begin_execute_start_time AND @0_t0_End_execute_start_time ) SELECT * FROM TempTable WHERE RowNumber BETWEEN (@StartIndex) AND (@PageIndex * @PageSize)

作者：情緣
出處：http://www.cnblogs.com/qingyuan/
關於作者：從事倉庫，生產軟體方面的開發，在專案管理以及企業經營方面尋求發展之路
版權宣告：本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段宣告，且在文章頁面明顯位置給出原文連結。
聯絡方式: 個人QQ 821865130 ; 倉儲技術QQ群 88718955,142050808 ;
吉特倉儲管理系統開源地址: https://github.com/hechenqingyuan/gitwms

Git.Framework 框架隨手記--ORM查詢資料集合一
2014-05-14
GitFramework框架ORM
Git.Framework 框架隨手記--ORM查詢返回實體物件
2014-05-13
GitFramework框架ORM物件
Git.Framework 框架隨手記--ORM新增操作
2014-05-06
GitFramework框架ORM
Git.Framework 框架隨手記--ORM專案工程
2014-05-05
GitFramework框架ORM
Git.Framework 框架隨手記--ORM編輯刪除
2014-05-07
GitFramework框架ORM
Git.Framework 框架隨手記--ORM條件組合
2014-05-09
GitFramework框架ORM
Git.Framework 框架隨手記--準備工作
2014-04-09
GitFramework框架
Git.Framework 框架隨手記--歷史原因
2014-03-15
GitFramework框架
Git.Framework 框架隨手記--SQL配置檔案的使用
2014-05-19
GitFramework框架SQL
Git.Framework 框架隨手記--儲存過程簡化
2014-05-20
GitFramework框架儲存過程
Git.Framework 框架隨手記-- 分享一個"比較垃圾"的專案
2015-01-28
GitFramework框架
Git.Framework 框架隨手記--IIS7執行序列化問題
2014-12-03
GitFramework框架
資料庫 - 連線查詢、巢狀查詢、集合查詢
2015-05-05
資料庫巢狀
Django框架：8、聚合查詢、分組查詢、F與Q查詢、ORM查詢最佳化、ORM事務操作、ORM常用欄位型別、ORM常用欄位引數
2022-12-19
Django框架ORM型別
MongoDB之資料查詢（巢狀集合）
2017-08-29
MongoDB巢狀
mysql 隨機查詢記錄
2024-04-08
MySql隨機
Room ORM 資料庫框架
2017-05-18
OOMORM資料庫框架
Java集合框架隨筆
2020-10-31
Java框架
ORM多表查詢下
2018-12-20
ORM
【資料結構】折半查詢（二分查詢）
2012-09-15
資料結構
MySQL 隨機查詢資料與隨機更新資料實現程式碼
2021-09-09
MySql隨機
如何實現MySQL隨機查詢資料與MySQL隨機更新資料？
2014-09-04
MySql隨機
【SQL】查詢資料的方式 (二)
2010-09-14
SQL
CQengine高效能記憶體資料快取查詢框架
2020-05-20
記憶體快取框架
Laravel 框架使用 whereIn 查詢 SQL 時資料出錯記錄
2019-11-06
Laravel框架SQL
隨手記一記之【JS資料型別】
2018-07-23
JS資料型別
【SQL查詢】集合查詢之INTERSECT
2016-11-13
SQL
手擼ORM淺談ORM框架之Query篇
2020-11-12
ORM框架
手擼ORM淺談ORM框架之Delete篇
2020-10-30
ORM框架delete
手擼ORM淺談ORM框架之Update篇
2020-10-26
ORM框架
【資料結構與演算法】手撕二叉查詢樹
2022-03-27
資料結構演算法
BlockingCollection 集合隨記
2017-08-14
BloCGC
Django-ORM 之查詢排序
2020-01-02
DjangoORM排序
golang beego orm 查詢條件 or and
2020-11-11
GolangORM
手擼ORM淺談ORM框架之Add篇
2020-10-21
ORM框架
手擼ORM淺談ORM框架之基礎篇
2020-10-14
ORM框架
手寫下拉選單，模糊查詢資料
2018-09-25
查詢重寫——資料倉儲手冊
2009-12-24

Git.Framework 框架隨手記--ORM查詢資料集合 二

相關文章

Git.Framework 框架隨手記--ORM查詢資料集合二