欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

【原创】重复造*之高仿EntityFramework

程序员文章站 2022-07-05 08:52:45
前言 在上一篇《【原创】打造基于Dapper的数据访问层》中,Dapper在应付多表*关联、分组查询、匿名查询等应用场景时经常要手动写SQL语句。看着代码里满屏的红色SQL字符串,简直头大,于是便萌生重复造ORM这个*的念头。本ORM在API设计上最大程度地借鉴 EF 的写法,支持链式查询(点标 ......
  • 前言

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

  • 性能

【原创】重复造*之高仿EntityFramework

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

  • 语法

1. 单表查询

// 查询表达式
var query = from a in context.GetTable<Inte_CRM.Demo>()
            select a;
var r1 = query.ToList();
// 点标记
query = context.GetTable<Inte_CRM.Demo>();
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<Inte_CRM.Thin>(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 中有详细说明,源码地址:https://github.com/TANZAME/Inte.XFramework