Selecting Consecutive Entries with LINQ to Entities

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 do开发者_如何学Gon't necessarily need to be split apart. i.e. in the previous example, a result of 9, 10, 11, 12 would also be acceptable.

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

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

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 generates the following 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 might be even better to use an inner join like 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);
    }
}

but when I compare the resulting queries in management studio they generate the same execution plan and take exactly the same time to execute. I have only this limited dataset you might compare the performance on your dataset if it is larger and pick the best if they differ.

The following code will find every "root".

    var query = this.commercialRepository.GetQuery();
    var count = 2;
    for (int i = 0; i < count; i++)
    {
        query = query.Join(query, outer => outer.Index + 1, inner => inner.Index, (outer, inner) => outer);
    }

    var dummy = query.ToList();

It will only find the first item in each group so you will either have to modify the query to remeber the other ones or you could make a query based on the fact that you have the roots and from those you know which indexes to get. I'm sorry I couldn't wrap it up before I had to go but maybe it helps a bit.

PS. if count is 2 as in this case it means if finds groups of 3.