子查询或者一对多查询
1. 查询一条数据
var getAll = db.Queryable<Student, School>((st, sc) => new object[] {
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语句，如果是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))
*/
在select中也可以使用
var getAll = db.Queryable<Student, School>((st, sc) => new object[] {
JoinType.Left,st.Id==sc.Id})
.Select(st =>
name = st.Name,
id = SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)
}).ToList();
2.IN和NOT 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` ) ))
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` ) ))
*/
3.更多操作
var getAll9= db.Queryable<Student>().Where(it => SqlFunc.Subqueryable<School>()
.Where(s => s.Id == it.Id).Max(s => s.Id) == 1).ToList();
var getAll9= db.Queryable<Student>().Where(it => SqlFunc.Subqueryable<School>()
.Where(s => s.Id == it.Id).Min(s => s.Id) == 1).ToList();
var getAll10 = db.Queryable<Student>().Where(it => SqlFunc.Subqueryable<School>()
.Where(s => s.Id == it.Id).Count() == 1).ToList();
4.4版本才支持的写法
单表
var list9 = db.Queryable<Student>("it")
.OrderBy(it => it.Id)
.In(it => it.Id,db.Queryable<School>().Where("it.id=schoolId").Select(it=>it.Id))
.ToList();
多表
var list11 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id)
.In(st => st.Name, db.Queryable<School>().Where(it=>it.Id==1).Where("id=st.schoolid").Select(it => it.Name))
.OrderBy(st => st.Id)
.Select(st => st)
.ToList();
4.8.1 Mapper功能 是Select的升级版
Mapper是在查询出结果后进行处理所以任何C#方法都支持也更强大,提供了
一对多
和
一对一
查询的高性能方案(不会产生循环和多查询一条没用数据，性能是所有ORM中一对多查询最高)
var s12 = db.Queryable<Student>()
.Mapper((it, cache) =>
//一次性查询出所要的外键引用数据
var allSchools = cache.GetListByPrimaryKeys<School>(vmodel => vmodel.SchoolId);
//sql select * from shool where id
//id in（list[0].SchoolId , list[1].SchoolId...）
//等同于上面写法这写法可以做复杂操作
//var allSchools2= cache.Get(list =>
// var ids=list.Select(i => it.SchoolId).ToList();
// return db.Queryable<School>().In(ids).ToList();
//}); 其中list是我们select()查询出来的对象，我们根据list查询出所有需要的外键，
// 然后一次性查询出我们要的数据进入缓存给下面的方案使用，这样就避免了循环
/*一对一*/
//高性能
it.School = allSchools.FirstOrDefault(i => i.Id == it.SchoolId);
//性能差相当于循环
//it.School = db.Queryable<School>().InSingle(it.SchoolId);
/*一对多*/
it.Schools = allSchools.Where(i => i.Id == it.SchoolId).ToList();
/*用C#处理你想要的结果*/
it.Name = it.Name == null ? "null" : it.Name;
}).ToList();
public class Student{
public int Id{get;set;}
public string Name{get;set;}
public int SchoolId{get;set;}
public List<School> Schools{get;set;}
public School School{get;set;}
public class School{
public int Id{get;set;}
public string Name{get;set;}
}
5子查询Join
var q1 = db.Queryable<Student, School>((st,sc)=>new object[] {
JoinType.Left,st.SchoolId==sc.Id
}).Select((st, sc) => new ViewModelStudent4() { Id=st.Id, Name=st.Name,SchoolName=sc.Name });
var q2 = db.Queryable<School>();
var innerJoinList = db.Queryable(q1, q2, (j1, j2) => j1.Id == j2.Id).Select((j1, j2) => j1).ToList();//inner join
var leftJoinList = db.Queryable(q1, q2,JoinType.Left, (j1, j2) => j1.Id == j2.Id).Select((j1, j2) => j1).ToList();//left join
