【原創】基於.NET的輕量級高效能 ORM - XFramework

田乃翔發表於2019-05-30
ORMFramework

 【前言】

　　接上一篇《【原創】打造基於Dapper的資料訪問層》，Dapper在應付多表自由關聯、分組查詢、匿名查詢等應用場景時不免顯得吃力，經常要手寫SQL語句（或者用工具生成SQL配置檔案）。試想一下，專案中整個DAL層都塞滿了SQL語句，對於後期維護來說無異於天災人禍，這個坑誰踩誰知道。本框架在API設計上最大程度地借鑑 EntityFramework 的寫法，乾淨的實體，絲滑的增刪改查，穩健的導航屬性，另外還支援鏈式查詢（點標記）、查詢表示式、聚合查詢等等。在實體對映轉換層面，使用 Emit 來動態構建繫結指令，效能最大限度地接近原生水平。

【XFramework 亮點】

  1. 原生.NET語法，零學習成本
  2. 支援LINQ查詢、拉姆達表示式
  3. 支援批量增刪改查和多表更新
  4. 支援 SqlServer、MySql、Postgre、Oracle，.NET Core
  5. 最大亮點，真正支援一對一、一對多導航屬性。這一點相信現有開源的ORM沒幾個敢說它支援的
  6. 實體欄位型別不必與資料庫的型別一致
  7. 支援臨時表、表變數操作
  8. 其它更多亮點，用了你就會知道

【效能】　　

　　看看與EntityFramework的效能對比，機器配置不同跑出來的結果可能也不一樣，僅供參考。需要特別說明的是EntityFramework是用了AsNoTracking的，不然有快取的話就沒有比較的意義了。
　　扯個題外話，有些ORM說比EntityFramework快百分多少多少，看起來挺美實際上在我看來那是在扯淡，沒有任何參考價值。限制實體欄位型別必須與資料庫的一致不說，甚至連導航屬性這種最基本的功能都不支援，這不在同一個等級同一個體量上的東西非要扯在一起比誰快，確定要這麼幽默嗎？本人非常推崇開源，也很尊重致力於開源的同行，但是像這種行為就非常有必要出來打假一波了。

　　

【功能說明】

 1. 實體定義　　

　　1.1. 如果類有 TableAttribute，則用 TableAttribute 指定的名稱做為表名，否則用類名稱做為表名
　　1.2. 實體的欄位可以指定 ColumnAttribute 特性來說明實體欄位與表欄位的對應關係，刪除/更新時如果傳遞的引數是一個實體，必須使用 [Column(IsKey = true)] 指定實體的主鍵
　　1.3. ForeignKeyAttribute 指定外來鍵，一對多外來鍵時型別必須是 IList<T> 或者 List<T>
　　1.4 ColumnAttribute.DataType 用來指定表欄位型別。以SQLSERVER為例，System.String 預設對應 nvarchar 型別。若是varchar型別，需要指定[Column(DbType= DbType.AnsiString)]

 1         [Table(Name = "Bas_Client")]
 2         public partial class Client
 3         {
 4             /// <summary>
 5             /// 初始化 <see cref="Client"/> 類的新例項
 6             /// </summary>
 7             public Client()
 8             {
 9                 this.CloudServerId = 0;
10                 this.Qty = 0;
11                 this.HookConstructor();
12             }
13 
14             /// <summary>
15             /// 初始化 <see cref="Client"/> 類的新例項
16             /// </summary>
17             public Client(Client model)
18             {
19                 this.CloudServerId = 0;
20                 this.Qty = 0;
21                 this.HookConstructor();
22             }
23 
24             /// <summary>
25             /// clientid
26             /// </summary>
27             [Column(IsKey = true)]
28             public virtual int ClientId { get; set; }
29 
30             /// <summary>
31             /// clientcode
32             /// </summary>
33             public virtual string ClientCode { get; set; }
34 
35             /// <summary>
36             /// clientname
37             /// </summary>
38             public virtual string ClientName { get; set; }
39 
40             /// <summary>
41             /// cloudserverid
42             /// </summary>
43             [Column(Default = 0)]
44             public virtual int CloudServerId { get; set; }
45 
46             /// <summary>
47             /// activedate
48             /// </summary>
49             public virtual Nullable<DateTime> ActiveDate { get; set; }
50 
51             /// <summary>
52             /// qty
53             /// </summary>
54             [Column(Default = 0)]
55             public virtual int Qty { get; set; }
56 
57             /// <summary>
58             /// state
59             /// </summary>
60             public virtual byte State { get; set; }
61 
62             /// <summary>
63             /// remark
64             /// </summary>
65             [Column(Default = "'預設值'")]
66             public virtual string Remark { get; set; }
67 
68             [ForeignKey("CloudServerId")]
69             public virtual CloudServer CloudServer { get; set; }
70 
71             [ForeignKey("CloudServerId")]
72             public virtual CloudServer LocalServer { get; set; }
73 
74             [ForeignKey("ClientId")]
75             public virtual List<ClientAccount> Accounts { get; set; }
76 
77             /// <summary>
78             /// 建構函式勾子
79             /// </summary>
80             partial void HookConstructor();
81         }
View Code

