I've got a database table with rows that every have a consecutive index. I wish to choose categories of rows which are consecutive based on this index column. For instance, basically had rows using the following index values:

1
3
4
5
7
9
10
11
12
15
16

and that i desired to choose all groups with 3 consecutive indices (the dpi will be different). I'd obtain the following groups:

3, 4, 5

9, 10, 11

10, 11, 12

Essentially, I am attempting to achieve something such as the question posed here:

choosing consecutive amounts using SQL query

However, I wish to implement this with LINQ to Organizations, not actual SQL. I'd also prefer to not use saved methods, and I'd rather not inflict kind of ToList/looping approach.

Edit: Groups using more than the asked for consecutive elements don't always have to be split apart. i.e. in the earlier example, a direct result 9, 10, 11, 12 would be also acceptable.

And So I think I have develop an excellent solution patterned after Brian's answer within the subject I associated with.

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 amount of consecutive rows you would like. This provides the first index of each and every number of consecutive rows. Put on the initial example I provided, you can get:

3
9
10

I believe this may work pretty effectively (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();
using (var model = new AlbinTestEntities())
{
    var triples = from t1 in model.Numbers
                  from t2 in model.Numbers
                  from t3 in model.Numbers
                  where t1.Number + 1 == t2.Number
                  where t2.Number + 1 == t3.Number
                  select new
                  {
                      t1 = t1.Number,
                      t2 = t2.Number,
                      t3 = t3.Number,
                  };

    foreach (var res in triples)
    {
        Console.WriteLine(res.t1 + ", " + res.t2 + ", " + res.t3);
    }
}

It creates the next SQL

SELECT 
[Extent1].[Number] AS [Number], 
[Extent2].[Number] AS [Number1], 
[Extent3].[Number] AS [Number2]
FROM   [dbo].[Numbers] AS [Extent1]
CROSS JOIN [dbo].[Numbers] AS [Extent2]
CROSS JOIN [dbo].[Numbers] AS [Extent3]
WHERE (([Extent1].[Number] + 1) = [Extent2].[Number]) AND (([Extent2].[Number] + 1) = [Extent3].[Number])

It may be better still to make use of an inner join such as this

using (var model = new AlbinTestEntities())
{
    var triples = from t1 in model.Numbers
                  join t2 in model.Numbers on t1.Number + 1 equals t2.Number
                  join t3 in model.Numbers on t2.Number + 1 equals t3.Number
                  select new
                  {
                      t1 = t1.Number,
                      t2 = t2.Number,
                      t3 = t3.Number,
                  };

    foreach (var res in triples)
    {
        Console.WriteLine(res.t1 + ", " + res.t2 + ", " + res.t3);
    }
}

however when I compare the resulting queries in management studio they create the same execution plan and take the identical time for you to execute. I've only this limited dataset you may compare the performance in your dataset if it's bigger and pick the right when they differ.