SQL Server/实体框架-如何按随机数排序，然后在以后重现结果? [英] SQL Server / Entity Framework - How to order by random number and then reproduce results at a later time?
问题描述
我正在将ASP.NET MVC 5和Entity Framework与Microsoft SQL Server后端一起使用.
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.
详细版本，从理论上讲，我的商店有100万个商品.每个商品有5个不同的卖家.我使用实体框架提取了只有1个卖方加入的所有1百万条记录.通过随机编号订购并首先选择，即随机选择了该卖方.假设显示10个结果的结果页，一旦我转到第2页，然后返回第1页，我希望第一个项目具有与第一次采摘的随机卖方相同的随机卖方，而不是再次随机采摘卖方.如果他们正在看卖方的一件商品，然后又看又一遍，那将是糟糕的用户体验.价格会改变，等等.
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.
现在，我已经通过制作搜索结果表来解决"此问题.我存储了与搜索到的物品随机挑选的卖方.这样，在搜索之后，我仅查看搜索结果表，而不是再次执行搜索.当我有100个左右的项目时，这很好，但是现在我有大量记录的可能性，而且我不想每次搜索都存储100万条记录.
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.
除非有人认为有更好的方法，否则我会想到我不确定如何应用.如果我可以存储生成随机数的方式，然后为仅包含该种子"的搜索结果存储1条记录，然后我再次使用种子进行搜索，并且它将随机得到相同的卖方第一次返回的结果，该怎么办?
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
我有一个例外:
"LINQ to Entities无法识别方法'Int32 Next()'方法，并且该方法无法转换为商店表达式."
"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.
推荐答案
您可以创建/存储一个随机数(种子):
You could create/store a random number (seed):
int seed = new Random().Next(1, 1000);
然后使用 CHECKSUM 结合种子和记录的ID，以创建可重复的序列，如下所示:
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;
这篇关于SQL Server/实体框架-如何按随机数排序，然后在以后重现结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
