SolidSnake4444 SolidSnake4444 - 3 years ago 177
SQL Question

SQL Server / Entity Framework - How to order by random number and then reproduce results at a later time?

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.

Edit

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


I got the exception:

"LINQ to Entities does not recognize the method 'Int32 Next()' method, and this method cannot be translated into a store expression."

I feel like this would be the answer if I could get entityframework to play nice.

Answer Source

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;

SqlFunctions.Checksum

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download