ABP-多表连接查询

Bertha 。 2022-03-19 07:00 514阅读 0赞

## 前言：什么是多表连接查询 ##

## 一：编写多表查询语句--内连接 ##

对应SQL语句：

select \* from Person  join Telephone on Person.Id = Telephone.PersonId

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd3ZW5ncnVpNDA_size_16_color_FFFFFF_t_70][]

### 第一种方式：LINQ语法 ###

public List<Twotable> GetTwo()
            {
                var query = from a in   _entityRepository.GetAll()
                            join b in _TelRepository.GetAll()
                            on a.Id equals b.PersonId
                            select new Twotable{
                                Name=a.Name,
                                Telephone=b.PhoneNumber
                            };
                return query.ToList();
            }

### 第二种方式：Lambda语法 ###

public List<Twotable> GetTwo()
            {
                var query = _entityRepository.GetAll()
            .Join(
                    _TelRepository.GetAll(),
            top => top.Id,
            art => art.PersonId,
            (top, art) => new Twotable
            {
                Name = top.Name,
                Telephone = art.PhoneNumber
            });
                return query.ToList();
            }

### 二：编写多表连接查询--左右连接 ###

左连接的概念：

对应SQL语句：

select \* from Person  left join Telephone on Person.Id = Telephone.PersonId

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd3ZW5ncnVpNDA_size_16_color_FFFFFF_t_70 1][]

![20190218101927902.png][]

右连接的概念：

对应SQL语句：

select \* from Person  right join Telephone on Person.Id = Telephone.PersonId

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd3ZW5ncnVpNDA_size_16_color_FFFFFF_t_70 2][]

![20190218102002301.png][]

多表查询

左右连接，取决于 a b2个的位置

### 方法1：LINQ语法 ###

var leftOuterJoin = from a in _entityRepository.GetAll()
                           join b in _TelRepository.GetAll()
                            on a.Id equals b.PersonId
                            into cls from c in cls.DefaultIfEmpty()
                            select new Twotable
                            {
                                Name = a.Name,
                                Telephone = c.PhoneNumber
                            };

### 方法2：Lambda语法 ###

var leftOuterJoin = _entityRepository.GetAll()
          .GroupJoin(
                  _TelRepository.GetAll(),
          top => top.Id,
          art => art.PersonId,
          (top, art) => new Twotable
          {
              Id = top.Id,
              Name = top.Name,
              Telephone =  art.FirstOrDefault(x => x.PersonId == top.Id).PhoneNumber
    
          }).Select(o => o);

## 三：编写多表连接查询---全连接 ##

全连接的概念

对应SQL语句

select \* from Person full outer join Telephone on Person.Id = Telephone.PersonId

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd3ZW5ncnVpNDA_size_16_color_FFFFFF_t_70 3][]

![20190218102134841.png][]

使用全连接：（合并左右连接就可以了）

//右连接
                var rightOuterJoin = from b in _TelRepository.GetAll()
                            join a in _entityRepository.GetAll()
    
                              on b.PersonId equals a.Id
                              into cls
                            from c in cls.DefaultIfEmpty()
                            select new Twotable
                            {
                                Id = c.Id,
                                Name = c.Name,
                                Telephone = b.PhoneNumber
                            };
         //左连接
    
               var leftOuterJoin = from a in _entityRepository.GetAll()
                            join b in _TelRepository.GetAll()
                            on a.Id equals b.PersonId
                            into cls from c in cls.DefaultIfEmpty()
                            select new Twotable
                            {
                                Name = a.Name,
                                Telephone = c.PhoneNumber
                            };

var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);

或者

var fullOuterJoin = leftOuterJoin.Concat(rightOuterJoin);

### 番外：  ###

如果想 3表 或 4表 连接，那就和SQL语句一样在下面加 join就好了

var query = from b in _TelRepository.GetAll()
                            join a in _entityRepository.GetAll()
                              on b.PersonId equals a.Id
                              into cls
                            from c in cls.DefaultIfEmpty()
    
                              join d in _userRepository.GetAll()
                               on c.Id equals  d.Id
                                from e in cls.DefaultIfEmpty()
    
                            select new Twotable
                            {
                                Id = c.Id,
                                //Name = c.Name,
                                Name =e.Name,
                                Telephone = b.PhoneNumber
                            };

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd3ZW5ncnVpNDA_size_16_color_FFFFFF_t_70]: /images/20220319/27e9d817fce44aa3bf997b084e3899ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd3ZW5ncnVpNDA_size_16_color_FFFFFF_t_70 1]: /images/20220319/22d74abe49a54326b31586f5c1e7466a.png
[20190218101927902.png]: /images/20220319/ddacae0d0116412a92158a93d1254fe5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd3ZW5ncnVpNDA_size_16_color_FFFFFF_t_70 2]: /images/20220319/3f99168faded41dd8129df9da6df38a3.png
[20190218102002301.png]: /images/20220319/682c34290f0c4245a5d9b53ad4c552d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd3ZW5ncnVpNDA_size_16_color_FFFFFF_t_70 3]: /images/20220319/d98fcd5f662149c6914655327c74344b.png
[20190218102134841.png]: /images/20220319/fa03389d34af46ddae4d73e25fe46572.png