索引: 商業開發實戰總結 一.API 列表 .FirstOrDefaultAsync() .FirstOrDefaultAsync<M>() 如: .FirstOrDefaultAsync<Agent>() , 用於 單表/多表連接 查詢. .FirstOrDefaultAsync<VM>() 如: ...
索引:
一.API 列表
.FirstOrDefaultAsync()
.FirstOrDefaultAsync<M>()
如: .FirstOrDefaultAsync<Agent>() , 用於 單表/多表連接 查詢.
.FirstOrDefaultAsync<VM>()
如: .FirstOrDefaultAsync<AgentVM>() , 用於 單表 查詢.
.FirstOrDefaultAsync<T>(Expression<Func<M, T>> columnMapFunc)
如: .FirstOrDefaultAsync<Guid>(it => it.Id) , 用於 單表 單列 查詢.
或者:
.FirstOrDefaultAsync<AgentVM>(it => new AgentVM
{
XXXX = it.Name,
YYYY = it.PathId
}) , 用於 單表 多列 查詢.
.FirstOrDefaultAsync<T>(Expression<Func<T>> columnMapFunc)
如: .Queryer<Agent, AgentInventoryRecord>(out var agent, out var agentRecord)
......
.FirstOrDefaultAsync<string>(() => agent.Name)
用於 多表連接 單列 查詢.
或者:
.Queryer<Agent, AgentInventoryRecord>(out var agent2, out var record2)
......
.FirstOrDefaultAsync(() => new AgentVM
{
nn = agent2.PathId,
yy = record2.Id,
xx = agent2.Id,
zz = agent2.Name,
mm = record2.LockedCount
}) , 用於 多表連接 多列 查詢.
二.API 單表-便捷 方法 舉例
1. 單表 單列 便捷方法
1 var pk = Guid.Parse("8f2cbb64-8356-4482-88ee-016558c05b2d"); 2 var date= DateTime.Parse("2018-08-20 19:12:05.933786"); 3 4 var res3 = await Conn
5 .FirstOrDefaultAsync<AlipayPaymentRecord, Guid>(it => it.Id == pk && it.CreatedOn == date,it=>it.Id);
生成 sql 如下
1 select `Id` 2 from `AlipayPaymentRecord` 3 where ( `Id`=@Id__2 && `CreatedOn`=@CreatedOn__3) 4 order by `CreatedOn` desc 5 limit 0,1;
2. 單表 多列 便捷方法
1 var pk = Guid.Parse("8f2cbb64-8356-4482-88ee-016558c05b2d"); 2 var date= DateTime.Parse("2018-08-20 19:12:05.933786"); 3 4 var res4 = await Conn
.FirstOrDefaultAsync<AlipayPaymentRecord, AlipayPaymentRecordVM>(it => it.Id == pk && it.CreatedOn == date, 5 it => new AlipayPaymentRecordVM 6 { 7 Id = it.Id, 8 TotalAmount = it.TotalAmount, 9 Description = it.Description 10 });
生成 sql 如下
1 select `Id` as Id, 2 `TotalAmount` as TotalAmount, 3 `Description` as Description 4 from `AlipayPaymentRecord` 5 where ( `Id`=@Id__2 && `CreatedOn`=@CreatedOn__3) 6 order by `CreatedOn` desc 7 limit 0,1;
3. 單表 單條 VM 便捷方法
1 var pk = Guid.Parse("8f2cbb64-8356-4482-88ee-016558c05b2d"); 2 var date = DateTime.Parse("2018-08-20 19:12:05.933786"); 3 4 var res5 = await Conn
5 .FirstOrDefaultAsync<AlipayPaymentRecord, AlipayPaymentRecordVM>(it => it.Id == pk && it.CreatedOn == date);
生成 sql 如下
1 select `Id`, 2 `CreatedOn`, 3 `TotalAmount`, 4 `Description`, 5 `CanceledOn` 6 from `AlipayPaymentRecord` 7 where ( `Id`=@Id__2 && `CreatedOn`=@CreatedOn__3) 8 order by `CreatedOn` desc 9 limit 0,1;
4. 單表 單條 M 便捷方法
1 var pk = Guid.Parse("8f2cbb64-8356-4482-88ee-016558c05b2d"); 2 var date = DateTime.Parse("2018-08-20 19:12:05.933786"); 3 4 var res6 = await Conn
5 .FirstOrDefaultAsync<AlipayPaymentRecord>(it => it.Id == pk && it.CreatedOn == date);
生成 sql 如下
1 select * 2 from `AlipayPaymentRecord` 3 where ( `Id`=@Id__2 && `CreatedOn`=@CreatedOn__3) 4 order by `CreatedOn` desc 5 limit 0,1;
三.API 單表-完整 方法 舉例
1. 單表 單列 完整方法
1 var time1 = DateTime.Parse("2018-08-16 19:22:01.716307"); 2 3 var res1 = await Conn 4 .Queryer<Agent>() 5 .Where(it => it.CreatedOn == time1) 6 .FirstOrDefaultAsync<Guid>(it => it.Id);
生成 sql 如下
1 select `Id` 2 from `Agent` 3 where `CreatedOn`=@CreatedOn__1 4 order by `CreatedOn` desc 5 limit 0,1;
2.單表 多列 完整方法
1 var res3 = await Conn 2 .Queryer<Agent>() 3 .Where(it => it.Id == Guid.Parse("000c1569-a6f7-4140-89a7-0165443b5a4b")) 4 .FirstOrDefaultAsync<AgentVM>(it => new AgentVM 5 { 6 XXXX = it.Name, 7 YYYY = it.PathId 8 });
生成 sql 如下
1 select `Name` as XXXX, 2 `PathId` as YYYY 3 from `Agent` 4 where `Id`=@Id__1 5 order by `CreatedOn` desc 6 limit 0,1;
3.單表 單條 VM 完整方法
1 var res1 = await Conn 2 .Queryer<Agent>() 3 .Where(it => it.Id == Guid.Parse("000c1569-a6f7-4140-89a7-0165443b5a4b")) 4 .FirstOrDefaultAsync<AgentVM>();
生成 sql 如下
1 select `Id`, 2 `CreatedOn`, 3 `UserId`, 4 `PathId`, 5 `Name`, 6 `Phone` 7 from `Agent` 8 where `Id`=@Id__1 9 order by `CreatedOn` desc 10 limit 0,1;
4.單表 單條 M 完整方法
1 var res1 = await Conn 2 .Queryer<BodyFitRecord>() 3 .Where(it => it.Id == Guid.Parse("1fbd8a41-c75b-45c0-9186-016544284e2e")) 4 .FirstOrDefaultAsync();
生成 sql 如下
1 select * 2 from `BodyFitRecord` 3 where `Id`=@Id__1 4 order by `CreatedOn` desc 5 limit 0,1;
四.API 多表連接-完整 方法 舉例
1.多表連接 單列 完整方法
1 var res1 = await Conn 2 .Queryer<Agent, AgentInventoryRecord>(out var agent, out var agentRecord) 3 .From(() => agent) 4 .InnerJoin(() => agentRecord) 5 .On(() => agent.Id == agentRecord.AgentId) 6 .Where(() => agent.AgentLevel == AgentLevel.DistiAgent) 7 .FirstOrDefaultAsync<string>(() => agent.Name);
生成 sql 如下
1 select agent.`Name` 2 from `Agent` as agent 3 inner join AgentInventoryRecord as agentRecord 4 on agent.`Id`=agentRecord.`AgentId` 5 where agent.`AgentLevel`=@AgentLevel__4 6 order by agent.`CreatedOn` desc 7 limit 0,1;
2.多表連接 多列 完整方法
1 var guid2 = Guid.Parse("544b9053-322e-4857-89a0-0165443dcbef"); 2 3 var res2 = await Conn 4 .Queryer<Agent, AgentInventoryRecord>(out var agent2, out var record2) 5 .From(() => agent2) 6 .InnerJoin(() => record2) 7 .On(() => agent2.Id == record2.AgentId) 8 .Where(() => agent2.Id == guid2) 9 .FirstOrDefaultAsync(() => new AgentVM 10 { 11 nn = agent2.PathId, 12 yy = record2.Id, 13 xx = agent2.Id, 14 zz = agent2.Name, 15 mm = record2.LockedCount 16 });
生成 sql 如下
1 select agent2.`PathId` as nn, 2 record2.`Id` as yy, 3 agent2.`Id` as xx, 4 agent2.`Name` as zz, 5 record2.`LockedCount` as mm 6 from `Agent` as agent2 7 inner join AgentInventoryRecord as record2 8 on agent2.`Id`=record2.`AgentId` 9 where agent2.`Id`=@Id__4 10 order by agent2.`CreatedOn` desc 11 limit 0,1;
3.多表連接 單條 M 完整方法
1 var guid6 = Guid.Parse("544b9053-322e-4857-89a0-0165443dcbef"); 2 3 var res6 = await Conn 4 .Queryer<Agent, AgentInventoryRecord>(out var agent6, out var record6) 5 .From(() => agent6) 6 .InnerJoin(() => record6) 7 .On(() => agent6.Id == record6.AgentId) 8 .Where(() => agent6.Id == guid6) 9 .FirstOrDefaultAsync<Agent>();
生成 sql 如下
1 select agent6.`*` 2 from `Agent` as agent6 3 inner join AgentInventoryRecord as record6 4 on agent6.`Id`=record6.`AgentId` 5 where agent6.`Id`=@Id__4 6 order by agent6.`CreatedOn` desc 7 limit 0,1;
蒙
2018-12-13 14:35 周四