Get next and previous entity of specific Id with Entity Framework?

Question

I have a employee form for editing or showing a personal information in my project with ASP.NET MVC5, this form get an Id and shows the employee info.

In this form I have to have Next and Previous button to allow admin for navigating to next or previous employee(if there isn't employee the button should be disable). In the other hand this form have a variable sorting that sets by Admin.

So I want to put two <a /> tags with next and previous employee Id that linked to employee form.

<< Previous employee Id by sorting             Next employee Id by sorting >>

I know how should I get current employee, It's pretty easy in Entity Framework.

But how can I get the next and previous Id of the current Id with Entity Framework?

Answer 1

If I understand correctly your question, the concept of "next" or "previous" is related to the rule you use to sort. Now, let me use the simple "order by ID" as the sorting rule. With 3 queries you can get the current, next and previous items:

int id = 562;  // the current id
var curr = (from x in dbCtx.Employees where x.ID == id select x).Single();
var prev = (from x in dbCtx.Employees where x.ID < id orderby x.ID descending select x).FirstOrDefault();
var next = (from x in dbCtx.Employees where x.ID > id orderby x.ID ascending select x).FirstOrDefault();

You can reduce from 3 to 2 queries. The first and the second can be mixed in a single query. This query return the current and the previous item:

(from x in dbCtx.Employees where x.ID <= id orderby x.ID descending select x).take(2)  

Answer 2

I came across this question while working on an editor for my blog. I basically wanted to be able to jump to the previous and next page quickly. Below is my implementation that seems to work nicely.

In my PageController I added two new ActionResults:

public ActionResult EditNext(int id)
{
    Page next= db.Pages.Where(x => x.Id > id).FirstOrDefault();
    return View("~/Areas/Admin/Views/Pages/Edit.cshtml", next);
}

public ActionResult EditPrev(int id)
{
    Page prev = db.Pages.Where(x => x.Id < id).OrderByDescending(y => y.Id).FirstOrDefault();
    return View("~/Areas/Admin/Views/Pages/Edit.cshtml", prev);
}

Then in my razor view I simply call them like this:

<div layout="row" layout-align="space-between center" ng-cloak> 
    <md-button class="md-primary md-btn-small" href="@Url.Action("EditPrev", "Pages", new { id = Model.Id })">Previous</md-button>
    <md-button class="md-primary md-btn-small" href="@Url.Action("EditNext", "Pages", new { id = Model.Id })">Next</md-button>
</div>