2. 上下文定義　　 

1 SQLSERVER：var context = new SqlDbContext(connString);
2 MySQL：var context = new MyMySqlDbContext(connString);
3 Postgre：var context = new NpgDbContext(connString);
4 Oracle：var context = new OracleDbContext(connString);

3. 匿名型別

 1 //// 匿名類
 2 var guid = Guid.NewGuid();
 3 var dynamicQuery =
 4     from a in context.GetTable<TDemo>()
 5     where a.DemoId <= 10
 6     select new
 7     {
 8         DemoId = 12,
 9         DemoCode = a.DemoCode,
10         DemoName = a.DemoName,
11         DemoDateTime_Nullable = a.DemoDateTime_Nullable,
12         DemoDate = sDate,
13         DemoDateTime = sDate,
14         DemoDateTime2 = sDate_null,
15         DemoGuid = guid,
16         DemoEnum = Model.State.Complete,        // 列舉型別支援
17         DemoEnum2 = Model.State.Executing,
18     };
19 var result0 = dynamicQuery.ToList();
20 
21 // 點標記
22 dynamicQuery = context
23     .GetTable<TDemo>()
24     .Where(a => a.DemoId <= 10)
25     .Select(a => new
26     {
27         DemoId = 13,
28         DemoCode = a.DemoCode,
29         DemoName = a.DemoName,
30         DemoDateTime_Nullable = a.DemoDateTime_Nullable,
31         DemoDate = sDate,
32         DemoDateTime = sDate,
33         DemoDateTime2 = sDate_null,
34         DemoGuid = Guid.NewGuid(),
35         DemoEnum = Model.State.Complete,
36         DemoEnum2 = Model.State.Executing
37     });
38 result0 = dynamicQuery.ToList();
View Code

 4. 所有欄位

 1  // Date,DateTime,DateTime2 支援
 2 var query =
 3     from a in context.GetTable<TDemo>()
 4     where a.DemoId <= 10 && a.DemoDate > sDate && a.DemoDateTime >= sDate && a.DemoDateTime2 > sDate
 5     select a;
 6 var result1 = query.ToList();
 7 // 點標記
 8 query = context
 9     .GetTable<TDemo>()
10     .Where(a => a.DemoId <= 10 && a.DemoDate > sDate && a.DemoDateTime >= sDate && a.DemoDateTime2 > sDate);
11 result1 = query.ToList();
View Code

5. 指定欄位

 1 // 指定欄位
 2 query = from a in context.GetTable<TDemo>()
 3         where a.DemoId <= 10
 4         select new TDemo
 5         {
 6             DemoId = (int)a.DemoId,
 7             DemoCode = (a.DemoCode ?? "N001"),
 8             DemoName = a.DemoId.ToString(),
 9             DemoDateTime_Nullable = a.DemoDateTime_Nullable,
10             DemoDate = sDate,
11             DemoDateTime = sDate,
12             DemoDateTime2 = sDate
13         };
14 result1 = query.ToList();
15 // 點標記
16 query = context
17     .GetTable<TDemo>()
18     .Where(a => a.DemoCode != a.DemoId.ToString() && a.DemoName != a.DemoId.ToString() && a.DemoChar == 'A' && a.DemoNChar == 'B')
19     .Select(a => new TDemo
20     {
21         DemoId = a.DemoId,
22         DemoCode = a.DemoName == "張三" ? "李四" : "王五",
23         DemoName = a.DemoCode == "張三" ? "李四" : "王五",
24         DemoChar = 'A',
25         DemoNChar = 'B',
26         DemoDateTime_Nullable = a.DemoDateTime_Nullable,
27         DemoDate = sDate,
28         DemoDateTime = sDate,
29         DemoDateTime2 = sDate
30     });
31 result1 = query.ToList();
View Code

 6.建構函式

　　用過 EntityFramework 的同學都知道，如果要通過建構函式的方式查詢指定欄位，除非老老實實重新定義一個新的實體，否則一個 “The entity or complex type cannot be constructed in a LINQ to Entities query“ 的異常馬上給甩你臉上。XFramework 框架的這個用法，就是為了讓你遠離這會呼吸的痛！~

 1 // 建構函式
 2 var query =
 3         from a in context.GetTable<Model.Demo>()
 4         where a.DemoId <= 10
 5         select new Model.Demo(a);
 6 var r1 = query.ToList();
 7 //SQL=> 
 8 //SELECT 
 9 //t0.[DemoId] AS [DemoId],
10 //t0.[DemoCode] AS [DemoCode],
11 //t0.[DemoName] AS [DemoName],
12 //...
13 //FROM [Sys_Demo] t0 
14 //WHERE t0.[DemoId] <= 10
15 query =
16     from a in context.GetTable<Model.Demo>()
17     where a.DemoId <= 10
18     select new Model.Demo(a.DemoId, a.DemoName);
19 r1 = query.ToList();
View Code

 7. 分頁查詢

 1 // 分頁查詢
 2 // 1.不是查詢第一頁的內容時，必須先OrderBy再分頁，OFFSET ... Fetch Next 分頁語句要求有 OrderBy
 3 // 2.OrderBy表示式裡邊的引數必須跟query裡邊的變數名一致，如此例裡的 a。SQL解析時根據此變更生成表別名
 4 query = from a in context.GetTable<TDemo>()
 5         orderby a.DemoCode
 6         select a;
 7 query = query.Skip(1).Take(18);
 8 result1 = query.ToList();
 9 // 點標記
