Is this the proper way to select the previous and next record given an ID for the current record?

Question

I need to run a LINQ query which will return 3 rows (The current record, Previous record, and the next record relative to the current record. ProductID is my autogenerated identity column.

Currently i'm doing this with a Union LINQ statement but I'm not sure if there is a better or a more efficient way to accomplish the same task.

Here's what I got:

var ProductID = 10;

var Results = (from p in DB.Products
where p.ProductID == ProductID - 1 //Previous record.
select new Product
{
    ProductID = p.ProductID,
    ProductTitle = p.ProductTitle,
    Views = p.Views,
}).Union(from p in DB.Products
where p.ProductID == ProductID //Current record
select new Product
{
    ProductID = p.ProductID,
    ProductTitle = p.ProductTitle,
    Views = p.Views,
}).Union(from p in DB.Products
where p.ProductID == ProductID + 1 //Next record.
select new Product
{
    ProductID = p.ProductID,
    ProductTitle = p.ProductTitle,
    Views = p.Views,
});

This should return 3 rows for ProductID 9, ProductID 10, ProductID 11. Thanks!

Answer 1

Personally I would use this approach: it has the benefit of working where Ids are missing in the range. A brave man assumes all Ids are accounted for and present.

 var currAndNext = Context.Set<TPoco>()
                  .Where<TPoco>(t=>t.id == id)
                  .OrderBy(t=>t.id)
                  .Skip(0)
                  .Take(2);
 var prev = Context.Set<TPoco>()
                  .Where<TPoco>(t=>t.id == id)
                  .OrderByDescending(t=>t.id)
                  .Skip(1)
                  .Take(1);

Answer 2

Your approach can be rewritten more shortly like this:

var ProductID = 10;

var Results = (from p in DB.Products
where p.ProductID >= ProductID - 1 &&
      p.ProductID <= ProductID + 1
select new Product
{
   ProductID = p.ProductID,
   ProductTitle = p.ProductTitle,
   Views = p.Views,
});

But note, this will return what you need only if none of the records corresponding to the specified productIDs have been deleted from the Products table.

Answer 3

GwynBleidd proposed a good solution, however you can also specify a list of IDs, in your case like this:

var ids = new[] {ProductID - 1, ProcuctID, ProductID + 1};

And use it in the where clause

var Results = from p in DB.Products
              where ids.Contains(p.ProductID)
              select new Product
              {
                 ProductID = p.ProductID,
                 ProductTitle = p.ProductTitle,
                 Views = p.Views,
              };

I think this is more versatile and EF will translate it to WHERE [ProductID] IN (...), which the query execution planner can handle quite well.