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

求助

 5  113

悬赏：5 飞吻

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）

Aaron 傲 VIP0

1周前

想到另外一种写法完成了以上需求 , 但是我还是想知道如何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 回复

fate stay night VIP0
1周前

文档子查询连表子查询

0 回复
Aaron 傲 VIP0
1周前

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

0 回复
fate stay night VIP0
1周前

@Aaron 傲：支持left子查询看子查询3的最后几行字

0 回复
Aaron 傲 VIP0
1周前

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

0 回复