乐趣区

关于c#:原创基于NET的轻量级高性能-ORM-RizXFramework

jiezi

前言

接上一篇《【原创】打造基于 Dapper 的数据拜访层》，Dapper 在应酬多表自在关联、分组查问、匿名查问等利用场景时未免显得吃力，常常要手写 SQL 语句（或者用工具生成 SQL 配置文件）。试想一下，我的项目中整个 DAL 层都塞满了 SQL 语句，对于前期保护来说无异于天下大乱，这个坑谁踩谁晓得。本框架在 API 设计上最大水平地借鉴 EntityFramework 的写法，洁净的实体，丝滑的增删改查，持重的导航属性，另外还反对链式查问（点标记）、查问表达式、聚合查问等等。在实体映射转换层面，应用 Emit 来动静构建绑定指令，性能最大限度地靠近原生程度。

XFramework 亮点

  • 原生.NET 语法，零学习老本
  • 反对 LINQ 查问、拉姆达表达式
  • 反对批量增删改查和多表关联更新
  • 反对 SqlServer、MySql、Postgre、Oracle，.NET Core
  • 最大亮点，真正反对一对一、一对多导航属性。这一点置信现有开源的 ORM 没几个敢说它反对的
  • 实体字段类型不用与数据库的类型统一
  • 反对长期表、表变量操作
  • 提供原生 ADO 操作
  • 其它更多亮点，用了你就会晓得

性能

看看与 EntityFramework 的性能比照，机器配置不同跑进去的后果可能也不一样，仅供参考。须要特地阐明的是 EntityFramework 是用了 AsNoTracking 的，不然有缓存的话就没有比拟的意义了

性能阐明

1. 实体定义

  • 如果类有 TableAttribute，则用 TableAttribute 指定的名称做为表名，否则用类名称做为表名
  • 实体的字段能够指定 ColumnAttribute 个性来阐明实体字段与表字段的对应关系，删除 / 更新时如果传递的参数是一个实体，必须应用 [Column(IsKey = true)] 指定实体的主键
  • ForeignKeyAttribute 指定外键，一对多外键时类型必须是 IList 或者 List
  • ColumnAttribute.DataType 用来指定表字段类型。以 SQLSERVER 为例，System.String 默认对应 nvarchar 类型。若是 varchar 类型，须要指定 [Column(DbType= DbType.AnsiString)]
`[Table(Name = "Bas_Client")]`
`public partial class Client`
`{`
 `/// <summary>`
 `/// 初始化 <see cref="Client"/> 类的新实例 `
 `/// </summary>`
 `public Client()`
 `{`
 `this.CloudServerId = 0;`
 `this.Qty = 0;`
 `this.HookConstructor();`
 `}`
 `/// <summary>`
 `/// 初始化 <see cref="Client"/> 类的新实例 `
 `/// </summary>`
 `public Client(Client model)`
 `{`
 `this.CloudServerId = 0;`
 `this.Qty = 0;`
 `this.HookConstructor();`
 `}`
 `/// <summary>`
 `/// clientid`
 `/// </summary>`
 `[Column(IsKey = true)]`
 `public virtual int ClientId {get; set;}`
 `/// <summary>`
 `/// activedate`
 `/// </summary>`
 `public virtual Nullable<DateTime> ActiveDate {get; set;}`
 `/// remark`
 `/// </summary>`
 `[Column(Default = "'默认值'")]`
 `public virtual string Remark {get; set;}`
 `[ForeignKey("CloudServerId")]`
 `public virtual CloudServer CloudServer {get; set;}`
 `[ForeignKey("ClientId")]`
 `public virtual List<ClientAccount> Accounts {get; set;}`
 `/// <summary>`
 `/// 构造函数勾子 `
 `/// </summary>`
 `partial void HookConstructor();`
`}`

2. 上下文定义 

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

3. 匿名类型 

