C# - LINQ Group By Query Help

Hello all,

Need some help on this.   I have a list that looks like this:

List<InvoiceCalc> this has the following properties:
- InvoiceID
- PeriodStartDate
- PeriodEndDate
- CalcAmount
- has other fields not listed here...

I think have another list as follows:
List<TotalInvoiceCalc> this has the following properties:
- PeriodStartDate
- PeriodEndDate
- TotalCalcAmount

What I need to do is I need to GroupBy the following:
- PeriodStartDate.Month
- PeriodStartDate.Year
- PeriodEndDate.Month
- PeriodEndDate.Year
- Sum (CalcAmount)

Essentially I need to ignore the day of the dates.

Then I need to insert into the List<TotalInvoiceCalc> the 1st Day of the PeriodStartDate Month and Year and the Last Day of the PeriodEndDate Month and Year.  

i.e. int days = DateTime.DaysInMonth(PeriodEndDate.Year, PeriodEndDate.Month);
Then put together the PeriodEndDate with the Month, days and Year.

Hope this makes sense.

Thanks all for any help
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis ParaskevopoulosCommented:

Here is a sample application for you:

void Main()
	//Test Data
	var list = new List<InvoiceCalc>()
		new InvoiceCalc(){InvoiceID=1,PeriodStartDate=new DateTime(2015,1,2),PeriodEndDate=new DateTime(2015,1,15),CalcAmount=100.50},
		new InvoiceCalc(){InvoiceID=2,PeriodStartDate=new DateTime(2015,1,5),PeriodEndDate=new DateTime(2015,2,15),CalcAmount=200.50},
		new InvoiceCalc(){InvoiceID=3,PeriodStartDate=new DateTime(2015,1,8),PeriodEndDate=new DateTime(2015,1,15),CalcAmount=110.50},
		new InvoiceCalc(){InvoiceID=4,PeriodStartDate=new DateTime(2015,2,2),PeriodEndDate=new DateTime(2015,2,15),CalcAmount=140.50},
		new InvoiceCalc(){InvoiceID=5,PeriodStartDate=new DateTime(2015,1,2),PeriodEndDate=new DateTime(2015,3,15),CalcAmount=165.50},
		new InvoiceCalc(){InvoiceID=6,PeriodStartDate=new DateTime(2015,1,2),PeriodEndDate=new DateTime(2015,3,15),CalcAmount=123.90},
		new InvoiceCalc(){InvoiceID=7,PeriodStartDate=new DateTime(2015,1,2),PeriodEndDate=new DateTime(2015,3,15),CalcAmount=2300.50},
		new InvoiceCalc(){InvoiceID=8,PeriodStartDate=new DateTime(2015,1,1),PeriodEndDate=new DateTime(2015,4,30),CalcAmount=1700.50},
	var results = list.GroupBy
		x=> new
			Start = x.PeriodStartDate.AddDays(-x.PeriodStartDate.Day+1).Date, // This is the first day of the start month
			End = x.PeriodEndDate.AddMonths(1).AddDays(-x.PeriodEndDate.Day).Date // This is the last day of the end month
		//x.Key contains an object based on which we have Grouped our data.
		x=>new TotalInvoiceCalc
			PeriodStartDate = x.Key.Start,
			PeriodEndDate = x.Key.End,
			TotalCalcAmount = x.Sum (s => s.CalcAmount)
	foreach (var item in results)
		Console.WriteLine("Period Start : {0}\tPeriod End : {1}\tPeriod Total : {2}",item.PeriodStartDate.ToShortDateString(),item.PeriodEndDate.ToShortDateString(),item.TotalCalcAmount);

public class InvoiceCalc
	public int  InvoiceID {get;set;}
	public DateTime   PeriodStartDate {get;set;}
	public DateTime   PeriodEndDate {get;set;}
	public double   CalcAmount {get;set;}
public class TotalInvoiceCalc
	public DateTime   PeriodStartDate {get;set;}
	public DateTime   PeriodEndDate {get;set;}
	public double   TotalCalcAmount {get;set;}

Open in new window

If you need any clarification let me know,


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sbornstein2Author Commented:
Thanks so much huge help for me.   Thank you
Ioannis ParaskevopoulosCommented:
Anytime. Glad i could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.