C# 从 Linq 到 Sql 的随机行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/648196/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Random row from Linq to Sql
提问by Julien Poulin
What is the best (and fastest) way to retrieve a random row using Linq to SQL when I have a condition, e.g. some field must be true?
当我有条件（例如某些字段必须为真）时，使用 Linq to SQL 检索随机行的最佳（和最快）方法是什么？
采纳答案by Marc Gravell
You can do this at the database, by using a fake UDF; in a partial class, add a method to the data context:
您可以在数据库中使用假 UDF 执行此操作；在分部类中，向数据上下文添加一个方法：
partial class MyDataContext {
[Function(Name="NEWID", IsComposable=true)]
public Guid Random()
{ // to prove not used by our C# code...
throw new NotImplementedException();
}
}
Then just order by ctx.Random(); this will do a random ordering at the SQL-Server courtesy of NEWID(). i.e.
然后只是order by ctx.Random(); 这将在 SQL-Server 提供随机排序NEWID()。IE
var cust = (from row in ctx.Customers
where row.IsActive // your filter
orderby ctx.Random()
select row).FirstOrDefault();
Note that this is only suitable for small-to-mid-size tables; for huge tables, it will have a performance impact at the server, and it will be more efficient to find the number of rows (Count), then pick one at random (Skip/First).
请注意，这仅适用于中小型桌子；对于大表，它会对服务器的性能产生影响，找到行数（Count），然后随机选择行数（）会更有效Skip/First。
for count approach:
对于计数方法：
var qry = from row in ctx.Customers
where row.IsActive
select row;
int count = qry.Count(); // 1st round-trip
int index = new Random().Next(count);
Customer cust = qry.Skip(index).FirstOrDefault(); // 2nd round-trip
回答by Jon Skeet
EDIT: I've only just noticed this is LINQ to SQL, not LINQ to Objects. Use Marc's code to get the database to do this for you. I've left this answer here as a potential point of interest for LINQ to Objects.
编辑：我刚刚注意到这是 LINQ to SQL，而不是 LINQ to Objects。使用 Marc 的代码让数据库为您执行此操作。我把这个答案留在这里作为 LINQ to Objects 的潜在兴趣点。
Strangely enough, you don't actually need to get the count. You do, however, need to fetch every element unless you get the count.
奇怪的是，你实际上并不需要得到计数。但是，除非获得计数，否则您确实需要获取每个元素。
What you can do is keep the idea of a "current" value and the current count. When you fetch the next value, take a random number and replace the "current" with "new" with a probability of 1/n where n is the count.
您可以做的是保留“当前”值和当前计数的想法。当您获取下一个值时，取一个随机数并以 1/n 的概率将“当前”替换为“新”，其中 n 是计数。
So when you read the first value, you alwaysmake that the "current" value. When you read the second value, you mightmake that the current value (probability 1/2). When you read the third value, you mightmake that the current value (probability 1/3) etc. When you've run out of data, the current value is a random one out of all the ones you read, with uniform probability.
因此，当您读取第一个值时，您总是将其设为“当前”值。当您读取第二个值时，您可能会将其设为当前值（概率为 1/2）。当您读取第三个值时，您可能会将其设为当前值（概率 1/3）等。当您用完数据时，当前值是您读取的所有值中的一个随机值，具有统一的概率。
To apply that with a condition, just ignore anything which doesn't meet the condition. The easiest way to do that is to only consider the "matching" sequence to start with, by applying a Where clause first.
要将其应用于条件，只需忽略不符合条件的任何内容。最简单的方法是首先考虑“匹配”序列，首先应用 Where 子句。
Here's a quick implementation. I thinkit's okay...
这是一个快速实现。我觉得没问题...
public static T RandomElement<T>(this IEnumerable<T> source,
Random rng)
{
T current = default(T);
int count = 0;
foreach (T element in source)
{
count++;
if (rng.Next(count) == 0)
{
current = element;
}
}
if (count == 0)
{
throw new InvalidOperationException("Sequence was empty");
}
return current;
}
回答by Ian Mercer
One way to achieve efficiently is to add a column to your data Shufflethat is populated with a random int (as each record is created).
一种有效实现的方法是向数据Shuffle中添加一列，该列填充有随机整数（在创建每条记录时）。
The partial query to access the table in random order is ...
以随机顺序访问表的部分查询是...
Random random = new Random();
int seed = random.Next();
result = result.OrderBy(s => (~(s.Shuffle & seed)) & (s.Shuffle | seed)); // ^ seed);
This does an XOR operation in the database and orders by the results of that XOR.
这会在数据库中执行 XOR 操作并根据该 XOR 的结果进行排序。
Advantages:-
好处：-
- Efficient: SQL handles the ordering, no need to fetch the whole table
- Repeatable: (good for testing) - can use the same random seed to generate the same random order
- 高效：SQL处理排序，无需取整表
- 可重复：（适合测试）- 可以使用相同的随机种子生成相同的随机顺序
This is the approach used by my home automation system to randomize playlists. It picks a new seed each day giving a consistent order during the day (allowing easy pause / resume capabilities) but a fresh look at each playlist each new day.
这是我的家庭自动化系统用来随机播放列表的方法。它每天选择一个新的种子，在白天给出一致的顺序（允许轻松暂停/恢复功能），但每天都会重新审视每个播放列表。
回答by Konstantin Tarkus
Another sample for Entity Framework:
实体框架的另一个示例：
var customers = db.Customers
.Where(c => c.IsActive)
.OrderBy(c => Guid.NewGuid())
.FirstOrDefault();
This does not work with LINQ to SQL. The OrderByis simply being dropped.
这不适用于 LINQ to SQL。在OrderBy简单地被丢弃。
回答by naiemk
If the purpose of getting random rows is sampling, I have talked very briefly hereabout a nice approach from Larson et al., Microsoft Research team where they have developed a sampling framework for Sql Server using materialized views. There is a link to the actual paper also.
如果获取随机行的目的是采样，我在这里非常简要地讨论了 Larson 等人的一个很好的方法，微软研究团队使用物化视图为 Sql Server 开发了一个采样框架。还有一个指向实际论文的链接。
回答by Artur Keyan
if you want to get e.g. var count = 16random rows from table, you can write
如果你想var count = 16从表中获取例如随机行，你可以写
var rows = Table.OrderBy(t => Guid.NewGuid())
.Take(count);
here I used E.F, and the Table is a Dbset
这里我用的是 EF，表是一个 Dbset
回答by midhun sankar
I have random function query against DataTables:
我有针对DataTables 的随机函数查询：
var result = (from result in dt.AsEnumerable()
order by Guid.NewGuid()
select result).Take(3);
回答by Fran
Came here wondering how to get a few random pages from a small number of them, so each user gets some different random 3 pages.
来到这里想知道如何从少数几个随机页面中获取一些随机页面，因此每个用户都会获得一些不同的随机 3 个页面。
This is my final solution, working querying with LINQ against a list of pages in Sharepoint 2010. It's in Visual Basic, sorry :p
这是我的最终解决方案，使用 LINQ 针对 Sharepoint 2010 中的页面列表进行查询。它在 Visual Basic 中，抱歉：p
Dim Aleatorio As New Random()
Dim Paginas = From a As SPListItem In Sitio.RootWeb.Lists("Páginas") Order By Aleatorio.Next Take 3
Probably should get some profiling before querying a great number of results, but it's perfect for my purpose
在查询大量结果之前可能应该进行一些分析，但它非常适合我的目的
回答by JCO
Using LINQ to SQL in LINQPad as C# statements look like
在 LINQPad 中使用 LINQ to SQL 作为 C# 语句看起来像
IEnumerable<Customer> customers = this.ExecuteQuery<Customer>(@"SELECT top 10 * from [Customers] order by newid()");
customers.Dump();
The generated SQL is
生成的 SQL 是
SELECT top 10 * from [Customers] order by newid()
回答by user1619860
The example below will call the source to retrieve a count and then apply a skip expression on the source with a number between 0 and n. The second method will apply order by using the random object (which will order everything in memory) and select the number passed into the method call.
下面的示例将调用源以检索计数，然后在源上使用 0 到 n 之间的数字应用跳过表达式。第二种方法将通过使用随机对象（它将对内存中的所有内容进行排序）应用顺序并选择传递给方法调用的数字。
public static class IEnumerable
{
static Random rng = new Random((int)DateTime.Now.Ticks);
public static T RandomElement<T>(this IEnumerable<T> source)
{
T current = default(T);
int c = source.Count();
int r = rng.Next(c);
current = source.Skip(r).First();
return current;
}
public static IEnumerable<T> RandomElements<T>(this IEnumerable<T> source, int number)
{
return source.OrderBy(r => rng.Next()).Take(number);
}
}
