SqlSugar 语句收集

var data1 = StudentDb.GetById(1);
//等同于
var data2 = Db.Queryable<Student>().Single(it => it.Id == 1);

多表查询

var list = Db.Queryable<Student, School>((st, sc) => new object[] {
        JoinType.Left,st.SchoolId==sc.Id})
      .Select((st,sc)=>new{Id=st.Id,Name=st.Name,SchoolName=sc.Name}).ToList();

生成的SQL如下

SELECT  [st].[ID] AS [id] , 
          [st].[Name] AS [name] , 
          [sc].[Name] AS [schoolName]  FROM [STudent] st 
          Left JOIN School sc ON ( [st].[SchoolId] =[sc].[Id])

多表查询自支持自动填充到ViewModel

var s11 = Db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id)
                        .Select<ViewModelStudent3>().ToList();

 public class ViewModelStudent3: Student
{
         public string SchoolName { get; set; }
 }

生成的Sql如下

SELECT
           sc.[Name] AS [SchoolName],--这一列神奇的自动出现了
           sc.[Id] AS [scId],
           st.[ID] AS [Id],
           st.[SchoolId] AS [SchoolId],
           st.[Name] AS [Name],
           st.[CreateTime] AS [CreateTime]
           
           
            FROM [STudent] st  ,[School]  sc  WHERE ( [st].[SchoolId] = [sc].[Id])

 

多表分页查询

 var list3 = Db.Queryable<Student, School>((st, sc) => new object[] {
              JoinType.Left,st.SchoolId==sc.Id
            }).Select<ViewModel>()
            .ToPageList(pageIndex,pageSize)

 

子查询

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))

 

一对一的查询

var getAll = Db.Queryable<Student, School>((st, sc) => new object[] {
JoinType.Left,st.Id==sc.Id})
.Select(st =>
       new{
              name = st.Name,
              id = SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)
       }).ToList();