【原創】重覆造輪子之高仿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