基本查询

基本查询

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

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

q.Where(a => a.Id == 1).FirstOrDefault();
/*
 * SELECT TOP 1 [Person].[Name] AS [Name],[Person].[Gender] AS [Gender],[Person].[Age] AS [Age],[Person].[CityId] AS [CityId],[Person].[CreateTime] AS [CreateTime],[Person].[EditTime] AS [EditTime],[Person].[Id] AS [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] AS [Name],[Person].[Gender] AS [Gender],[Person].[Age] AS [Age],[Person].[CityId] AS [CityId],[Person].[CreateTime] AS [CreateTime],[Person].[EditTime] AS [EditTime],[Person].[Id] AS [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] AS[Name],[Person].[Gender] AS[Gender],[Person].[Age] AS[Age],[Person].[CityId] AS[CityId],[Person].[CreateTime] AS[CreateTime],[Person].[EditTime] AS[EditTime],[Person].[Id] AS[Id] 
   FROM[Person] AS[Person] 
   WHERE[Person].[Id] IN(1, 2, 3)
 */

/* in 子查询 */
/* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
persons = q.Where(a => this.DbContext.Query<City>().Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); 
/*
 * SELECT [Person].[Name] AS [Name],[Person].[Gender] AS [Gender],[Person].[Age] AS [Age],[Person].[CityId] AS [CityId],[Person].[CreateTime] AS [CreateTime],[Person].[EditTime] AS [EditTime],[Person].[Id] AS [Id] 
   FROM [Person] AS [Person] 
   WHERE [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] AS [Id],[Person].[Name] AS [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] AS [Name],[Person].[Gender] AS [Gender],[Person].[Age] AS [Age],[Person].[CityId] AS [CityId],[Person].[CreateTime] AS [CreateTime],[Person].[EditTime] AS [EditTime],[Person].[Id] AS [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] AS [Name] FROM [Person] AS [Person]
 */

exists 查询:

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

更多用法请查看使用进阶