how to get random rows in linq with last inserted

Question

i am trying to show random products in view for each request, this OrderBy(r => Guid.NewGuid()) works fine, but i am trying to increase perfomance when table records are huge , so i used second option from here

my action:

public ActionResult ProductType(string id)
{
List<ProductsView> productlist = (from a in this.dbo.ProductTable
                                 join ca in dbo.Category on a.CategoryID equals ca.CategoryID
                                 where ca.Category == id 
                                 select new ProductsView()
                                 {
                                 CategoryID = c.CategoryID,
                                 Categorycount = c.Categorycount
                                 }).ToList<ProductsView>();

// here shuffle or mix products

int count = productlist.Count();
int index = new Random().Next(count);
ViewBag.Products = productlist.Skip(index).ToList();

 return View();

}

but when view returns some records are missing, eg:

first request count 4 index 1

shows 3 product in view

second request count 4 index 2

shows 2 product in view

third request count 4 index 3

shows 1 product in view

finally i have one more requirement can i show last inserted row in top and let other products be random ?

may i know what i am missing ?

any help would be great.

Answer 1

Okay, so the updated requirements are:

• Fetch all items
• Ordering is random other than the first item, which should be the last one added

Firstly, get rid of your Skip call. You're not trying to skip anything. Just fetch all the products (possibly ordered - see below) into a list.

For the randomness part, I'd do that in the calling code, using a modified Fischer-Yates shuffle - there are numerous examples of that on Stack Overflow, such as here.

In this case, you probably want to get the most recent item to the start of the list and then just shuffle the rest of the list. That's easy enough to do with a slight modification to the shuffling code - but you need to get the most recent item to the start of the list first. You could either do that by using OrderByDescending(x => x.InsertionDate) (or whatever) in your LINQ query, or just fetch everything and find the latest row in an O(n) pass over the rows in memory. Using OrderByDescending will be simpler, but potentially slightly less efficient (as you don't really need full ordering).