Entity Framework Linq SelectMany with condition

Question

I have been trying for a while to find a suitable solution for a linq query I am trying to write.

In my model structure I have my Item class, which holds a list of PaymentRecords. What I want my query to achieve is:

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 matches the condition below (which works fine), or where PaymentRecord is null e.g. the Items class has no PaymentRecord. Is there a way in which 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 have not been able to get anything similar to 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 from the answer given I have this:

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

Solution 1

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

that way you get a List<Item> instead of List<PaymentRecord>

Solution 2

Select all Items, where the PaymentRecord list matches the condition below (which works fine), or where PaymentRecord is null e.g. the Items class has no 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();

I don't see any reason for using SelectMany here. Simple Where condition can do the work.

Comments

[Thewads]

with this method, it does not seem to return any Items where a PaymentRecord is set which matches the mentioned conditions e.g. I have an item with a paymentRecord with FinalPayment true, and it is not showing in the list

[Jens Kloster]

in your example - if FinalPayment is true, then the date conditions must also be true. Are the date conditions also true?

[Thewads]

yes. For some reason the query seems to only return 1 Item, instead of the 3 expected

[Jens Kloster]

hmm im 99% sure the query is right. I belive your data is not that you think it is. Could you post the data you belive should be returned by the query?

[Thewads]

please see my latest edit. it may possibly be my additional where clauses?

[Jens Kloster]

hmm it's getting very localized. my advice: add the rest of the condition in seperate Where's, and comment them out (like i did in my edided example) to see which one is cousing you trouble. Try to work on a small amount of data, so its clear what the answer should be.

[Thewads]

well removing all the additional clauses is still giving incorrect data back unfortunately

[Jens Kloster]

maybe we should change !c.PaymentRecords to c.PaymentRecords I'v might have flipped your condition wrong

[Jens Kloster]

let us continue this discussion in chat