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.