SqlSugar常用查询实例-拉姆达表达式

SqlSugar常用查询实例-拉姆达表达式

SqlSugar支持拉姆达表达式查询，匿名对象参数等，相对还是比较方便好用的。

一.查询列表：

    //查询列表

    SqlSugarClient db = SugarContext.GetInstance();

    List<teacher> tList = db.Queryable<teacher>().ToList();

    Console.WriteLine(tList.Count);

    var studentDynamic = db.Queryable<student>().ToDynamic();

    Console.WriteLine(studentDynamic);

    string teaJson = db.Queryable<teacher>().ToJson();

    Console.WriteLine(teaJson);

    //Json序列化的DateTime处理

    db.SerializerDateFormat = "yyyy年MM月dd日";

    string teacher1 = db.Queryable<teacher>().OrderBy("tbirthday desc").Take().ToJson();

    string teacher2 = db.Sqlable().From<teacher>("t").SelectToJson(" top 1 *");

    string teacher3 = db.SqlQueryJson("select top 1 * from teacher;");

    Console.WriteLine(teacher1);

    Console.WriteLine(teacher2);

    Console.WriteLine(teacher3);

    Console.WriteLine(tList.First().tbirthday.Value.ToString("yyyy-MM-dd"));

特别说明：

1、SqlSugar类库中的json序列化使用的.Net自带的“JavaScriptSerializer”

2、其中Date的格式化原理是先生成json，字符串/\/Date(xxxx)\/，将取出总毫秒数再进行转换。

更多点击参考

3.源代码中的日期转化不支持1970年以前的时间，但是可以自己修改一下：

           // string pattern = @"\\/Date\((\d+)\)\\/"; //源代码中的正则

            string pattern = @"\\/Date\((-*\d+)\)\\/"; //2016-11-22 修改正则

二、常用查询

//查询单条

SqlSugarClient db = SugarContext.GetInstance();

teacher tea1 = db.Queryable<teacher>().Single(t => t.tno == "");//如果查询数据库没有数据，抛出异常

Console.WriteLine(tea1.tname);

//根据主键查询单条

teacher tea2 = db.Queryable<teacher>().InSingle("");//如果查询数据为空，返回null

Console.WriteLine(tea2.ToJsonString());// 输出：null

/***考虑查询为空的情况***/

teacher tea3 = db.Queryable<teacher>().SingleOrDefault(t => t.tno == "");

Console.WriteLine(tea3.ToJsonString());// 输出：null

teacher tea4 = db.Queryable<teacher>()

    .Where(q => q.tno == "")

    .FirstOrDefault(); //此处扩展方法仿EF First()/FirstOrDefault()

Console.WriteLine(tea4.ToJsonString()); // 输出：null

/*****常用查询******/

//1.判断是否存在

bool exists1 = db.Queryable<teacher>().Any(q => q.tno == "");

Console.WriteLine(exists1);

//2.模糊查询，含有

List<teacher> contains1 = db.Queryable<teacher>()

    .Where(q => q.tname.Contains("李"))

    .ToList();

Console.WriteLine(contains1.ToJsonString());

//以xxx结尾

List<teacher> end1 = db.Queryable<teacher>()

        .Where(q => q.tname.EndsWith("成"))

        .ToList();

Console.WriteLine(end1.ToJsonString());

//3. in关键词查询

string[] idList = new string[] { "", "" };

List<teacher> in1 = db.Queryable<teacher>()

    .In(q => q.tno, idList)  //如果不支持拉姆达表达式，默认为主键

    .ToList();

Console.WriteLine(in1.ToJsonString());

List<teacher> in2 = db.Queryable<teacher>()

    .Where(q => idList.Contains(q.tno))

    .ToList();

Console.WriteLine(in2.ToJsonString());

/*********汇总查询等***********/

//1.获取总数

int count = db.Queryable<teacher>()

    .Where(q => q.tsex == "男")

    .Count();

Console.WriteLine("count=" + count);

//2.获取最大值

decimal maxDegree = db.Queryable<score>()

    .Where(q => q.cno == "3-105")

    .Max(q => q.degree) //此处Max返回的结果为Object需要拆箱

    .ObjToDecimal();

Console.WriteLine("maxDegree:" + maxDegree);

//3.获取最小值

decimal minDegree = db.Queryable<score>()

    .Where(q => q.cno == "3-105")

    .Min(q => q.degree)

    .ObjToDecimal();

Console.WriteLine("minDegree:" + minDegree);

//4.平均值

//SqlSugar 不支持 平均值的扩展方法

/***复杂的查询可以使用Sql字符串实现***/

