【原创】基于.NET的轻量级高性能 ORM - Riz.XFramework

前言

接上一篇《【原创】打造基于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();`

左联查询

注意看第二个左关联，使用常量作为关联键，翻译出来的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();`

导航属性

`// 更简单的赋值方式`
`// 适用场景：在显示列表时只想显示外键表的一两个字段`
`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 协议，托管地址：
码云：gitee.com/TANZAME/Riz…
GitHub：github.com/TANZAME/Riz…

技术交流群:816425449