这很好用。但是我如何自定义添加过滤器的条件呢?input是用户输入的值
var resultList = dbContext.BuyerBill
.Include(x=>x.BuyerBillItems.Where(x=>x.Status == input.Status && x.BuildTime > input.BeginTime && x.BuildTime < input.EndTime))
.ToList();
我想要的方式:
var query = WhereIf(input.Status!=null,x=>x.Status == input.Status);
query = WhereIf(input.BeginTime!=null,x=>x.BuildTime > input.BeginTime);
query = WhereIf(input.EndTime!=null,x=>x.BuildTime > input.EndTime);
这是我的实体
public class BuyerBill
{
public BuyerBill()
{
BuyerBillItems = new List<BuyerBillItems>();
}
public int Id {get;set;}
public int BuyUserId {get;set;}
public int OrderId {get;set;}
public List<BuyerBillItems> BuyerBillItems { get; set; }
....
}
public class BuyerBillItems
{
public int Id {get;set;}
public int BuyerBillId {get;set;}
public decimal Fee {get;set;}
public int Status {get;set;}
public dateTime CreateTime {get;set;}
public BuyerBill BuyerBill {get;set;}
....
}
1、如果用户没有选择时间查询
Select * from BuyerBill as buy inner join BuyerBillItems As item On buy.Id=item.BuyerBillId
where item.Status=1
2、如果用户选择时间查询
Select * from BuyerBill as buy inner join BuyerBillItems as item on buy.Id=item.BuyerBillId
where item.Status=1 and item.BuildTime > '2022-7-19' and item.BuildTime < '2022-7-19'
如何使用efcore实现我描述的SQL条件?
主要是想根据条件过滤子实体,如果只有一个实体,我知道可以用Where()方法过滤,但是不知道子实体怎么用条件过滤
我用LinqKit解决了上面的问题,但现在我有了一个新的问题。
var predicatess = PredicateBuilder.New<BuyerBillItems>(true);
predicatess = predicatess.And(x => x.CreateTime > StringHelper.AddDateTime("2022-07-16"));
predicatess = predicatess.And(x => x.Status == 2);
//I'm dumb and this line of code seems redundant. But I don't know how to convert implicitly
var convertPredicate = (Expression<Func<BuyerBillItems, bool>>)predicatess;
var query = dbContext.BuyerBill.AsExpandable().Include(x => x.BuyerBillItems.Where(x => convertPredicate.Invoke(x)))
.Where(x => x.BuyerBillItems.Any(s => convertPredicate.Invoke(s)))
.Where(x => x.BuyUserId == 4);
//If you don't use Select, everything is normal
var result1 = query.ToList();
//BuyerBillItemsDto result is incorrect after using Select
var result2 = query.Select(x => new BuyerBillDto
{
Id = x.Id,
BuyUserId = x.BuyUserId,
OrderId = x.OrderId,
BuyerBillItemsDto = mapper.Map<List<BuyerBillItems>, List<BuyerBillItemsDto>>(x.BuyerBillItems)
}).ToList();
我必须使用select筛选列以避免性能损失
1条答案
按热度按时间3qpi33ja1#
目前您不能在
Include
主体中使用自己的扩展名。因此,请考虑按以下方式编写查询:根据更新的要求进行更新
对于完全自定义投影,不需要构建
Include
,因为Select
指定了需要从数据库中检索哪些数据。可以使用
WhereIf
扩展名重写查询: