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)?