/***只查一列***/
//First: select top 1 id
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)     
//max例1: 
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Max(s => s.Id)
//max例2: 
SqlFunc.Subqueryable<School>()..Where(s=>s.Id==st.Id).Select(s=>SqlFunc.AggregateMax(s.Id))      
//min:
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Min(s => s.Id)
//avg:
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Avg(s => s.Id)
//count:
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Count()
//sum:
SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Sum(s => s.num)
//去重后匯總數(shù)量
SqlFunc.Subqueryable<School>().Where(s => z.Id == st.Id).Select(s=>SqlFunc.AggregateDistinctCount(s.Name))
//逗號(hào)分割列 
SqlFunc.Subqueryable<Order>().Where(z=>z.Id==it.Id).SelectStringJoin(z => z.Name, ",")
//完整用例：看標(biāo)題8/***返回List或者實(shí)體***/
新功能：5.1.3.36
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList()
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(it=>new Class(){ Name=it.Name})
//更多看標(biāo)題4/***返回單個(gè)實(shí)體***/
//SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).First() 
//更多看標(biāo)題9/***IN、Not in、Exists、Not Exists***/
//看標(biāo)題2

var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id) )
.Where(st => st.Id == SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id))
.ToList();/*生成的MYSQL語(yǔ)句，如果是SqlServer就是TOP 1
SELECT `st`.`ID`,`st`.`SchoolId`,`st`.`Name`,`st`.`CreateTime` FROM `STudent` st Left JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` )  WHERE ( `st`.`ID` =(SELECT `Id` FROM `School` WHERE ( `Id` = `st`.`ID` ) limit 0,1))
*/

//等同于IN
var getAll7 = db.Queryable<Student>()
.Where(it => SqlFunc.Subqueryable<School>().Where(s =>s.Id==it.Id).Any()).ToList();/*生成的SQL(等于同于it.id in(select id from school)只是寫法不一樣
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it 
WHERE (EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) )) 
*///等同于NOT IN
var getAll8 = db.Queryable<Student>().Where(it => 
SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).NotAny()).ToList();/*生成的SQL
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it  
WHERE (NOT EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) ))
*///搜索關(guān)鍵詞：exits

db.Queryable<Order>()
.Where(it=>it.Id==SqlFunc.Subqueryable<Custom>().GroupBy(z=>z.OrderId).Select(z=>z.OrderId)).ToList()//Sql
//SELECT [Id],[Name] FROM [Order]  
// WHERE  [Id] in ((SELECT [OrderId] FROM [Custom] GROUP BY [OrderId]))

db.Queryable<Order>().In(it=>it.Id,db.Queryable<Order>().Skip(1).Take(100).Select(it=>it.Id)).ToList();

 string sql=" (select top 1 id from [Order] ) ";var list = db.Queryable<Order>().Select(it => new{name=it.Name,customName= SqlFunc.MappingColumn<string>(sql)//老版本MappingColumn(default(string),sql) }).ToList();//如果存在SQL拼接將SQL提取到外面在傳進(jìn)來(lái)，有些VS拼接會(huì)有問(wèn)題

SELECT  [Name] AS [name] , (select top 1 id from [Order] ) AS [customName] FROM [Order]

//請(qǐng)升級(jí)到 5.1.3.36+ db.Queryable<Order>().Select(it => new{CustomId = it.CustomId,OrderId = it.Id,OrderName = it.Name,CustomList = SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList()}).ToList();//也可以DTOSqlFunc.Subqueryable<Custom>().Where(c=> c.Id == it.CustomId).ToList(c=> new Class(){ Name=c.Name}) //轉(zhuǎn)成DTO//注意：where  ToList里面 c 的別名要一致//只查L(zhǎng)ist<string>這種集合 
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(c=>c.Name)//最新版本//自動(dòng)DTO 5.1.4.70
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList<DTO>()
SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList(it=>new DTO(){},true);//和Queryable類似

var treeRoot=db.Queryable<Tree>().ToList();//可以聯(lián)表查詢，只要返回List中有關(guān)聯(lián)字段就可以//填充子對(duì)象 
//原理： 數(shù)據(jù)庫(kù)一次讀取 In (list[0].id,list[1].id,list[2].id...) 內(nèi)存分配到對(duì)應(yīng)Child
//所以無(wú)論集合多少條記錄  ThenMapper 只會(huì)操作一次庫(kù)  
db.ThenMapper(treeRoot, item =>{   //用例1：簡(jiǎn)單填充  //參數(shù)解釋：子表的ParentId和主表的Id 進(jìn)行關(guān)聯(lián)查詢（有Select寫到SetContext前面）//可以額外加Where寫SetContext前面，但是不能帶有item對(duì)象,item要寫SetContext里面item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList();//用例2：可以多屬性//item.Child=xxxx//item.Parent=xxxx//用例3：類型轉(zhuǎn)換類轉(zhuǎn)成DTO （注意：Select中的關(guān)聯(lián)字段要賦值）//item.Child = db.Queryable<Tree>()//         .Select(it=>new ViewTree(){ParentId=x.ParentId,Id=i.Id,Name=it.Name }) //         .SetContext(x => x.ParentId, () => item.Id, item).ToList();//用例4：聯(lián)表填充//item.Child=db.Queryable<Tree>()//.LeftJoin<Tree>((i,y)=>i.ParentId==y.Id)//.Select((i,y)=>new viewmodel(){ Parentid=i.Parentid..........})//.SetContext(x => x.ParentId, () => item.Id, item).ToList();});//異步
var treeRoot=await db.Queryable<Tree>().ToListAsync(); 
await db.ThenMapperAsync(treeRoot, async item =>
{   //給集合賦值  參數(shù)解釋  子表的ParentId和主表的Id 進(jìn)行關(guān)聯(lián)查詢item.Child =await db.Queryable<Tree>().SetContextAsync(x=>x.ParentId,()=>item.Id, item);
});//無(wú)限層級(jí)
var treeRoot=db.Queryable<Tree>().Where(it => it.Id == 1).ToList();
//第一層
db.ThenMapper(treeRoot, item =>
{item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList();
});
//第二層
db.ThenMapper(treeRoot.SelectMany(it=>it.Child), it =>
{it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList();
});//跨庫(kù) 
var mydb=db.GetConnection(1);
mydb.ThenMapper(root,item=>{...});

var list = db.Queryable<Order>().Where(it =>SqlFunc.Subqueryable<A>() .LeftJoin<B>((i,y)=>i.ItemId==y.ItemId).InnerJoin<C>((i,y,z) => i.ItemId == z.ItemId).Where((i,y,z)=>i.ItemId==it.Aid).Any()).ToList();
//新版本支持了Subquery.join使用 Where<T1,T2>((x,y)=> x.id==y.id)