MyDAL - in && not in 条件 使用
索引：
一.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));
生成 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));
生成 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();
生成 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();
生成 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>();
生成 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>();
生成 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();
生成 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();
生成 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();
生成 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();
生成 SQL 如下
1 select * 2 from `agent` 3 where `AgentLevel` not in (@AgentLevel_2,@AgentLevel_3);
蒙
2019-03-04 22:10 周一