Choosing consecutive records with LINQ to Entities

Question

I have a database table with rows, each of which contains a sequential index. I want to select groups of rows that are sequentially based on 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 change). I would get the following groups:

 3, 4, 5 9, 10, 11 10, 11, 12 

Basically, I am trying to achieve something similar to the question asked here:

selection of sequential numbers using SQL query

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

Edit: Groups with more than the requested sequential elements need not be separated. that is, in the previous example, a result of 9, 10, 11, 12 would also be acceptable.

Answer 1

So, I think I came up with a pretty good solution, modeled after Brian’s answer in the topic I’m connected 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 number of consecutive lines you want. This gives you the first index of each group of consecutive lines. For the original example that I provided, you get:

 3 9 10 

Answer 2

I think this can work quite 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(); 

Answer 3

 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 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 the management studio, they generate the same execution plan and execute the same time for execution. I have only this limited data set that can be compared with the performance on your data set, if it is larger, and choose the best if they differ.

Answer 4

The following code will find each "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 find only the first element in each group, so you either have to modify the query to remember others, or you could make a query based on the fact that you have roots and from those you know which indexes to get. I wish I could wrap it before I had to leave, but maybe that helps a bit.

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