Is it right to select the previous and next record with the identifier of the current record?

Question

I need to run a LINQ query that will return 3 rows (current record, previous record and next record relative to the current record. ProductID is my auto-generated identifier column.

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

Here is 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. Thank you!

Answer 1

Your approach can be rewritten more briefly as follows:

 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 that this will return what you need only if none of the entries matching the specified product identifiers have been deleted from the Products table.

Answer 2

Personally, I would use this approach: it has the advantage of working when identifiers are not in the range. A brave person assumes that all identifiers are counted 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 3

GwynBleidd offers a good solution, however you can also specify a list of identifiers in your case as follows:

 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 universal, and EF will translate it to WHERE [ProductID] IN (...) , which the query planner can handle quite well.

Answer 4

This is how I would solve the problem - avoiding the use of + 1, -1.

In my case, I was trying to show previous / next published blog posts . + 1, -1 will not work if the next / previous blog post was unpublished . Not to mention the possibility that identifiers are not always sequential.

In your case, you may not want to display over-the-counter products.

 var products = db.Products.Where(x => x.Quantity > 0).OrderBy(x => x.ProductId).ToList(); var previous = products.LastOrDefault(x => x.ProductId < id), var next = products.FirstOrDefault(x => x.ProductId > id) 

This will return the previous and next products using the ProductId closest to your id start.

Note: .OrderBy(x => x.ProductId) not required if your list is already in order.