CODEWITHSUNDEEP
c#asp.netlinqentity-framework
Mohammad Dayyan
Mohammad Dayyan

Reputation: 22408

Get the next and previous sql row by Id and Name, EF?

Assume we have the following data in a SQL Server table (sorted by name) :

Id   Name        LName
-------------------------
5   Abbas        Dayyan
3   Mohammad     KD
4   Nima         Ahmad
1   Omid         Zangene
2   Pedram       Ahmadi

we have an Id query string and we wanna get the next and previous row (if exists) from Id.

e.g :

the Id query string is 4, so we wanna get Mohammad KD as previous row and Omid Zangene as next row.

Could you please guide me how can do it with LINQ to Entity Framework.

Edit:
In practice the number of table rows is around 1 million.
Table rows didn't sort by Name by default, wa need to sort them by Name for the result.

Upvotes: 2

Views: 5785

Answers (2)

Risky Martin
Risky Martin

Reputation: 2521

How about this?

var result = (from person in db.People
              where person.Id == id
              let ordered = db.People.OrderBy(p => p.Name)                  
              let reversed = db.People.OrderByDescending(p => p.Name)                  
              let previous = reversed.SkipWhile(p => p.Id != id).Skip(1).FirstOrDefault()
              let next = ordered.SkipWhile(p => p.Id != id).Skip(1).FirstOrDefault()
              select new { previous, next }).First();

Edit: Took the new specifications into account.

Edit 2: Modified the code to not use LastOrDefault, which doesn't work with LINQ to Entities.

Upvotes: 7

Thousand
Thousand

Reputation: 6638

Try this:

 int id = 4;     
 var list = (from x in ctx.Table
            where x.id >= id - 1 && x.id <= id + 1
            select x).OrderBy(o -> o.name).ToList();

edit: this will return elements with ID 3,4 and 5. I dont know if you are actually ordering the table in the database by name, but if you are i think it would be easier to just use a loop instead of using linq to get the previous and next element.

update: sorted the result by name

Upvotes: 1

Related Questions