基础查询
用到的表结构、数据、实体类
方便测试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`