多表连接查询 （二）

在上一篇文章中有提怎么创建框架，今天主要说一些复杂的查询，也是多表的查询
框架里的查询都是Linq和lambda操作。下面就来看一些代码

1.建立连接

var user_city_province = context.Query<User>()
                         .InnerJoin<City>((user, city) => user.CityId == city.Id)
                         .InnerJoin<Province>((user, city, province) => city.ProvinceId == province.Id);

 上面建立连接是一个三表连接查询，如需要更多的查询，在后继续添加，
 下边也是一种建立连接多表查询，但本人感觉很复杂

    IQuery<User> users = context.Query<User>();
    IQuery<Product> products = context.Query<Product>();
    IJoiningQuery<User, Product> user_product = users.InnerJoin(products, (user, product) => user.ID == product.c_admin_ID);

2. 查询信息

 返回所有的字段

//调用Tolist就返回new里面的list集合，返回所有的信息
var linked= user_city_province.Select((user, city, province) => new
{
    User = user,
    City = city,
    Province = province
});
var result = linked.Where(a => a).ToList();

----生成的SQL语句
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 
left join city as city  on Users.cityId = city.Id 
left join Province as  Province on city.ProvinceId = Province.Id
where Users.Id = 1

 如果不想返回所有字段，可以使用以下的方法

//返回的字段，都是指定的字段
var result1 = qq.Where(a => a.User.Id == 1)
                .Select(a => new { UserId = a.User.Id, UserName = a.User.Name, CityName = a.City.Name, ProvinceName = a.Province.Name })
                .ToList();

//可以获取指定的字段
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();

---生成的 sql语句 只会包含 所需的字段
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

本人后续还会更新这篇文章，敬请期待。。。。。。