Calculating difference between different columns in different rows

I have a table that records what happens to a vehicle during a visit. For each visit, there is are multiple rows to denote what has been done. The table looks like this

VisitID | ActionID | StartTime | EndTime
   0    |    0     | 1/1/2013  | 1/2/2013
   1    |    0     | 1/2/2013  | 1/4/2013
   1    |    1     | 1/4/2013  | 1/7/2013
   2    |    0     | 1/4/2013  | 1/5/2013
   2    |    1     | 1/5/2013  | 1/6/2013
   2    |    2     | 1/6/2013  | 1/7/2013

I wish to construct a LINQ query capable of getting the amount of time a visit took. TotalTime is calculated by first finding the first and last (lowest and highest) ActionID , then last.EndTime - first.StartTime . Expected results:

VisitID | TotalTime
   0    |   1
   1    |   5
   2    |   3

I can generate my expected results by doing

var first = db.Visits.Where(v => v.ActionID == 0)
var last = db.Visits.GroupBy(x => x.VisitID).Select(g => g.OrderByDescending(x => x.ActionID).First())

first.Join(last, f => f.VisitID, l => l.VisitID, (f, l) new{ VisitID = Key, TotalTime = l.EndTime - f.StartTime});

I really don't like the hack I used to get the last ActionID, and I would really like to be able to do this within 1 LINQ statement. What do I need to do to achieve this?

---

I think this should work...

var result = db.Visits.GroupBy(v => v.VisitID)
            .Select(g => new
                    {
                        VisitId = g.Key,
                        TotalTime = g.Max(v => v.EndTime).Subtract(g.Min(v => v.StartTime)).Days
                    });

Edit: This assumes the actionid doesn't matter so much as the max and min start dates. Here is a different solution where the actionid's are ordered and the first and last Visits are used to calculate the time difference.

var result2 = db.Visits.GroupBy(v => v.VisitID)
            .Select(g => new
                    {
                        VisitId = g.Key,
                        TotalTime =
                    g.OrderBy(v => v.ActionID).Last().EndTime.Subtract(g.OrderBy(v => v.ActionID).First().StartTime).Days
                    });

---

db.Visits.GroupBy(v => v.VisitID)
    .Select(g => new { VisitId = g.Key, 
            Days = g.Select(x => x.EndTime.ToOADate()).Sum()
            -g.Select(x => x.StartTime.ToOADate()).Sum() });

使用一些小技巧为每次访问添加所有开始日期和结束日期，并获得差异。

链接地址: http://www.djcxy.com/p/69770.html

上一篇: 如何计算没有网络延迟的HTTP请求处理时间？

下一篇: 计算不同行中不同列之间的差异