实体框架Linq SelectMany with condition
本文关键字:with condition SelectMany Linq 框架 实体 | 更新日期: 2023-09-27 18:04:06
我一直在尝试一段时间找到一个合适的解决方案的linq查询我试图写。
在我的模型结构中,我有我的Item类,它包含一个PaymentRecords列表。我想要我的查询实现的是:
public class PaymentRecord
{
public int PaymentRecordId { get; set; }
[Required]
public double PaymentAmount { get; set; }
[Required]
public DateTime DateOfPayment { get; set; }
[Required]
public bool FinalPayment { get; set; }
[JsonIgnore]
public Item Item{ get; set; }
}
public class Item
{
public int ItemId { get; set; }
public List<PaymentRecord> PaymentRecords {get; set;}
...various other properties
}
选择所有Items,其中PaymentRecord列表匹配下面的条件(工作良好),或者PaymentRecord为空,例如Items类没有PaymentRecord。有办法做到这一点吗?
var result = m_context.Item
.SelectMany(
x => x.PaymentRecords.Where(p => (p.FinalPayment == true
&& p.DateOfPayment >= _revenueStatementRequest.StartDate
&& p.DateOfPayment <= _revenueStatementRequest.EndDate)
|| p.FinalPayment != true),
(x, p) => x
)
.ToList();
理想情况下,我想做下面的事情,但我还没有能够得到任何类似的工作:
var result = m_context.Item
.SelectMany(
x => x.PaymentRecords.Where(p => (p.FinalPayment == true
&& p.DateOfPayment >= _revenueStatementRequest.StartDate
&& p.DateOfPayment <= _revenueStatementRequest.EndDate)
|| p.FinalPayment != true)
|| x.PaymentRecords == null,
(x, p) => x
)
.ToList();
从给出的答案工作后,我有这个:
m_context.Item.Where(c => (!
c.PaymentRecords.Any(q => (q.FinalPayment &&
q.DateOfPayment >= _revenueStatementRequest.StartDate &&
q.DateOfPayment <= _revenueStatementRequest.EndDate)
|| q.FinalPayment != true
)
)
&& (c..Type == Booked || c.Type == Reserved)
&& (c.StartDate < _revenueStatementRequest.StartDate)
)
你可以不用SelectMany
List<Item> res = m_context.Items
.Where(c => !c.PaymentRecords
.Any(q => (q.FinalPayment &&
q.DateOfPayment >=_revenueStatementRequest.StartDate &&
q.DateOfPayment <= _revenueStatementRequest.EndDate)
|| !q.FinalPayment)
)
//**EDIT**
.Where(c => c.StartDate < _revenueStatementRequest.StartDate)
//this is comment out, so we can better test on the more complex part of the query
//.Where(c => c.Type == Booked || c.Type == Reserved)
.ToList();
这样就得到了List<Item>
而不是List<PaymentRecord>
选择所有Items,其中PaymentRecord列表与条件匹配下面(它工作得很好),或者PaymentRecord为空,例如Items类没有PaymentRecord。
var result = m_context.Item
.Where(x => !x.PaymentRecords.Any() ||
x.PaymentRecords.Any(p => (p.FinalPayment == true
&& p.DateOfPayment >= _revenueStatementRequest.StartDate
&& p.DateOfPayment <= _revenueStatementRequest.EndDate)
|| p.FinalPayment != true))
.ToList();
我看不出这里使用SelectMany
的任何理由。简单的Where
条件可以完成工作