Tough Linq Query

Question

I have an IEnumerable of invoices, these invoices have line items. These line items have a priority. I'm programming a variety of strategies to automatically apply cash against these line items and one is giving me some trouble. My pattern has been to prepare a linq statement to order the line items of the invoices then iterate over the linq query applying cash in order until I run out.

An example of this linq statement for the simplest strategy, pay each line item by priority and due date, is shown below:

from lineItem in invoices.SelectMany(invoice => invoice.LineItems)
orderby lineItem.Priority, lineItem.DueDate
select lineItem;

One of the strategies is to apply cash to the oldest remaining item with a given priority, in priority order, then move to the next oldest of each priority.

EDIT: Example of how one might start the query I'm asking for -

from lineItem in invoices.SelectMany(invoice => invoice.LineItems)
group lineItem by lineItem.Priority into priorities
orderby priorities.Key
select priorities.OrderBy(item => item.DueDate);

We now have "buckets" of line items with the same priority, ordered by due date within the bucket. I need to extract the first line item from each bucket, followed by the second, etc. until I have ordered all of the items. I would like to perform this ordering purely in linq.

Can anyone think of a way to express this entirely in linq?

Answer 1

LINQ = Language Integrated QUERY not Language Integrated PROCEDURAL CODE.

If you want a query that returns the line items you need to apply the payment to, then that's do-able (see .Aggregate), but if you want to actually apply the money to the line items as you go, then a foreach loop is a fine construct to use.

See http://blogs.msdn.com/b/ericlippert/archive/2009/05/18/foreach-vs-foreach.aspx

Answer 2

I don't see how you'll get this down to a better query than what you have, perhaps nest from queries to automatically do the SelectMany.

var proposedPayments = new List<LineItem>();
decimal cashOnHand = ...;
var query = invoices.SelectMany(iv => iv.LineItems)
                    .GroupBy(li => li.Priority)
                    .SelectMany(gg =>
                         gg.OrderBy(li => li.DueDate)
                           .Select((li,idx) => Tuple.Create(idx, gg.Key, li)))
                    .OrderBy(tt => tt.Item1)
                    .ThenBy(tt => tt.Item2)
                    .Select(tt => tt.Item3);
foreach (var item in query)
{
    if (cashOnHand >= item.Cost)
    {
        proposedPayments.Add(item); 
        cashOnHand -= item.Cost;
    }

    if (cashOnHand == 0m) break;
}

Edit: updated to match the paragraph the author wanted. Selected as first of each priority.