`//// 匿名类 `
`var guid = Guid.NewGuid();`
`var dynamicQuery =`
 `from a in context.GetTable<TDemo>()`
 `where a.DemoId <= 10`
 `select new`
 `{`
 `DemoId = 12,`
 `DemoCode = a.DemoCode,`
 `DemoEnum = Model.State.Complete,// 枚举类型反对 `
 `};`
`var result = dynamicQuery.ToList();`
`// 点标记 `
`dynamicQuery = context`
 `.GetTable<TDemo>()`
 `.Where(a => a.DemoId <= 10)`
 `.Select(a => new`
 `{`
 `DemoId = 12,`
 `DemoCode = a.DemoCode,`
 `DemoEnum = Model.State.Complete,// 枚举类型反对 `
 `});`
`result0 = dynamicQuery.ToList();`

4. 所有字段 

`// Date,DateTime,DateTime2 反对 `
`var query =`
 `from a in context.GetTable<TDemo>()`
 `where a.DemoId <= 10 && a.DemoDate > sDate && a.DemoDateTime >= sDate && a.DemoDateTime2 > sDate`
 `select a;`
`var result1 = query.ToList();`
`// 点标记 `
`query = context`
 `.GetTable<TDemo>()`
 `.Where(a => a.DemoId <= 10 && a.DemoDate > sDate && a.DemoDateTime >= sDate && a.DemoDateTime2 > sDate);`
`result1 = query.ToList();`

5. 指定字段 

`// 指定字段 `
`query = from a in context.GetTable<TDemo>()`
 `where a.DemoId <= 10`
 `select new TDemo`
 `{`
 `DemoId = (int)a.DemoId,`
 `DemoCode = (a.DemoCode ?? "N001")`
 `};`
`result1 = query.ToList();`
`// 点标记 `
`query = context`
 `.GetTable<TDemo>()`
 `.Where(a => a.DemoCode != a.DemoId.ToString() && a.DemoName != a.DemoId.ToString() && a.DemoChar == 'A' && a.DemoNChar == 'B')`
 `.Select(a => new TDemo`
 `{`
 `DemoId = a.DemoId,`
 `DemoCode = a.DemoName == "张三" ? "李四" : "王五",`
 `});`
`result1 = query.ToList();`

6. 构造函数

用过 EntityFramework 的同学都晓得，如果要通过构造函数的形式查问指定字段，除非老老实实从新定义一个新的实体，否则一个“The entity or complex type cannot be constructed in a LINQ to Entities query“的异样马上给甩你脸上。XFramework 框架的这个用法，就是为了让你远离这会呼吸的痛！~

`// 构造函数 `
`var query =`
 `from a in context.GetTable<Model.Demo>()`
 `where a.DemoId <= 10`
 `select new Model.Demo(a);`
`var r1 = query.ToList();`
`//SQL=>`
`//SELECT`
`//t0.[DemoId] AS [DemoId],`
`//t0.[DemoCode] AS [DemoCode],`
`//t0.[DemoName] AS [DemoName],`
`//...`
`//FROM [Sys_Demo] t0`
`//WHERE t0.[DemoId] <= 10`
`query =`
 `from a in context.GetTable<Model.Demo>()`
 `where a.DemoId <= 10`
 `select new Model.Demo(a.DemoId, a.DemoName);`
`r1 = query.ToList();`

7. 分页查问 

`// 分页查问 `
`// 1. 不是查问第一页的内容时，必须先 OrderBy 再分页，OFFSET ... Fetch Next 分页语句要求有 OrderBy`
`// 2.OrderBy 表达式里边的参数必须跟 query 里边的变量名统一，如此例里的 a。SQL 解析时依据此变更生成表别名 `
`query = from a in context.GetTable<TDemo>()`
 `orderby a.DemoCode`
 `select a;`
`query = query.Skip(1).Take(18);`
`result1 = query.ToList();`
`// 点标记 `
`query = context`
 `.GetTable<TDemo>()`
 `.OrderBy(a => a.DemoCode)`
 `.Skip(1)`
 `.Take(18);`
`result1 = query.ToList();`

