LINQ Select entries X Minutes

Question

I have a simple table that keeps track of the input date. I would like to select records located in minutes.

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

From the above example, I would like the query to return elements that are at least X minutes long, say 30 minutes. therefore, from the list above 2227.jpg, 2229.jpg and 2231.jpg will only be returned.

This is what I still have that simply returns the latest images, however I need the latter, but they are separated by at least 30 minutes between recordings.

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

Answer 1

This is a quick attempt to achieve exactly what you requested, 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- ?

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

Answer 2

SelectMany :

using (var db = new GibFrontierEntities())
{
    var images = db.CCTV_IMAGES;
    var result = images
        .SelectMany(i => images,
            (first, second) => new { First = first, Second = second })
        .Where(i => i.First != i.Second)
        .Where(i => Math.Abs(
            EntityFunctions
                .DiffMinutes(i.First.ImageDate, i.Second.ImageDate)) >= 30)
        .Select(i => i.First)
        .Distinct()
        .OrderByDescending(i => i.ImageDate)
        .Take(rows)
        .ToList();
    return result;
}

Answer 3

As already mentioned, this could be easily achieved through iteration. However, you really need to deal with the LINQ expression, here's a quick and dirty pattern that will return dates that are 30 minutes away:

List<DateTime> dateLlist = new List<DateTime>();

        dateLlist.Add(new DateTime(2014, 1, 1, 1, 0, 0, 0));
        dateLlist.Add(new DateTime(2014, 1, 1, 1, 10, 0, 0));
        dateLlist.Add(new DateTime(2014, 1, 1, 1, 45, 0, 0));

        DateTime previousTime = new DateTime();
        bool shouldAdd = false;
        List<DateTime> newList = dateLlist.Where(x =>
        {
            shouldAdd = (previousTime == DateTime.MinValue || previousTime.AddMinutes(30) < x);
            previousTime = x;
            return shouldAdd;
        }).ToList();