EF Linq query comparing data from multiple rows

Question

I would like to create a Linq query that compares date from multiple rows in a single table. The table consists of data that polls a web-services for balance data for account. Unfortunately the polling interval is not a 100% deterministic which means there can be 0-1-more entries for each account per day.

For the application i would need this data to be reformatted in a certain formatted (see below under output). I included sample data and descriptions of the table.

Can anybody help me with a EF Linq query that will produce the required output?

table:

id          The account id
balance     The available credits in the account at the time of the measurement
create_date The datetime when the data was retrieved

Table name:Balances
Field: id (int)
Field: balance (bigint)
Field: create_date (datetime)

sample data:

id  balance create_date
3   40  2012-04-02 07:01:00.627 
1   55  2012-04-02 13:41:50.427
2   9   2012-04-02 03:41:50.727
1   40  2012-04-02 16:21:50.027
1   49  2012-04-02 16:55:50.127
1   74  2012-04-02 23:41:50.627
1   90  2012-04-02 23:44:50.427
3   3   2012-04-02 23:51:50.827

3   -10 2012-04-03 07:01:00.627
1   0   2012-04-03 13:41:50.427
2   999 2012-04-03 03:41:50.727
1   50  2012-04-03 15:21:50.027
1   49  2012-04-03 16:55:50.127
1   74  2012-04-03 23:41:50.627
2   -10 2012-04-03 07:41:50.727
1   100 2012-04-03 23:44:50.427
3   0   2012-04-03 23:51:50.827

expected output:

id The account id
date The data component which was used to produce the date in the row
balance_last_measurement The balance at the last measurement of the date
difference The difference in balance between the first- and last measurement of the date

• On 2012-04-02 id 2 only has 1 measurement which sets the difference value equal to the last(and only) measurement.

id      date            balance_last_measurement     difference
1       2012-04-02      90                           35
1       2012-04-03      100                          10
2       2012-04-02      9                            9
2       2012-04-03      -10                         -19
3       2012-04-02      3                           -37
3       2012-04-03      0                            37

update 2012-04-10 20:06

The answer from Raphaël Althaus is really good but i did make a small mistake in the original request. The difference field in the 'expected output' should be either:

1. the difference between the last measurement of the previous day and the last measurement of the day
2. if there is no previous day then first measurement of the day should be used and the last measurement

Is this possible at all? It seems to be quite complex?

Answer 1

Well, a probable not optimized solution, but just see if it seems to work.

First, we create a result class

public class BalanceResult
    {
        public int Id { get; set; }
        public DateTime CreationDate { get; set; }
        public IList<int> BalanceResults { get; set; }
        public int Difference { get; set; }

        public int LastBalanecResultOfDay {get { return BalanceResults.Last(); }}
        public bool HasManyResults {get { return BalanceResults != null && BalanceResults.Count > 1; }}
        public int DailyDifference { get { return HasManyResults ? BalanceResults.Last() - BalanceResults.First() : BalanceResults.First(); } }

    }

then we change a little bit our query

var query = db.Balances
                .GroupBy(m => new
                                  {
                                      id = m.Id,
                                      year = SqlFunctions.DatePart("mm", m.CreationDate),
                                      month = SqlFunctions.DatePart("dd", m.CreationDate),
                                      day = SqlFunctions.DatePart("yyyy", m.CreationDate)
                                  }).ToList()//enumerate there, this is what we need from db
                .Select(g => new BalanceResult
                                 {
                                     Id = g.Key.id,
                                     CreationDate = new DateTime(g.Key.year, g.Key.month, g.Key.day),
                                     BalanceResults = g.OrderBy(l => l.CreationDate).Select(l => l.BalanceValue).ToList()
                                 }).ToList(); 

and finally

foreach (var balanceResult in balanceResults.ToList())
            {
                var previousDayBalanceResult = balanceResults.FirstOrDefault(m => m.Id == balanceResult.Id && m.CreationDate == balanceResult.CreationDate.AddDays(-1));
                balanceResult.Difference = previousDayBalanceResult != null ? balanceResult.LastBalanecResultOfDay - previousDayBalanceResult.LastBalanecResultOfDay : balanceResult.DailyDifference;
            }

as indicated, performance (use of dictionaries, for example), code readability should of course be improved, but... that's the idea !

Answer 2

I would try something like that.

var query = db.Balances
                .OrderBy(m => m.Id)
                .ThenBy(m => m.CreationDate)
                .GroupBy(m => new
                                  {
                                      id = m.Id,
                                      year = SqlFunctions.DatePart("mm", m.CreationDate),
                                      month = SqlFunctions.DatePart("dd", m.CreationDate),
                                      day = SqlFunctions.DatePart("yyyy", m.CreationDate)
                                  }).ToList()//enumerate there, this is what we need from db
                .Select(g => new
                                 {
                                     id = g.Key.id,
                                     date = new DateTime(g.Key.year, g.Key.month, g.Key.day),
                                     last_balance = g.Select(m => m.BalanceValue).LastOrDefault(),
                                     difference = (g.Count() == 1 ? g.First().BalanceValue : g.Last().BalanceValue - g.First().BalanceValue)
                                 });