MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString); IQuery<User> users = context.Query<User>(); IQuery<City> cities = context.Query<City>(); IQuery<Province> provinces = context.Query<Province>(); IJoiningQuery<User, City> user_city = users.InnerJoin(cities, (user, city) => user.CityId == city.Id); IJoiningQuery<User, City, Province> user_city_province = user_city.InnerJoin(provinces, (user, city, province) => city.ProvinceId == province.Id);

只获取 UserId,CityName,ProvinceName：

user_city_province.Select((user, city, province) => new { UserId = user.Id, CityName = city.Name, ProvinceName = province.Name }).Where(a => a.UserId == 1).ToList(); /* * SELECT [Users].[Id] AS [UserId],[City].[Name] AS [CityName],[Province].[Name] AS [ProvinceName] FROM [Users] AS [Users] INNER JOIN [City] AS [City] ON [Users].[CityId] = [City].[Id] INNER JOIN [Province] AS [Province] ON [City].[ProvinceId] = [Province].[Id] WHERE [Users].[Id] = 1 */

调用 Select 方法返回一个包含所有信息的 IQuery<T> 对象：

var view = user_city_province.Select((user, city, province) => new { User = user, City = city, Province = province });

查出一个用户及其隶属的城市和省份：

view.Where(a => a.User.Id == 1).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],[City].[Id] AS [Id0],[City].[Name] AS [Name0],[City].[ProvinceId] AS [ProvinceId],[Province].[Id] AS [Id1],[Province].[Name] AS [Name1] FROM [Users] AS [Users] INNER JOIN [City] AS [City] ON [Users].[CityId] = [City].[Id] INNER JOIN [Province] AS [Province] ON [City].[ProvinceId] = [Province].[Id] WHERE [Users].[Id] = 1 */

这时候也可以选取指定的字段：

view.Where(a => a.User.Id == 1).Select(a => new { UserId = a.User.Id, CityName = a.City.Name, ProvinceName = a.Province.Name }).ToList(); /* * SELECT [Users].[Id] AS [UserId],[City].[Name] AS [CityName],[Province].[Name] AS [ProvinceName] FROM [Users] AS [Users] INNER JOIN [City] AS [City] ON [Users].[CityId] = [City].[Id] INNER JOIN [Province] AS [Province] ON [City].[ProvinceId] = [Province].[Id] WHERE [Users].[Id] = 1 */

Chloe 也支持 Left Join、Right Join、Full Join连接，用法和 Inner Join 一样，就不一一介绍了。

IQuery<User> q = context.Query<User>(); q.Select(a => DbFunctions.Count()).First(); /* * SELECT TOP (1) COUNT(1) AS [C] FROM [Users] AS [Users] */ q.Select(a => new { Count = DbFunctions.Count(), LongCount = DbFunctions.LongCount(), Sum = DbFunctions.Sum(a.Age), Max = DbFunctions.Max(a.Age), Min = DbFunctions.Min(a.Age), Average = DbFunctions.Average(a.Age) }).First(); /* * SELECT TOP (1) COUNT(1) AS [Count],COUNT_BIG(1) AS [LongCount],SUM([Users].[Age]) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Average] FROM [Users] AS [Users] */ var count = q.Count(); /* * SELECT COUNT(1) AS [C] FROM [Users] AS [Users] */ var longCount = q.LongCount(); /* * SELECT COUNT_BIG(1) AS [C] FROM [Users] AS [Users] */ var sum = q.Sum(a => a.Age); /* * SELECT SUM([Users].[Age]) AS [C] FROM [Users] AS [Users] */ var max = q.Max(a => a.Age); /* * SELECT MAX([Users].[Age]) AS [C] FROM [Users] AS [Users] */ var min = q.Min(a => a.Age); /* * SELECT MIN([Users].[Age]) AS [C] FROM [Users] AS [Users] */ var avg = q.Average(a => a.Age); /* * SELECT CAST(AVG([Users].[Age]) AS FLOAT) AS [C] FROM [Users] AS [Users] */