10 query = context
11     .GetTable<TDemo>()
12     .OrderBy(a => a.DemoCode)
13     .Skip(1)
14     .Take(18);
15 result1 = query.ToList();
View Code

8. 過濾條件

 1 // 過濾條件
 2 query = from a in context.GetTable<TDemo>()
 3         where a.DemoName == "D0000002" || a.DemoCode == "D0000002"
 4         select a;
 5 result1 = query.ToList();
 6 // 點標記
 7 query = context.GetTable<TDemo>().Where(a => a.DemoName == "D0000002" || a.DemoCode == "D0000002");
 8 result1 = query.ToList();
 9 query = context.GetTable<TDemo>().Where(a => a.DemoName.Contains("004"));
10 result1 = query.ToList();
11 query = context.GetTable<TDemo>().Where(a => a.DemoCode.StartsWith("Code000036"));
12 result1 = query.ToList();
13 query = context.GetTable<TDemo>().Where(a => a.DemoCode.EndsWith("004"));
14 result1 = query.ToList();
View Code

9. 更多條件

 1 // 支援的查詢條件
 2 // 區分 nvarchar,varchar,date,datetime,datetime2 欄位型別
 3 // 支援的字串操作=> Trim | TrimStart | TrimEnd | ToString | Length
 4 int m_byte = 9;
 5 Model.State state = Model.State.Complete;
 6 query = from a in context.GetTable<TDemo>()
 7         where
 8             a.DemoCode == "002" &&
 9             a.DemoName == "002" &&
10             a.DemoCode.Contains("TAN") &&                                   // LIKE '%%'
11             a.DemoName.Contains("TAN") &&                                   // LIKE '%%'
12             a.DemoCode.StartsWith("TAN") &&                                 // LIKE 'K%'
13             a.DemoCode.EndsWith("TAN") &&                                   // LIKE '%K'
14             a.DemoCode.Length == 12 &&                                      // LENGTH
15             a.DemoCode.TrimStart() == "TF" &&
16             a.DemoCode.TrimEnd() == "TF" &&
17             a.DemoCode.TrimEnd() == "TF" &&
18             a.DemoCode.Substring(0) == "TF" &&
19             a.DemoDate == DateTime.Now &&
20             a.DemoDateTime == DateTime.Now &&
21             a.DemoDateTime2 == DateTime.Now &&
22             a.DemoName == (
23                 a.DemoDateTime_Nullable == null ? "NULL" : "NOT NULL") &&   // 三元表示式
24             a.DemoName == (a.DemoName ?? a.DemoCode) &&                     // 二元表示式
25             new[] { 1, 2, 3 }.Contains(a.DemoId) &&                         // IN(1,2,3)
26             new List<int> { 1, 2, 3 }.Contains(a.DemoId) &&                 // IN(1,2,3)
27             new List<int>(_demoIdList).Contains(a.DemoId) &&                // IN(1,2,3)
28             a.DemoId == new List<int> { 1, 2, 3 }[0] &&                     // IN(1,2,3)
29             _demoIdList.Contains(a.DemoId) &&                          // IN(1,2,3)
30             a.DemoName == _demoName &&
31             a.DemoCode == (a.DemoCode ?? "CODE") &&
32             new List<string> { "A", "B", "C" }.Contains(a.DemoCode) &&
33             a.DemoByte == (byte)m_byte &&
34             a.DemoByte == (byte)Model.State.Complete ||
35             a.DemoInt == (int)Model.State.Complete ||
36             a.DemoInt == (int)state ||
37             (a.DemoName == "STATE" && a.DemoName == "REMARK")// OR 查詢
38         select a;
39 result1 = query.ToList();
View Code

10. DataTable和DataSet

 1 // DataTable
 2 query = from a in context.GetTable<TDemo>()
 3         orderby a.DemoCode
 4         select a;
 5 query = query.Take(18);
 6 var result3 = context.Database.ExecuteDataTable(query);
 7 
 8 // DataSet
 9 var define = query.Resolve();
10 List<DbCommandDefinition> sqlList = new List<DbCommandDefinition> { define, define, define };
11 var result4 = context.Database.ExecuteDataSet(sqlList);
View Code

11. 內聯查詢

 1 // INNER JOIN
 2 var query =
 3     from a in context.GetTable<Model.Client>()
 4     join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId
 5     where a.ClientId > 0
 6     select a;
 7 var result = query.ToList();
 8 // 點標記
 9 query = context
10     .GetTable<Model.Client>()
11     .Join(context.GetTable<Model.CloudServer>(), a => a.CloudServerId, b => b.CloudServerId, (a, b) => a)
12     .Where(a => a.ClientId > 0);
13 result = query.ToList();
View Code

12. 左聯查詢

