Selecting Consecutive Entries with LINQ to Entities

Question

I have a database table with rows that each contain a sequential index. I want to select groups of rows that are consecutive based upon this index column. For example, if I had rows with the following index values:

1
3
4
5
7
9
10
11
12
15
16

and I wanted to select all groups with 3 consecutive indices (this number will vary). I would get the following groups:

3, 4, 5

9, 10, 11

10, 11, 12

Basically, I'm trying to achieve something similar to the question posed here:

selecting consecutive numbers using SQL query

However, I want to implement this with LINQ to Entities, not actual SQL. I would also prefer not to use stored procedures, and I don't want to do any sort of ToList/looping approach.

Edit: Groups with more than the requested consecutive elements don't necessarily need to be split apart. i.e. in the previous example, a result of 9, 10, 11, 12 would also be acceptable.

Answer 1

So I think I've come up with a pretty good solution modeled after Brian's answer in the topic I linked to.

var q = from a in query
        from b in query
        where a.Index < b.Index
        && b.Index < a.Index + 3
        group b by new { a.Index }
            into myGroup
            where myGroup.Count() + 1 == 3
            select myGroup.Key.Index;

Change 3 to the number of consecutive rows you want. This gives you the first index of every group of consecutive rows. Applied to the original example I provided, you would get:

3
9
10

Answer 2

I think this might work pretty efficiently (C# though):

int[] query = { 1, 3, 4, 5, 7, 9, 10, 11, 12, 15, 16 };
int count = 3;
List<List<int>> numbers = query
   .Where(p => query.Where(q => q >= p && q < p + count).Count() == count)
   .Select(p => Enumerable.Range(p, count).ToList())
   .ToList();