8. 过滤条件 

`// 过滤条件 `
`query = from a in context.GetTable<TDemo>()`
 `where a.DemoName == "D0000002" || a.DemoCode == "D0000002"`
 `select a;`
`result1 = query.ToList();`
`// 点标记 `
`query = context.GetTable<TDemo>().Where(a => a.DemoName == "D0000002" || a.DemoCode == "D0000002");`
`result1 = query.ToList();`
`query = context.GetTable<TDemo>().Where(a => a.DemoName.Contains("004"));`
`result1 = query.ToList();`
`query = context.GetTable<TDemo>().Where(a => a.DemoCode.StartsWith("Code000036"));`
`result1 = query.ToList();`
`query = context.GetTable<TDemo>().Where(a => a.DemoCode.EndsWith("004"));`
`result1 = query.ToList();`

9. 更多条件 

`// 反对的查问条件 `
`// 辨别 nvarchar,varchar,date,datetime,datetime2 字段类型 `
`// 反对的字符串操作 => Trim | TrimStart | TrimEnd | ToString | Length`
`int m_byte = 9;`
`Model.State state = Model.State.Complete;`
`query = from a in context.GetTable<TDemo>()`
 `where`
 `a.DemoCode == "002" &&`
 `a.DemoName == "002" &&`
 `a.DemoCode.Contains("TAN") &&                                   // LIKE '%%'`
 `a.DemoName.Contains("TAN") &&                                   // LIKE '%%'`
 `a.DemoCode.StartsWith("TAN") &&                                 // LIKE 'K%'`
 `a.DemoCode.EndsWith("TAN") &&                                   // LIKE '%K'`
 `a.DemoCode.Length == 12 &&                                      // LENGTH`
 `a.DemoCode.TrimStart() == "TF" &&`
 `a.DemoCode.TrimEnd() == "TF" &&`
 `a.DemoCode.TrimEnd() == "TF" &&`
 `a.DemoCode.Substring(0) == "TF" &&`
 `a.DemoDate == DateTime.Now &&`
 `a.DemoDateTime == DateTime.Now &&`
 `a.DemoDateTime2 == DateTime.Now &&`
 `a.DemoName == (`
 `a.DemoDateTime_Nullable == null ? "NULL" : "NOT NULL") &&   // 三元表达式 `
 `a.DemoName == (a.DemoName ?? a.DemoCode) &&                     // 二元表达式 `
 `new[] { 1, 2, 3}.Contains(a.DemoId) &&                         // IN(1,2,3)`
 `new List<int> {1, 2, 3}.Contains(a.DemoId) &&                 // IN(1,2,3)`
 `new List<int>(_demoIdList).Contains(a.DemoId) &&                // IN(1,2,3)`
 `a.DemoId == new List<int> {1, 2, 3}[0] &&                     // IN(1,2,3)`
 `_demoIdList.Contains(a.DemoId) &&                          // IN(1,2,3)`
 `a.DemoName == _demoName &&`
 `a.DemoCode == (a.DemoCode ?? "CODE") &&`
 `new List<string> {"A", "B", "C"}.Contains(a.DemoCode) &&`
 `a.DemoByte == (byte)m_byte &&`
 `a.DemoByte == (byte)Model.State.Complete ||`
 `a.DemoInt == (int)Model.State.Complete ||`
 `a.DemoInt == (int)state ||`
 `(a.DemoName == "STATE" && a.DemoName == "REMARK")// OR 查问 `
 `select a;`
`result1 = query.ToList();`

10. DataTable 和 DataSet

`// DataTable`
`query = from a in context.GetTable<TDemo>()`
 `orderby a.DemoCode`
 `select a;`
`query = query.Take(18);`
`var result3 = context.Database.ExecuteDataTable(query);`
`// DataSet`
`var define = query.Resolve();`
`List<DbCommandDefinition> sqlList = new List<DbCommandDefinition> {define, define, define};`
`var result4 = context.Database.ExecuteDataSet(sqlList);`

