+-
首页 专栏 c# 文章详情
畴丸 发布于 4 月 25 日
关注作者
关注作者
0
【原创】基于.NET的轻量级高性能 ORM - Riz.XFramework
畴丸 发布于 4 月 25 日
前言
接上一篇《【原创】打造基于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
c#
阅读 60 发布于 4 月 25 日
举报
赞
收藏
分享
本作品系原创， 采用《署名-非商业性使用-禁止演绎 4.0 国际》许可协议
畴丸
为者常成，行者常至
1 声望
0 粉丝
0 条评论
得票数 最新
提交评论
畴丸
为者常成，行者常至
1 声望
0 粉丝
宣传栏
目录
▲
前言
接上一篇《【原创】打造基于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
