Fetch rows before and after the actual item using LINQ

Question

I currently have a controller with the following ActionResult

    public ActionResult Details(string sku)
    {
        Product p = _productRepository.GetProduct(sku);

        return View("Details", p);
    }

I would like to expand on the LINQ query to return the product before as well as the product after to the view. So instead of returning one product I would now return three products.

1. Product Before (null if there is no product before)
2. Product whose SKU is known
3. Product After (null if there is no product after)

Any help would be highly appreciated.

Thanks

Answer 1

Change the View to be a Dictionary type, and return a dictionary with keys Previous, Current, Next. Fill the values from your repository. In the View check values from the Dictionary to be null, and maybe have a control for displaying the SKU(if it displays more than one data item.).

Dictionary<string, string> skuDictionary = new Dictionary<string, string>();
skuDictionary.Add("Previous", previousSKU);
skuDictionary.Add("Current", currentSKU);
skuDictionary.Add("Next", nextSKU);

        return View(skuDictionary);

Edit: The code for using LINQ to get the items from the repo is in a different answer. I was saying you would do something like the following to get them to the view

and the view would have the following

Inherits="System.Web.Mvc.ViewPage<System.Collections.Generic.Dictionary<string, string>>"

<% if (Model.ContainsKey("Previous")) { %><%=Model["Previous"] %><% } %>

Answer 2

Using a table I had handy (as you provided no schema), using 5 as a placeholder for an actual variable in LINQPad:

var pabove = (from p in PolicyStatuses
where p.PolicyStatusID >= 5 
orderby p.PolicyStatusID ascending
select p).Take(2);

var pbelow = (from p in PolicyStatuses
where p.PolicyStatusID <= 5
orderby p.PolicyStatusID descending
select p).Take(2);

pabove.Union(pbelow).Dump();

This will grab one above and one below. Note however that returning null doesn't happen here when you don't find a row above or below, it simply excludes such results. If you actually care, you can take the count of pabove and pbelow to detect if such a record was found.

The result (obviously again from my schema):

IOrderedQueryable<PolicyStatus> (3 items)  
PolicyStatusID Status RecordCreated 
4
 Unknown
 8/26/2007 11:06:11 PM

5
 Expired
 8/26/2007 11:06:11 PM

6
 Cancelled
 8/26/2007 11:06:11 PM

Note that 4, 5 and 6 were found. This has an advantage over loading the entire table and then picking the results near the one you want. Using Take(2) only 3 records should go across the wire from your SQL server to you web server. If your table is small enough, simply query the table with a sort and filter what you need.

Here is the SQL produced by LINQ (some fields omitted):

SELECT [t2].[PolicyStatusID], [t2].[Status], [t2].[RecordCreated]
FROM (
    SELECT TOP (2) [t0].[PolicyStatusID], [t0].[Status], [t0].[RecordCreated]
    FROM [PolicyStatus] AS [t0]
    WHERE [t0].[PolicyStatusID] >= @p0
    ORDER BY [t0].[PolicyStatusID]
    UNION
    SELECT TOP (2) [t1].[PolicyStatusID], [t1].[Status], [t1].[RecordCreated]
    FROM [PolicyStatus] AS [t1]
    WHERE [t1].[PolicyStatusID] <= @p1
    ORDER BY [t1].[PolicyStatusID] DESC
    ) AS [t2]

Answer 3

You need to define an order for your products, before you can determine which product comes before or after another product.

Once you define an order, you can do something similar to what @Godeke suggested.

The order is entirely up to you, but should make some sort of sense to the user. Ordering by an ID column probably isn't the right approach. Ordering by Name/Display Name, Date Added, Lowest Price, etc will make sense.

Better yet, allow the user to define the order.