public static void JoinQuery()
{
//建立连接
var user_city_province = context.Query <User>()
.InnerJoin <City>((user, city) => user.CityId == city.Id)
.InnerJoin <Province>((user, city, province) => city.ProvinceId == province.Id);
//查出用户及其隶属的城市和省份的所有信息
var view = user_city_province.Select((user, city, province) => new { User = user, City = city, Province = province }).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
*/
//也可以只获取指定的字段信息：UserId,UserName,CityName,ProvinceName
user_city_province.Select((user, city, province) => new { UserId = user.Id, UserName = user.Name, CityName = city.Name, ProvinceName = province.Name }).Where(a => a.UserId > 1).ToList();
/*
* SELECT [Users].[Id] AS [UserId],[Users].[Name] AS [UserName],[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
*/
/* quick join and paging. */
context.JoinQuery <User, City>((user, city) => new object[]
{
JoinType.LeftJoin, user.CityId == city.Id
})
.Select((user, city) => new { User = user, City = city })
.Where(a => a.User.Id > -1)
.OrderByDesc(a => a.User.Age)
.TakePage(1, 20)
.ToList();
context.JoinQuery <User, City, Province>((user, city, province) => new object[]
{
JoinType.LeftJoin, user.CityId == city.Id, /* 表 User 和 City 进行Left连接 */
JoinType.LeftJoin, city.ProvinceId == province.Id /* 表 City 和 Province 进行Left连接 */
})
.Select((user, city, province) => new { User = user, City = city, Province = province }) /* 投影成匿名对象 */
.Where(a => a.User.Id > -1) /* 进行条件过滤 */
.OrderByDesc(a => a.User.Age) /* 排序 */
.TakePage(1, 20) /* 分页 */
.ToList();
ConsoleHelper.WriteLineAndReadKey();
}
