public HouseSearchResult Search(HouseSearchOptions options) { using (ZSZDbContext ctx = new ZSZDbContext()) { BaseService <HouseEntity> bs = new BaseService <Entities.HouseEntity>(ctx); var items = bs.GetAll().Where(h => h.Community.Region.CityId == options.CityId && h.TypeId == options.TypeId); //指定过滤条件区域 if (options.RegionId != null) { items = items.Where(t => t.Community.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.Community.Name.Contains(options.Keywords) || t.Community.Location.Contains(options.Keywords) || t.Community.Traffic.Contains(options.Keywords)); } //获取总条数 long totalCount = items.LongCount(); items = items.Include(h => h.Community) .Include(nameof(HouseEntity.Community) + "." + nameof(CommunityEntity.Region) + "." + nameof(RegionEntity.City)) .Include(nameof(HouseEntity.Community) + "." + nameof(CommunityEntity.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.MonthRentDesc: items = items.OrderByDescending(t => t.MonthRent); break; case HouseSearchOrderByType.MonthRentAsc: items = items.OrderBy(t => t.MonthRent); break; case HouseSearchOrderByType.AreaDesc: items = items.OrderByDescending(t => t.Area); break; case HouseSearchOrderByType.AreaAsc: items = items.OrderBy(t => t.Area); break; case HouseSearchOrderByType.CreateDateDesc: items = items.OrderByDescending(t => t.CreateDateTime); break; default: items = items.OrderBy(t => t.CreateDateTime); break; } //分页,一定要先排序在分页,给用户看的页码从1开始,程序中从0开始 items = items.Skip((options.CurrentIndex - 1) * options.PageSize).Take(options.PageSize); HouseSearchResult searchResult = new HouseSearchResult(); searchResult.totalCount = totalCount; List <HouseDTO> dto = new List <HouseDTO>(); foreach (var item in items) { //把数据转存到DTO中 dto.Add(ToDTO(item)); } searchResult.result = dto.ToArray(); return(searchResult); } }
public HouseSearchResult Search(HouseSearchOptions options) { using (ZSZDbContext ctx = new ZSZDbContext()) { BaseService <HouseEntity> bs = new BaseService <HouseEntity>(ctx); var items = bs.GetAll().Where(p => p.Community.Region.CityId == options.CityId && p.TypeId == options.TypeId); if (options.RegionId != null) { items = items.Where(p => p.Community.RegionId == options.RegionId); } if (options.StartMonthRent != null) { items = items.Where(p => p.MonthRent >= options.StartMonthRent); } if (options.EndMonthRent != null) { items = items.Where(p => p.MonthRent <= options.EndMonthRent); } if (string.IsNullOrWhiteSpace(options.Keywords)) { items = items.Where(p => p.Address.Contains(options.Keywords) || p.Description.Contains(options.Keywords) || p.Community.Name.Contains(options.Keywords) || p.Community.Location.Contains(options.Keywords) || p.Community.Traffic.Contains(options.Keywords) ); } var totalCount = items.LongCount(); items = items.Include(h => h.Attachments).Include(h => h.Community) .Include(h => nameof(HouseEntity.Community) + "." + nameof(CommunityEntity.Region) + "." + nameof(RegionEntity.City)) .Include(h => nameof(HouseEntity.Community) + "." + nameof(CommunityEntity.Region)) .Include(h => h.DecorateStatus) .Include(h => h.HousePics) .Include(h => h.RoomType) .Include(h => h.Status) .Include(h => h.Type); switch (options.OrderByType) { case OrderByType.MonthRentDesc: items = items.OrderByDescending(p => p.MonthRent); break; case OrderByType.MonthRentAsc: items = items.OrderBy(p => p.MonthRent); break; case OrderByType.AreaDesc: items = items.OrderByDescending(p => p.Area); break; case OrderByType.AreaAsc: items = items.OrderBy(p => p.Area); break; case OrderByType.CreateDateDesc: items = items.OrderByDescending(p => p.CreateDateTime); break; default: break; } items = items.Skip((options.CurrentIndex - 1) * options.PageSize).Take(options.PageSize); 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 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); } }
/// <summary> /// 搜索,返回值包含:总条数和HouseDTO[](房子的数据)两个属性 /// </summary> /// <param name="options">搜索条件</param> /// <returns></returns> public HouseSearchResult Search(HouseSearchOptions options) { /* * using (ZSZDbContext ctx = new ZSZDbContext()) * { * BaseService<HouseEntity> bs = new BaseService<HouseEntity>(ctx); * var items = bs.GetAll().Where(h => h.Address.Contains("楼")); * long c = items.LongCount(); * items.Take(10).ToList(); * }*/ using (ZSZDbContext ctx = new ZSZDbContext()) { BaseService <HouseEntity> bs = new BaseService <HouseEntity>(ctx); //先获得所有的房子信息然后通过城市的id和房屋的类型过滤(因为城市id和房屋类型不会为空) var items = bs.GetAll().Where(h => h.Community.Region.CityId == options.CityId && h.TypeId == options.TypeId); if (options.RegionId != null)//区域是否为空 { items = items.Where(t => t.Community.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))//搜索关键词是否为空 { //contains被EF翻译为like操作 items = items.Where(t => t.Address.Contains(options.Keywords) || t.Description.Contains(options.Keywords) || t.Community.Name.Contains(options.Keywords) || t.Community.Location.Contains(options.Keywords) || t.Community.Traffic.Contains(options.Keywords)); } long totalCount = items.LongCount();//总搜索结果条数 items = items.Include(h => h.Attachments).Include(h => h.Community) .Include(nameof(HouseEntity.Community) + "." + nameof(CommunityEntity.Region) + "." + nameof(RegionEntity.City)) .Include(nameof(HouseEntity.Community) + "." + nameof(CommunityEntity.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开始,跳过(当前页码-1)*每页多少条,取options.PageSize条数据 items = items.Skip((options.CurrentIndex - 1) * options.PageSize) .Take(options.PageSize); HouseSearchResult searchResult = new HouseSearchResult(); searchResult.totalCount = totalCount;//总条数 List <HouseDTO> houses = new List <HouseDTO>(); //遍历取出来的房屋数据 foreach (var item in items) { //把房屋数据转换为dto houses.Add(ToDTO(item)); } searchResult.result = houses.ToArray(); return(searchResult); } }