注意看第二個左關聯，使用常量作為關聯鍵，翻譯出來的SQL語句大概是這樣的：

SELECT ***
FROM [Bas_Client] t0
LEFT JOIN [Sys_CloudServer] t1 ON t0.[CloudServerId] = t1.[CloudServerId] AND N'567' = t1.[CloudServerCode]
WHERE t1.[CloudServerName] IS NOT NULL

有沒有看到熟悉的味道，兄dei？

 1 // LEFT JOIN
 2 query =
 3         from a in context.GetTable<Model.Client>()
 4         join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId into u_b
 5         from b in u_b.DefaultIfEmpty()
 6         select a;
 7 query = query.Where(a => a.CloudServer.CloudServerName != null);
 8 result = query.ToList();
 9 
10 // LEFT JOIN
11 query =
12         from a in context.GetTable<Model.Client>()
13         join b in context.GetTable<Model.CloudServer>() on new { a.CloudServerId, CloudServerCode = "567" } equals new { b.CloudServerId, b.CloudServerCode } into u_b
14         from b in u_b.DefaultIfEmpty()
15         select a;
16 query = query.Where(a => a.CloudServer.CloudServerName != null);
17 result = query.ToList();
View Code

 13. 右聯查詢

左關聯和右關聯的語法我這裡用的是一樣的，不過是 DefaultIfEmpty 方法加多了一個過載，DefaultIfEmpty(true) 即表示右關聯。

1 // RIGHT JOIN
2 query =
3         from a in context.GetTable<Model.CloudServer>()
4         join b in context.GetTable<Model.Client>() on a.CloudServerId equals b.CloudServerId into u_b
5         from b in u_b.DefaultIfEmpty(true)
6         where a.CloudServerName == null
7         select b;
8 result = query.ToList();
View Code

 14. Union查詢

我們的Union查詢支援 UNION 操作後再分頁哦~

 1 // UNION 注意UNION分頁的寫法，僅支援寫在最後
 2 var q1 = context.GetTable<Model.Client>().Where(x => x.ClientId == 0);
 3 var q2 = context.GetTable<Model.Client>().Where(x => x.ClientId == 0);
 4 var q3 = context.GetTable<Model.Client>().Where(x => x.ClientId == 0);
 5 var query6 = q1.Union(q2).Union(q3);
 6 var result6 = query6.ToList();
 7 result6 = query6.Take(2).ToList();
 8 result6 = query6.OrderBy(a => a.ClientId).Skip(2).ToList();
 9 query6 = query6.Take(2);
10 result6 = query6.ToList();
11 query6 = query6.OrderBy(a => a.ClientId).Skip(1).Take(2);
12 result6 = query6.ToList();
View Code

15. 導航屬性

 1 // 更簡單的賦值方式 
 2 // 適用場景：在顯示列表時只想顯示外來鍵表的一兩個欄位
 3 query =
 4     from a in context.GetTable<Model.Client>()
 5     select new Model.Client(a)
 6     {
 7         CloudServer = a.CloudServer,
 8         LocalServer = new Model.CloudServer
 9         {
10             CloudServerId = a.CloudServerId,
11             CloudServerName = a.LocalServer.CloudServerName
12         }
13     };
14 result = query.ToList();
View Code

16. 一對一一對多導航

 1 // 1：1關係，1：n關係
 2 query =
 3     from a in context.GetTable<Model.Client>()
 4     where a.ClientId > 0
 5     orderby a.ClientId
 6     select new Model.Client(a)
 7     {
 8         CloudServer = a.CloudServer,
 9         Accounts = a.Accounts
10     };
11 result = query.ToList();
View Code

17. Include 語法

EntityFramework 有Include語法，我們也有，而且是實打實的一次性載入！！！

 1 // Include 語法
 2 query =
 3     context
 4     .GetTable<Model.Client>()
 5     .Include(a => a.CloudServer);
 6 --query =
 7 --    from a in query
 8 --    join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId
 9 --    orderby a.ClientId
