Query

基本查询

根据 Id 查询出一个 User 对象：

IQuery<Person> q = dbContext.Query<Person>();

q.Where(a => a.Id == 1).FirstOrDefault();
/*
 * SELECT TOP 1 [Person].[Name],[Person].[Gender],[Person].[Age],[Person].[CityId],[Person].[CreateTime],[Person].[EditTime],[Person].[Id] 
   FROM [Person] AS [Person] 
   WHERE [Person].[Id] = 1
 */

like 查询：

//注：在 lambda 里使用常量不会参数化处理，使用时切记要将值以变量的形式传进 lambda
q.Where(a => a.Name.Contains("Chloe") || a.Name.StartsWith("C") || a.Name.EndsWith("e")).ToList();
/*
 * SELECT [Person].[Name],[Person].[Gender],[Person].[Age],[Person].[CityId],[Person].[CreateTime],[Person].[EditTime],[Person].[Id] 
   FROM [Person] AS [Person] 
   WHERE ([Person].[Name] LIKE '%' + N'Chloe' + '%' OR [Person].[Name] LIKE N'C' + '%' OR [Person].[Name] LIKE '%' + N'e')
 */

in 查询：

List<int> ids = new List<int>() { 1, 2, 3 };
q.Where(a => ids.Contains(a.Id)).ToList();
/*
 * SELECT [Person].[Name],[Person].[Gender],[Person].[Age],[Person].[CityId],[Person].[CreateTime],[Person].[EditTime],[Person].[Id]
   FROM [Person] AS[Person] 
   WHERE [Person].[Id] IN (1, 2, 3)
 */
 
/* in 子查询 */
/* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
IQuery<int> cityIdQuery = this.DbContext.Query<City>().Select(c => c.Id);
persons = q.Where(a => !cityIdQuery.ToList().Contains((int)a.CityId)).ToList();   //注：cityIdQuery.ToList() 中的 ToList() 方法一定要写在 lambda 里

/*
 * SELECT [Person].[Name],[Person].[Gender],[Person].[Age],[Person].[CityId],[Person].[CreateTime],[Person].[EditTime],[Person].[Id]
   FROM [Person] AS [Person] 
   WHERE [Person].[CityId] IN (SELECT [City].[Id] AS [C] FROM [City] AS [City])
 */

not in 查询：

List<int> ids = new List<int>() { 1, 2, 3 };
q.Where(a => !ids.Contains(a.Id)).ToList();
/*
 * SELECT [Person].[Name],[Person].[Gender],[Person].[Age],[Person].[CityId],[Person].[CreateTime],[Person].[EditTime],[Person].[Id]
   FROM [Person] AS[Person] 
   WHERE NOT [Person].[Id] IN (1, 2, 3)
 */
 
/* not in 子查询 */
IQuery<int> cityIdQuery = this.DbContext.Query<City>().Select(c => c.Id);
persons = q.Where(a => !cityIdQuery.ToList().Contains((int)a.CityId)).ToList();   //注：cityIdQuery.ToList() 中的 ToList() 方法一定要写在 lambda 里
/*
 * SELECT [Person].[Name],[Person].[Gender],[Person].[Age],[Person].[CityId],[Person].[CreateTime],[Person].[EditTime],[Person].[Id]
   FROM [Person] AS [Person] 
   WHERE NOT [Person].[CityId] IN (SELECT [City].[Id] AS [C] FROM [City] AS [City])
 */

可以选取指定的字段，返回一个匿名类型：

q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
 * 只会生成包含 Id 和 Name 两个字段的 sql 语句：
 * SELECT TOP 1 [Person].[Id],[Person].[Name]
   FROM [Person] AS [Person] 
   WHERE [Person].[Id] = 1
 */

排序分页：

q.Where(a => a.Id > 0).OrderBy(a => a.Age).ThenBy(a => a.Id).TakePage(1, 20).ToList();
/*
 * SELECT [Person].[Name],[Person].[Gender],[Person].[Age],[Person].[CityId],[Person].[CreateTime],[Person].[EditTime],[Person].[Id]
   FROM [Person] AS [Person] 
   WHERE [Person].[Id] > 0 ORDER BY [Person].[Age] ASC,[Person].[Id] ASC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
 */

distinct 查询：

q.Select(a => new { a.Name }).Distinct().ToList();
/*
 * SELECT DISTINCT [Person].[Name] FROM [Person] AS [Person]
 */

exists 查询：

/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
persons = dbContext.Query<Person>().Where(a => dbContext.Query<City>().Where(c => c.Id == a.CityId).Any()).ToList();
/*
 * String @P_0 = '1';
   SELECT [Person].[Name],[Person].[Gender],[Person].[Age],[Person].[CityId],[Person].[CreateTime],[Person].[EditTime],[Person].[Id]
   FROM [Person] AS [Person] 
   WHERE Exists (SELECT @P_0 AS [C] FROM [City] AS [City] WHERE [City].[Id] = [Person].[CityId])
 */

更多用法请查看使用进阶。