.net core ef 连表查询

Information和TypeInfo连表查询

类似：

select st.Title1,si.* from [Star_Information] si left join Star_TypeInfo st on si.typeId2=st.id

先在EfCoreDbContext.cs配置

  protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            //TypeInfo 连表查询配置
            builder.Entity<Information>()
            .HasOne(si => si.TypeInfo)
            .WithMany()
            .HasForeignKey(si => si.TypeId2);// 跟TypeInfo表相关联的TypeId2
        }

在Information.cs实体类新增 public TypeInfo TypeInfo { get; set; } // 导航属性 

 

接口实现

  /// <summary>
        /// 获取信息列表
        /// </summary>
        /// <param name="channel">调用参数</param>
        /// <param name="pageSize">每页记录数。必须大于等于1</param>
        /// <param name="pageIndex">页码。首页从1开始，页码必须大于等于1</param>
        /// <param name="typeIds">分类Id集</param>
        /// <param name="typeInfoIds">多选分类Id集</param>
        /// <param name="auditIds">审核Id集</param>
        /// <param name="keywords">搜索关键词</param>
        /// <returns></returns>
        [HttpGet]
        [Authorize("Manage_View")]
        public async Task<MessageDto> List(string channel, int pageSize, int pageIndex, string typeIds = "", string typeInfoIds = "", string auditIds = "", string keywords = "")
        {
            var parameter = (InformationParameter)await _adminMenuService.GetParameter(channel, new InformationParameter());
            if (parameter.Channel == "")
            {
                return new MessageDto { Message = "信息不存在" };
            }
            var where = PredicateBuilder.True<Information>();
            where = where.And(w => w.Channel == parameter.Channel && w.WebSiteId == _authorityModel.WebSite.Id);
            if (!string.IsNullOrEmpty(typeIds))
            {
                var lastPathId = typeIds.Split(',').LastOrDefault();
                where = where.And(w => w.TypeIdPath.Contains(lastPathId));
            }
            if (!string.IsNullOrEmpty(typeInfoIds))
            {
                var lastPathId = typeInfoIds.Split(',').LastOrDefault();
                where = where.And(w => w.TypeIdStrPath.Contains(lastPathId));
            }
            if (!string.IsNullOrEmpty(auditIds) && StringHelper.IsNumber(auditIds.Split(',').LastOrDefault()))
            {
                var lastPathId = long.Parse(auditIds.Split(',').LastOrDefault());
                where = where.And(w => w.AuditStatus == lastPathId);
            }
            if (!string.IsNullOrEmpty(keywords))
            {
                //连表查询TypeInfo的标题
                where = where.And(w => w.TypeInfo.Title1.Contains(keywords));
            }

            //联表查询
            var infoList = await _informationService.GetListAsync(pageSize, pageIndex, where
                , order => order.OrderByDescending(o => o.IsTop).ThenBy(o => o.Sort.Length).ThenBy(o => o.Sort).ThenByDescending(o => o.ReleaseDate)
                 );


            //联表查询 这个可以直接查出已经关联的人才表
            //var infoList = await _informationService.GetListAsync(pageSize, pageIndex, where,
            //    order => order.OrderByDescending(o => o.IsTop).ThenBy(o => o.Sort.Length).ThenBy(o => o.Sort).ThenByDescending(o => o.ReleaseDate)
            //    , info => info.Include(i => i.TypeInfo));

            var totleCount = await _informationService.CountAsync(where);
            var listData = _mapper.Map<List<Information>, List<InformationDto>>(infoList);
            var typeInfoList = await _typeInfoService.GetListAsync(0, w => w.Channel == parameter.TypeChannel && w.WebSiteId == _authorityModel.WebSite.Id, order => order.OrderBy(o => o.Sort));
            foreach (var item in listData)
            {
                //如果有关联简介的id
                if (item.TypeId2!=0)
                {
                    var typeInfo = await _typeInfoService.GetAsync(item.TypeId2);

                    if (typeInfo!=null)
                    {                     
                        item.Type2Name= typeInfo.Title1;
                        item.Type2Img = typeInfo.PicURL1;
                    }

                }

                if (!string.IsNullOrEmpty(item.TypeIdPath))
                {
                    item.TypeIdPathName = new List<string>();
                    var itemTypeList = typeInfoList.Where(w => item.TypeIdPath.Contains(w.Id.ToString()));
                    foreach (var type in itemTypeList)
                    {
                        item.TypeIdPathName.Add(type.Title1);

                        if (item.Channel == "ScientificResearchPlatform")
                        {
                            string[] attrid = item.TypeIdPath.Split(',');

                            if (type.DisplayMode == 0)
                            {
                                if (item.TypeIdPath != "")
                                {
                                    if (attrid.Length > 1)
                                    { 
                                    item.Url = "http://103.236.254.221:20002/#/Platform/" + attrid[0] + "/" + attrid[1] + "/0/1";
                                    }
                                }
                            }
                            else if (type.DisplayMode == 1)
                            {
                                item.Url = "http://103.236.254.221:20002/#/NewsInfo/ScientificResearchPlatform/" + item.Id + "/" + attrid[0] + "";
                            }
                            else if (type.DisplayMode == 2)
                            {
                                item.Url = "";
                            }
                        }
                    }
                }


            }



            return new MessageDto { Success = true, Data = new { listData, totleCount } };

        }