Get the next record in EF where records are not ordered by ID

369 Views Asked by At

I need to write a query that will return a single record, and this record would be the next record from the record with provided ID.

var x = GetNextRecord(int recordId);

The problem is that data is not ordered by ID, but by some other criteria. Example of query:

SELECT * FROM SomeTable
WHERE conditions
ORDER BY column1, column2, column3

This query, for example, returns 25 records. Lets assume that in this resultset a record with ID=42 is 7th record. That would mean that we need to return the 8th record.

An equivalent Linq would be

list.OrderBy(x => x.a)
    .ThenBy(x => x.b)
    .ThenBy(x=> x.c)
    .SkipWhile(x => x.Id != id) // we skip records until we arrive to the record with given Id
    .Take(2) // we take the next two records (current and the next one)
    .LastOrDefault() // and finally we take the last one, which is our next record;

This query, however, does not work with linq to entities, so what would be an equivalent in L2E (or EntitySQL)?

0

There are 0 best solutions below