重复造轮子之高仿EntityFramework

  • 前言

在上一篇《【原创】打造基于Dapper的数据访问层》中,Dapper在应付多表自由关联、分组查询、匿名查询等应用场景时经常要手动写SQL语句。看着代码里满屏的红色SQL字符串,简直头大,于是便萌生重复造ORM这个轮子的念头。本ORM在API设计上最大程度地借鉴
EF
的写法,支持链式查询(点标记)、查询表达式、聚合查询、分组排序、批量插入、批量更新、批量删除、1:1关系外键等。在实体绑定层面,使用
Emit 来动态构建绑定指令,性能最大限度地接近原生水平。

  • 性能

乐百家棋牌游戏平台 1

 7000笔记录循环读1000次,同时加载1:1关系的外键,速度比 EF 稍快。

  • 语法
  1. 单表查询

    // 查询表达式
    var query = from a in context.GetTable()

             select a;
    

    var r1 = query.ToList();
    // 点标记
    query = context.GetTable();
    r1 = query.ToList();
    //SQL=>
    //SELECT
    //t0.[DemoId] AS [DemoId],
    //t0.[DemoCode] AS [DemoCode],
    //t0.[DemoName] AS [DemoName],
    //…
    //t0.[DemoLong] AS [DemoLong],
    //t0.[DemoLong_Nullable] AS [DemoLong_Nullable]
    //FROM [Sys_Demo] t0

  2. 关联查询

    // INNER JOIN
    var query =

     from a in context.GetTable<Inte_CRM.CRM_SaleOrder>()
     join b in context.GetTable<Inte_CRM.Client>() on a.ClientId equals b.ClientId
     join c in context.GetTable<Inte_CRM.CloudServer>() on b.CloudServerId equals c.CloudServerId
     where a.ClientId > 0
     select a;
    

    var r1 = query.ToList();
    // 点标记
    query = context

     .GetTable<Inte_CRM.CRM_SaleOrder>()
     .Join(context.GetTable<Inte_CRM.Client>(), a => a.ClientId, b => b.ClientId, (a, b) => new { Sale = a, Buyer = b })
     .Join(context.GetTable<Inte_CRM.CloudServer>(), b => b.Buyer.CloudServerId, c => c.CloudServerId, (a, c) => new Inte_CRM.CRM_SaleOrder { })
     .Where(a => a.ClientId > 0);
    

    //r1 = query.ToList();
    //SQL=>
    //SELECT
    //t0.[OrderId] AS [OrderId],
    //t0.[OrderNo] AS [OrderNo],
    //t0.[Remark] AS [Remark],
    //t0.[ClientId] AS [ClientId]
    //FROM [CRM_SaleOrder] t0
    //INNER JOIN [Bas_Client] t1 ON t0.[ClientId] = t1.[ClientId]
    //INNER JOIN [Sys_CloudServer] t2 ON t1.[CloudServerId] = t2.[CloudServerId]
    //WHERE t0.[ClientId] > 0

  3. 乐百家棋牌游戏平台,分组分页

    // 分组后再分页
    query =

     from a in context.GetTable<Inte_CRM.Client>()
     where a.ClientName == "TAN"
     group a by new { a.ClientId, a.ClientName } into g
     where g.Key.ClientId > 0
     orderby new { g.Key.ClientName, g.Key.ClientId }
     select new
     {
         Id = g.Key.ClientId,
         Name = g.Min(a => a.ClientId)
     };
    

    query = query.Skip(2).Take(3);
    r1 = query.ToList();
    //SQL=>
    //SELECT
    //t0.[Id],
    //t0.[Name]
    //FROM (
    // SELECT
    // t0.[ClientId] AS [Id],
    // MIN(t0.[ClientId]) AS [Name],
    // t0.[ClientName] AS [ClientName]
    // FROM [Bas_Client] t0
    // WHERE t0.[ClientName] = N’TAN’
    // GROUP BY t0.[ClientId],t0.[ClientName]
    // Having t0.[ClientId] > 0
    // ) t0
    //ORDER BY t0.[ClientName]
    //OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY

  4. 批量插入

    context.Insert(collection);
    context.SubmitChanges();
    //SQL=>
    //INSERT INTO[Sys_Thin]
    //([ThinId],[ThinName])
    //VALUES
    //(2, N’002′),(3,N’003′)

  5. 导航属性

    // 更简单的赋值方式
    // 适用场景:在显示列表时只想显示外键表的一两个字段
    query =

     from a in context.GetTable<Inte_CRM.CRM_SaleOrder>()
     select new Inte_CRM.CRM_SaleOrder(a)
     {
         Client = new Inte_CRM.Client(a.Client)
         {
             CloudServer = new Inte_CRM.CloudServer
             {
                 CloudServerId = a.Client.CloudServer.CloudServerId,
                 CloudServerName = a.Client.CloudServer.CloudServerName
             }
         },
         HeavyBuyer = new Inte_CRM.Client
         {
             ClientId = a.Client.ClientId + 10,
             ClientName = a.Client.ClientName + "_heavy",
             CloudServer = new Inte_CRM.CloudServer
             {
                 CloudServerId = a.Client.CloudServer.CloudServerId + 10,
                 CloudServerName = a.Client.CloudServer.CloudServerName + "_heavy",
             }
         }
     };
    

    r1 = query.ToList();
    //SQL=>
    //SELECT
    //t0.[OrderId] AS [OrderId],
    //t0.[OrderNo] AS [OrderNo],
    //t0.[Remark] AS [Remark],
    //t0.[ClientId] AS [ClientId],
    //t1.[ClientId] AS [ClientId1],
    //t1.[ClientCode] AS [ClientCode],
    //t1.[ClientName] AS [ClientName],
    //t1.[State] AS [State],
    //t1.[ActiveDate] AS [ActiveDate],
    //t1.[CloudServerId] AS [CloudServerId],
    //t2.[CloudServerId] AS [CloudServerId1],
    //t2.[CloudServerName] AS [CloudServerName],
    //t1.[ClientId] + 10 AS [ClientId2],
    //t1.[ClientName] + N’_heavy’ AS [ClientName1],
    //t2.[CloudServerId] + 10 AS [CloudServerId2],
    //t2.[CloudServerName] + N’_heavy’ AS [CloudServerName1]
    //FROM [CRM_SaleOrder] t0
    //LEFT JOIN [Bas_Client] t1 ON t0.[ClientId] = t1.[ClientId]
    //LEFT JOIN [Sys_CloudServer] t2 ON t1.[CloudServerId] = t2.[CloudServerId]

 

其它更多示例在源码的 demo
中有详细说明,源码地址:

 

相关文章