我的ORM之一 -- 查询

我的ORM索引

概述

http://code.taobao.org/svn/MyOql/

 

这是我自己写的开源ORM教程,我想先从场景示例中切入介绍,先有一个感性的认识,以小见大,触类旁通,有了这个认识,就能自行扩展出更多的用法。

数据常用操作 CURD,ORM也一样。

ORM生成的实体应该有一个根,我们叫它: dbr ( db root )

假设有一些表:如 Menu表 , User 表 等。

生成的两类表对象:

  • 一个对象是元数据,表示数据库的结构,如有哪些列,哪些是主键,哪些是自增键,以及列的类型。生成 MenuRule
  • 另一个对象用来存放真实的数据。生成 MenuRule+Entity

在Rule类中生成实体类。如:

 

public class MenuRule : RuleBase
{

    public class Entity
    {

        public string Name;

    }

    public ColumnDefine Name;
}

 

一个系统,可以按模块分解;一个大系统可以分解为多个小的系统。Mvc可以按模块分成多个Area,数据库表也应该符合这个规则。

对数据库表进行分组,如:平台组,Erp组,Hr组,财务组。在引用的时候,按组进行引用,如: dbr.PlateForm.User  , dbr.Erp.Income , dbr.Hr.UserJob , dbr.Money.Banlance ...

 

查询写法:

1. 普通查询:

  dbr.表

    .Select(列)

    .Join(它表,On关系,它表的列)

    .Where(条件)

    .Skip(分页跳过条数)

    .Take(分页条数)

    .GroupBy(分组)

    .Having(分组过滤)

    .OrderBy(排序)

    .ToEntityList(默认实体对象);

例子:

C#:

List<MenuRule.Entity> results = dbr.Menu

    .Select(o=>new Columns(){ o.Id,o.Name })

    .Where(o=> o.Id> 0)

    .Skip(10)

    .Take(10)

    .ToEntityList(o=>o._) ;

生成SQL:  

