Linq Select Records X Minutes Apart

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

Solution 1:

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;
            }

            returnfalse;
        }).ToList();

Per comments and a clearer definition of the requirement:

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);

Solution 2:

You can use SelectMany to achieve what you want:

using (var db = newGibFrontierEntities())
{
    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;
}

Solution 3:

As mentioned already, this could be easily achievable through iteration. However, it you really have to deal with LINQ expression, here's a quick and dirty sample that will return dates that are 30 minutes apart:

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

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

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