11. 内联查问 

`// INNER JOIN`
`var query =`
 `from a in context.GetTable<Model.Client>()`
 `join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId`
 `where a.ClientId > 0`
 `select a;`
`var result = query.ToList();`
`// 点标记 `
`query = context`
 `.GetTable<Model.Client>()`
 `.Join(context.GetTable<Model.CloudServer>(), a => a.CloudServerId, b => b.CloudServerId, (a, b) => a)`
 `.Where(a => a.ClientId > 0);`
`result = query.ToList();`
  1. 左联查问

留神看第二个左关联，应用常量作为关联键，翻译进去的 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？

`// LEFT JOIN`
`query =`
 `from a in context.GetTable<Model.Client>()`
 `join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId into u_b`
 `from b in u_b.DefaultIfEmpty()`
 `select a;`
`query = query.Where(a => a.CloudServer.CloudServerName != null);`
`result = query.ToList();`
`// LEFT JOIN`
`query =`
 `from a in context.GetTable<Model.Client>()`
 `join b in context.GetTable<Model.CloudServer>() on new { a.CloudServerId, CloudServerCode = "567"} equals new {b.CloudServerId, b.CloudServerCode} into u_b`
 `from b in u_b.DefaultIfEmpty()`
 `select a;`
`query = query.Where(a => a.CloudServer.CloudServerName != null);`
`result = query.ToList();`

13. 右联查问

左关联和右关联的语法我这里用的是一样的，不过是 DefaultIfEmpty 办法加多了一个重载，DefaultIfEmpty(true) 即示意右关联。

`// RIGHT JOIN`
`query =`
 `from a in context.GetTable<Model.CloudServer>()`
 `join b in context.GetTable<Model.Client>() on a.CloudServerId equals b.CloudServerId into u_b`
 `from b in u_b.DefaultIfEmpty(true)`
 `where a.CloudServerName == null`
 `select b;`
`result = query.ToList();`

14. Union 查问

咱们的 Union 查问反对 UNION 操作后再分页哦~

`// UNION 留神 UNION 分页的写法，仅反对写在最初 `
`var q1 = context.GetTable<Model.Client>().Where(x => x.ClientId == 0);`
`var q2 = context.GetTable<Model.Client>().Where(x => x.ClientId == 0);`
`var q3 = context.GetTable<Model.Client>().Where(x => x.ClientId == 0);`
`var query6 = q1.Union(q2).Union(q3);`
`var result6 = query6.ToList();`
`result6 = query6.Take(2).ToList();`
`result6 = query6.OrderBy(a => a.ClientId).Skip(2).ToList();`
`query6 = query6.Take(2);`
`result6 = query6.ToList();`
`query6 = query6.OrderBy(a => a.ClientId).Skip(1).Take(2);`
`result6 = query6.ToList();`
  1. 导航属性 
`// 更简略的赋值形式 `
`// 实用场景：在显示列表时只想显示外键表的一两个字段 `
`query =`
 `from a in context.GetTable<Model.Client>()`
 `select new Model.Client(a)`
 `{`
 `CloudServer = a.CloudServer,`
 `LocalServer = new Model.CloudServer`
 `{`
 `CloudServerId = a.CloudServerId,`
 `CloudServerName = a.LocalServer.CloudServerName`
 `}`
 `};`
`result = query.ToList();`

16. 一对一一对多导航 

`// 1：1 关系，1：n 关系 `
`query =`
 `from a in context.GetTable<Model.Client>()`
 `where a.ClientId > 0`
 `orderby a.ClientId`
 `select new Model.Client(a)`
 `{`
 `CloudServer = a.CloudServer,`
 `Accounts = a.Accounts`
 `};`
`result = query.ToList();`

17. Include 语法

EntityFramework 有 Include 语法，咱也有，而且是实打实的一次性加载！！！

`// Include 语法 `
`query =`
 `context`
 `.GetTable<Model.Client>()`
 `.Include(a => a.CloudServer);`