10 --    select new Model.Client(a)
11 --    {
12 --        CloudServer = a.CloudServer
13 --    };
14 result = query.ToList();
15 
16 // 還是Include，無限主從孫 ### 
17 query =
18     from a in context
19         .GetTable<Model.Client>()
20         .Include(a => a.Accounts)
21         .Include(a => a.Accounts[0].Markets)
22         .Include(a => a.Accounts[0].Markets[0].Client)
23     where a.ClientId > 0
24     orderby a.ClientId
25     select a;
26 result = query.ToList();
27 
28 // Include 分頁
29 query =
30 from a in context
31     .GetTable<Model.Client>()
32     .Include(a => a.Accounts)
33     .Include(a => a.Accounts[0].Markets)
34     .Include(a => a.Accounts[0].Markets[0].Client)
35 where a.ClientId > 0
36 orderby a.ClientId
37 select a;
38 query = query
39     .Where(a => a.ClientId > 0 && a.CloudServer.CloudServerId > 0)
40     .Skip(10)
41     .Take(20);
42 result = query.ToList();
43 query =
44     from a in context
45         .GetTable<Model.Client>()
46         .Include(a => a.CloudServer)
47         .Include(a => a.Accounts)
48     where a.ClientId > 0
49     select a;
50 query = query.OrderBy(a => a.ClientId);
51 result = query.ToList();
52 
53 // Include 語法查詢 主 從 孫 關係<注：相同的導航屬性不能同時用include和join>
54 var query1 =
55     from a in
56         context
57         .GetTable<Model.Client>()
58         .Include(a => a.CloudServer)
59         .Include(a => a.Accounts)
60         .Include(a => a.Accounts[0].Markets)
61         .Include(a => a.Accounts[0].Markets[0].Client)
62     group a by new { a.ClientId, a.ClientCode, a.ClientName, a.CloudServer.CloudServerId } into g
63     select new Model.Client
64     {
65         ClientId = g.Key.ClientId,
66         ClientCode = g.Key.ClientCode,
67         ClientName = g.Key.ClientName,
68         CloudServerId = g.Key.CloudServerId,
69         Qty = g.Sum(a => a.Qty)
70     };
71 query1 = query1
72     .Where(a => a.ClientId > 0)
73     .OrderBy(a => a.ClientId)
74     .Skip(10)
75     .Take(20)
76     ;
77 var result1 = query1.ToList();
View Code

18. 分組查詢

1 var query2 =
2     from a in context.GetTable<Model.Client>()
3     group a by a.ClientId into g
4     select new
5     {
6         ClientId = g.Key,
7         Qty = g.Sum(a => a.Qty)
8     };
9 query2 = query2.OrderBy(a => a.ClientId).ThenBy(a => a.Qty);
View Code

19. 聚合函式

1 var result1 = query2.Max(a => a.ClientId);
2 var result2 = query2.Sum(a => a.Qty);
3 var result3 = query2.Min(a => a.ClientId);
4 var result4= query2.Average(a => a.Qty);
5 var result5 = query2.Count();
View Code

20. 分組分頁

 1 // 分組後再分頁
 2 var query8 =
 3         from a in context.GetTable<Model.Client>()
 4         where a.ClientName == "TAN"
 5         group a by new { a.ClientId, a.ClientName } into g
 6         where g.Key.ClientId > 0
 7         orderby new { g.Key.ClientName, g.Key.ClientId }
 8         select new
 9         {
10             Id = g.Key.ClientId,
11             Name = g.Min(a => a.ClientId)
12         };
13 query8 = query8.Skip(2).Take(3);
14 var result8 = query8.ToList();
View Code

21. 子查詢

 1 // 強制轉為子查詢
 2 query =
 3         from a in context.GetTable<Model.Client>()
 4         join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId into u_c
 5         from b in u_c.DefaultIfEmpty()
 6         select a;
 7 query = query.OrderBy(a => a.ClientId).Skip(10).Take(10).AsSubQuery();
 8 query = from a in query
 9         join b in context.GetTable<Model.Client>() on a.ClientId equals b.ClientId
10         select a;
11 result = query.ToList();
View Code

22. Any 查詢

 1 // Any
 2 var isAny = context.GetTable<Model.Client>().Any();
 3 isAny = context.GetTable<Model.Client>().Any(a => a.ActiveDate == DateTime.Now);
 4 isAny = context.GetTable<Model.Client>().Distinct().Any(a => a.ActiveDate == DateTime.Now);
 5 isAny = context.GetTable<Model.Client>().OrderBy(a => a.ClientId).Skip(2).Take(5).Any(a => a.ActiveDate == DateTime.Now);
 6 //SQL=> 
 7 //IF EXISTS(
 8 //    SELECT TOP 1 1
 9 //    FROM[Bas_Client] t0
10 //   WHERE t0.[ActiveDate] = '2018-08-15 14:07:09.784'
11 //) SELECT 1 ELSE SELECT 0
View Code

 23. 單個刪除

1 // 1. 刪除單個記錄
2 var demo = new TDemo { DemoId = 1 };
3 context.Delete(demo);
4 context.SubmitChanges();
View Code

24. 批量刪除

1 // 2.WHERE 條件批量刪除
2 context.Delete<TDemo>(a => a.DemoId == 2 || a.DemoId == 3 || a.DemoName == "N0000004");
3 var qeury =
4     context
5     .GetTable<TDemo>()
6     .Where(a => a.DemoId == 2 || a.DemoId == 3 || a.DemoName == "N0000004");
7 // 2.WHERE 條件批量刪除
8 context.Delete<TDemo>(qeury);
9 context.SubmitChanges();
View Code

25. 多表關聯刪除

 1 // 3.Query 關聯批量刪除
 2 var query1 =
 3     from a in context.GetTable<Model.Client>()
 4     join b in context.GetTable<Model.ClientAccount>() on a.ClientId equals b.ClientId
 5     join c in context.GetTable<Model.ClientAccountMarket>() on new { b.ClientId, b.AccountId } equals new { c.ClientId, c.AccountId }
 6     where c.ClientId == 5 && c.AccountId == "1" && c.MarketId == 1
 7 select a;
 8 context.Delete<Model.Client>(query1);
 9 
