Entity Framework Linq SelectMany with the condition

Question

I tried for some time to find a suitable solution for the linq query that I am trying to write.

In my model structure, I have an Item class that contains a PaymentRecords list. I want my request to be reached:

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 } 

Select all items where the PaymentRecord list meets the condition below (which works fine) or where PaymentRecord is null, for example. the Items class does not have a PaymentRecord. Is there any way to do this?

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

Ideally, I would like to do something like below, but I could not get anything like work:

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

After working with the specified answer, I have the following:

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

Answer 1

You can do it without 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(); 

this way you get List<Item> instead of List<PaymentRecord>

Answer 2

Select all elements where the PaymentRecord list matches the condition below (which works fine) or where PaymentRecord is null, for example. The class member does not have a PaymentRecord account.

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

I see no reason to use SelectMany here. A simple Where clause can do the job.