Retrieve Random Record?

8 posts, 0 answers
  1. Jay
    Jay avatar
    28 posts
    Member since:
    Apr 2006

    Posted 03 Sep 2009 Link to this post

    How would I go about retrieving a random database record with the linq api?  Should I use the OQL api instead?  Here's is an example that did not work.

    var results = from fact in Scope.Extent<CompanyFact>() 
                              where fact.Display 
                              orderby new Random().Next() 
                              select fact; 
    return results.First(); 

  2. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 04 Sep 2009 Link to this post

    Hello Ben Friedman,

    The new Random().Next() function is unable to be resolved from the Linq expression tree into a valid SQL call. I would suggest you to execute Random().Next() on the client side and then pass it as a parameter to the Linq query.

    int rnd = new Random().Next(); 
     
    var results = from fact in Scope.Extent<CompanyFact>()  
                              where fact.Display  
                              orderby rnd  
                              select fact;  
    return results.First();  


    Sincerely yours,
    Zoran
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  3. DevCraft banner
  4. Jay
    Jay avatar
    28 posts
    Member since:
    Apr 2006

    Posted 04 Sep 2009 Link to this post

    This doesn't work unfortunately.  The query returns an empty result set even though there are plenty of records in the database.  Even if it were to work, I don't see how it would return a random record.  The translated sql query would look something like "select * from CompanyFact order by 3" and this would just attempt to order the result set by whatever was in the 3rd column of the table (for sql server anyways).  Any other ideas?
  5. Robert
    Robert avatar
    82 posts
    Member since:
    Jun 2009

    Posted 04 Sep 2009 Link to this post

    Hey Ben,

    I'm not sure if this is the best way to accomplish this, but here is what I came up with:

    int randomRecord = new Random().Next() % scope.Extent<CompanyFact>().Count(); 
    var qData = scope.Extent<CompanyFact>().Skip(randomRecord).Take(1); 
    var qValue = qData.ToList().First(); 

    - Robert
  6. Jay
    Jay avatar
    28 posts
    Member since:
    Apr 2006

    Posted 08 Sep 2009 Link to this post

    That's perfect.  Thanks Robert!
  7. Jay
    Jay avatar
    28 posts
    Member since:
    Apr 2006

    Posted 30 Jan 2010 Link to this post

    Robert's solution seemed to work but it actually randomly returns null.  The logic looks sound so I'm wondering why that's the case.  Would there be any other way for me to create a linq query that accomplishes what I want?  If not, what would the OQL look like?

    Thanks
  8. Jay
    Jay avatar
    28 posts
    Member since:
    Apr 2006

    Posted 30 Jan 2010 Link to this post

    Whoops I jumped the gun.  The error is somewhere else.
  9. Markus
    Markus avatar
    205 posts
    Member since:
    Nov 2005

    Posted 11 Dec 2013 Link to this post

    This seems to be working for me 

    ... .OrderBy(x => Guid.NewGuid()).Take(3);

    Markus

    public virtual IQueryable<zuol_mobile.T_banner> GetRandomBanners()
           {
     
               var Banners = repository.GetAllBy(p => p.BannerID != 1).OrderBy(x => Guid.NewGuid()).Take(3);
     
               if (Banners == null)
               {
                   throw new HttpResponseException(HttpStatusCode.MethodNotAllowed);
               }
     
               return Banners;
           }
Back to Top
DevCraft banner