LINQ/EF - How to select range of data

Question

I have a set of employees (id, name, age) in the employee table. I want to select a range of employees which are just next & previous to the given ID.

So e.g if employee ids are {1, 4, 5, 6, 8, 10, 25, 26, 40} then given an id to search for of 10 and a range of 2. Then it should select the 2 items which come before and after 10.

Output should be {6, 8, 10, 25, 26}.

I want to make the fewest possible calls to the database (preferably only one call). I tried writing the LINQ query as follows

The problem is how would I get the value of start index in the following query.

 var v = (from x in employeeList
                 where x.ID == itemToSearch
                 select x).ToList().GetRange(index-2,4);

Answer 1

You can use this query:

var index = 3;
var range = 2;
var query = employeeList
    .Where(c=>c.ID <= index)
    .OrderByDescending(c=>c.ID)
    .Take(range + 1)
    .Union(
             employeeList
               .Where(c=>c.ID >= index)
               .OrderBy(c=>c.ID)
               .Take(range + 1)
         );

In EF this will produce something like this:

SELECT * 
FROM
    (SELECT TOP 2 *
     FROM employee
     WHERE ID <= 3
     ORDER BY ID DESC
     UNION
     SELECT TOP 2 *
     FROM employee
     WHERE ID >= 3
     ORDER BY ID) A

Answer 2

Try this code

    class Program
        {
            static void Main(string[] args)
            {
                int[] a = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
                var b = GetRange(a.AsEnumerable(), 2, 2);
                foreach (var i in b)
                {
                    Console.WriteLine(i);
                }
                Console.ReadLine();
            }

            private static List GetRange(IEnumerable intList, int current, int range)
            {
                List lst = new List();
                int newCurrent = current;
                for (int i = 0; i  intList, int current)
            {
                return intList.SkipWhile(i => !i.Equals(current)).Skip(1).First();
            }

            private static int GetPrevious(IEnumerable intList, int current)
            {
                return intList.TakeWhile(i => !i.Equals(current)).Last();
            }


        }

Answer 3

You can use following extension method to return range of items around first item matching some predicate:

public static IEnumerable<T> GetRange<T>(
    this IEnumerable<T> source, Func<T, bool> predicate, int range)
{
    int itemsToFetch = range * 2 + 1;
    Queue<T> queue = new Queue<T>(range + 1);
    bool itemFound = false;
    int itemsFetched = 0;

    using (var iterator = source.GetEnumerator())
    {
        while (iterator.MoveNext())
        {
            T current = iterator.Current;

            if (itemFound) // if item found, then just yielding all next items
            {
                yield return current; // we don't need queue anymore
                itemsFetched++;
            }
            else
            {
                if (predicate(current))
                {
                    itemFound = true;

                    while (queue.Any()) // yield all content of queue
                        yield return queue.Dequeue();

                    yield return current; // and item which matched predicate
                    itemsToFetch = range;
                }
                else
                {
                    queue.Enqueue(current);

                    if (queue.Count >= range)
                        queue.Dequeue();
                }
            }

            if (itemsFetched == itemsToFetch)
                break;
        }
    }
}

Usage is simple

var result = employeeList.GetRange(e => e.ID == 10, 2);

It uses queue to keep track of last items which was checked. When item matching predicate found, all queue content it yielded. Then we return next range count of items (if there is enough items in source).

For given ids {1, 4, 5, 6, 8, 10, 25, 26, 40} following data is returned:

itemToSearch | range | result
---------------------------------------------
    10           2     { 6, 8, 10, 25, 26 }
    40           2     { 25, 26, 40 }
     1           2     { 1, 4, 5 }
    10           0     { 10 }     

Answer 4

I don't think you can do this in 1 query, because there's no concept of "previous" or "next" record in SQL. It would all depend on how the results are ordered, anyway.

I'm thinking you can:

1. Get up to n records before and including the itemToSearch
2. Get up to n records after and including the itemToSearch
3. Get the union of (1) and (2).

Code example:

var before = (from x in employeeList
              where x.ID <= itemToSearch
              select x).Take(2);

var after = (from x in employeeList
             where x.ID >= itemToSearch
             select x).Take(2);

var result = before.Union(after);

Answer 5

var v = (from x in employeeList select x).ToList().GetRange( 
((int)((from d in employeeList select d.ID).ToList().FindIndex(e => e == itemToSearch))) - 2 , 5);

Answer 6

Here is the alternative solution:

 var itemIndex = employeeList.IndexOf(itemToSearch);
 var result = employeeList.Select((item, index) => {
                                       var diff = Math.Abs(index - itemIndex);
                                       if(diff <= 2) return item;
                                       else return Int32.MinValue;
                                       })
                           .Where(x => x != Int32.MinValue)
                           .ToList();

Answer 7

You can do it like this.

int key=10;
int range=2;

var v= employeeList.GetRange((employeeList.FindIndex(x => x == key) - range), (range + range + 1));

this works for me.