Select * From 
( Select Row_Number() Over (Order by [ID] asc) As [#__IgNoRe__AutoId], * From 
( 
select 
    [Menu].[ID] As [Id],
    [Menu].[Name] As [Name]
 From [S_Menu] As [Menu]
 where [Menu].[ID] > 0
) As [__SubQuery__] ) As [___SubQuery___]
where [#__IgNoRe__AutoId] between 11 and 20

 

2. From子查询

要生成的SQL:

select 
    [a].[m] As [m],
    [a].[ShortName] As [ShortName]
From (
  select 
    min([City].[ID]) As [m],
    [City].[ShortName] As [ShortName]
   From [S_City] As [City]
   group by [City].[ShortName]
) As [a]
 where [a].[ShortName] Like '%河%' And [a].[m] = 1
 Order by [#__IgNoRe__1] asc

 

C#写法:

dbr.City.Select(o => new Columns() { o.Id.Min().As("m") , o.ShortName })
    .AutoGroup()
    .SelectWrap("a")
    .Where(o => o.ShortName.Contains("") & new RawColumn(System.Data.DbType.Int32 , "a","m") == 1 )
  .OrderBy(o=>o.ShortName.Asc) .ToMyOqlSet() ;

 

3.Join子查询

select 
    [ProductInfo].[ID] As [Id],
    [ProductInfo].[ProductTypeID] As [ProductTypeID],
    [ProductInfo].[CarInfoID] As [CarInfoID],
    [ProductInfo].[Name] As [Name],
    [ProductInfo].[Logo] As [Logo],
    [ProductInfo].[Remark] As [Remark],
    [ProductInfo].[Price] As [Price],
    [ProductInfo].[SortID] As [SortID],
    [ProductInfo].[PlatCost] As [PlatCost],
    [ProductInfo].[Color] As [Color],
    [ProductInfo].[Unit] As [Unit],
    [ProductInfo].[CorpID] As [CorpID],
    [ProductInfo].[Status] As [Status],
    [ProductInfo].[Group] As [Group],
    [ProductInfo].[CreateAt] As [CreateAt],
    [ProductInfo].[UpdateAt] As [UpdateAt],
    [ProductInfo].[CreateBy] As [CreateBy],
    [ProductInfo].[StandardPrice] As [StandardPrice],
    [ProductInfo].[IsSuitCar] As [IsSuitCar],
    [ProductInfo].[ParentProductID] As [ParentProductID]
 From [P_ProductInfo] As [ProductInfo] left join(select 
    [PUser].[ID] As [Id],
    [PUser].[Name] As [Name]
 From [P_User] As [PUser]
 where [PUser].[ID] > 100) As b on ([ProductInfo].[CreateBy] = [b].[Id])
 Order by [ProductInfo].[ID] asc

C#:

dbr.PlatForm.ProductInfo.Select()
    .Join(SqlKeyword.LeftJoin, dbr.PUser.Select(o => new Columns(o.Id, o.Name)).Where(o => o.Id > 100).As("b") , (a, b) => a.CreateBy == new RawColumn(System.Data.DbType.Int32,"b","Id") )
    .ToMyOqlSet();

 

4. 树查询:

使用SQL的 With ... As 做一个树查询,在此结果上,再做查询。SQL写法:

with [ProductType] as (
select * from [P_ProductType] where [CorpID] = 1 And [PID] = 0
union all
select t.* from [ProductType] as p join [P_ProductType] as t on ( p.[ID] = t.[PID])
)select 
    [ProductType].[ID] As [Id],
    [ProductType].[Name] As [Name],
    [ProductType].[CarInfoID] As [CarInfoID],
    [ProductType].[PID] As [Pid],
    [ProductType].[Wbs] As [Wbs],
    [ProductType].[CarGroup] As [CarGroup],
    [ProductType].[Remark] As [Remark],
    [ProductType].[SortID] As [SortID],
    [ProductType].[CorpID] As [CorpID],
    [ProductType].[CreateAt] As [CreateAt],
    [ProductType].[CreateBy] As [CreateBy],
    [ProductType].[Status] As [Status],
    [ProductType].[Logo] As [Logo],
    [ProductType].[IsLeaf] As [IsLeaf],
    [PUser].[Name] As [CreateByName]
 From [ProductType]
 inner join [P_User] As [PUser] on ([ProductType].[CreateBy] = [PUser].[ID])
 where [ProductType].[CorpID] = 1
 Order by [ProductType].[ID] asc

 

C#:

dbr.PlatForm.ProductType
    .SelectTree(null, o => o.CorpID == 1 & o.Pid == 0, o => o.Pid, o => o.Id)
    .Join(dbr.PUser, (a, b) => a.CreateBy == b.Id, b => b.Name.As("CreateByName"))
    .Where(o => o.CorpID == 1)
    .ToMyOqlSet();

 

连接列

选择一个符号,将多个列连接起来,组合为一个列集合。可重载的运算符:https://msdn.microsoft.com/zh-cn/library/8edha89s(v=vs.80).aspx

由于大部分运算符被SQL运算符优先使用,所以只能选持一个生僻的运算符。仅剩余 ^ 可使用。

 

使用 ^ 可以连接多个列,这是最简单的方法:

dbr.Menu.Select(o=>o.Id ^ o.Name)

普通方式:

dbr.Menu.Select(o=>new Columns(o.Id,o.Name))

使用SQL自定义函数

数据库函数分为:表值,标量,聚合,系统四大类。

表值函数:

表值函数的特点和表差不多,可以理解为带参数的表。它可以像表一样,利用实体生成器生成结构。

如Split 函数,输入字符串,返回按","分隔的表。

SQL:

select 
    [Split].[Value] As [Value]
 From [Split]('abc,def') As [Split]
 where [Split].[Value] Like '%b%'

 

C#:

dbr.Split("abc,def")
  .Select()
  .Where(o => o.Value.Contains("b"))
  .ToMyOqlSet() ;

 

标量函数:

标量函数需要利用 C#扩展方法,对列进行扩展,如:

        public static WhereClip IsSameDay(this ColumnClip column, DateTime Dt)
        {
            var retVal = new RawColumn(DbType.Boolean);
            retVal.Expression = "dbo.F_IsSameDay({0},{1})";
            retVal.Parameter = new Columns() { column.Clone() as ColumnClip, new ConstColumn(Dt) }.Embattle();
            return retVal == 1;
        }

 

判断两个值是否是同一天:输入两个时间值,返回bit。

SQL:

select 
    [PUser].[ID] As [Id],
    [PUser].[LoginName] As [LoginName],
    [PUser].[Name] As [Name],
    [PUser].[QQOpenID] As [QQOpenID],
    [PUser].[Logo] As [Logo],
    [PUser].[Score] As [Score],
    [PUser].[Type] As [Type],
    [PUser].[BirthDay] As [BirthDay],
    [PUser].[IDCard] As [IDCard],
    [PUser].[IDCardPhoto] As [IDCardPhoto],
    [PUser].[Email] As [Email],
    [PUser].[Sex] As [Sex],
    [PUser].[Mobile] As [Mobile],
    [PUser].[ImName] As [ImName],
    [PUser].[ImNum] As [ImNum],
    [PUser].[ValidateStatus] As [ValidateStatus],
    [PUser].[CreateAt] As [CreateAt],
    [PUser].[WorkType] As [WorkType],
    [PUser].[CityID] As [CityID],
    [PUser].[MaritalStatus] As [MaritalStatus],
    [PUser].[NPCityID] As [NPCityID],
    [PUser].[CorpID] As [CorpID],
    [PUser].[IsCorpAdmin] As [IsCorpAdmin],
    [PUser].[BankID] As [BankID],
    [PUser].[BankNo] As [BankNo],
    [PUser].[BankDetailName] As [BankDetailName]
 From [P_User] As [PUser]
 where dbo.F_IsSameDay([PUser].[CreateAt],'2015-05-26 00:00:00') = 1

 

C#:

dbr.PUser
  .Select()   .Where(o
=> o.CreateAt.IsSameDay("2015-05-26".AsDateTime()))   .ToMyOqlSet();

 

聚合函数:

聚合函数,要带有GroupBy,分组进行计算。聚合函数也需要自行扩展,如JoinStr 函数:

        public static ColumnClip JoinStr(this ColumnClip column)
        {
            var retVal = new RawColumn(DbType.String);
            retVal.Expression = "dbo.JoinStr({0})";
            retVal.IsPolymer = true;
            retVal.Parameter = column.Clone() as ColumnClip;
            return retVal;
        }

SQL:

select 
    dbo.JoinStr([PUser].[Name])
 From [P_User] As [PUser]
 where dbo.F_IsSameDay([PUser].[CreateAt],'2015-05-26 00:00:00') = 1

C#:

dbr.PUser
  .Select(o=>o.Name.JoinStr())
  .Where(o => o.CreateAt.IsSameDay("2015-05-26".AsDateTime()))
  .ToMyOqlSet();

 

如果扩展系统函数,可以按上述方式进行。看系统函数是哪一类。

 

使用存储过程:

存储过程,在形式上就像一个函数,有输入参数,有返回值。可以使用代码生成器搞定。

调用:

PUserRule.Entity user = dbr.P_Login("admin","1234") ;

 

支持的关键字:

Case When关键字:

C#:

dbr.PUser
  .Select(o =>new Columns( o.Id, o.Name.JoinStr() ) )
  .Where(o
=> o.CreateAt.IsSameDay("2015-05-26".AsDateTime()))   .OrderBy(o =>     dbo.CaseWhen(dbr.PUser.Id < 100, new ConstColumn(1))       .WhenThen(dbr.PUser.Id.Between(100, 200), new ConstColumn(2))       .ElseEnd(new ConstColumn(3))     .Asc)   .AutoGroup()   .ToMyOqlSet();

 

Sql:

select 
    [PUser].[ID] As [Id],
    dbo.JoinStr([PUser].[Name])
 From [P_User] As [PUser]
 where dbo.F_IsSameDay([PUser].[CreateAt],'2015-05-26 00:00:00') = 1
 group by [PUser].[ID]
 Order by Case When [PUser].[ID] < 100 Then 1 When ([PUser].[ID] Between 100 And 200) Then 2 Else 3 End asc

 

其它关键字很简单,就不示例了:

运算符:

加(+) ,减(-) ,乘(×),除(÷) ,等于(=),不等于(!=),大于(>),小于(<),大于等于(≥),小于等于(≤),And (&),Or(|),求模(%)

关键字:

As,BitAnd,BitOr,In,NotIn,Like,Exists,NotExists,Escape, Between,NotBetween,Cast,IsNull,Union,UnionAll,Now

字符串函数:

Len,SizeOf(DataLength),Left,Right,Reverse,AscII,Unicode,Char,NChar,StringIndex,SubString,Stuff,PatIndex,Replace,IsNumeric,LTrim,RTrim,Trim

聚合函数:

Count,CountDistict,Sum,Max,Min,Avg

时间函数:

IsDate,Year,Month,Day,DateDiff,DateAdd

数字函数:

Abs,Sign,Floor,Ceiling,Power,Square,Sqrt,Random。

 

posted @ 2015-05-22 21:28  NewSea  阅读(2262)  评论(0编辑  收藏  举报