I have the following class structure :
public class PriceLog
{
public DateTime LogDateTime {get; set;}
public int Price {get; set;}
}
For a List< PriceLog > I want a Linq query to generate an output which is equivalent to the data represented as below:
LogDateTime | AVG(Price)
Jan 2012 | 2000
Feb 2012 | 3000
Simply : I want to compute the average price over each month of the year.
Note: LogDateTime property should be formatted as LogDateTime.ToString("MMM yyyy")
I have tried the following, but not sure whether it will generate the desired result:
var result = from priceLog in PriceLogList
group priceLog by priceLog.LogDateTime.ToString("MMM yyyy") into dateGroup
select new PriceLog { GoldPrice = (int)dateGroup.Average(p => p.GoldPrice), SilverPrice = (int)dateGroup.Average(p => p.SilverPrice)};
This will give you sequence of anonymous objects, containing date string and two properties with average price:
var query = from p in PriceLogList
group p by p.LogDateTime.ToString("MMM yyyy") into g
select new {
LogDate = g.Key,
AvgGoldPrice = (int)g.Average(x => x.GoldPrice),
AvgSilverPrice = (int)g.Average(x => x.SilverPrice)
};
If you need to get list of PriceLog objects:
var query = from p in PriceLogList
group p by p.LogDateTime.ToString("MMM yyyy") into g
select new PriceLog {
LogDateTime = DateTime.Parse(g.Key),
GoldPrice = (int)g.Average(x => x.GoldPrice),
SilverPrice = (int)g.Average(x => x.SilverPrice)
};
from p in PriceLog
group p by p.LogDateTime.ToString("MMM") into g
select new
{
LogDate = g.Key.ToString("MMM yyyy"),
GoldPrice = (int)dateGroup.Average(p => p.GoldPrice),
SilverPrice = (int)dateGroup.Average(p => p.SilverPrice)
}
var result = priceLog.GroupBy(s => s.LogDateTime.ToString("MMM yyyy")).Select(grp => new PriceLog() { LogDateTime = Convert.ToDateTime(grp.Key), Price = (int)grp.Average(p => p.Price) }).ToList();
I have converted it to int because my Price field was int and Average method return double .I hope this will help
You should try it like this:
var result =
from priceLog in PriceLogList
group priceLog by priceLog.LogDateTime.ToString("MMM yyyy") into dateGroup
select new {
LogDateTime = dateGroup.Key,
AvgPrice = dateGroup.Average(priceLog => priceLog.Price)
};
Source: Stackoverflow.com