基础查询

用到的表结构、数据、实体类#

方便测试Demo

Student#

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `StudentId` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `SchoolId` int(11) NOT NULL,
  PRIMARY KEY (`StudentId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `student` VALUES (1, 'Zzr', 1);
INSERT INTO `student` VALUES (2, 'Ls', 2);
INSERT INTO `student` VALUES (3, 'Wem', 3);
INSERT INTO `student` VALUES (4, 'Hpf', 4);
INSERT INTO `student` VALUES (5, 'Zzr', 5);
INSERT INTO `student` VALUES (6, 'hpf', 2);
INSERT INTO `student` VALUES (7, 'hpf', 2);
INSERT INTO `student` VALUES (9, 'hpf', 2);
INSERT INTO `student` VALUES (11, 'qwe', 0);
INSERT INTO `student` VALUES (12, 'qwe', 0);

public class Student
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }

        [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是Student类里面的
        public School School { get; set; } //不能赋值
    }

1.查所有#

List<Student> list = 
db.Queryable<Student>().ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`

2.按条件查询#

List<Student> list = db.Queryable<Student>().Where(it=> it.Id.Equals("1")).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `student`  WHERE  (`Id` = 1)

3.多条件查询#

List<Student> list1 = db.Queryable<Student().Where(it => it.SchoolId > 5 && it.Name == "G").ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE (( `SchoolId` > 5 ) AND ( `StudentName` = 'G' ))


List<Student> list2 = db.Queryable<Student>().Where(it => it.SchoolId > 5).Where(it => it.Name == "G").ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE ( `SchoolId` > 5 )  AND ( `StudentName` = 'G' )

4.动态OR查询#

var exp = Expressionable.Create<Student>();
exp.OrIF(1==1,it=>it.Id.Equals("3"));
exp.Or(it => it.Name == "E");
List<Student> list = db.Queryable<Student().Where(exp.ToExpression()).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE ((`Id` = 3) OR( `StudentName` = 'E' ))

5.模糊查询#

List<Student> list = db.Queryable<Student>().Where(it=>it.Id.Contains("4")).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE(`Id` like concat('%', 4, '%'))

6.根据主键查询#

Student model1 = db.Queryable<Student>().InSingle(5); //通过主键查询

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE `Id` = '5'


Student model2 = db.Queryable<Student>().Single(it => it.Id == 5); 

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE( `Id` = '5')

7.查询第一条#

First == Linq中的 FirstOrDefault 没有数据返回Null

 Student model = db.Queryable<Student>().First(it => it.Id == 1);
 
//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`   WHERE( `Id`= '8')  ORDER BY NOW()  LIMIT 0,1

8.查前几条#

List<Student> list = db.Queryable<Student>().Take(3).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  LIMIT 0,3

9.数据行数#

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

//SELECT Count(*) FROM `Student`

10.设置新表明#

 List<Student> list1 = db.Queryable<Student>().AS("SS").ToList();
 
//SELECT `Id`,`SchoolId`,`StudentName` FROM `SS`

List<Student> list2 = db.Queryable<Student>().AS("dbo.Student").ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `dbo`.`Student`

11.是否存在记录#

 bool b = db.Queryable<Student>().Any(it => it.Id == 9);
 
//SELECT 1 FROM `Student`  WHERE( `Id` = '9')

12.IN查询，IN的使用#

int[] nums = new int[3] { 1, 3, 5 };
List<Student> list = db.Queryable<Student>().Where(it => nums.Contains(it.Id)).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE(`Id` IN(1, 3, 5))


string[] nums = new string[3] { "A", "F", "G" };
Expressionable<Student> exp = new Expressionable<Student>();
foreach (var num in nums)
{
    exp.Or(it => it.Name.Contains(num));
}
List<Student> list = db.Queryable<Student>().Where(exp.ToExpression()).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE (((`StudentName` like concat('%','A','%')) OR (`StudentName` like concat('%','F','%')) ) OR  (`StudentName` like concat('%','G','%')) )

13.NOT IN#

int[] nums = new int[3] { 1, 3, 5 };
List<Student> list = db.Queryable<Student>().Where(it => !nums.Contains(it.Id)).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE NOT (`Id` IN (1,3,5))

14.简单排序#

 List<Student> list = db.Queryable<Student>().OrderBy(it => it.Id, OrderByType.Desc).ToList();
 
//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student` ORDER BY `Id`DESC

15.查询一列#

List<string> list = db.Queryable<Student>().Select(it => it.Name).ToList();

//SELECT `StudentName` FROM `Student`

16.查询单条#

Student cs = db.Queryable<Student>().Single(it => it.Id == 10);

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE( `Id` = '10')

17.获取最大值#

int cs = db.Queryable<Student>().Max(it => it.Id);

//SELECT MAX(`Id`) FROM `Student`

18.获取最小值#

 int cs = db.Queryable<Student>().Min(it => it.Id);
 
//SELECT MIN(`Id`) FROM `Student`

19.求和#

int cs = db.Queryable<Student>().Sum(it => it.Id);

//SELECT SUM(`Id`) FROM `Student`

20.查询过滤某一个字段#

仅支持单表查询

List<Student> list = db.Queryable<Student>().IgnoreColumns(it=>it.Name).ToList();

//SELECT `Id`,`SchoolId` FROM `Student`

📄文档参考：基础查询