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

本文关键字：何获得 随机 代码 EF | 更新日期: 2023-09-27 18:11:26

如何构建一个查询，我将检索随机行?

如果我要用SQL写它，那么我会按newid()排序，并从顶部切断n行。无论如何，首先在EF代码中做到这一点?

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

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

任何想法?

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

调用:

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

比较两个选项:

跳过(随机行数)

方法
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();
}
  • 接收2个查询

生成的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

方法
private T getRandomEntityInPlace<T>(IGenericRepository<T> repo) {
    return repo.Items.OrderBy(o => Guid.NewGuid()).First();
}

生成的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

ef core 6 +有一个新函数:ef . functions . random ()

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

您可以尝试以下方法:

 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;
                }
            }
        }

如果你在EF中有一个类，就像下面这个创建表的类。

public partial class tblRandomTexts
{
    [Key]
    public long TextRowID { get; set; }
    [MaxLength(1500)]
    public String TextBuddy { get; set; }
    [MaxLength(100)]
    public String TextWriter { get; set; }
}
相关文章：