//var list = db.Queryable<InsertTest>().Where("id=@id and name=@name", new { id = 1, name = xxx }).ToList();

三、分页查询：

//分页查询处理

SqlSugarClient db = SugarContext.GetInstance();

//分页方式1

Queryable<teacher> query = db.Queryable<teacher>();

query = query.Where(q => q.tno.StartsWith(""));

//排序

//query = query.OrderBy("tbirthday desc"); //排序说明，如果是倒叙，需要传入排序的sql片段

query = query.OrderBy(q => q.tbirthday);    //如果是正序，可以指定拉姆达表达式

int allCount = query.Count();

//分页

//List<teacher> list1 = query.Skip(1).Take(2).ToList();

//或者

List<teacher> list1 = query.ToPageList(, );

Console.WriteLine("list1-count:" + allCount);

Console.WriteLine("list1:" + list1.ToJsonString());

//分页方式2+符合查询 多where是and拼接

Queryable<student> query2 = db.Queryable<student>();

query2 = query2.Where(q => q.ssex == "男");

query2 = query2.Where(q => q.sname.EndsWith("军"));

//排序

query2 = query2.OrderBy(q => q.sbirthday);

int allCount2 = ;

//分页

List<student> list2 = query2.ToPageList(, , ref allCount2);

Console.WriteLine("list2-count:" + allCount2);

Console.WriteLine("list2:" + list2.ToJsonString());

四、汇总查询2

//分组，查询汇总

SqlSugarClient db = SugarContext.GetInstance();

dynamic result1 = db.Queryable<student>()

    .GroupBy(q => q.ssex)

    .Select("ssex,count(*) Count")

    .ToDynamic();

foreach (var item in result1)

{

    Console.WriteLine(item.ssex + "----" + item.Count);

}

string result2 = db.Queryable<score>()

    .GroupBy(q => q.sno)

    .Select("sno,isnull(sum(degree),0)")

    .ToJson();

Console.WriteLine(result2);

五、关联表查询

static void QueryFive()

{

    //关联表查询，筛选等

    SqlSugarClient db = SugarContext.GetInstance();

    //将结果转换成其他对象

    var result = db.Queryable<score>()

        .JoinTable<student>((s1, s2) => s1.sno == s2.sno)

        .OrderBy(s1 => s1.degree)

        .Take()

        .Select<score, student, Score_Student>((s1, s2) => new Score_Student

        {

            sno = s1.sno,

            sname = s2.sname,

            cno = s1.cno,

            degree = s1.degree

        }).ToList();

    Console.WriteLine(result.ToJsonString());

    //输出结果

    //[{"sno":"101","sname":"李军","degree":64.0,"cno":"3-105"}]

    //关联查询

    //特别说明：如果指定了关联表代表参数s1,s2,那么在后边的筛选等 *** 作中需要使用表字段的地方，必须使用s1或s2

    var result2 = db.Queryable<score>()

        .JoinTable<student>((s1, s2) => s1.sno == s2.sno)

        .Where(s1 => s1.degree > )

        .Where(s1 => s1.degree < )

        .ToJson();

    Console.WriteLine(result2);

    //输出结果：

    //[{"sno":"109","cno":"3-245","degree":68.0,

    //"sno1":"109","sname":"王芳","ssex":"女","sbirthday":"\/Date(161193600000)\/","class":"95031"}]

    //获取单个表的投影

    //特别说明，如果想获取单个表的投影，需要先将查询结果.ToList(),然后在创建匿名对象

    //或者 Select()中使用sql指定列名

    var result3 = db.Queryable<student>()

        .OrderBy(q => q.sbirthday)

        .Take()

        .ToList()

        .Select(q => new

        {

            sno = q.sno,

            sname = q.sname

        });

    Console.WriteLine(result3.ToJsonString());

    //输出结果：

    //[{"sno":"103","sname":"陆军"}]

    //获取多个表，投影

    var result4 = db.Queryable<score>()

        .JoinTable<student>((s1, s2) => s1.sno == s2.sno)

        .OrderBy(s1 => s1.degree)

        .Take()

        .Select("s1.*,s2.sname as sname")

        .ToJson();

    Console.WriteLine(result4);

    //输出结果：

    //[{"sno":"101","cno":"3-105","degree":64.0,"sname":"李军","row_index":1}]

}

public class Score_Student

{

    public string sno { get; set; }

    public string sname { get; set; }

    public decimal degree { get; set; }

    public string cno { get; set; }

}

更多参考：

.Net开源SqlServer ORM框架SqlSugar整理

SqlSugar框架T4模板的使用