`result = query.ToList();`
`// 还是 Include，有限主从孙 ###`
`query =`
 `from a in context`
 `.GetTable<Model.Client>()`
 `.Include(a => a.Accounts)`
 `.Include(a => a.Accounts[0].Markets)`
 `.Include(a => a.Accounts[0].Markets[0].Client)`
 `where a.ClientId > 0`
 `orderby a.ClientId`
 `select a;`
`result = query.ToList();`
`// Include 分页 `
`query =`
`from a in context`
 `.GetTable<Model.Client>()`
 `.Include(a => a.Accounts)`
 `.Include(a => a.Accounts[0].Markets)`
 `.Include(a => a.Accounts[0].Markets[0].Client)`
`where a.ClientId > 0`
`orderby a.ClientId`
`select a;`
`query = query`
 `.Where(a => a.ClientId > 0 && a.CloudServer.CloudServerId > 0)`
 `.Skip(10)`
 `.Take(20);`
`result = query.ToList();`
`query =`
 `from a in context`
 `.GetTable<Model.Client>()`
 `.Include(a => a.CloudServer)`
 `.Include(a => a.Accounts)`
 `where a.ClientId > 0`
 `select a;`
`query = query.OrderBy(a => a.ClientId);`
`result = query.ToList();`
`// Include 语法查问 主 从 孙 关系 < 注：雷同的导航属性不能同时用 include 和 join>`
`var query1 =`
 `from a in`
 `context`
 `.GetTable<Model.Client>()`
 `.Include(a => a.CloudServer)`
 `.Include(a => a.Accounts)`
 `.Include(a => a.Accounts[0].Markets)`
 `.Include(a => a.Accounts[0].Markets[0].Client)`
 `group a by new {a.ClientId, a.ClientCode, a.ClientName, a.CloudServer.CloudServerId} into g`
 `select new Model.Client`
 `{`
 `ClientId = g.Key.ClientId,`
 `ClientCode = g.Key.ClientCode,`
 `ClientName = g.Key.ClientName,`
 `CloudServerId = g.Key.CloudServerId,`
 `Qty = g.Sum(a => a.Qty)`
 `};`
`query1 = query1`
 `.Where(a => a.ClientId > 0)`
 `.OrderBy(a => a.ClientId)`
 `.Skip(10)`
 `.Take(20)`
 `;`
`var result1 = query1.ToList();`

18. 分组查问 

`var query2 =`
 `from a in context.GetTable<Model.Client>()`
 `group a by a.ClientId into g`
 `select new`
 `{`
 `ClientId = g.Key,`
 `Qty = g.Sum(a => a.Qty)`
 `};`
`query2 = query2.OrderBy(a => a.ClientId).ThenBy(a => a.Qty);`

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();`

20. 分组分页 

`// 分组后再分页 `
`var query8 =`
 `from a in context.GetTable<Model.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)`
 `};`
`query8 = query8.Skip(2).Take(3);`
`var result8 = query8.ToList();`

21. 子查问 

`// 强制转为子查问 `
`query =`
 `from a in context.GetTable<Model.Client>()`
 `join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId into u_c`
 `from b in u_c.DefaultIfEmpty()`
 `select a;`
`query = query.OrderBy(a => a.ClientId).Skip(10).Take(10).AsSubQuery();`
`query = from a in query`
 `join b in context.GetTable<Model.Client>() on a.ClientId equals b.ClientId`
 `select a;`
`result = query.ToList();`

22. Any 查问 

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

23. 单个删除 

`1 // 1. 删除单个记录 `
`2 var demo = new TDemo {DemoId = 1};`
`3 context.Delete(demo);`
`4 context.SubmitChanges();`

24. 批量删除 

`// 2.WHERE 条件批量删除 `
`context.Delete<TDemo>(a => a.DemoId == 2 || a.DemoId == 3 || a.DemoName == "N0000004");`
`var qeury =`
 `context`
 `.GetTable<TDemo>()`
 `.Where(a => a.DemoId == 2 || a.DemoId == 3 || a.DemoName == "N0000004");`
