Complex query

复杂查询

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

连接子查询：

var personQuery = context.Query<Person>();

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

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

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

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

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

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

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

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

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

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

exists 查询：

/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
var persons = context.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])
 */

select 子查询：

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

统计：

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