LINQ Select记录相距X分钟 [英] LINQ Select Records X Minutes apart

问题描述

我有一个简单的表，可以跟踪输入日期.我想选择相隔X分钟的记录.

I have a simple table that keeps track of the entry date. I would like to select records that are X minutes apart.

IMAGE_LOCATION  IMAGE DATE
============== =============
2227.jpg    08/03/2014 22:27:47
2228.jpg    08/03/2014 22:28:48
2229.jpg    08/03/2014 22:59:49
2230.jpg    08/03/2014 23:12:50
2231.jpg    08/03/2014 23:29:49

从上面的示例中，我希望查询返回至少相隔X分钟的项目，比如说30分钟.因此从2227.jpg，2229.jpg和2231.jpg上方的列表中将仅返回.

From the sample above i would like the query to return items that are at least X minutes apart, lets say 30 min. so from the list above 2227.jpg, 2229.jpg and 2231.jpg would be returned only.

到目前为止，这是我只返回最新图像的内容，但是我需要最新图像，但记录之间至少要间隔30分钟.

This is what i have so far that just returns the latest images, however i need the latest ones but separated by at least 30 minutes between records.

using (var db = new GibFrontierEntities())
{
    var result = (from u in db.CCTV_IMAGES.OrderByDescending(u => u.ImageDate)
                  select u).Take(rows);
    return result.ToList();
}

推荐答案

这是实现LINQ解决方案(已在.NET 4中测试并正常工作)的一种快速尝试:

This is a quick attempt to achieve exactly what you asked for, a LINQ solution (tested and working in .NET 4):

var list = db.CCTV_IMAGES.OrderByDescending(u => u.ImageDate);
return list.Where((d, i) =>
        {
            //Look ahead to compare against the next if it exists.
            if (list.ElementAtOrDefault(i + 1) != null)
            {
                return d.ImageDate.Subtract(list.ElementAtOrDefault(i + 1).ImageDate).TotalMinutes > 30;
            }

            //Look behind to compare against the previous if this is the last item in the list.
            if (list.ElementAtOrDefault(i - 1) != null)
            {
                return list.ElementAtOrDefault(i - 1).ImageDate.Subtract(d.ImageDate).TotalMinutes > 30;
            }

            return false;
        }).ToList();

每个注释和更清晰的要求定义:

由于您在下面的评论中说，您每分钟会有1个项目，而您之前曾说过您需要将它们间隔至少 30分钟，那么您是否考虑简化每30个项目要抓取一次的逻辑列表中的项目?

Because you stated in the comments below that you will have 1 item a minute and you previously stated that you need them separated by at least 30 minutes, would you consider simplifying the logic to grab every 30th item from the list?

return list.Where((d, i) => i % 30 == 0);

这篇关于LINQ Select记录相距X分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！