`// 2.WHERE 条件批量删除 `
`context.Delete<TDemo>(qeury);`
`context.SubmitChanges();`

25. 多表关联删除 

`// 3.Query 关联批量删除 `
`var query1 =`
 `from a in context.GetTable<Model.Client>()`
 `join b in context.GetTable<Model.ClientAccount>() on a.ClientId equals b.ClientId`
 `join c in context.GetTable<Model.ClientAccountMarket>() on new { b.ClientId, b.AccountId} equals new {c.ClientId, c.AccountId}`
 `where c.ClientId == 5 && c.AccountId == "1" && c.MarketId == 1`
`select a;`
`context.Delete<Model.Client>(query1);`
`// oracle 不反对导航属性关联删除 `
`// 3.Query 关联批量删除 `
`var query2 =`
 `from a in context.GetTable<Model.Client>()`
 `join b in context.GetTable<Model.ClientAccount>() on a.ClientId equals b.ClientId`
 `where a.CloudServer.CloudServerId == 20 && a.LocalServer.CloudServerId == 2`
 `select a;`
`context.Delete<Model.Client>(query2);`
`// 4.Query 关联批量删除 `
`var query3 =`
 `from a in context.GetTable<Model.Client>()`
 `where a.CloudServer.CloudServerId == 20 && a.LocalServer.CloudServerId == 2`
 `select a;`
`context.Delete<Model.Client>(query3);`
 `// 5. 子查问批量删除 `
`// 子查问更新 `
`var sum =`
 `from a in context.GetTable<Model.ClientAccount>()`
 `where a.ClientId <= 20`
 `group a by new {a.ClientId} into g`
 `select new Model.Client`
 `{`
 `ClientId = g.Key.ClientId,`
 `Qty = g.Sum(a => a.Qty)`
 `};`
`var query4 =`
 `from a in context.GetTable<Model.Client>()`
 `join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId`
 `join c in context.GetTable<Model.CloudServer>() on a.CloudServerId equals c.CloudServerId`
 `join d in sum on a.ClientId equals d.ClientId`
 `where a.ClientId > 10 && a.CloudServerId < 0`
 `select a;`
`context.Delete<Model.Client>(query4);`

26. 单个更新 

`var demo = context`
 `.GetTable<TDemo>()`
 `.FirstOrDefault(x => x.DemoId > 0);`
`// 整个实体更新 `
`demo.DemoName = "001'.N";`
`context.Update(demo);`
`context.SubmitChanges();`

27. 批量更新 

`// 2.WHERE 条件批量更新 `
`context.Update<TDemo>(x => new TDemo`
`{`
 `DemoDateTime2 = DateTime.UtcNow,`
 `DemoDateTime2_Nullable = null,`
 `//DemoTime_Nullable = ts`
`}, x => x.DemoName == "001'.N"|| x.DemoCode =="001'.N");`
`context.SubmitChanges();`

28. 多表关联更新

这里还反对将 B 表字段的值更新回 A 表，有多不便你本人领会。当时申明，Oracle 和 Postgre 是不反对这种 sao 操作的。

`// 3.Query 关联批量更新 `
 `var query =`
 `from a in context.GetTable<Model.Client>()`
 `where a.CloudServer.CloudServerId != 0`
 `select a;`
`context.Update<Model.Client>(a => new Model.Client`
`{`
 `Remark = "001.TAN"`
`}, query);`
`// 更新本表值等于从表的字段值 `
`query =`
 `from a in context.GetTable<Model.Client>()`
 `join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId`
 `join c in context.GetTable<Model.ClientAccount>() on a.ClientId equals c.ClientId`
 `where c.AccountId == "12"`
 `select a;`
`context.Update<Model.Client, Model.CloudServer>((a, b) => new Model.Client`
`{`
 `CloudServerId = b.CloudServerId,`
 `Remark = "001.TAN"`
`}, query);`
`context.SubmitChanges();`

