class Program
{
static SugarDbContext sugar = new SugarDbContext();
static void Main(string[] args)
{
var db = sugar.Db;
var list = db.Queryable<Student, StudentGrad>((st, sc) => new object[] {
JoinType.Inner,st.GradID==sc.GradID})
.Select((st, sc) => new { StudentName = st.StudentName, GradName = sc.GradName }).ToList();
var list2 = db.Queryable<Student, StudentGrad>((st, sc) => new object[] {
JoinType.Inner,st.GradID==sc.GradID})
.Select((st, sc) => new ViewModel { StudentName = st.StudentName, GradName = sc.GradName }).ToList();
///3张表关联查询
//var list3 = db.Queryable<Student, School, Student>((st, sc, st2) => new object[] {
// JoinType.Left,st.SchoolId==sc.Id,
// JoinType.Left,st.SchoolId==st2.Id
//})
// .Where((st, sc, st2) => st2.Id == 1 || sc.Id == 1 || st.Id == 1)
// .OrderBy((sc) => sc.Id)
// .OrderBy((st, sc) => st.Name, OrderByType.Desc)
// .Select((st, sc, st2) => new { st = st, sc = sc }).ToList();
///分页查询
var pageIndex = 1;
var pageSize = 10;
var list4 = db.Queryable<Student, StudentGrad>((st, sc) => new object[] {
JoinType.Left,st.GradID==sc.GradID
}).Select((st, sc) => new ViewModel { StudentName = st.StudentName, GradName = sc.GradName })
.ToPageList(pageIndex, pageSize);
foreach(var item in list4)
{
Console.WriteLine(item.GradName + item.StudentName);
}
///五张表关联查询
//var list2 = db.Queryable<Student, School, Student, Student, Student>((st, sc, st2, st3, st4) => new object[] {
// JoinType.Left,st.SchoolId==sc.Id,
// JoinType.Left,st.Id==st2.Id,
// JoinType.Left,st.Id==st3.Id,
// JoinType.Left,st.Id==st4.Id
//}).Where((st, sc) => sc.Id == 1)
// .Select((st, sc, st2, st3, st4) => new { id = st.Id, name = st.Name, st4 = st4 }).ToList();
///二个Queryable的Join(4.6.0.9)
var q1 = db.Queryable<Student>();
var q2 = db.Queryable<StudentGrad>();
var innerJoinList = db.Queryable(q1, q2, (j1, j2) => j1.GradID == j2.GradID).Select((j1, j2) => j1).ToList();//inner join
///多表查询的简化 默认为inner join
var list5 = db.Queryable<Student, StudentGrad>((st, sc) => st.GradID == sc.GradID).Select((st, sc) => new ViewModel { StudentName= st.StudentName, GradName= sc.GradName}).ToList();
///3表查询
//var list6 = db.Queryable<Student, School, School>((st, sc, sc2) => st.SchoolId == sc.Id && sc.Id == sc2.Id)
// .Select((st, sc, sc2) => new { st.Name, st.Id, schoolName = sc.Name, schoolName2 = sc2.Name }).ToList();
///3表查询分页
//var list7 = db.Queryable<Student, School, School>((st, sc, sc2) => st.SchoolId == sc.Id && sc.Id == sc2.Id)
//.Select((st, sc, sc2) => new { st.Name, st.Id, schoolName = sc.Name, schoolName2 = sc2.Name }).ToPageList(1, 2);
///qlFunc.Subqueryable 子查询
// 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))
Console.ReadLine();
}
}
class ViewModel
{
public string StudentName { get; set; }
public string GradName { get; set; }
}