从 Linq 到 Sql 的随机行 [英] Random row from Linq to Sql

问题描述

当我有条件时，使用 Linq to SQL 检索随机行的最佳(和最快)方法是什么，例如某些字段必须为真?

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?

推荐答案

您可以在数据库中使用假 UDF 执行此操作；在分部类中，向数据上下文添加一个方法:

You can do this at the database, by using a fake UDF; in a partial class, add a method to the data context:

partial class MyDataContext {
     [Function(Name="NEWID", IsComposable=true)] 
     public Guid Random() 
     { // to prove not used by our C# code... 
         throw new NotImplementedException(); 
     }
}

然后只是按ctx.Random()排序；这将在 NEWID() 提供的 SQL-Server 上进行随机排序.即

Then just order by ctx.Random(); this will do a random ordering at the SQL-Server courtesy of NEWID(). i.e.

var cust = (from row in ctx.Customers
           where row.IsActive // your filter
           orderby ctx.Random()
           select row).FirstOrDefault();

请注意，这仅适用于中小型桌子；对于大表，它会对服务器的性能产生影响，找到行数(Count)，然后随机选择一个(Skip/First).

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).

对于计数方法:

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

这篇关于从 Linq 到 Sql 的随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！