10 // oracle 不支援導航屬性關聯刪除
11 // 3.Query 關聯批量刪除
12 var query2 =
13     from a in context.GetTable<Model.Client>()
14     join b in context.GetTable<Model.ClientAccount>() on a.ClientId equals b.ClientId
15     where a.CloudServer.CloudServerId == 20 && a.LocalServer.CloudServerId == 2
16     select a;
17 context.Delete<Model.Client>(query2);
18 // 4.Query 關聯批量刪除
19 var query3 =
20     from a in context.GetTable<Model.Client>()
21     where a.CloudServer.CloudServerId == 20 && a.LocalServer.CloudServerId == 2
22     select a;
23 context.Delete<Model.Client>(query3);
24 
25 
26  // 5.子查詢批量刪除
27 // 子查詢更新
28 var sum =
29     from a in context.GetTable<Model.ClientAccount>()
30     where a.ClientId <= 20
31     group a by new { a.ClientId } into g
32     select new Model.Client
33     {
34         ClientId = g.Key.ClientId,
35         Qty = g.Sum(a => a.Qty)
36     };
37 var query4 =
38     from a in context.GetTable<Model.Client>()
39     join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId
40     join c in context.GetTable<Model.CloudServer>() on a.CloudServerId equals c.CloudServerId
41     join d in sum on a.ClientId equals d.ClientId
42     where a.ClientId > 10 && a.CloudServerId < 0
43     select a;
44 context.Delete<Model.Client>(query4);
View Code

26. 單個更新

1 var demo = context
2     .GetTable<TDemo>()
3     .FirstOrDefault(x => x.DemoId > 0);
4 
5 // 整個實體更新
6 demo.DemoName = "001'.N";
7 context.Update(demo);
8 context.SubmitChanges();
View Code

27.批量更新

1 // 2.WHERE 條件批量更新
2 context.Update<TDemo>(x => new TDemo
3 {
4     DemoDateTime2 = DateTime.UtcNow,
5     DemoDateTime2_Nullable = null,
6     //DemoTime_Nullable = ts
7 }, x => x.DemoName == "001'.N" || x.DemoCode == "001'.N");
8 context.SubmitChanges();
View Code

28. 多表關聯更新

這裡還支援將B表欄位的值更新回A表，有多方便你自己體會。事先宣告，Oracle和Postgre是不支援這種sao操作的。

 1 // 3.Query 關聯批量更新
 2     var query =
 3     from a in context.GetTable<Model.Client>()
 4     where a.CloudServer.CloudServerId != 0
 5     select a;
 6 context.Update<Model.Client>(a => new Model.Client
 7 {
 8     Remark = "001.TAN"
 9 }, query);
10 
11 // 更新本表值等於從表的欄位值
12 query =
13     from a in context.GetTable<Model.Client>()
14     join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId
15     join c in context.GetTable<Model.ClientAccount>() on a.ClientId equals c.ClientId
16     where c.AccountId == "12"
17     select a;
18 context.Update<Model.Client, Model.CloudServer>((a, b) => new Model.Client
19 {
20     CloudServerId = b.CloudServerId,
21     Remark = "001.TAN"
22 }, query);
23 context.SubmitChanges();
View Code

29. 子查詢更新

 1  // 子查詢更新
 2 var sum =
 3     from a in context.GetTable<Model.ClientAccount>()
 4     where a.ClientId > 0
 5     group a by new { a.ClientId } into g
 6     select new Model.Client
 7     {
 8         ClientId = g.Key.ClientId,
 9         Qty = g.Sum(a => a.Qty)
10     };
11 if (_databaseType == DatabaseType.SqlServer || _databaseType == DatabaseType.MySql)
12 {
13     var uQuery =
14         from a in context.GetTable<Model.Client>()
15         join b in sum on a.ClientId equals b.ClientId
16         where a.ClientId > 0 && b.ClientId > 0
17         select a;
18     context.Update<Model.Client, Model.Client>((a, b) => new Model.Client { Qty = b.Qty }, uQuery);
19 }
20 else
21 {
22     // npg oracle 翻譯成 EXISTS,更新欄位的值不支援來自子查詢
23     var uQuery =
24         from a in context.GetTable<Model.Client>()
25         join b in sum on a.ClientId equals b.ClientId
26         where a.ClientId > 0 // b.ClientId > 0
27         select a;
28     context.Update<Model.Client>(a => new Model.Client { Qty = 9 }, uQuery);
29 }
30 context.SubmitChanges();
View Code

