左连接子查询如何处理 返回

求助
5 113
public async Task<ISugarQueryable<vw_aa022_papers_image_show>> GetViewQuery(vw_aa022_papers_image_show_Select inModel)
        {
            var EnumPositionStatusUSQuery = await _bllTbAa000Enum.GetQuery(new tb_aa000_enum_Select
            {
                EnumNameList = new List<string> { "EnumPositionStatusUS" }
            });

            var queryTemp = DbAsync.Queryable<tb_aa013_papers_image, tb_aa001_user, tb_aa001_user, tb_aa001_user, tb_aa00e_us_locations>((a, b, c, d, e) =>
                         new JoinQueryInfos(JoinType.Left, a.StaffID == b.StaffID,
                                            JoinType.Left, a.CreateID == c.StaffID,
                                            JoinType.Left, a.UpdateID == d.StaffID,
                                            JoinType.Left, b.WorkPlace == e.LocationCode))
                
                .Select((a, b, c, d, e) => new vw_aa022_papers_image_show
                {
                    ImageID = a.ImageID,
                    StaffID = a.StaffID,
                    ImagePath = a.ImagePath,
                    CardType = a.CardType,
                    CreateID = a.CreateID,
                    CreaterTime = a.CreaterTime,
                    UpdateID = a.UpdateID,
                    UpdateTime = a.UpdateTime,
                    DateOfValidity = a.DateOfValidity,
                    CertificateExpiryDate = a.CertificateExpiryDate,
                    CountryCode = b.CountryCode,
                    BadgeID = b.BadgeID,
                    PaperEnabled = a.PaperEnabled,
                    NameENLong = b.NameENLong,
                    CreateName = b.UserName,
                    //CardTypeName = "",
                    UpdateName = d.UserName,
                    //PaperEnabledName="",
                    WorkPlace = b.WorkPlace,
                    WorkPlaceNameUS = e.CityName,
                    PositionStatusUS =      //f.Desc,
                    PositionStatus = b.PositionStatus
                })

                .MergeTable()

                .WhereIF(inModel.WorkPlaceExpand.IsAny(), p => inModel.WorkPlaceExpand.Contains(p.WorkPlace))

                ;
        }

如上所示 , 我想以  queryTemp 为左表  , 即将查询的 EnumPositionStatusUSQuery 为右表 , 查询字段放入 vw_aa022_papers_image_show 的 PositionStatusUS 的字段,查阅了文档很久 , 只发现一个inner join  , 并没有发现任何的相关的我这种的left join 想要的子查询的需求 . 


热忱回答5

  • 想到另外一种写法完成了以上需求 , 但是我还是想知道如何Left join 一个子查询 . 

    以下为新的实现方式

    var queryTemp = DbAsync.Queryable<tb_aa013_papers_image, tb_aa001_user, tb_aa001_user, tb_aa001_user, tb_aa00e_us_locations, tb_aa000_enum>((a, b, c, d, e, f) =>
                             new JoinQueryInfos(JoinType.Left, a.StaffID == b.StaffID,
                                                JoinType.Left, a.CreateID == c.StaffID,
                                                JoinType.Left, a.UpdateID == d.StaffID,
                                                JoinType.Left, b.WorkPlace == e.LocationCode,
                                                JoinType.Left, b.PositionStatus == f.Value && f.EnumName == "EnumPositionStatusUS"))
    
                    .Select((a, b, c, d, e, f) => new vw_aa022_papers_image_show
                    {
                        ImageID = a.ImageID,
                        StaffID = a.StaffID,
                        ImagePath = a.ImagePath,
                        CardType = a.CardType,
                        CreateID = a.CreateID,
                        CreaterTime = a.CreaterTime,
                        UpdateID = a.UpdateID,
                        UpdateTime = a.UpdateTime,
                        DateOfValidity = a.DateOfValidity,
                        CertificateExpiryDate = a.CertificateExpiryDate,
                        CountryCode = b.CountryCode,
                        BadgeID = b.BadgeID,
                        PaperEnabled = a.PaperEnabled,
                        NameENLong = b.NameENLong,
                        CreateName = b.UserName,
                        //CardTypeName = "",
                        UpdateName = d.UserName,
                        //PaperEnabledName="",
                        WorkPlace = b.WorkPlace,
                        WorkPlaceNameUS = e.CityName,
                        PositionStatusUS = f.Desc,   //f.Desc,
                        PositionStatus = b.PositionStatus
                    })
    
                    .MergeTable()
    
                    .WhereIF(inModel.WorkPlaceExpand.IsAny(), p => inModel.WorkPlaceExpand.Contains(p.WorkPlace))
    
                    ;

    以 b.PositionStatus == f.Value && f.EnumName == "EnumPositionStatusUS" 来实现

    希望生成的sql是我所想的那样 . 

    0 回复
  • 文档 子查询 连表子查询

    0 回复
  • @fate stay night:只有inner join的子查询 , 或者where 一样 in 语法的子查询 , 没有我说的这种的子查询 . 

    0 回复
  • @Aaron 傲:支持left子查询 看子查询3的最后几行字

    0 回复
  • @fate stay night:我滴个天 , 还真没有发现  , 要F12 查看重载 .  I 服了 YOU . 多谢啦 . 

    0 回复

版块

学习文档

新注册

发布达人

回贴达人