0

Still learning Linq here. So I've got a class that looks like this (comes from a db table):

class FundRaisingData { public double funds { get; set; } public DateTime date { get; set; } public string agentsGender { get; set; } }

What I need to do is transform this to list of anonymous objects that looks something like this (it will be transformed and returned as JSON later ). I know this is not an anonymous object, but it will give you some idea of what I'm trying to do:

class groupedFunds { public string gender { get; set; } public List<double> fundsRaised { get; set; } }

So I need to figure out how I can sum the funds for each year in the right order (2010-2014).

Eventually it should look like this in JSON:

ListOfGroupedFunds[ { "gender" : "Female", "fundsRaised" : [2000, 2500, 3000] }, { "gender" : "Male", "fundsRaised": [4300,2300,3100] } ];

So fundsRaised[0] would correspond to 2012, fundsRaised[1] would correspond to 2013, etc. but not actually say "2013" anywhere in the object.

I've read a ton of articles today on Linq and searched through similar StackOverflow articles but I still just can't quite figure it out. Any help in pointing me in the right direction would be awesome.

-- Edit 2 (changing code more closely match solution) --

I think the code by Mike worked well, but because I'm not sure how many years there will be in advance I've modified it slightly:

var results = data.GroupBy(g=> Gender = g.agentsGender) .Select(g=> new { gender = g.Key years = g.GroupBy(y=> y.Date.Year) .OrderBy(y=> y.Key) .Select(y=> y.Sum(z=> z.funds)) .ToArray() }) .ToArray();

Is there anything wrong about the above? It seems to work but I'm open to better solutions of course.

Improve this question
0

2 Answers 2

Reset to default
1

Querying for any number of years is just another sub GroupBy() in your gender group.

var results = data.GroupBy(g=> Gender = g.agentsGender) .Select(g=> new { gender = g.Key years = g.GroupBy(y=> y.Date.Year) .OrderBy(y=> y.Key) .Select(y=> y.Sum(y.funds)) .ToArray() }) .ToArray();
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

How are is the index of each year's data guaranteed?
You just sort on your group key with OrderBy() so the earliest years will be first.
What if one year has no data in the group?
Is that a requirement of the OP?
No it's not a requirement. I can (and will need to) pull that data out in a separate query. But it does in fact require that I pull the years out in order in a separate data structure.
|
0

Thins LINQ statement will Group By Gender, then get the sum of funds per year as a List.

var query = from d in data group d by d.agentsGender into g select new { gender = g.Key, fundsRaised = new List<double> { g.Where(f => f.date.Year == 2012).Sum(f => f.funds), g.Where(f => f.date.Year == 2013).Sum(f => f.funds), g.Where(f => f.date.Year == 2014).Sum(f => f.funds), }};
Improve this answer

3 Comments

Thanks Mike! What if I don't know how many years I'm going to have?
That gets way more complex, because year is inferred by position in the a fixed length array. If for example, your data had no data for year 2013, but did for 2012 & 2014, then a group by would return an only 2 elements. So basically in order to ensure a fixed array size, the years have to be specified in some way, because it can always be inferred from the data. If you don't specify the years in linq you'd have to do it somewhere. I hope that makes sense. If you could change your data structure to include year & sum, this wold be much simpler.
So you're saying create a data structure with year and sum in it already and then use a different operation to pull out the data that I'm looking for? I've been playing with it, and I think I may have found the solution I'm looking for thanks to your help. I'll post the code in the solution

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.