十年河东，十年河西，莫欺少年穷
学无止境，精益求精
接着上篇博客继续深入Sugar用法上篇博客地址为：https://www.cnblogs.com/chenwolong/p/SqlSugar.html
时间原因，直接摘抄官方文档，如下：
1、简单查询
简单查询
Demo的Student类定义如下:
//实体定义 http://www.codeisbug.com/Doc/8/1141
public class Student
{
public int ID { get; set; }
public string Name { get; set; }
}
查询所有
var getAll = db.Queryable<Student>().ToList();
/*
生成SQL: SELECT [ID],[Name] FROM [Student]
*/
取前5条
var top5 = db.Queryable<Student>().Take(5).ToList();
/*
生成SQL:
SQL Server:
SELECT * FROM (SELECT [ID],[Name],ROW_NUMBER() OVER( ORDER BY GetDate() ) AS RowIndex FROM [Student] ) T WHERE RowIndex BETWEEN 1 AND 5
SQL虽长但是性能和TOP5方式是一样的ORM保证了写法统一改成ROW方式
MySQL:
SELECT `ID`,`Name` FROM `Student` LIMIT 0,5
Oracle:
SELECT * FROM (SELECT "ID","Name",ROW_NUMBER() OVER( ORDER BY sysdate ) AS RowIndex FROM "Student" ) T WHERE RowIndex BETWEEN 1 AND 5
Sqlite:
SELECT `ID`,`Name` FROM `Student` LIMIT 0,5
*/
无锁查询
var getAllNoLock = db.Queryable<Student>().With(SqlWith.NoLock).ToList();
根据主键查询
var getByPrimaryKey = db.Queryable<Student>().InSingle(2);
/*
生成SQL: SELECT [ID],[Name] FROM [Student] WHERE [ID] = @param;
@param 值为 2
*/
查询单条没有数据返回NULL, Single超过1条会报错，First不会
var getSingleOrDefault = db.Queryable<Student>().Single();
var getFirstOrDefault = db.Queryable<Student>().First();
/*
生成SQL: 参考Take函数
如果使用Single方法返回单条, 实际返回超过1条, 会引发异常, 使用First返回单条不会引发异常, 只返回第一条, 忽略其它的结果.
*/
UNION ALL
db.UnionAll<Student>(db.Queryable<Student>(),db.Queryable<Student>()).ToList();
/*
生成SQL:
SQL Server:
SELECT * FROM (SELECT [ID],[Name] FROM [Student] UNION ALL SELECT [ID],[Name] FROM [Student] ) unionTable
MySQL:
SELECT * FROM (SELECT `ID`,`Name` FROM `Student` UNION ALL SELECT `ID`,`Name` FROM `Student` ) unionTable
Oracle:
SELECT * FROM (SELECT "ID","NAME" FROM "STUDENT" UNION ALL SELECT "ID","NAME" FROM "STUDENT" ) UNIONTABLE
Sqlite:
SELECT * FROM (SELECT `ID`,`Name` FROM `Student` UNION ALL SELECT `ID`,`Name` FROM `Student` ) unionTable
*/
IN查询
//Id In (1,2,3), 指定列In查询
var in1 = db.Queryable<Student>().In(it=>it.ID,new int[] { 1, 2, 3 }).ToList();
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student] WHERE [ID] IN ('1','2','3')
其它数据库类似, 就不一一举例了
*/
//主键 In (1,2,3) 不指定列, 默认根据主键In
var in2 = db.Queryable<Student>().In(new int[] { 1, 2, 3 }).ToList();
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student] WHERE [ID] IN ('1','2','3')
其它数据库类似, 就不一一举例了
*/
//Id In (1,2) 指定列的另外一种写法
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable<Student>().Where(it=>array.Contains(it.ID)).ToList();
/*
生成SQL和上面一样
*/
NOT IN查询
var in3 = db.Queryable<Student>().Where(it=>!array.Contains(it.ID)).ToList();
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student] WHERE NOT ([ID] IN ('1','2'))
其它数据库类似, 就不一一举例了
*/
条件查询
var getByWhere = db.Queryable<Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student] WHERE (( [ID] = @ID0 ) OR ( [Name] = @Name1 ))
其中@ID0值为1, @Name1值为a
MySQL:
SELECT `ID`,`Name` FROM `Student` WHERE (( `ID` = @ID0 ) OR ( `Name` = @Name1 ))
其中@ID0值为1, @Name1值为a
Oracle:
SELECT "ID","NAME" FROM "STUDENT" WHERE (( "ID" = :ID0 ) OR ( "NAME" = :Name1 ))
其中:ID0值为1, :Name1值为a
Sqlite:
SELECT `ID`,`Name` FROM `Student` WHERE (( `ID` = @ID0 ) OR ( `Name` = @Name1 ))
其中@ID0值为1, @Name1值为a
*/
使用函数 SqlFunc类
//查询Name列不为空的结果, SqlFunc提供的功能远不止这一个, 在查询函数里面有详解
var getByFuns = db.Queryable<Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student] WHERE ( [Name]='' OR [Name] IS NULL )
MySQL:
SELECT `ID`,`Name` FROM `Student` WHERE ( `Name`='' OR `Name` IS NULL )
Oracle:
SELECT "ID","NAME" FROM "STUDENT" WHERE ( "NAME"='' OR "NAME" IS NULL )
Sqlite:
SELECT `ID`,`Name` FROM `Student` WHERE ( `Name`='' OR `Name` IS NULL )
*/
可以使用 SUM MAX MIN AVG查询单个字段
var sum = db.Queryable<Student>().Sum(it => it.ID);
/*
生成SQL:
SQL Server:
SELECT SUM([ID]) FROM [Student]
其它数据库类型, 不一一列举
*/
Between 1 and 20
var between = db.Queryable<Student>().Where(it => SqlFunc.Between(it.ID, 1, 20)).ToList();
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student] WHERE ([ID] BETWEEN @MethodConst0 AND @MethodConst1)
其中@MethodConst0值为1, @MethodConst1值为20
MySQL:
SELECT `ID`,`Name` FROM `Student` WHERE (`ID` BETWEEN @MethodConst0 AND @MethodConst1)
其中@MethodConst0值为1, @MethodConst1值为20
Oracle:
SELECT "ID","NAME" FROM "STUDENT" WHERE ("ID" BETWEEN :MethodConst0 AND :MethodConst1)
其中:MethodConst0值为1, :MethodConst1值为20
Sqlite:
SELECT `ID`,`Name` FROM `Student` WHERE (`ID` BETWEEN @MethodConst0 AND @MethodConst1)
其中@MethodConst0值为1, @MethodConst1值为20
*/
使用 AS 取新的表名
var getListByRename = db.Queryable<School>().AS("Student").ToList();
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student]
*/
排序
var getAllOrder = db.Queryable<Student>().OrderBy(it => it.ID).ToList(); //默认为ASC排序
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student] ORDER BY [ID] ASC
其它数据库类似
*/
var getAllOrder = db.Queryable<Student>().OrderBy(it => it.ID, OrderByType.Desc).ToList(); //收到设置为DESC排序
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student] ORDER BY [ID] DESC
*/
多个字段排序
var data = db.Queryable<Student>()
.OrderBy(it => it.ID, OrderByType.Desc)
.OrderBy(it => it.Name, OrderByType.Asc)
.ToList();
/*
生成SQL:
SQL Server:
SELECT [ID],[Name] FROM [Student] ORDER BY [ID] DESC,[Name] ASC
其它数据库类似
*/
是否存在这条记录
var isAny = db.Queryable<Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable<Student>().Any(it => it.Id == -1);
获取同一天的记录
var getTodayList = db.Queryable<Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
/*
生成SQL:
SQL Server:
SELECT [ID],[Name],[CreateTime] FROM [Student] WHERE (DATEDIFF(day,[CreateTime],@MethodConst0)=0)
其中@MethodConst0值为当前时间
MySQL:
SELECT `ID`,`Name`,`CreateTime` FROM `Student` WHERE (TIMESTAMPDIFF(day,`CreateTime`,@MethodConst0)=0)
其中@MethodConst0值为当前时间
*/
注意：SqlSugar拉姆达解析的函数都在SqlFunc这个类中，就算是Convert也请使用SqlFunc中的转换方法，这样做有3个好处
1.提高性能
2.减少底层
3.让用户知道我这个ORM到底支持哪些函数，可以快速上手
不足点
打破用户的一些习惯
View Code
2、多表查询
多表查询
注意：例 如Queryable(st,sc)=> 那么 Where OrderBy Select 中就不能取其它别名 也只能用st和sc(特殊需求需要不限别名参考http://www.codeisbug.com/Doc/8/1127)
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])
如果实体中的字段取名比较好支持自动填充(4.8.1)
var list = db.Queryable<Student, School>((st, sc) => new object[] {
JoinType.Left,st.SchoolId==sc.Id})
.Select<ViewModel>().ToList();
//sql
SELECT [st].[ID] AS [id] ,
[st].[Name] AS [name] ,
[sc].[Name] AS [schoolName]/*ORM自动识别出这一列*/
FROM [STudent] st
Left JOIN School sc ON ( [st].[SchoolId] =[sc].[Id])
//class
public class ViewModel{
public int ID{get;set;}
public string Name{get;set;}
public string schoolName{get;set;}//类名+Name
}
2.两表查询将结果返回到实体对象
List<VStudent> list = db.Queryable<Student, School>((st, sc) => new object[] {
JoinType.Left,st.SchoolId==sc.Id})
.Select((st,sc)=>new VStudent{Id=st.Id,Name=st.Name,SchoolName=sc.Name}).ToList();
public class VStudent(){
public string Name{get;set;}
public int Id{get;set;}
public string SchoolName{get;set;}
}
3.三表查询并且返回st和sc的完整对象集合
var list = 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();
SELECT [st].[Id] AS [Student.Id] ,
[st].[SchoolId] AS [Student.SchoolId] ,
[st].[Name] AS [Student.Name] ,
[st].[CreateTime] AS [Student.CreateTime] ,
[sc].[Id] AS [School.Id] ,
[sc].[Name] AS [School.Name]
FROM [STudent] st
LEFT JOIN School sc
ON ( [st].[SchoolId] =[sc].[Id])
LEFT JOIN STudent st2
ON ( [st].[SchoolId] =[st2].[ID])
WHERE ((( [st2].[ID] = @Id0 )
OR ( [sc].[Id] = @Id1 ))
OR ( [st].[ID] = @Id2 ))ORDER BY [sc].[ID] ASC,[st].[Name] ASC
我们可以看出查询返回的结果是完整对象
Student st=list[0].st;
School sc=list[0].sc;
4.多表查询分页
var list3 = db.Queryable<Student, School>((st, sc) => new object[] {
JoinType.Left,st.SchoolId==sc.Id
}).Select((st, sc) => new ViewModelStudent { Name = st.Name, SchoolId = sc.Id })
.ToPageList(pageIndex,pageSize)
5.五表查询例子
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();
6.二个Queryable的Join(4.6.0.9)
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
多表查询的简化
当我们不需要用LEFT JOIN或者 RIGHT JOIN 只是单纯的INNER JOIN时我们还提供了更简单的语法实现多表查询
//2表查询
var list5 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id).Select((st,sc)=>new {st.Name,st.Id,schoolName=sc.Name}).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);
使用复杂模型查询
//性能差，推荐用Mapper功能实现（http://www.codeisbug.com/Doc/8/1161）
var students = db.Queryable<CMStudent>().ToList();
if (students != null)
{
foreach (var item in students)
{
Console.WriteLine(item.SchoolName);
Console.WriteLine(item.SchoolSingle.Name);
Console.WriteLine(item.SchoolList.Count);
}
}
复杂模型参考：
http://www.codeisbug.com/Doc/8/1143
子查询
4.5.2.2 版本支持的写法
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 =>
new{
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();
Union all
var getUnionAllList2 = db.UnionAll(db.Queryable<Student>(), db.Queryable<Student>()).ToList();
View Code
3、SqlFunc查询函数
查询函数
用法如下：
db.Queryable<Student>().Where(it => SqlFunc.ToLower(it.Name) == SqlFunc.ToLower("JACK")).ToList();
注意：拉姆达解析支持的原生函数有限
.ToString
.Contains
.Length
.ToLower
.ToUpper
.ToSubstring
.Equals
.HasValue
.Replace
.EndsWith
.StartsWith
.Trim
.HasValue
.Value
和常用的Convert.ToInt32等
如果不满足需求请使用SqlFunc这个类，为我们提供了大量函数
SqlFunc函数：
三元判段 ，相当于 it.id==1?1:2
SqlFunc.IIF(it.Id == 1, 1, 2)
if else 4.6.0.1
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id)
IsNulll4.6.0.1
SqlFunc.IsNull(it.Id, 0)//如果是null则是0
获取数据库时间4.6.0.1
SqlFunc.GetDate();
判段是NULL或者空
SqlFunc.IsNullOrEmpty(object thisValue)
判段不是NULL并且不是空
SqlFunc.HasValue(object thisValue)
判段大于0并且不等于NULL
SqlFunc.HasNumber(object thisValue)
转小写
SqlFunc.ToLower(object thisValue)
转大写
SqlFunc.ToUpper(object thisValue)
去前后空格
SqlFunc.Trim(object thisValue)
模糊查询 like %@p%
SqlFunc.Contains(string thisValue, string parameterValue)
也可以使用 .Where(it=>it.Name.Contains("a"));
In操作 thisValue={1,2,3} 生成的Sql就是 paramterValie in (1,2,3)
SqlFunc.ContainsArray(object[] thisValue, string parameterValue)
也可以使用 .Where(it=>数组变量.Contains(it.Id));
Not In 操作
.Where(it=>!Array.Contains(it.Id));
模糊查询 like @p%
SqlFunc.StartsWith(object thisValue, string parameterValue)
模糊查询 like %@p
SqlFunc.EndsWith(object thisValue, string parameterValue)
等于
SqlFunc.Equals(object thisValue, object parameterValue)
是否是同一天
SqlFunc.DateIsSame(DateTime date1, DateTime date2)
是否是同一时间 （dataType 可以是年、月、天、小时、分钟、秒和毫秒）
SqlFunc.DateIsSame(DateTime date1, DateTime date2, DateType dataType)
在当前时间加一定时间（dataType 可以是年、月、天、小时、分钟、秒和毫秒）
SqlFunc.DateAdd(DateTime date, int addValue, DateType dataType)
在当前时间加N天
SqlFunc.DateAdd(DateTime date, int addValue)
获取当前时间的年、月、天、小时、分钟、秒或者毫秒
SqlFunc.DateValue(DateTime date, DateType dataType)
范围判段
SqlFunc.Between(object value, object start, object end)
类型转换
SqlFunc.ToInt32(object value)
SqlFunc.ToInt64(object value)
SqlFunc.ToDate(object value)
SqlFunc.ToString(object value)
SqlFunc.ToDecimal(object value)
SqlFunc.ToGuid(object value)
SqlFunc.ToDouble(object value)
SqlFunc.ToBool(object value)
截取字符串
SqlFunc.Substring(object value, int index, int length)
替换字符串
SqlFunc.Replace(object value, string oldChar, string newChar)
获取字符串长度
SqlFunc.Length(object value)
聚合函数
SqlFunc.AggregateSum<TResult>(TResult thisValue)
SqlFunc.AggregateAvg<TResult>(TResult thisValue)
SqlFunc.AggregateMin(TResult thisValue)
SqlFunc.AggregateMax<TResult>(TResult thisValue)
SqlFunc.AggregateCount<TResult>(TResult thisValue)
如果还不支持可以用 MappingColumn 实现复杂的功能
var s2 = db.Queryable<Student>()
.Select(it => new { id = it.Id, rowIndex=SqlFunc.MappingColumn(it.Id, " row_number() over(order by id)") }).ToList();
//生成SQL
//SELECT [ID] AS [id] , row_number() over(order by id) AS [rowIndex] FROM [STudent]
如果还有不支持的可以写字符串
db.Queryable<Student>().Where("id=@id",new{id=1}).Select("id,name").ToList()
如果还不支持可以下面的自定义扩展SqlFunc让你的代码更漂亮
让拉姆达支持我自定义的方法（4.6.0.6）
public class Demo
{
public static SqlSugarClient GetDb()
{
//Create ext method
var expMethods = new List<SqlFuncExternal>();
expMethods.Add(new SqlFuncExternal()
{
UniqueMethodName = "MyToString",
MethodValue = (expInfo, dbType, expContext) =>
{
if(dbtype=DbType.SqlServer)
return string.Format("CAST({0} AS VARCHAR(MAX))", expInfo.Args[0].MemberName);
else
throw new Exception("未实现")'
}
});
var config = new ConnectionConfig()
{
ConnectionString = Config.ConnectionString,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,
ConfigureExternalServices = new ConfigureExternalServices()
{
SqlFuncServices = expMethods//set ext method
}
};
SqlSugarClient db = new SqlSugarClient(config);
return db;
}
public static string MyToString<T>(T str)
{
throw new NotSupportedException("Can only be used in expressions");
}
public static void Init()
{
var db = GetDb();
var list = db.Queryable<Student>().Where(it => MyToString(it.Id) == "1302583").ToList();
var sql = db.Queryable<Student>().Where(it => MyToString(it.Id) == "1302583").ToSql();
Console.WriteLine(sql);
}
}
View Code
4、分页查询
分页查询
var db = GetInstance();
var pageIndex = 1;
var pageSize = 2;
var totalCount = 0;
单表分页
var page = db.Queryable<Student>().OrderBy(it => it.Id).ToPageList(pageIndex, pageSize, ref totalCount);
多表分页
var pageJoin = db.Queryable<Student, School>((st, sc) => new object[] {
JoinType.Left,st.SchoolId==sc.Id
}).ToPageList(pageIndex, pageSize, ref totalCount);
取前5条
var top5 = db.Queryable<Student>().Take(5).ToList();
取前5条之后的所有数据
var skip5 = db.Queryable<Student>().Skip(5).ToList();
注意：SqlServer版本底层采用的是Rownumber分页，在排序字段有索引的情况下性能是最好的分页，优于12分页和TOP分页，
RowNumber分页是页码越小性能越快符合多数人的使用习惯，如果追求极限性能 当前码数 大于总页数的一半时可以采用倒序分页法实现对大页码的优化同样达到小页码的性能。
参考代码：
//常规写法
db.Queryable<Student>().OrderBy(it=>it.Id).ToPageList(pageIndex,pageSize);
//针对rowNumber分页的优化写法，该写法可以达到分页最高性能，非对性能要求过高的可以不用这么写
var Tempdb=db.Queryable<Student>();
int count = Tempdb.Count();
var Skip = (R.Page - 1) * R.PageCount;
var Take = R.PageCount;
if (R.Page*R.PageCount > P.Count / 2)//页码大于一半用倒序
{
Tempdb.OrderBy(x => x.ID, OrderByType.Desc);
var Mod = P.Count % R.PageCount;
var Page = (int)Math.Ceiling((Decimal)P.Count / R.PageCount);
if (R.Page * R.PageCount >= P.Count)
{
Skip = 0; Take = Mod == 0 ? R.PageCount : Mod;
}
else
{
Skip = (Page - R.Page - 1) * R.PageCount + Mod;
}
}
else
{
Tempdb.OrderBy(x => x.ID);//升序
}
Tempdb.Skip(Skip);
Tempdb.Take(Take);
var list=Tempdb.ToList();
View Code
5、动态查询
简单动态
与EF区别 sqlsugar queryable是引用类型，我们需用queryable.clone()解决一些问题
var queryable=db.Queryable<Student>();
//拼接会比EF方便些，不像EF需要queryable+=
queryable.Where(it => it.Id==1);
queryable.Where(it => it.Name=="a");
//防止queryable相互影响我们用clone解决
var id=queryable.Clone().Select(it=>it.Id).First();
var list=queryable.Clone().ToList();
案例1： WhereIF函数
根据条件判段是否执行过滤，我们可以用WhereIf来实现，true执行过滤，false则不执行
var list = db.Queryable<Student>()
.WhereIF(!string.IsNullOrEmpty(a),it => it.Name == a)
.WhereIF(!string.IsNullOrEmpty(b), it => it.Name == b).ToList();
案例2.：MergeTable 函数 4.4
是将多表查询的结果Select里的内容变成一张表， 如果是多表查询的时候，我们无论是使用 where 还是 orderBy 都需要加别名，这样我们就不能实现动态排序，因为我不知道别名叫什么, 可以用MergeTable解决这个问题
以前
var pageJoin = db.Queryable<Student, School>((st, sc) => new object[]
{
JoinType.Left, st.SchoolId == sc.Id
})
.Where(st=>st.id==1)//别名是st
.OrderBy("sc.name asc")//别名是sc
.Select((st,sc)=>new { id=st.Id,name=sc.Name})
.ToList();
现在
var pageJoin = db.Queryable<Student, School>((st, sc) => new object[]
{
JoinType.Left,st.SchoolId==sc.Id
})
.Select((st,sc) => new
{
id = st.Id,
name = sc.Name
})
.MergeTable()
.Where(XXX=>XXX.id==1).OrderBy("name asc").ToList();//别名不限
案例3： SqlQueryable 4.5.2.5
可以方便的把SQL变成表来操作
var t12 = db.SqlQueryable<Student>("select * from student").ToPageList(1, 2);
案例4： 将表单组装成 List<ConditionalModel>实现查询 4.5.9
List<IConditionalModel> conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" });
conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Like, FieldValue = "1" });
var student = db.Queryable<Student>().Where(conModels).ToList();
//4.6.4.4 版本支持了 复杂的OR
// and id=100 and (id=1 or id=2 and id=1)
conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "100" });
conModels.Add(new ConditionalCollections() { ConditionalList=
new List<KeyValuePair<WhereType, SqlSugar.ConditionalModel>>()
{
new KeyValuePair<WhereType, ConditionalModel>
( WhereType.And ,
new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }),
new KeyValuePair<WhereType, ConditionalModel>
(WhereType.Or,
new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" }),
new KeyValuePair<WhereType, ConditionalModel>
( WhereType.And,
new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" })
}
});
var student = db.Queryable<Student>().Where(conModels).ToList();
案例5: 拼接拉姆达 4.5.9.8
var exp= Expressionable.Create<Student>()
.OrIF(1==1,it => it.Id == 11)
.And(it=>it.Id==1)
.AndIF(2==2,it => it.Id == 1)
.Or(it =>it.Name == "a1").ToExpression();//拼接表达式
var list=db.Queryable<Student>().Where(exp).ToList();
复杂动态
Queryable是支持字符串与拉姆达混用或者纯字符串拼接模式，可以满足复杂的一些需求
例子1:
var queryable = db.Queryable<Student>("t");
queryable.Where("t.id in (select id from xxx)");
queryable.Where(it => it.Id == 1);
//更多操作拼接qureyable
var result = queryable.Select(@"
id,
name,
(select name form school where shoolid=t.id) as schoolName
").ToList();
例子2:
dynamic join3 = db.Queryable("Student", "st")
.AddJoinInfo("School", "sh", "sh.id=st.schoolid")
.Where("st.id>@id")
.AddParameters(new { id = 1 })
.Select("st.*").ToList(); //也可以Select<T>(“*”).ToList()返回实体集合
例子3:
var list = db.Queryable<Student>().
Select(it => new Student()
{
Name = it.Name,
Id = SqlFunc.MappingColumn(it.Id, "(select top 1 id from school)") // 动态子查询
}).ToList();
安全拼SQL
使用参数化过滤
private static void Where()
{
var db = GetInstance();
string value = "'jack';drop table Student";
var list = db.Queryable<Student>().Where("name=@name", new { name = value }).ToList();
//没有发生任何事情
}
字段是无法用参数化实现的，我们就可以采用这种方式过滤
private static void OrderBy()
{
var db = GetInstance();
try
{
var propertyName = "Id'"; //类中的属性的名称
var dbColumnName = db.EntityProvider.GetDbColumnName<Student>(propertyName);
var list2 = db.Queryable<Student>().OrderBy(dbColumnName).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
View Code
6、分组查询
分组查询
1.分组查询
var group = db.Queryable<Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
多个GroupBy写法如下
var group = db.Queryable<Student>().GroupBy(it => it.Id).GroupBy(it => it.Type)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
还可以
.GroupBy(it => new {it.Id,it.Type})
2.简单去重
var list3 = db.Queryable<Student>()
.GroupBy(it => new { it.Id, it.Name }).Select(it=>new{ it.id,it.Name}).ToList();
// 性能优于 select distinct id,name from student所以我暂不支持distinct去重，结果是一样的
3.去重并且返回所有列(分组第一条)
group by只能返回特定列，PartitionBy可以返回所有列
var list3 = db.Queryable<Student>()
.PartitionBy(it => new { it.Id, it.Name }).Take(1).ToList();
View Code
参考：http://www.codeisbug.com/Doc/8/1170