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);
}
}