EF 代码优先: 如何获得随机行

如何构建检索随机行的查询？

如果我用 SQL 编写它，那么我会在 newid ()上下一个订单，然后从顶部删除 n 行。不管怎样，先用 EF 代码做这个？

我尝试创建一个使用 newid ()的查询，并使用 DbSet 执行它。SqlQuery ().虽然它的工作，它不是最干净的解决方案。

此外，尝试检索所有的行，并按照一个新的 guid 对它们进行排序。尽管行数相当少，但这仍然不是一个好的解决方案。

有什么想法吗？

41601 次浏览
小开
最佳答案

Just call:

something.OrderBy(r => Guid.NewGuid()).Take(5)
小开

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

Generated 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

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
小开

you can try follow method:

 public static String UdfGetRandomText()
{
using (Models.DbContextModel db = new Models.DbContextModel())
{
try
{
Entity.tblRandomTexts t = new Entity.tblRandomTexts();
t = db.tblRandomTexts.OrderBy(r => Guid.NewGuid()).First();
return (t.TextBuddy + Environment.NewLine + t.TextWriter);
}
catch (Exception ee)
{
return ee.Message;
}
}
}

Provided you have a class in EF like the one below that creates the table.

public partial class tblRandomTexts
{
[Key]
public long TextRowID { get; set; }
[MaxLength(1500)]
public String TextBuddy { get; set; }
[MaxLength(100)]
public String TextWriter { get; set; }
}
小开

ef core 6 + there's a new function : EF.Functions.Random()

something.OrderBy(r => EF.Functions.Random()).Take(5)

提交答案