none
Selecting a single random row RRS feed

  • Question

  • Hi All

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

    1ALTER PROCEDURE WebUser.GetPhoto 
    2     
    3    ( 
    4        @GalleryID int = -1 
    5    ) 
    6     
    7AS 
    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 

    so far I have the following:
    1Random r = new Random();  
    2var photo = (from p in db.Photos 
    3                         where (p.GalleryID == galleryID || galleryID == -1) 
    4                         orderby r.Next() 
    5                         select p 
    6                        ).Take(1).SingleOrDefault(); 

    This isn't working for me. Any tips?




    Monday, December 15, 2008 10:24 AM

All replies

  • 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();



    Wayward LINQ Lacky
    Tuesday, December 16, 2008 5:28 AM
    Moderator
  • Thanks for the reply - I settled on using LINQ to call the proc. No point in trying to re-invent the wheel...
    Tuesday, December 16, 2008 5:15 PM