LINQ-To-SQL row number greater than

Question

If i have an object of type Photo and a Result set which is sorted in a particular order, is there a way for me to get the position of the current Photo object in the result set. and then get all objects that would follow it?

Answer 1

Not sure if I understand correctly but this might help:

var result = photos.Skip(100); // 100 would be the position of current object.

// if you don't know the index of the current object:
// This won't work on LINQ to SQL directly, do it on a list or something.
var result = photos.SkipWhile(x => x != currentObject).Skip(1);

In reality, if you are dealing with a database, there should be some identifier (a set of columns) you are sorting by. If you want to do the whole thing on the server side, you can grab the properties of the current object and filter the result set specifically for objects that would come after that in the sort order you want.

Answer 2

Index of the photo:

result.IndexOf(photo);

Items after it:

result.SkipWhile((q, i) => i <= result.IndexOf(photo));

Answer 3

If you're sorting against an Id:

// gets the previous photo, or null if none:
var previousPhoto = db.Photos
    .Where(p => p.Id < currentPhotoId)
    .OrderByDescending(p => p.Id)
    .FirstOrDefault();

// gets the next photo, or null if none:
var nextPhoto = db.Photos
    .Where(p => p.Id > currentPhotoId)
    .OrderBy(p => p.Id)
    .FirstOrDefault();

If you have custom ordering, you'd need to replace the OrderBy/OrderByDescending expression with your custom ordering. You'd also need to use the same ordering criteria in Where() to get only those photos before or after the current photo.

Answer 4

You can do something like this (not terribly efficient):

var result =
  photos.Select((p, i) => new { Index = i, Photo = p })
  .SkipWhile(x => x.Photo != photo).Skip(1);

This will give you all photos following photo combined with their index in the original collection.