索引:
一.API 列表
C# 代码中 接口 IList.Contains() 方法生成 SQL 对应的 in(val1,val2,... ...)
如:.Queryer<Agent>()
... ...
.Where(it => new AgentLevel?[] { AgentLevel.CityAgent, AgentLevel.DistiAgent }.Contains(it.AgentLevel))
... ... 用于 单表 in 条件
.Queryer(out Agent agent, out AgentInventoryRecord record)
... ...
.Where(() => !new AgentLevel?[] { AgentLevel.CityAgent, AgentLevel.DistiAgent }.Contains(agent.AgentLevel))
... ... 用于 多表连接 in 条件
二.API 单表-便捷 方法 举例
1. in 条件
1 var res2 = await Conn 2 .QueryListAsync<Agent>(it => new List<AgentLevel?> { AgentLevel.CityAgent, AgentLevel.DistiAgent }.Contains(it.AgentLevel));
以 MySQL 为例,生成 SQL 如下:
1 select * 2 from `agent` 3 where `AgentLevel` in (?AgentLevel_2,?AgentLevel_3);
2. not in 条件
1 var res2 = await Conn 2 .QueryListAsync<Agent>(it => !new List<AgentLevel?> { AgentLevel.CityAgent, AgentLevel.DistiAgent }.Contains(it.AgentLevel));
以 MySQL 为例,生成 SQL 如下:
1 select * 2 from `agent` 3 where `AgentLevel` not in (?AgentLevel_2,?AgentLevel_3);
三.API 单表-完整 方法 举例
1. in 条件
1 var res5 = await Conn 2 .Queryer<Agent>() 3 .Where(it => new List<string> { "黄银凤", "刘建芬" }.Contains(it.Name)) 4 .QueryListAsync();
以 MySQL 为例,生成 SQL 如下:
1 select * 2 from `agent` 3 where `Name` in (?Name_2,?Name_3);
2. not in 条件
1 var res5 = await Conn 2 .Queryer<Agent>() 3 .Where(it => !new List<string> { "黄银凤", "刘建芬" }.Contains(it.Name)) 4 .QueryListAsync();
以 MySQL 为例,生成 SQL 如下:
1 select * 2 from `agent` 3 where `Name` not in (?Name_2,?Name_3);
四.API 多表连接-完整 方法 举例
1. in 条件
1 var res1 = await Conn 2 .Queryer(out Agent agent, out AgentInventoryRecord record) 3 .From(() => agent) 4 .InnerJoin(() => record) 5 .On(() => agent.Id == record.AgentId) 6 .Where(() => new AgentLevel?[] { AgentLevel.CityAgent, AgentLevel.DistiAgent }.Contains(agent.AgentLevel)) 7 .QueryListAsync<Agent>();
以 MySQL 为例,生成 SQL 如下:
1 select agent.`*` 2 from `agent` as agent 3 inner join `agentinventoryrecord` as record 4 on agent.`Id`=record.`AgentId` 5 where agent.`AgentLevel` in (?AgentLevel_5,?AgentLevel_6);
2. not in 条件
1 var res1 = await Conn 2 .Queryer(out Agent agent, out AgentInventoryRecord record) 3 .From(() => agent) 4 .InnerJoin(() => record) 5 .On(() => agent.Id == record.AgentId) 6 .Where(() => !new AgentLevel?[] { AgentLevel.CityAgent, AgentLevel.DistiAgent }.Contains(agent.AgentLevel)) 7 .QueryListAsync<Agent>();
以 MySQL 为例,生成 SQL 如下:
1 select agent.`*` 2 from `agent` as agent 3 inner join `agentinventoryrecord` as record 4 on agent.`Id`=record.`AgentId` 5 where agent.`AgentLevel` not in (?AgentLevel_5,?AgentLevel_6);
五.数组 Array 举例
1. in 条件
1 var enumArray = new AgentLevel?[] 2 { 3 AgentLevel.CityAgent, 4 AgentLevel.DistiAgent 5 }; 6 7 var res12 = await Conn 8 .Queryer<Agent>() 9 .Where(it => enumArray.Contains(it.AgentLevel)) 10 .QueryListAsync();
以 MySQL 为例,生成 SQL 如下:
1 select * 2 from `agent` 3 where `AgentLevel` in (?AgentLevel_2,?AgentLevel_3);
2. not in 条件
1 var res1 = await Conn 2 .Queryer<Agent>() 3 .Where(it => !new AgentLevel?[] { AgentLevel.CityAgent, AgentLevel.DistiAgent }.Contains(it.AgentLevel)) 4 .QueryListAsync();
以 MySQL 为例,生成 SQL 如下:
1 select * 2 from `agent` 3 where `AgentLevel` not in (?AgentLevel_2,?AgentLevel_3);
六.列表 List<T> 举例
1. in 条件
1 var enums = new List<AgentLevel?> 2 { 3 AgentLevel.CityAgent, 4 AgentLevel.DistiAgent 5 }; 6 7 var res1 = await Conn 8 .Queryer<Agent>() 9 .Where(it => enums.Contains(it.AgentLevel)) 10 .QueryListAsync();
以 MySQL 为例,生成 SQL 如下:
1 select * 2 from `agent` 3 where `AgentLevel` in (?AgentLevel_2,?AgentLevel_3);
2. not in 条件
1 var res1 = await Conn 2 .Queryer<Agent>() 3 .Where(it => !new List<AgentLevel?> { AgentLevel.CityAgent, AgentLevel.DistiAgent }.Contains(it.AgentLevel)) 4 .QueryListAsync();
以 MySQL 为例,生成 SQL 如下:
1 select * 2 from `agent` 3 where `AgentLevel` not in (?AgentLevel_2,?AgentLevel_3);
蒙
2019-03-04 22:10 周一
2019-04-13 20:28 周六