SqlSugar查询操作

系列文章目录

第1章 SqlSugar-表到实体、实体到表及库表操作
第2章 SqlSugar增删改操作
第3章 SqlSugar查询操作
第4章 SqlSugar导航查询、反向导航查询、导航插入、导航更新

一、简单查询

1.1、查询集合

//select * from StudentInfo
List<StudentInfo> list = db.Queryable<StudentInfo>().ToList();

1.2、查询总数

int count = db.Queryable<StudentInfo>().Count();

1.3、条件查询

List<StudentInfo> studentList;
//按条件查询//select * from StudentInfo where id=1
studentList = db.Queryable<StudentInfo>()
    			.Where(it => it.Id == 1).ToList();

多条件查询

{
    studentList = db.Queryable<StudentInfo>()
        			.Where(it => it.Id > 10 && it.Name == "名称_11").ToList();

    //select * from StudentInfo where id>10 and name='a'
    studentList = db.Queryable<StudentInfo>()
			        .Where(it => it.Id > 10)
			        .Where(it => it.Name == "名称_11").ToList();
}

多个字段 条件拼接查询

{
    List<StudentInfo> OrderList = new List<StudentInfo>()
    {
        new StudentInfo{ Id = 1,Name="名称_1"},
        new StudentInfo{ Id = 2,Name="名称_2"},
        new StudentInfo{ Id = 3,Name="名称_3"}
    };
    Expressionable<StudentInfo> exp = new Expressionable<StudentInfo>();
    foreach (var item in OrderList)
    {
        exp.Or(it => it.Id == item.Id && it.Name == item.Name);
    }
    //使用构造好的表达式
    var studentlist = db.Queryable<StudentInfo>().Where(exp.ToExpression()).ToList();
}

1.4、动态OR查询

{
    Expressionable<StudentInfo> exp = Expressionable.Create<StudentInfo>();
    string name = "名称_11";

    //.OrIf 是条件成立才会拼接OR
    exp.OrIF(!string.IsNullOrWhiteSpace(name), it => it.Name.Contains(name));

    //拼接OR
    exp.Or(it => it.Name.Contains("名称_"));
    studentList = db.Queryable<StudentInfo>()
        .Where(exp.ToExpression()).ToList();
}

1.5、模糊查询

{
    studentList = db.Queryable<StudentInfo>()
        .Where(it => it.Name.Contains("名称_")).ToList();
    //select  * from  StudentInfo where name like %名称_%
}

1.6、主键查询

//根据主键查询
{
    /*单主键查询*/
    //通过主键查询 SingleById
    var it=db.Queryable<StudentInfo>().InSingle(2);

    //根据ID查询
    //select * from StudentInfo where id=2
    it=db.Queryable<StudentInfo>().Single(it => it.Id == 2);

    /*多主键查询*/
    var getAll = db.Queryable<StudentInfo>()
        .WhereClassByPrimaryKey(new StudentInfo() { Id = 123 })
        .ToList(); //单个实体  

    getAll = db.Queryable<StudentInfo>()
		        .WhereClassByPrimaryKey(new List<StudentInfo>()
		        {
		            new StudentInfo() { Id = 123 },
		            new StudentInfo() { Id = 234 },
		            new StudentInfo() { Id = 345 }
		        }).ToList(); //支持集合
}

1.7、查第一条

//没有返回Null
StudentInfo student = db.Queryable<StudentInfo>().First(it => it.Id == 1);

1.8、查前几条

前10条：

List<StudentInfo> studetntlist = db.Queryable<StudentInfo>().Take(10).ToList();

1.9、查询是否有记录

isExists = db.Queryable<StudentInfo>().Any(it => it.Id > 11); 

二、连接查询

2.1、准备

