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

• 前言

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

• 性能

 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