Paging in LINQ+MVC with a randomly ordered collection

Question

I have a bunch of records that are sorted RANDOMLY, like so:

var entries = DataContext.Entries.OrderBy(e => Random());

The Random function returns a randomly-generated GUID, thereby ordering the records in a random manner. Now my problem is paging. In MVC, I have a List action for the Entry controller that lists the entries:

public class EntryController : Controller 
{
     public ActionResult List(int page)
     {
          int pageSize = 10;

          var entries = DataContext.Entries.OrderBy(e => Random()).Skip((page - 1) *      pageSize).Take(pageSize);
          ViewData["entries"] = entries;
          return View();
     }
}

My problem here is that whenever I go from one page to another page, the entries are REARRANGED ANEW. So when I go to page 1 (step 1), then go to page 2 (step 2), then back to page 1 (step 3), the entries that were shown in the step 1 are different from those shown in step 3. I absolutely need to have the records arranged randomly the first time around, but not in the subsequent look ups.

Any ideas on how best to address this problem?

Answer 1

Because each time you invoke the action the randomness occurs all over again to the whole records before paging, what you need to do is:

1. Order the records randomly as you want it.
2. Cache the result.

3. Fetch the cache every time the action is invoked while applying paging.

   public ActionResult List(int page)
    {
         var dataSource = CacheContext["RandomRecords"];
         if(dataSource == null){
            dataSource = DataContext.Entries.OrderBy(e => Random());
         }
   
         int pageSize = 10;
         var entries = dataSource.Skip((page - 1) * pageSize).Take(pageSize);
   
         ViewData["entries"] = entries;
         return View();
    }
   

Answer 2

Add a column named sortOrder, being an int. Do not forget to add a non-unique index on it or sorting on it will be suboptimal.

Put a number there, increased from record to record, not in the same order than your id or an order that would sort the records alphabetically.

Then

dataSource = DataContext.Entries.OrderBy(e => e.sortOrder);

Guids will be slower to sort on, but you can alternatively use a guid column for the task. Every now and then (once per night), you can loop through all records to put new values in the sortOrder column so people will not recognize the order.

Answer 3

You can add following SQL code to get products, you only need to generate some random number and store it into the session or cache and get product again..

DECLARE @NumberOfProductsInDatabase INT
DECLARE @RandomNumber VARCHAR(10)

SET @NumberOfProductsInDatabase = (SELECT COUNT(*) FROM Product)
SET @RandomNumber = '45'
SELECT CAST(100000 *  SQRT(EXP(COS(p.SellerId + @RandomNumber))) AS INT) % @NumberOfProductsInDatabase AS RandomProductNr,
    p.Id,p.Name,pv.Price,pv.SalePrice
FROM Product p,ProductVariant pv
WHERE pv.ProductId = p.Id
ORDER BY RandomProductNr