public HouseSearchResult Search(HouseSearchOptions options) { //先获得所有未被软删除的房源信息 using (MyDbContext ctx = new MyDbContext()) { BaseService <HouseEntity> bs = new BaseService <HouseEntity>(ctx); var items = bs.GetAll().Where(h => h.Communitie.Region.CityId == options.CityId && h.TypeId == options.TypeId); //regionId可为空,判断不为空再继续过滤 //拼接查询语句 //where有返回值的,一定要items = ....;拿到返回值之后重新赋值才有用 if (options.RegionId != null) { items = items.Where(t => t.Communitie.RegionId == options.RegionId); } if (options.StartMonthRent != null) { items = items.Where(t => t.MonthRent >= options.StartMonthRent); } if (options.EndMonthRent != null) { items = items.Where(t => t.MonthRent <= options.EndMonthRent); } if (!string.IsNullOrEmpty(options.Keywords)) { items = items.Where(t => t.Address.Contains(options.Keywords) || t.Description.Contains(options.Keywords) || t.Communitie.Name.Contains(options.Keywords) || t.Communitie.Location.Contains(options.Keywords) || t.Communitie.Traffic.Contains(options.Keywords)); } //取出总条数 //log4Net中查看数据库执行语句顺序,count是单独执行一条SQL语句 //不会把数据取到内存中 long totalcount = items.LongCount(); //避免延迟加载 items = items.Include(h => h.Attachments).Include(h => h.Communitie) .Include(nameof(HouseEntity.Communitie) + "." + nameof(CommunitieEntity.Region) + "." + nameof(RegionEntity.City)) .Include(nameof(HouseEntity.Communitie) + "." + nameof(CommunitieEntity.Region)) .Include(h => h.DecorateStatus) .Include(h => h.HousePics) .Include(h => h.RoomType) .Include(h => h.Status) .Include(h => h.Type).Include(h => h.Attachments); //查询出来之后排序 switch (options.OrderByType) { case HouseSearchOrderByType.AreaAsc: items = items.OrderBy(t => t.Area); break; case HouseSearchOrderByType.AreaDesc: items = items.OrderByDescending(t => t.Area); break; case HouseSearchOrderByType.CreateDateDesc: items = items.OrderByDescending(t => t.CreateDateTime); break; case HouseSearchOrderByType.MonthRentAsc: items = items.OrderBy(t => t.MonthRent); break; case HouseSearchOrderByType.MonthRentDesc: items = items.OrderByDescending(t => t.MonthRent); break; } //一定不要items.Where //而要items=items.Where(); //OrderBy要在Skip和Take之前 //给用户看的页码从1开始,程序中是从0开始 //分页 //假如pagesize=10 //当前页是第1页,跳过0条,取pagesize条 // 2 10 10 items = items.Skip((options.CurrentIndex - 1) * options.PageSize) .Take(options.PageSize); //返回结果,返回结果类型为HouseSearchResult //在这里遍历数据的时候才会真正查询 HouseSearchResult searchResult = new HouseSearchResult(); searchResult.totalCount = totalcount; List <HouseDTO> houses = new List <HouseDTO>(); foreach (var item in items) { houses.Add(ToDTO(item)); } searchResult.result = houses.ToArray(); return(searchResult); } }
public BaseService(MyDbContext ctx1) { this.ctx = ctx1; }