Git.Framework 框架随手记--ORM查询数据集合 二
2014-05-15 08:27 贺臣 阅读(1899) 评论(5) 编辑 收藏 举报一. 分组查询
在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() { } }
很熟悉是不是,.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
