LINQ SUM data based on Array Results

Question

I'm stuck on this issue. I know it can be done nicely with LINQ (I don't want to use multiple foreach loops), but I simply cannot get it to work. Here is the problem:

I have two classes:

Class Invoice

public class Invoice
{
public int InvoiceID { get; set; }
public string Name { get; set; }
public DateTime DueDate { get; set; }

public Invoice(int ID, string sName, DateTime dt_Date)
{
    this.InvoiceID = ID;
    this.Name = sName;
    this.DueDate = dt_Date;
}
}

and Class Activity

public class Activity
{
    public int ActivityID { get; set; }
    public int InvoiceID { get; set; }
    public int Count { get; set; }
    public double Price { get; set; }

    public Activity(int ID, int InvoiceID, int iCount, double dPrice)
    {
        this.ActivityID = ID;
        this.InvoiceID = InvoiceID;
        this.Count = iCount;
        this.Price = dPrice;
    }
}

The logic is that Each Invoice contains multiple Activities. E.g. you have a bill from a shop (Invoice), which includes items such as bread, butter, milk (Activities).

What I want to do is that based on user selected Date, I want to return total amount paid (basically I want to perform SUM of all bills from specific Date period).

Here is what I have:

//user selected DateTime - for the sake of test we make it current day
DateTime selectedDate = DateTime.Now;

//Retrieve invocies that match user selected Date
var retrievedInvoices = invList
  .Where(n => n.DueDate.ToShortDateString() == selectedDate.ToShortDateString());

This is fine, as we have retrieved list of all Invoices based on desired Date. But now? I tried something as following:

//now make SUM of activities that match retrievedInvoices -> look at their 
//ID's and if match is found, then multiply price x count
double dResult = invActivity
    .Where(n => retrievedInvoices.Where(x=>x.InvoiceID == n.InvoiceID))
    .Sum(n => n.Price * n.Count);

But it is not working. I am not that proficient in LINQ so there might be more elegant way of doing it (maybe in one line of code) but I don't know.

Could you guys help me with this one, please?

EDIT:

Interesting thing to note also for others that might be looking at this thread: I have first tried to use List in order to retrieve my list of invoices that match specific time period (DateTime_From and DateTime_To); but it behaved strangely as sometimes it worker correctly, sometimes not (even though the code was correct). After I changed List <Invoice> retrievedInvoice to var retrievedInvoice it suddenly worked without a problem. I don't understand why this is so, but I will definitely be more aware next time of what type do I use. Thanks again folks!

Tip: the phrase "it is not working" does not fly here. Describe how it does not work to your expectations. — gunr2171
– gunr2171, Commented May 21, 2014 at 14:51
I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented May 21, 2014 at 14:51

Felipe Oriani · Accepted Answer · 2014-05-21 15:00:11Z

3

Your code looks fine, but try some changes like this:

use .Date to compare just Dates without Time (hours, minutes, etc..) and select just eh InvoiceID proeprty into a array

var retrievedInvoices = invList.Where(n => n.DueDate.Date == selectedDate.Date)
                               .Select(x => x.InvoiceID);

use .Contains() to check the condition which return a bool value.

double dResult = invActivity
    .Where(n => retrievedInvoices.Contains(n.InvoiceID))
    .Sum(n => n.Price * n.Count);

Some changes was suggested by Tuespetre user in comments bellow!

edited May 21, 2014 at 15:00

answered May 21, 2014 at 14:54

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tuespetre Over a year ago

Why not select all of the invoice IDs matching the criteria and use a contains instead of Any?

tuespetre Over a year ago

By calling ToArray(), the query is executed. If you leave off ToArray(), LINQ should be smart enough to put in a subquery (... WHERE InvoiceId IN (SELECT InvoiceId FROM Invoices WHERE ...))

Felipe Oriani Over a year ago

Nice, I know it works with NHibernate, is it works with Entity Framework?

tuespetre Over a year ago

Yes. For an example, see the accepted answer here: stackoverflow.com/questions/5443122/entity-framework-subquery They declare the subquery inline instead of a previous variable like you have (I prefer the previous variable over inline, easier to read) and when you think about how it is parsed, EF will see a contains and then determine whether it is IQueryable or not. If it is IQueryable, it can build a SQL subquery, but if not, then it has to autofill a whole bunch of parameters for each item in the array or list.

Collectives™ on Stack Overflow

LINQ SUM data based on Array Results

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related