ConnectionConfig connectionConfig = new ConnectionConfig()
{
    ConnectionString = CustomConnectionConfig.ConnectionString001,
    IsAutoCloseConnection = true,
    DbType = DbType.SqlServer
};
using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
{
    db.CodeFirst.InitTables<Company>();
    db.CodeFirst.InitTables<User>();
    db.CodeFirst.InitTables<UserScore>();
    db.Deleteable<UserScore>().ExecuteCommand();
    db.Deleteable<Company>().ExecuteCommand();
    db.Deleteable<User>().ExecuteCommand();

    List<Company> companyList = new List<Company>();
    for (int i = 0; i < 3; i++)
    {
        companyList.Add(new Company()
        {
            CreateTime = DateTime.Now,
            Id = i + 1,
            CompanyName = $"供热_{i + 1}"
        });
    }
    db.Insertable<Company>(companyList).ExecuteCommand();
    List<User> userList = new List<User>();

    List<UserScore> userScoreList = new List<UserScore>();
    for (int i = 0; i < 10; i++)
    {
        userList.Add(new User()
        {
            CreateTime = DateTime.Now,
            Id = i + 1,
            UserName = $"用户_{i + 1}",
            CompanyId = companyList[0].Id
        });

        userScoreList.Add(new UserScore()
        {
            UserId = i + 1,
            Subject = "考勤",
            Achievement = 90,
            CreateTime = DateTime.Now
        });

        userScoreList.Add(new UserScore()
        {
            UserId = i + 1,
            Subject = "绩效",
            Achievement = 90,
            CreateTime = DateTime.Now
        });
        userScoreList.Add(new UserScore()
        {
            UserId = i + 1,
            Subject = "项目奖金",
            Achievement = 89,
            CreateTime = DateTime.Now
        });

    }
    db.Insertable<User>(userList).ExecuteCommand();
    db.Insertable<UserScore>(userScoreList).ExecuteCommand();

    //配置生成sql语句
    {
        db.Aop.OnLogExecuting = (s, p) =>
        {
            Console.WriteLine("----------------------------");
            Console.WriteLine($"Sql语句:{s}");
        };
    }

2.2、左连接

var query1 = db.Queryable<Company>()
          .LeftJoin<User>((c, u) => c.Id == u.CompanyId)
          .LeftJoin<UserScore>((c, u, us) => u.Id == us.UserId)
          .Where(c => c.Id == 1)
          .Select((c, u, us) => new
          {
              CompanyId = c.Id,
              CompanyName = c.CompanyName,
              UserNae = u.UserName,
              Subject = us.Subject,
              ScoreCoutn = us.Achievement
          })
          .ToList();

等价sql：

SELECT  [c].[Id] AS [CompanyId] , [c].[CompanyName] AS [CompanyName] , [u].[UserName] AS [UserNae] , 
		[us].[Subject] AS [Subject] , [us].[Achievement] AS [ScoreCoutn]  
FROM [Company] c 
Left JOIN [User] u ON ( [c].[Id] = [u].[CompanyId] )  
Left JOIN [UserScore] us ON ( [u].[Id] = [us].[UserId] )   
WHERE ( [c].[Id] = @Id0 )

2.3、右连接

var query1 = db.Queryable<Company>()
             .RightJoin<User>((c, u) => c.Id == u.CompanyId)
              .RightJoin<UserScore>((c, u, us) => u.Id == us.UserId)
              .Where(c => c.Id == 1)
              .Select((c, u, us) => new
              {
                  CompanyId = c.Id,
                  CompanyName = c.CompanyName,
                  UserNae = u.UserName,
                  Subject = us.Subject,
                  ScoreCoutn = us.Achievement
              })
              .ToList();

等价SQL：

SELECT  [c].[Id] AS [CompanyId] , [c].[CompanyName] AS [CompanyName] , 
		[u].[UserName] AS [UserNae] , [us].[Subject] AS [Subject] , [us].[Achievement] AS [ScoreCoutn]  
FROM [Company] c 
Right JOIN [User] u ON ( [c].[Id] = [u].[CompanyId] )  
Right JOIN [UserScore] us ON ( [u].[Id] = [us].[UserId] )   
WHERE ( [c].[Id] = @Id0 )

2.4、内连接

var query1 = db.Queryable<Company>()
           .InnerJoin<User>((c, u) => c.Id == u.CompanyId)
           .InnerJoin<UserScore>((c, u, us) => u.Id == us.UserId)
           .Where(c => c.Id == 1)
           .Select((c, u, us) => new
           {
               CompanyId = c.Id,
               CompanyName = c.CompanyName,
               UserNae = u.UserName,
               Subject = us.Subject,
               ScoreCoutn = us.Achievement
           })
           .ToList();

SELECT  [c].[Id] AS [CompanyId] , [c].[CompanyName] AS [CompanyName] , 
		[u].[UserName] AS [UserNae] , [us].[Subject] AS [Subject] , [us].[Achievement] AS [ScoreCoutn]  
FROM [Company] c 
Inner JOIN [User] u ON ( [c].[Id] = [u].[CompanyId] )  
Inner JOIN [UserScore] us ON ( [u].[Id] = [us].[UserId] )   
WHERE ( [c].[Id] = @Id0 )

三、复杂查询

3.1、分组聚合

{
    var list = db.Queryable<Student>()
             .GroupBy(it => new { it.Id, it.Name })
             .Having(it => SqlFunc.AggregateAvg(it.Id) > 0)
             .Select(it => new
             {
                 idAvg = SqlFunc.AggregateAvg(it.Id),
                 count = SqlFunc.AggregateCount(it.Id),
                 name = it.Name
             })
             .ToList();
}

等价sql：

SELECT  AVG([Id]) AS [idAvg] , COUNT([Id]) AS [count] , [StudentName] AS [name]  
FROM [dbstudent] GROUP BY [Id],[StudentName]  HAVING (AVG([Id]) > @Const0 )

3.2、去重

var list = db.Queryable<Student>()
			   .Distinct()
			   .Select(it => new { it.Name }).ToList();

3.3、分页查询

单表分页

var page = db.Queryable<Student>()
   			 .ToPageList(pagenumber, pageSize, ref totalCount);

SELECT * FROM 
	(SELECT [Id],[SchoolId],[StudentName],[CreateTime],ROW_NUMBER() 
	OVER( ORDER BY GetDate() ) AS RowIndex  FROM [dbstudent] ) T 
WHERE RowIndex BETWEEN 3 AND 4

或：

var page1 = db.Queryable<Student>()
              .ToOffsetPage(pagenumber, pageSize, ref totalCount);

SELECT [Id],[SchoolId],[StudentName],[CreateTime] 
FROM [dbstudent]  
ORDER BY GETDATE()  
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY

多表分页
如果SqlServer不想有Rownumber可以用 ToOffsetPage 较新版本支持

{
    int pagenumber = 1; // pagenumber是从1开始的不是从零开始的
    int pageSize = 20;
    int totalCount = 0;
    var list = db.Queryable<Company>()
   .LeftJoin<User>((c, u) => c.Id == u.CompanyId)
   .Select((c, u) => new
   {
       Id = c.Id,
       cName = c.CompanyName,
       uId = u.Id,
       uName = u.UserName
   }).ToOffsetPage(pagenumber, pageSize, ref totalCount);
}

SELECT COUNT(1) 
FROM 
	(SELECT  [c].[Id] AS [Id] , [c].[CompanyName] AS [cName] , [u].[Id] AS [uId] , [u].[UserName] AS [uName]  
	FROM [Company] c Left JOIN [User] u ON ( [c].[Id] = [u].[CompanyId] )  ) 
CountTable
----------------------------
SELECT  [c].[Id] AS [Id] , [c].[CompanyName] AS [cName] , [u].[Id] AS [uId] , [u].[UserName] AS [uName]  
FROM [Company] c 
Left JOIN [User] u ON ( [c].[Id] = [u].[CompanyId] )  
ORDER BY GETDATE()  OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY