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

前言

接上一篇《【原创】打造基于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 协定，托管地址：
码云：https://gitee.com/TANZAME/Riz...
GitHub：https://github.com/TANZAME/Ri...

技术交换群:816425449