public static void JoinQuery()
{
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);
//查出一个用户及其隶属的城市和省份的所有信息
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" "USERS" INNER JOIN "CITY" "CITY" ON "USERS"."CITYID" = "CITY"."ID" INNER JOIN "PROVINCE" "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" "USERS" INNER JOIN "CITY" "CITY" ON "USERS"."CITYID" = "CITY"."ID" INNER JOIN "PROVINCE" "PROVINCE" ON "CITY"."PROVINCEID" = "PROVINCE"."ID" WHERE "USERS"."ID" = 1
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void JoinQuery()
{
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);
//查出一个用户及其隶属的城市和省份的所有信息
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
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void JoinQuery()
{
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);
//查出一个用户及其隶属的城市和省份的所有信息
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
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void JTest()
{
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
// */
////假设已经有5个表建立了连接的对象为 jq_q1_q5
//IJoiningQuery<T1, T2, T3, T4, T5> jq_q1_q5 = null;
////jq_q1_q5 调用 Select 方法，返回一个包含 T1-T5 的 IQuery<T> 对象 view_q1_q5
//var view_q1_q5 = jq_q1_q5.Select((t1, t2, t3, t4, t5) => new { T1 = t1, T2 = t2, T3 = t3, T4 = t4, T5 = t5 });
////假设第6个表的 IQuery<T6> 对象为 q6
//IQuery<T6> q6 = null;
////这时，view_q1_q5 与 q6 建立连接，返回 IJoiningQuery 对象 jq
//var jq = view_q1_q5.InnerJoin(q6, (t1_t5, t6) => t1_t5.T5.XX == t6.XXX);
////然后我们调用 jq 的 Select 方法，返回一个包含 T1-T6 的 IQuery<T> 对象 q。
////q 又是一个 IQuery<T> 对象，泛型参数为包含 T1-T6 所有信息的匿名对象，拿到它，我们就可以为所欲为了。
//var q = jq.Select((t1_t5, t6) => new { T1 = t1_t5.T1, T2 = t1_t5.T2, T3 = t1_t5.T3, T4 = t1_t5.T4, T5 = t1_t5.T5, T6 = t6 });
////可以直接查出数据库中 T1-T6 的所有信息
//q.ToList();
////也可以选取 T1-T6 中我们想要的字段
//q.Select(a => new { a.T1.xx, a.T2.xx, a.T3.xx /*...*/}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static List <FreeRecordJoin> GetList(int freeId, int pageSize, int pageIndex, out int totalCount)
{
IQuery <FreeRecord> record = MySqlHelper.context.Query <FreeRecord>();
IJoiningQuery <FreeRecord, Users> record_user = record.InnerJoin <Users>((r, u) => r.UserID == u.ID);
IJoiningQuery <FreeRecord, Users, FreeProduct> record_user_product = record_user.InnerJoin <FreeProduct>((r, u, p) => r.FreeID == p.ID);
IQuery <FreeRecordJoin> frj = record_user_product.Select((r, u, p) => new FreeRecordJoin {
FR = r, U = u, FP = p
});
if (freeId > 0)
{
frj = frj.Where(rup => rup.FR.FreeID == freeId);
}
frj = frj.OrderBy(f => f.FR.AddedTime);
totalCount = frj.Count();
frj = frj.Skip((pageIndex - 1) * pageSize).Take(pageSize);
return(frj.ToList());
}
public static void JoinQuery()
{
IQuery <User> users = context.Query <User>();
//建立连接
IJoiningQuery <User, City> user_city = users.InnerJoin <City>((user, city) => user.CityId == city.Id);
IJoiningQuery <User, City, Province> user_city_province = user_city.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,
JoinType.LeftJoin, city.ProvinceId == province.Id
}).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();
}
public static void JoinQuery()
{
IQuery <User> users = context.Query <User>();
//建立连接
IJoiningQuery <User, City> user_city = users.InnerJoin <City>((user, city) => user.CityId == city.Id);
IJoiningQuery <User, City, Province> user_city_province = user_city.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" "USERS" INNER JOIN "CITY" "CITY" ON "USERS"."CITYID" = "CITY"."ID" INNER JOIN "PROVINCE" "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" "USERS" INNER JOIN "CITY" "CITY" ON "USERS"."CITYID" = "CITY"."ID" INNER JOIN "PROVINCE" "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,
JoinType.LeftJoin, city.ProvinceId == province.Id
}).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();
}
public static void JoinQuery()
{
IQuery <User> users = context.Query <User>();
//建立连接
IJoiningQuery <User, City> user_city = users.InnerJoin <City>((user, city) => user.CityId == city.Id);
IJoiningQuery <User, City, Province> user_city_province = user_city.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,
JoinType.LeftJoin, city.ProvinceId == province.Id
}).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();
}
