C# Linq to Entities，随机顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/654906/
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
Linq to Entities, random order
提问by NikolaiDante
How do i return matching entities in a random order?
Just to be clear this is Entity Framework stuff and LINQ to Entities.
如何以随机顺序返回匹配的实体？
只是要清楚这是实体框架的东西和 LINQ to Entities。
(air code)
(航空代码)
IEnumerable<MyEntity> results = from en in context.MyEntity
where en.type == myTypeVar
orderby ?????
select en;
Thanks
谢谢
Edit:
I tried adding this to the context:
编辑：
我尝试将其添加到上下文中：
public Guid Random()
{
return new Guid();
}
And using this query:
并使用此查询：
IEnumerable<MyEntity> results = from en in context.MyEntity
where en.type == myTypeVar
orderby context.Random()
select en;
But i got this error:
但我收到了这个错误：
System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Guid Random()' method, and this method cannot be translated into a store expression..
Edit (Current code):
编辑（当前代码）：
IEnumerable<MyEntity> results = (from en in context.MyEntity
where en.type == myTypeVar
orderby context.Random()
select en).AsEnumerable();
采纳答案by Michael Damatov
The simple solution would be creating an array (or a List<T>) and than randomize its indexes.
简单的解决方案是创建一个数组（或 a List<T>）然后随机化其索引。
EDIT:
编辑：
static IEnumerable<T> Randomize<T>(this IEnumerable<T> source) {
var array = source.ToArray();
// randomize indexes (several approaches are possible)
return array;
}
EDIT: Personally, I find the answer of Jon Skeet is more elegant:
编辑：就个人而言，我发现 Jon Skeet 的答案更优雅：
var results = from ... in ... where ... orderby Guid.NewGuid() select ...
And sure, you can take a random number generator instead of Guid.NewGuid().
当然，您可以使用随机数生成器而不是Guid.NewGuid().
回答by Jon Skeet
A simple way of doing this is to order by Guid.NewGuid()but then the ordering happens on the client side. You may be able to persuade EF to do something random on the server side, but that's not necessarily simple - and doing it using "order by random number" is apparently broken.
这样做的一个简单方法是按顺序排序，Guid.NewGuid()然后在客户端进行排序。您也许可以说服 EF 在服务器端做一些随机的事情，但这并不一定很简单——而且使用“按随机数排序”显然是坏的。
To make the ordering happen on the .NET side instead of in EF, you need AsEnumerable:
要在 .NET 端而不是在 EF 中进行排序，您需要AsEnumerable：
IEnumerable<MyEntity> results = context.MyEntity
.Where(en => en.type == myTypeVar)
.AsEnumerable()
.OrderBy(en => context.Random());
It would be better to get the unorderedversion in a list and then shuffle that though.
最好在列表中获取无序版本，然后对其进行洗牌。
Random rnd = ...; // Assume a suitable Random instance
List<MyEntity> results = context.MyEntity
.Where(en => en.type == myTypeVar)
.ToList();
results.Shuffle(rnd); // Assuming an extension method on List<T>
Shuffling is more efficient than sorting, aside from anything else.
See my article on randomnessfor details about acquiring an appropriate Randominstance though. There are lots of Fisher-Yates shuffle implementations available on Stack Overflow.
除了其他任何事情之外，洗牌比排序更有效。有关获取适当实例的详细信息，请参阅我关于随机性的文章Random。Stack Overflow 上有很多 Fisher-Yates shuffle 实现。
回答by Klinger
How about this:
这个怎么样：
var randomizer = new Random();
var results = from en in context.MyEntity
where en.type == myTypeVar
let rand = randomizer.Next()
orderby rand
select en;
回答by Migol
Toro's answer is the one I would use, but rather like this:
Toro 的答案是我会使用的答案，但更像是这样：
static IEnumerable<T> Randomize<T>(this IEnumerable<T> source)
{
var list = source.ToList();
var newList = new List<T>();
while (source.Count > 0)
{
//choose random one and MOVE it from list to newList
}
return newList;
}
回答by Fabrice
The solutions provided here execute on the client. If you want something that executes on the server, here is a solution for LINQ to SQLthat you can convert to Entity Framework.
此处提供的解决方案在客户端上执行。如果您想要在服务器上执行某些内容，这里是 LINQ to SQL 的解决方案，您可以将其转换为实体框架。
回答by Jamie
Here is a nice way of doing this (mainly for people Googling).
这是一个很好的方法（主要用于谷歌搜索）。
You can also add .Take(n) on the end to only retrieve a set number.
您还可以在末尾添加 .Take(n) 以仅检索一组数字。
model.CreateQuery<MyEntity>(
@"select value source.entity
from (select entity, SqlServer.NewID() as rand
from Products as entity
where entity.type == myTypeVar) as source
order by source.rand");
回答by Drew Noakes
Jon's answer is helpful, but actually you canhave the DB do the ordering using Guidand Linq to Entities (at least, you can in EF4):
Jon 的回答很有帮助，但实际上您可以让 DB 使用GuidLinq to Entities进行排序（至少，您可以在 EF4 中）：
from e in MyEntities
orderby Guid.NewGuid()
select e
This generates SQL resembling:
这会生成类似于以下内容的 SQL：
SELECT
[Project1].[Id] AS [Id],
[Project1].[Column1] AS [Column1]
FROM ( SELECT
NEWID() AS [C1], -- Guid created here
[Extent1].[Id] AS [Id],
[Extent1].[Column1] AS [Column1],
FROM [dbo].[MyEntities] AS [Extent1]
) AS [Project1]
ORDER BY [Project1].[C1] ASC -- Used for sorting here
In my testing, using Take(10)on the resulting query (converts to TOP 10in SQL), the query ran consistently between 0.42 and 0.46 sec against a table with 1,794,785 rows. No idea whether SQL Server does any kind of optimisation on this or whether it generated a GUID for everyrow in that table. Either way, that would be considerably faster than bringing all those rows into my process and trying to sort them there.
在我的测试中，使用Take(10)结果查询（TOP 10在 SQL 中转换为），查询在 0.42 到 0.46 秒之间始终如一地针对具有 1,794,785 行的表运行。不知道 SQL Server 是否对此进行了任何类型的优化，或者是否为该表中的每一行生成了一个 GUID 。无论哪种方式，这都比将所有这些行带入我的流程并尝试在那里对它们进行排序要快得多。
回答by Segev -CJ- Shmueli
Theoretically speaking (I haven't actually tried it yet), the following should do the trick :
从理论上讲（我还没有真正尝试过），以下应该可以解决问题：
Add a partial class to your context class :
将分部类添加到您的上下文类：
public partial class MyDataContext{
[Function(Name = "NEWID", IsComposable = true)]
public Guid Random()
{
// you can put anything you want here, it makes no difference
throw new NotImplementedException();
}
}
implementation :
执行 ：
from t in context.MyTable
orderby context.Random()
select t;
回答by lolo_house
I think it's better not to add properties to the class. Better to use the position:
我认为最好不要向类添加属性。最好使用以下位置：
public static IEnumerable<T> Randomize<T>(this IEnumerable<T> pCol)
{
List<T> lResultado = new List<T>();
List<T> lLista = pCol.ToList();
Random lRandom = new Random();
int lintPos = 0;
while (lLista.Count > 0)
{
lintPos = lRandom.Next(lLista.Count);
lResultado.Add(lLista[lintPos]);
lLista.RemoveAt(lintPos);
}
return lResultado;
}
And the call will (as toList() or toArray()):
并且调用将（如 toList() 或 toArray()）：
var result = IEnumerable.Where(..).Randomize();
var 结果 = IEnumerable.Where(..).Randomize();
回答by drzaus
(cross-posting from EF Code First: How to get random rows)
（从EF Code First交叉发布：如何获取随机行）
Comparing two options:
比较两个选项：
Skip(random number of rows)
跳过（随机行数）
Method
方法
private T getRandomEntity<T>(IGenericRepository<T> repo) where T : EntityWithPk<Guid> {
var skip = (int)(rand.NextDouble() * repo.Items.Count());
return repo.Items.OrderBy(o => o.ID).Skip(skip).Take(1).First();
}
- Takes 2 queries
- 需要 2 个查询
Generated SQL
生成的 SQL
SELECT [GroupBy1].[A1] AS [C1]
FROM (SELECT COUNT(1) AS [A1]
FROM [dbo].[People] AS [Extent1]) AS [GroupBy1];
SELECT TOP (1) [Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[Age] AS [Age],
[Extent1].[FavoriteColor] AS [FavoriteColor]
FROM (SELECT [Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[Age] AS [Age],
[Extent1].[FavoriteColor] AS [FavoriteColor],
row_number() OVER (ORDER BY [Extent1].[ID] ASC) AS [row_number]
FROM [dbo].[People] AS [Extent1]) AS [Extent1]
WHERE [Extent1].[row_number] > 15
ORDER BY [Extent1].[ID] ASC;
Guid
指南
Method
方法
private T getRandomEntityInPlace<T>(IGenericRepository<T> repo) {
return repo.Items.OrderBy(o => Guid.NewGuid()).First();
}
Generated SQL
生成的 SQL
SELECT TOP (1) [Project1].[ID] AS [ID],
[Project1].[Name] AS [Name],
[Project1].[Age] AS [Age],
[Project1].[FavoriteColor] AS [FavoriteColor]
FROM (SELECT NEWID() AS [C1],
[Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[Age] AS [Age],
[Extent1].[FavoriteColor] AS [FavoriteColor]
FROM [dbo].[People] AS [Extent1]) AS [Project1]
ORDER BY [Project1].[C1] ASC
So in newer EF, you can again see that NewGuidis translated into SQL (as confirmed by @DrewNoakes https://stackoverflow.com/a/4120132/1037948). Even though both are "in-sql" methods, I'm guessing the Guid version is faster? If you didn't have to sort them in order to skip, and you could reasonably guess the amount to skip, then maybe the Skip method would be better.
因此，在较新的 EF 中，您可以再次看到它NewGuid被转换为 SQL（正如@DrewNoakes https://stackoverflow.com/a/4120132/1037948所确认的那样）。即使两者都是“in-sql”方法，我猜测 Guid 版本更快？如果您不必为了跳过而对它们进行排序，并且您可以合理地猜测要跳过的数量，那么也许 Skip 方法会更好。