30. 帶自增列新增

 1 // 帶自增列
 2 var demo = new TDemo
 3 {
 4     DemoCode = "D0000001",
 5     DemoName = "N0000001",
 6     DemoBoolean = true,
 7     DemoChar = 'A',
 8     DemoNChar = 'B',
 9     DemoByte = 64,
10     DemoDate = DateTime.Now,
11     DemoDateTime = DateTime.Now,
12     DemoDateTime2 = DateTime.Now,
13     DemoDecimal = 64,
14     DemoDouble = 64,
15     DemoFloat = 64,
16     DemoGuid = Guid.NewGuid(),
17     DemoShort = 64,
18     DemoInt = 64,
19     DemoLong = 64
20 };
21 context.Insert(demo);
22 context.SubmitChanges();
23 
24 var demo2 = new TDemo
25 {
26     DemoCode = "D0000002",
27     DemoName = "N0000002",
28     DemoBoolean = true,
29     DemoChar = 'A',
30     DemoNChar = 'B',
31     DemoByte = 65,
32     DemoDate = DateTime.Now,
33     DemoDateTime = DateTime.Now,
34     DemoDateTime2 = DateTime.Now,
35     DemoDecimal = 65,
36     DemoDouble = 65,
37     DemoFloat = 65,
38     DemoGuid = Guid.NewGuid(),
39     DemoShort = 65,
40     DemoInt = 65,
41     DemoLong = 65
42 };
43 context.Insert(demo2);
44 
45 var demo3 = new TDemo
46 {
47     DemoCode = "D0000003",
48     DemoName = "N0000003",
49     DemoBoolean = true,
50     DemoChar = 'A',
51     DemoNChar = 'B',
52     DemoByte = 66,
53     DemoDate = DateTime.Now,
54     DemoDateTime = DateTime.Now,
55     DemoDateTime2 = DateTime.Now,
56     DemoDecimal = 66,
57     DemoDouble = 66,
58     DemoFloat = 66,
59     DemoGuid = Guid.NewGuid(),
60     DemoShort = 66,
61     DemoInt = 66,
62     DemoLong = 66
63 };
64 context.Insert(demo3);
65 context.Insert(demo);
66 context.SubmitChanges();
View Code

31. 批量新增

批量新增翻譯的SQL不帶引數，只是純SQL文字。SQLSERVER的同學如果想更快，可以嚐嚐 SqlDbContext.BulkCopy方法。

 1  // 批量增加
 2 // 產生 INSERT INTO VALUES(),(),()... 語法。注意這種批量增加的方法並不能給自增列自動賦值
 3 context.Delete<TDemo>(x => x.DemoId > 1000000);
 4 demos = new List<TDemo>();
 5 for (int i = 0; i < 1002; i++)
 6 {
 7     TDemo d = new TDemo
 8     {
 9         DemoCode = "D0000001",
10         DemoName = "N0000001",
11         DemoBoolean = true,
12         DemoChar = 'A',
13         DemoNChar = 'B',
14         DemoByte = 64,
15         DemoDate = DateTime.Now,
16         DemoDateTime = DateTime.Now,
17         DemoDateTime2 = DateTime.Now,
18         DemoDecimal = 64,
19         DemoDouble = 64,
20         DemoFloat = 64,
21         DemoGuid = Guid.NewGuid(),
22         DemoShort = 64,
23         DemoInt = 64,
24         DemoLong = 64
25     };
26     demos.Add(d);
27 }
28 context.Insert<TDemo>(demos);
29 context.SubmitChanges();
View Code

32. 關聯查詢新增

 1  // 子查詢增
 2 var sum =
 3     from a in context.GetTable<Model.ClientAccount>()
 4     where a.ClientId > 0
 5     group a by new { a.ClientId } into g
 6     select new Model.Client
 7     {
 8         ClientId = g.Key.ClientId,
 9         Qty = g.Sum(a => a.Qty)
10     };
11 sum = sum.AsSubQuery();
12 
13 maxId = context.GetTable<Model.Client>().Max(x => x.ClientId);
14 nextId = maxId + 1;
15 var nQuery =
16     from a in sum
17     join b in context.GetTable<Model.Client>() on a.ClientId equals b.ClientId into u_b
18     from b in u_b.DefaultIfEmpty()
19     where b.ClientId == null
20     select new Model.Client
21     {
22         ClientId = SqlMethod.RowNumber<int>(x => a.ClientId) + nextId,
23         ClientCode = "ABC3",
24         ClientName = "啊啵呲3",
25         CloudServerId = 11,
26         State = 3,
27         Qty = a.Qty,
28     };
29 context.Insert(nQuery);
View Code

33. 增刪改同時查出資料

 1 context.Update<Model.Client>(x => new Model.Client
 2 {
 3     ClientName = "蒙3"
 4 }, x => x.ClientId == 3);
 5 var query =
 6     from a in context.GetTable<Model.Client>()
 7     where a.ClientId == 1
 8     select 5;
 9 context.AddQuery(query);
10 List<int> result1 = null;
11 context.SubmitChanges(out result1);
12 
13 context.Update<Model.Client>(x => new Model.Client
14 {
15     ClientName = "蒙4"
16 }, x => x.ClientId == 4);
17 query =
18     from a in context.GetTable<Model.Client>()
19     where a.ClientId == 1
20     select 5;
21 context.AddQuery(query);
22 var query2 =
23     from a in context.GetTable<Model.Client>()
24     where a.ClientId == 1
25     select 6;
26 context.AddQuery(query2);
27 result1 = null;
28 List<int> result2 = null;
29 context.SubmitChanges(out result1, out result2);
View Code

34. 一次性載入多個列表

 1 // 一性載入多個列表 ****
 2 var query3 =
 3     from a in context.GetTable<Model.Client>()
 4     where a.ClientId >= 1 && a.ClientId <= 10
 5     select 5;
 6 var query4 =
 7     from a in context.GetTable<Model.Client>()
 8     where a.ClientId >= 1 && a.ClientId <= 10
 9     select 6;
