使用进阶-复杂查询

复杂查询

IQuery<T> 接口支持多表连接查询、聚合查询、分组查询,这几个接口配合使用可以让我们开发达到事半功倍的效果。对于一般的复杂查询,Chloe 还是游刃有余的。

连接子查询:

var userQuery = context.Query<User>();

var cityIdsQuery = context.Query<City>().Where(a => a.Name.Contains("北京")).Select(a => a.Id);

/* 建立连接 */
var view = userQuery.InnerJoin(cityIdsQuery, (user, cityId) => user.CityId == cityId).Select((user, cityId) => user);

/* 执行查询 */
List<User> userList = view.ToList();

/*
 * 生成的 sql:
 * 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] 
   INNER JOIN 
   (SELECT [City].[Id] AS [C] FROM [City] AS [City] WHERE [City].[Name] LIKE '%' + N'北京' + '%') AS [T] 
   ON [Users].[CityId] = [T].[C]
 */

连接、分组、聚合查询结合:

现有 Users 表、City 表,他们的关系是一个 User 隶属一个 City,一个 City 有多个用户。假设,现在有需求要查出 City 的信息,同时也要把该 City 下用户最小的年龄输出,如果用原生 sql 写的话大概是:

select City.*,T.MinAge from City left join (select CityId,Min(Users.Age) as MinAge 
from Users group by Users.CityId) as T on City.Id=T.CityId

这类的查询 Chloe 也完全可以做到:

IQuery<User> users = context.Query<User>();
IQuery<City> cities = context.Query<City>();
var gq = users.GroupBy(a => a.CityId).Select(a => new { a.CityId, MinAge = Sql.Min(a.Age) });

cities.LeftJoin(gq, (city, g) => city.Id == g.CityId).Select((city, g) => new { City = city, MinAge = g.MinAge }).ToList();
/*
 * SELECT [T].[MinAge] AS [MinAge],[City].[Id] AS [Id],[City].[Name] AS [Name],[City].[ProvinceId] AS [ProvinceId] 
   FROM [City] AS [City] 
   LEFT JOIN (SELECT [Users].[CityId] AS [CityId],MIN([Users].[Age]) AS [MinAge] 
   FROM [Users] AS [Users] GROUP BY [Users].[CityId]) AS [T] ON [City].[Id] = [T].[CityId]
 */

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])
 */

select 子查询:

var result = context.Query<User>().Select(a => new
{
    CityName = context.Query<City>().Where(c => c.Id == a.CityId).First().Name,
    User = a
}).ToList();
/*
 * SELECT 
 *      (SELECT TOP (1) [City].[Name] AS [C] FROM [City] AS [City] WHERE [City].[Id] = [Users].[CityId]) AS [CityName],
 *      [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]
 */

统计:

var statisticsResult = context.Query<City>().Select(a => new
{
    UserCount = context.Query<User>().Where(u => u.CityId == a.Id).Count(),
    MaxAge = context.Query<User>().Where(u => u.CityId == a.Id).Max(c => c.Age),
    AvgAge = context.Query<User>().Where(u => u.CityId == a.Id).Average(c => c.Age),
}).ToList();
/*
 * SELECT 
 *      (SELECT COUNT(1) AS [C] FROM [Users] AS [Users] WHERE [Users].[CityId] = [City].[Id]) AS [UserCount],
 *      (SELECT MAX([Users].[Age]) AS [C] FROM [Users] AS [Users] WHERE [Users].[CityId] = [City].[Id]) AS [MaxAge],
 *      (SELECT CAST(AVG([Users].[Age]) AS FLOAT) AS [C] FROM [Users] AS [Users] WHERE [Users].[CityId] = [City].[Id]) AS [AvgAge]
 * FROM [City] AS [City]
 */