I'm using ASP.NET MVC 5 and Entity Framework with a Microsoft SQL Server backend.
Short version, I would like to perform a search, order parts of it by a random number, and then be able to bring up the search results at a later time.
Detailed version, theoretically let's say I have a store that has 1 million items. Each item has 5 different sellers. I use entity framework to pull all 1 million records joined with only 1 seller. That seller was randomly picked by ordering by a random number and selecting first. Assuming a results page that displays 10 results, once I go to page 2 and then back to page 1, I want the first item to have the same random seller it picked the first time and not to random pick the seller again. It would be a bad user experience if they were looking at one item from a seller and then they look again and it's different. The price would change etc.
Right now I have "solved" this issue by making a search results table. I store the seller that was randomly picked with the item that was searched on. This way after the search, I only look at the search results table instead of perform the search again. This was fine when I had 100 items or so but now I have a possibility of huge records and I don't want to store 1 million records per search.
Unless something thinks there is a better way, I thought of something that I'm not sure how to apply. What if I could store the way the random number was generated and then store 1 record for search results that just contains that "seed" and then I perform the search again with the seed and it would randomly get the same seller returned first each time.
Is something like that possible? If so how can I call random in entity framework while giving it a seed to produce the same results?
Would prefer the answer to involve Entity Framework.
I tried the random idea as mentioned in the comments with entity framework with the following:
var test = DateTime.Now.Ticks;
Random rand = new Random((int)test);
query.OrderBy(o => rand.Next()).ToList()
//Save test in database so we can retrieve it later to get the same results
You could create/store a random number (seed):
int seed = new Random().Next(1, 1000);
Then use CHECKSUM to combine the seed and the ID of the record in order to create a reproducible sequence, like this:
query.OrderBy(x => SqlFunctions.Checksum(x.ID, seed, x.ID)) .ThenBy(x => x.ID) // Just in case two checksums produce the same value;