29. 子查问更新 

`// 子查问更新 `
`var sum =`
 `from a in context.GetTable<Model.ClientAccount>()`
 `where a.ClientId > 0`
 `group a by new {a.ClientId} into g`
 `select new Model.Client`
 `{`
 `ClientId = g.Key.ClientId,`
 `Qty = g.Sum(a => a.Qty)`
 `};`
`if (_databaseType == DatabaseType.SqlServer || _databaseType == DatabaseType.MySql)`
`{`
 `var uQuery =`
 `from a in context.GetTable<Model.Client>()`
 `join b in sum on a.ClientId equals b.ClientId`
 `where a.ClientId > 0 && b.ClientId > 0`
 `select a;`
 `context.Update<Model.Client, Model.Client>((a, b) => new Model.Client {Qty = b.Qty}, uQuery);`
`}`
`else`
`{`
 `// npg oracle 翻译成 EXISTS, 更新字段的值不反对来自子查问 `
 `var uQuery =`
 `from a in context.GetTable<Model.Client>()`
 `join b in sum on a.ClientId equals b.ClientId`
 `where a.ClientId > 0 // b.ClientId > 0`
 `select a;`
 `context.Update<Model.Client>(a => new Model.Client { Qty = 9}, uQuery);`
`}`
`context.SubmitChanges();`

30. 带自增列新增 

`// 带自增列 `
`var demo = new TDemo`
`{`
 `DemoCode = "D0000001",`
 `DemoName = "N0000001"`
`};`
`context.Insert(demo);`
`context.SubmitChanges();`
`var demo2 = new TDemo`
`{`
 `DemoCode = "D0000002",`
 `DemoName = "N0000002"`
`};`
`context.Insert(demo2);`
`var demo3 = new TDemo`
`{`
 `DemoCode = "D0000003",`
 `DemoName = "N0000003",`
`};`
`context.Insert(demo3);`
`context.Insert(demo);`
`context.SubmitChanges();`

31. 批量新增

批量新增翻译的 SQL 不带参数，只是纯 SQL 文本。SQLSERVER 的同学如果想更快，能够尝尝 SqlDbContext.BulkCopy 办法。

`// 批量减少 `
`// 产生 INSERT INTO VALUES(),(),()... 语法。留神这种批量减少的办法并不能给自增列主动赋值 `
`context.Delete<TDemo>(x => x.DemoId > 1000000);`
`demos = new List<TDemo>();`
`for (int i = 0; i < 1002; i++)`
`{`
 `TDemo d = new TDemo`
 `{`
 `DemoCode = "D0000001",`
 `DemoName = "N0000001"`
 `};`
 `demos.Add(d);`
`}`
`context.Insert<TDemo>(demos);`
`context.SubmitChanges();`

32. 关联查问新增 

`// 子查问增 `
`var sum =`
 `from a in context.GetTable<Model.ClientAccount>()`
 `where a.ClientId > 0`
 `group a by new {a.ClientId} into g`
 `select new Model.Client`
 `{`
 `ClientId = g.Key.ClientId,`
 `Qty = g.Sum(a => a.Qty)`
 `};`
`sum = sum.AsSubQuery();`
`maxId = context.GetTable<Model.Client>().Max(x => x.ClientId);`
`nextId = maxId + 1;`
`var nQuery =`
 `from a in sum`
 `join b in context.GetTable<Model.Client>() on a.ClientId equals b.ClientId into u_b`
 `from b in u_b.DefaultIfEmpty()`
 `where b.ClientId == null`
 `select new Model.Client`
 `{`
 `ClientId = SqlMethod.RowNumber<int>(x => a.ClientId) + nextId,`
 `ClientCode = "ABC3",`
 `CloudServerId = 11,`
 `State = 3,`
 `Qty = a.Qty,`
 `};`
`context.Insert(nQuery);`

33. 增删改同时查出数据 

`context.Update<Model.Client>(x => new Model.Client`
`{`
 `ClientName = "蒙 3"`
