【前言】
接上一篇《【原创】打造基于Dapper的数据访问层》,Dapper在应付多表自由关联、分组查询、匿名查询等应用场景时不免显得吃力,经常要手写SQL语句(或者用工具生成SQL配置文件)。试想一下,项目中整个DAL层都塞满了SQL语句,对于后期维护来说无异于天灾人祸,这个坑谁踩谁知道。本框架在API设计上最大程度地借鉴 EntityFramework 的写法,干净的实体,丝滑的增删改查,稳健的导航属性,另外还支持链式查询(点标记)、查询表达式、聚合查询等等。在实体映射转换层面,使用 Emit 来动态构建绑定指令,性能最大限度地接近原生水平。
【XFramework 亮点】
- 原生.NET语法,零学习成本
- 支持LINQ查询、拉姆达表达式
- 支持批量增删改查和多表更新
- 支持 SqlServer、MySql、Postgre、Oracle,.NET Core
- 最大亮点,真正支持一对一、一对多导航属性。这一点相信现有开源的ORM没几个敢说它支持的
- 实体字段类型不必与数据库的类型一致
- 支持临时表、表变量操作
- 其它更多亮点,用了你就会知道
【性能】
看看与EntityFramework的性能对比,机器配置不同跑出来的结果可能也不一样,仅供参考。需要特别说明的是EntityFramework是用了AsNoTracking的,不然有缓存的话就没有比较的意义了。
扯个题外话,有些ORM说比EntityFramework快百分多少多少,看起来挺美实际上在我看来那是在扯淡,没有任何参考价值。限制实体字段类型必须与数据库的一致不说,甚至连导航属性这种最基本的功能都不支持,这不在同一个等级同一个体量上的东西非要扯在一起比谁快,确定要这么幽默吗?本人非常推崇开源,也很尊重致力于开源的同行,但是像这种行为就非常有必要出来打假一波了。
【功能说明】
1. 实体定义
1.1. 如果类有 TableAttribute,则用 TableAttribute 指定的名称做为表名,否则用类名称做为表名
1.2. 实体的字段可以指定 ColumnAttribute 特性来说明实体字段与表字段的对应关系,删除/更新时如果传递的参数是一个实体,必须使用 [Column(IsKey = true)] 指定实体的主键
1.3. ForeignKeyAttribute 指定外键,一对多外键时类型必须是 IList<T> 或者 List<T>
1.4 ColumnAttribute.DataType 用来指定表字段类型。以SQLSERVER为例,System.String 默认对应 nvarchar 类型。若是varchar类型,需要指定[Column(DbType= DbType.AnsiString)]
[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>
/// clientcode
/// </summary>
public virtual string ClientCode { get; set; }
/// <summary>
/// clientname
/// </summary>
public virtual string ClientName { get; set; }
/// <summary>
/// cloudserverid
/// </summary>
[Column(Default = 0)]
public virtual int CloudServerId { get; set; }
/// <summary>
/// activedate
/// </summary>
public virtual Nullable<DateTime> ActiveDate { get; set; }
/// <summary>
/// qty
/// </summary>
[Column(Default = 0)]
public virtual int Qty { get; set; }
/// <summary>
/// state
/// </summary>
public virtual byte State { get; set; }
/// <summary>
/// remark
/// </summary>
[Column(Default = "'默认值'")]
public virtual string Remark { get; set; }
[ForeignKey("CloudServerId")]
public virtual CloudServer CloudServer { get; set; }
[ForeignKey("CloudServerId")]
public virtual CloudServer LocalServer { 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 MyMySqlDbContext(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,
DemoName = a.DemoName,
DemoDateTime_Nullable = a.DemoDateTime_Nullable,
DemoDate = sDate,
DemoDateTime = sDate,
DemoDateTime2 = sDate_null,
DemoGuid = guid,
DemoEnum = Model.State.Complete, // 枚举类型支持
DemoEnum2 = Model.State.Executing,
};
var result0 = dynamicQuery.ToList();
// 点标记
dynamicQuery = context
.GetTable<TDemo>()
.Where(a => a.DemoId <= 10)
.Select(a => new
{
DemoId = 13,
DemoCode = a.DemoCode,
DemoName = a.DemoName,
DemoDateTime_Nullable = a.DemoDateTime_Nullable,
DemoDate = sDate,
DemoDateTime = sDate,
DemoDateTime2 = sDate_null,
DemoGuid = Guid.NewGuid(),
DemoEnum = Model.State.Complete,
DemoEnum2 = Model.State.Executing
});
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"),
DemoName = a.DemoId.ToString(),
DemoDateTime_Nullable = a.DemoDateTime_Nullable,
DemoDate = sDate,
DemoDateTime = sDate,
DemoDateTime2 = sDate
};
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 == "张三" ? "李四" : "王五",
DemoName = a.DemoCode == "张三" ? "李四" : "王五",
DemoChar = 'A',
DemoNChar = 'B',
DemoDateTime_Nullable = a.DemoDateTime_Nullable,
DemoDate = sDate,
DemoDateTime = sDate,
DemoDateTime2 = sDate
});
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();
12. 左联查询
注意看第二个左关联,使用常量作为关联键,翻译出来的SQL语句大概是这样的:
SELECT ***
FROM [Bas_Client] t0
LEFT JOIN [Sys_CloudServer] t1 ON t0.[CloudServerId] = t1.[CloudServerId] AND N'567' = t1.[CloudServerCode]
WHERE t1.[CloudServerName] IS NOT NULL
有没有看到熟悉的味道,兄dei?
// 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();
15. 导航属性
// 更简单的赋值方式
// 适用场景:在显示列表时只想显示外键表的一两个字段
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);
--query =
-- from a in query
-- join b in context.GetTable<Model.CloudServer>() on a.CloudServerId equals b.CloudServerId
-- orderby a.ClientId
-- select new Model.Client(a)
-- {
-- CloudServer = 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",
DemoBoolean = true,
DemoChar = 'A',
DemoNChar = 'B',
DemoByte = 64,
DemoDate = DateTime.Now,
DemoDateTime = DateTime.Now,
DemoDateTime2 = DateTime.Now,
DemoDecimal = 64,
DemoDouble = 64,
DemoFloat = 64,
DemoGuid = Guid.NewGuid(),
DemoShort = 64,
DemoInt = 64,
DemoLong = 64
};
context.Insert(demo);
context.SubmitChanges();
var demo2 = new TDemo
{
DemoCode = "D0000002",
DemoName = "N0000002",
DemoBoolean = true,
DemoChar = 'A',
DemoNChar = 'B',
DemoByte = 65,
DemoDate = DateTime.Now,
DemoDateTime = DateTime.Now,
DemoDateTime2 = DateTime.Now,
DemoDecimal = 65,
DemoDouble = 65,
DemoFloat = 65,
DemoGuid = Guid.NewGuid(),
DemoShort = 65,
DemoInt = 65,
DemoLong = 65
};
context.Insert(demo2);
var demo3 = new TDemo
{
DemoCode = "D0000003",
DemoName = "N0000003",
DemoBoolean = true,
DemoChar = 'A',
DemoNChar = 'B',
DemoByte = 66,
DemoDate = DateTime.Now,
DemoDateTime = DateTime.Now,
DemoDateTime2 = DateTime.Now,
DemoDecimal = 66,
DemoDouble = 66,
DemoFloat = 66,
DemoGuid = Guid.NewGuid(),
DemoShort = 66,
DemoInt = 66,
DemoLong = 66
};
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",
DemoBoolean = true,
DemoChar = 'A',
DemoNChar = 'B',
DemoByte = 64,
DemoDate = DateTime.Now,
DemoDateTime = DateTime.Now,
DemoDateTime2 = DateTime.Now,
DemoDecimal = 64,
DemoDouble = 64,
DemoFloat = 64,
DemoGuid = Guid.NewGuid(),
DemoShort = 64,
DemoInt = 64,
DemoLong = 64
};
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",
ClientName = "啊啵呲3",
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,一个多月的熬夜坚持,个中酸爽只有经历过才能体会。你的喜爱和支持是我在开源的路上一路狂奔的最大动力,撸码不易,不喜请轻喷。但我相信,这绝对是一款人性化、有温度的数据持久框架!!!
XFramework 现已完全开源,遵循 Apache2.0 协议,托管地址:
码云:https://gitee.com/zame/XFramework
GitHub:https://github.com/TANZAME/XFramework
ORM 技术交流群: 816425449