How do I get total Qty using one linq query?

Question

I have two linq queries, one to get confirmedQty and another one is to get unconfirmedQty.

There is a condition for getting unconfirmedQty. It should be average instead of sum.

result = Sum(confirmedQty) + Avg(unconfirmedQty)

Is there any way to just write one query and get the desired result instead of writing two separate queries?

My Code

class Program
{
    static void Main(string[] args)
    {
        List<Item> items = new List<Item>(new Item[]
        {
            new Item{ Qty = 100, IsConfirmed=true },
            new Item{ Qty = 40, IsConfirmed=false },
            new Item{ Qty = 40, IsConfirmed=false },
            new Item{ Qty = 40, IsConfirmed=false },
        });

        int confirmedQty = Convert.ToInt32(items.Where(o => o.IsConfirmed == true).Sum(u => u.Qty));
        int unconfirmedQty = Convert.ToInt32(items.Where(o => o.IsConfirmed != true).Average(u => u.Qty));

        //Output => Total : 140
        Console.WriteLine("Total : " + (confirmedQty + unconfirmedQty));

        Console.Read();
    }

    public class Item
    {
        public int Qty { get; set; }
        public bool IsConfirmed { get; set; }
    }
}

Sergey Berezovskiy · Accepted Answer · 2013-12-29T21:08:24.247

Actually accepted answer enumerates your items collection 2N + 1 times and it adds unnecessary complexity to your original solution. If I'd met this piece of code

(from t in items
 let confirmedQty = items.Where(o => o.IsConfirmed == true).Sum(u => u.Qty)
 let unconfirmedQty = items.Where(o => o.IsConfirmed != true).Average(u => u.Qty)
 let total = confirmedQty + unconfirmedQty
 select new { tl = total }).FirstOrDefault();

it would take some time to understand what type of data you are projecting items to. Yes, this query is a strange projection. It creates SelectIterator to project each item of sequence, then it create some range variables, which involves iterating items twice, and finally it selects first projected item. Basically you have wrapped your original queries into additional useless query:

items.Select(i => {
    var confirmedQty = items.Where(o => o.IsConfirmed).Sum(u => u.Qty);
    var unconfirmedQty = items.Where(o => !o.IsConfirmed).Average(u => u.Qty);
    var total = confirmedQty + unconfirmedQty;
    return new { tl = total };
}).FirstOrDefault();

Intent is hidden deeply in code and you still have same two nested queries. What you can do here? You can simplify your two queries, make them more readable and show your intent clearly:

int confirmedTotal = items.Where(i => i.IsConfirmed).Sum(i => i.Qty);
// NOTE: Average will throw exception if there is no unconfirmed items!
double unconfirmedAverage = items.Where(i => !i.IsConfirmed).Average(i => i.Qty);
int total = confirmedTotal + (int)unconfirmedAverage;

If performance is more important than readability, then you can calculate total in single query (moved to extension method for readability):

public static int Total(this IEnumerable<Item> items)
{
    int confirmedTotal = 0;
    int unconfirmedTotal = 0;
    int unconfirmedCount = 0;

    foreach (var item in items)
    {
        if (item.IsConfirmed)
        {
            confirmedTotal += item.Qty;
        }
        else
        {
            unconfirmedCount++;
            unconfirmedTotal += item.Qty;
        }
    }

    if (unconfirmedCount == 0)
        return confirmedTotal;
    // NOTE: Will not throw if there is no unconfirmed items
    return confirmedTotal + unconfirmedTotal / unconfirmedCount;
}

Usage is simple:

items.Total();

BTW Second solution from accepted answer is not correct. It's just a coincidence that it returns correct value, because you have all unconfirmed items with equal Qty. This solution calculates sum instead of average. Solution with grouping will look like:

var total = 
    items.GroupBy(i => i.IsConfirmed)
         .Select(g => g.Key ? g.Sum(i => i.Qty) : (int)g.Average(i => i.Qty))
         .Sum();

Here you have grouping items into two groups - confirmed and unconfirmed. Then you calculate either sum or average based on group key, and summary of two group values. This also neither readable nor efficient solution, but it's correct.

How do I get total Qty using one linq query?

1 Answers1