SqlServer-基本查询

基本查询

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

string connString = "Your connection string";
MsSqlContext context = new MsSqlContext(connString);
context.PagingMode = PagingMode.OFFSET_FETCH;
IQuery<User> q = context.Query<User>();

q.Where(a => a.Id == 1).FirstOrDefault();
/*
 *生成的 sql:
 * SELECT TOP (1) [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] 
   FROM [Users] AS [Users] 
   WHERE [Users].[Id] = 1
 */

like 查询:

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

in 查询:

List<int> ids = new List<int>() { 1, 2, 3 };
q.Where(a => ids.Contains(a.Id)).ToList();
/*
 * Int32 @P_0 = 1;
   Int32 @P_1 = 2;
   Int32 @P_2 = 3;
   SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] 
   FROM [Users] AS [Users] 
   WHERE [Users].[Id] IN (@P_0,@P_1,@P_2)
 */

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

排序:

q.Where(a => a.Id > 0).OrderBy(a => a.Age).ThenBy(a => a.Id).ToList();
/*
 * SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] 
   FROM [Users] AS [Users] 
   WHERE [Users].[Id] > 0 
   ORDER BY [Age] ASC,[Id] ASC
 */

分页:

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

distinct 查询:

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

exists 查询:

/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
users = context.Query<User>().Where(a => context.Query<City>().Where(c => c.Id == a.CityId).Any()).ToList();
/*
 * SELECT 
 *      [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name] 
 * FROM [Users] AS [Users] 
 * WHERE Exists (SELECT N'1' AS [C] FROM [City] AS [City] WHERE [City].[Id] = [Users].[CityId])
 */

更多用法请查看使用进阶