10 var tuple = context.Database.ExecuteMultiple<int, int>(query3, query4);
11 
12 query3 =
13     from a in context.GetTable<Model.Client>()
14     where a.ClientId >= 1 && a.ClientId <= 10
15     select 5;
16 query4 =
17     from a in context.GetTable<Model.Client>()
18     where a.ClientId >= 1 && a.ClientId <= 10
19     select 6;
20 var  query5 =
21     from a in context.GetTable<Model.Client>()
22     where a.ClientId >= 1 && a.ClientId <= 10
23     select 7;
24 var tuple2 = context.Database.ExecuteMultiple<int, int, int>(query3, query4, query5);
View Code

35. 事務操作

借鑑 EntityFramework的思想，本框架也支援自身開啟事務，或者從其它上下文開啟事務後再在本框架使用該事務。

// 事務1. 上下文獨立事務
try
{
    using (var transaction = context.Database.BeginTransaction())
    {
        var result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId <= 10);
        context.Update<Model.Client>(x => new Model.Client
        {
            ClientName = "事務1"
        }, x => x.ClientId == result.ClientId);
        context.SubmitChanges();
        result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId == result.ClientId);

        context.Update<Model.Client>(x => new Model.Client
        {
            ClientName = "事務2"
        }, x => x.ClientId == result.ClientId);
        context.SubmitChanges();
        result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId == result.ClientId);

        //throw new Exception("假裝異常");
        //transaction.Rollback();
        transaction.Commit();
    }
}
finally
{
    // 開啟事務後必需顯式釋放資源
    context.Dispose();
}

// 事務2. 使用其它的事務
IDbTransaction transaction2 = null;
IDbConnection connection = null;
try
{
    connection = context.Database.DbProviderFactory.CreateConnection();
    connection.ConnectionString = context.Database.ConnectionString;
    if (connection.State != ConnectionState.Open) connection.Open();
    transaction2 = connection.BeginTransaction();

    // 指定事務
    context.Database.Transaction = transaction2;

    var result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId <= 10);
    context.Update<Model.Client>(x => new Model.Client
    {
        ClientName = "事務3"
    }, x => x.ClientId == result.ClientId);
    context.SubmitChanges();
    result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId == result.ClientId);

    context.Update<Model.Client>(x => new Model.Client
    {
        ClientName = "事務4"
    }, x => x.ClientId == result.ClientId);
    result = context.GetTable<Model.Client>().FirstOrDefault(x => x.ClientId == result.ClientId);

    string sql = @"UPDATE Bas_Client SET ClientName = N'事務5' WHERE ClientID=2;UPDATE Bas_Client SET ClientName = N'事務6' WHERE ClientID=3;";
    context.AddQuery(sql);
    context.SubmitChanges();


    transaction2.Commit();
}
catch
{
    if (transaction2 != null) transaction2.Rollback();
    throw;
}
finally
{
    if (transaction2 != null) transaction2.Dispose();
    if (connection != null) connection.Close();
    if (connection != null) connection.Dispose();

    context.Dispose();
}
View Code

36. 表變數

SQLSERVER的童鞋看過來，你要的爽本框架都能給~

 1 // 宣告表變數
 2 var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo<SqlServerModel.JoinKey>();
 3 context.AddQuery(string.Format("DECLARE {0} [{1}]", typeRuntime.TableName, typeRuntime.TableName.TrimStart('@')));
 4 List<SqlServerModel.JoinKey> keys = new List<SqlServerModel.JoinKey>
 5 {
 6     new SqlServerModel.JoinKey{ Key1 = 2 },
 7     new SqlServerModel.JoinKey{ Key1 = 3 },
 8 };
 9 // 向表變數寫入資料
10 context.Insert<SqlServerModel.JoinKey>(keys);
11 // 像物理表一樣操作表變數
12 var query =
13     from a in context.GetTable<Model.Client>()
14     join b in context.GetTable<SqlServerModel.JoinKey>() on a.ClientId equals b.Key1
15     select a;
16 context.AddQuery(query);
17 // 提交查詢結果
18 List<Model.Client> result = null;
19 context.SubmitChanges(out result);
View Code

【結語】

　　XFramework 已成熟運用於我們公司的多個核心專案，完全代替了之前的 Dapper + DbHelper的資料持久方案。從最初只支援SQLSERVER到支援MySQL、Postgre和Oracle，一個多月的熬夜堅持，箇中酸爽只有經歷過才能體會。你的喜愛和支援是我在開源的路上一路狂奔的最大動力，擼碼不易，不喜請輕噴。但我相信，這絕對是一款人性化、有溫度的資料持久框架！！！
　　XFramework 現已完全開源，遵循 Apache2.0 協議，託管地址：
　　碼雲：https://gitee.com/zame/XFramework
　　GitHub：https://github.com/TANZAME/XFramework

　　ORM 技術交流群：733941663（一群，已滿） 816425449（二群）

　　

相關文章