选择一个随机行 [英] Selecting a single random row

问题描述

Hi All

我试图从SQL中选择一个类似于以下SQL代码的随机行：

1	ALTER PROCEDURE WebUser.GetPhoto
2
3	（
4	@GalleryID int = -1
5	）
6
7	AS
8	SET NOCOUNT ON
9
10	SELECT TOP 1
11	PhotoID，
12	[名称 ]，
13	PhotoPath
14	FROM 照片
15	WHERE GalleryID = @GalleryID OR @ GalleryID = -1
16	ORDER BY NewID（）
17
18	RETURN

解决方案

虽然LINQ to SQL数据库查询看起来像LINQ to objects版本，但它不是100％相同。该查询正在转换为SQL并发送到服务器以供执行。查询的片段（如'r.Next（）'）无法在服务器上执行，而是转换为参数。这意味着'r.Next（）'只被评估一次（不是像你在SQL查询中调用NewID那样对每行进行评估。）这很少是一个问题，因为查询的大多数用户从不注入 - 影响表达。在您的情况下，您希望为每一行生成一个唯一值，因此它显然很重要。

您可以通过各种方式解决您的问题。

1）使用您在SQL示例中使用的相同服务器端函数。这怎么可能？目前没有.Net方法由L2S转换为该方法。但是有一种方法可以指定你自己的。使用相同的技术，L2S允许您公开和调用存储过程，表值函数和用户定义的函数，您可以在DataContext上编写自己的方法签名并将其映射到SQL的"NewID"函数。
> public partial class MyDataContext {
...

[功能（IsComposable = true）]
public Guid NewID（）{
{
return（Guid）this.ExecuteMethodCall（ this，（MethodInfo）MethodInfo.GetCurrentMethod（））。ReturnValue;
}




2）在客户端上随机选择一行＃并使用Skip / Take将其选中

int row = r.Next（0，db.Photos.Count（））;
Photo p = db.Photos.Skip（row）.Take（1）.Single（）;
搜索结果

Hi All

I am trying to select a single random row from SQL similar to the following SQL code:

1	ALTER PROCEDURE WebUser.GetPhoto
2
3	(
4	@GalleryID int = -1
5	)
6
7	AS
8	SET NOCOUNT ON
9
10	SELECT          TOP 1
11	PhotoID,
12	[Name],
13	PhotoPath
14	FROM            Photo
15	WHERE           GalleryID = @GalleryID OR @GalleryID = -1
16	ORDER BY        NewID()
17
18	RETURN

解决方案

While the LINQ to SQL database query does look like the LINQ to objects version, it is not 100% the same.  The query is being translated to SQL and shipped to the server for execution.  Fragments of the query (like your 'r.Next()') that are not possible to execute on the server are turned into parameters instead.  This means that 'r.Next()' is only ever evaluated once (not for each row like you did with the call to NewID in your SQL query.)  This is rarely a problem, since most users of a query never inject side-effecting expressions.  In your case, you expect a unique value to be produced for each row so it clearly matters.

You can solve your problem a variety of ways.  

1) Use the same server side function that you used in the SQL example.  How is this possible?  There is currently no .Net method that is translated by L2S into that method. But there is a way to specify your own.  Using the same technique L2S allows you to expose and call stored procedures, table-valued functions and user-defined function, you can write your own method signature on your DataContext and map it to the SQL 'NewID' function.

public partial class MyDataContext {
    ...

    [Function(IsComposable=true)]
    public Guid NewID() {
    {
        return (Guid)this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod()).ReturnValue;
     }
}


2) Pick a row # randomly on the client and use Skip/Take to pick it out

int row = r.Next(0, db.Photos.Count());
Photo p = db.Photos.Skip(row).Take(1).Single();

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