Linq to SQL: Get number of consecutive daily views

Question

I have a table that records when a user views a page. The table looks something like this:

ID | Date
1  | 01/01/10 00:00:01.000
2  | 03/01/10 00:00:01.000
3  | 03/01/10 00:00:02.000
4  | 04/01/10 00:00:01.000
5  | 05/01/10 00:00:01.000
6  | 05/01/10 00:00:02.000
7  | 05/01/10 00:00:03.000

Using Linq-to-SQL I want to count the last group of consecutive days a page was viewed, but of course I'm not sure how I would go about doing this. For example, running the query on the data above should return 3.

Any help would be muchly appreciated :)

Answer 1

This is the test data. I added 6th Oct at the end of list to make sure the result only pick up last consecutive date set.

List<DateTime> viewDates = new List<DateTime>();
viewDates.Add(new DateTime(10, 10, 1));
viewDates.Add(new DateTime(10, 10, 3));
viewDates.Add(new DateTime(10, 10, 3));
viewDates.Add(new DateTime(10, 10, 4));
viewDates.Add(new DateTime(10, 10, 5));
viewDates.Add(new DateTime(10, 10, 5));
viewDates.Add(new DateTime(10, 10, 5));
viewDates.Add(new DateTime(10, 10, 6));

First, we need to find out the last consecutive date:

var lastConsecutiveDate = viewDates
                .Distinct()
                .Reverse()
                .SkipWhile(distinctDate => viewDates.Where(viewDate => (viewDate.Date == distinctDate.Date)).Count() < 2).FirstOrDefault();

We have 2 choices here, to use foreach or LINQ? foreach is more readable compared to LINQ. I will show both implementations.

This is the implementation for foreach:

var consecutiveDates = new List<DateTime>();
foreach (var item in viewDates.Where(viewDate => viewDate <= lastConsecutiveDate).Distinct().OrderByDescending(a => a.Date))
{

    //break if datediff is not 1
    int count = consecutiveDates.Count;
    if ((count > 0) && (consecutiveDates[count - 1].Date.AddDays(-1) != item.Date))
    {
        break;
    }

    consecutiveDates.Add(item);
}

This is the implementation for LINQ, less readable:

var consecutiveDates = viewDates
    .Where(viewDate => viewDate <= lastConsecutiveDate)
    .Distinct()
    .OrderByDescending(viewDate => viewDate.Date)
    .Aggregate
    (
        new { List = new List<DateTime>() },
        (result, viewDate) =>
        {
            int count = result.List.Count;
            if ((count > 0) && (result.List[count - 1].Date.AddDays(-1) != viewDate.Date))
            {
                return new { result.List };
            }

            result.List.Add(viewDate);
            return new { result.List };
        },
        a => a.List
    );

Console.WriteLine(consecutiveDates.Count()); will print out "3". I am expecting more refactoring on my code, but I am off for break.