I have a list of things that I sort alphabetically. I am able to load this list using the following code:

model.Posters = this.NoTrackSet<Presentation>()
                    .OrderBy(v => v.Title)
                    .AsNoTracking().ToList();

Now, in my UI, the user is allowed to click on one of these items to dig into the details of the object.

My task has been to add 'next' and 'previous' items on the details page, however I cannot figure out the SQL to find which item was previous, and which item was next.

I have considered loading the whole list twice and trying to figure out where I am and do an array forward / back by one, but it seems like there must be a more straightforward way of doing it. I need to get the prev and next item alphabetically by title.

To humor the SO gods, here is my current next / prev SQL code

Presentation prevPoster = this.NoTrackSet<Presentation>()
    .FirstOrDefault(x => x.PresentationTypeId == prs.PresentationTypeId                                                                                                          
                         && x.SessionId == prs.SessionId);

Presentation nextPoster = this.NoTrackSet<Presentation>()
    .FirstOrDefault(x => x.PresentationTypeId == prs.PresentationTypeId                                  
                         && x.SessionId == prs.SessionId);

It obviously doesn't work right.

CodePudding user response：

Something like this:

previous:

db.Set<Presentation>()
  .Where(x => x.Title < currentTitle)
  .OrderByDescending(x => x.Title)
  .FirstOrDefault()

next:

db.Set<Presentation>()
  .Where(x => x.Title > currentTitle)
  .OrderBy(x => x.Title)
  .FirstOrDefault()

CodePudding user response：

You can also do it with one querry

            var myList = db.Set<Presentation>().OrderBy(x => x.Title ).ToList();

            int index = myList.FindIndex(a => a.Title == currentTitle);

            var previous = index > 1 ? myList[index - 1] : null;

            var next = index < myList.Count() ? myList[index   1] : null;