2

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.

Improve this question
6
  • What are you looking for as an answer here? If you need to order you data randomly and then order it by the same random order again you are going to have to store the order somewhere. Maybe store it in the session, cache or a temp table? Doesn't need to be all the data but might as well instead of querying all the data again. Commented Sep 29, 2017 at 14:08
  • 2
    ordering by a random number seems like a poor user experience too. Commented Sep 29, 2017 at 14:08
  • @SeanLange I want to be able to store something in a search results table, but I don't know how to get a seed of a random number generator and use that. juharr, Do you know a better way to get a random seller? The seller should be randomly picked at the time of the initial search. The user would not know of the other sellers in this case. I'm not giving a choice, but ever seller would have an equal change to show. Commented Sep 29, 2017 at 14:13
  • You could think about a solution based on Random called with a seed, which produces deterministic set of values. If you store only the seed, you could get the same output later. Commented Sep 29, 2017 at 14:25
  • This is so extremely vague. I can come up with about a dozen possibilities but it all depends on your implementation and what you are trying to do. Commented Sep 29, 2017 at 14:57

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

So I guess id in my example would be the seller id? And if you don't mind, what exactly is a checksum? I'm going to test this out now.
@SolidSnake4444 - ID would need to be something that uniquely identifies your row
Tested this about 50 times on about 50 rows and it seems to be working perfectly. I get a random result each time and then if I supply a repeat seed, I get the same results each time. Seems Fast. Thank you so much!
I tested it on a record set that was a basic list and not the actual sellers etc. Now that I'm doing that, the order is random but the sellers themselves are always in the same order. So seller 1 is always before 2 but seller one may appear in slot 300 and seller 2 in slot 789 but 1 is always before 2. I want 2 to sometimes be first and to be able to reproduce. I may need to just tweak your checksum a bit. I'll try that next unless you can guess why this is happening?
@SolidSnake4444 - You could try playing around with the checksum, but I think you are limited by your Seller ID (It is probably an ascending list of integers). Try adding additional columns to the checksum SqlFunctions.Checksum(x.ID, x.Name, seed) or if that doesnt work, create a new column and store a random number in it
|
-1

If you use a fixed seed for Random then you can do this:

Random rand = new Random(42); query.ToArray().OrderBy(o => rand.Next()).ToList()

You just need to then save the 42 to the database. The next time you run this the results are the same (so long as query returns the same results in the same order).

The .ToArray() brings the results into memory so that you can call rand.Next() to order the results.

Improve this answer

3 Comments

I need it to be in SQL as the amount of records returned would be huge if I had to bring every seller into memory as well.
@SolidSnake4444 - Yes, but it's no different than query.OrderBy(o => rand.Next()).ToList() that you put in your question.
@Enigmativity I think if the OP was wishing to use what he puts in his question, he wouldn't have to ask the question in first. And OrderBy is a pure method so you have to use the return value to get the result, it's not like the old Sort method.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.