`}, x => x.ClientId == 3);`
`var query =`
 `from a in context.GetTable<Model.Client>()`
 `where a.ClientId == 1`
 `select 5;`
`context.AddQuery(query);`
`List<int> result1 = null;`
`context.SubmitChanges(out result1);`
`context.Update<Model.Client>(x => new Model.Client`
`{`
 `ClientName = "蒙 4"`
`}, x => x.ClientId == 4);`
`query =`
 `from a in context.GetTable<Model.Client>()`
 `where a.ClientId == 1`
 `select 5;`
`context.AddQuery(query);`
`var query2 =`
 `from a in context.GetTable<Model.Client>()`
 `where a.ClientId == 1`
 `select 6;`
`context.AddQuery(query2);`
`result1 = null;`
`List<int> result2 = null;`
`context.SubmitChanges(out result1, out result2);`

34. 一次性加载多个列表 

`// 一性加载多个列表 ****`
`var query3 =`
 `from a in context.GetTable<Model.Client>()`
 `where a.ClientId >= 1 && a.ClientId <= 10`
 `select 5;`
`var query4 =`
 `from a in context.GetTable<Model.Client>()`
 `where a.ClientId >= 1 && a.ClientId <= 10`
 `select 6;`
`var tuple = context.Database.ExecuteMultiple<int, int>(query3, query4);`
`query3 =`
 `from a in context.GetTable<Model.Client>()`
 `where a.ClientId >= 1 && a.ClientId <= 10`
 `select 5;`
`query4 =`
 `from a in context.GetTable<Model.Client>()`
 `where a.ClientId >= 1 && a.ClientId <= 10`
 `select 6;`
`var  query5 =`
 `from a in context.GetTable<Model.Client>()`
 `where a.ClientId >= 1 && a.ClientId <= 10`
 `select 7;`
`var tuple2 = context.Database.ExecuteMultiple<int, int, int>(query3, query4, query5);`

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();`
`}`

36. 表变量

SQLSERVER 的童鞋看过去，你要的爽本框架都能给~

`// 申明表变量 `
`var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo<SqlServerModel.JoinKey>();`
`context.AddQuery(string.Format("DECLARE {0} [{1}]", typeRuntime.TableName, typeRuntime.TableName.TrimStart('@')));`
`List<SqlServerModel.JoinKey> keys = new List<SqlServerModel.JoinKey>`
`{`
 `new SqlServerModel.JoinKey{Key1 = 2},`
 `new SqlServerModel.JoinKey{Key1 = 3},`
`};`
`// 向表变量写入数据 `
`context.Insert<SqlServerModel.JoinKey>(keys);`
`// 像物理表一样操作表变量 `
`var query =`
 `from a in context.GetTable<Model.Client>()`
 `join b in context.GetTable<SqlServerModel.JoinKey>() on a.ClientId equals b.Key1`
 `select a;`
`context.AddQuery(query);`
`// 提交查问后果 `
`List<Model.Client> result = null;`
`context.SubmitChanges(out result);`

结语

XFramework 已成熟使用于咱们公司的多个外围我的项目，齐全代替了之前的 Dapper + DbHelper 的数据长久计划。从最后只反对 SQLSERVER 到反对 MySQL、Postgre 和 Oracle，一个多月的熬夜保持，个中酸爽只有经验过能力领会。你的青睐和反对是我在开源的路上一路狂奔的最大能源，撸码不易，不喜请轻喷。但我置信，这相对是一款人性化、有温度的数据长久框架！！！

后记：这篇文件其实是两年前曾经上博客园上发过了，明天在 Github 上发现了 websync 这个一键发多个平台文件的货色，特意拿这篇文件来玩玩，看官不喜勿轻喷。

  • Riz.XFramework 现已齐全开源，遵循 Apache2.0 协定，托管地址：
  • 码云：https://gitee.com/TANZAME/Riz…
  • GitHub：https://github.com/TANZAME/Ri…

技术交换群:816425449

退出移动版