This is a migrated thread and some comments may be shown as answers.
Retrieve Random Record?
7 Answers 107 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Jay
Top achievements
Rank 1
Jay asked on 03 Sep 2009, 04:13 PM
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(); 

7 Answers, 1 is accepted

Sort by
0
Zoran
Telerik team
answered on 04 Sep 2009, 02:57 PM
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.
0
Jay
Top achievements
Rank 1
answered on 04 Sep 2009, 05:35 PM
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?
0
Robert
Top achievements
Rank 1
answered on 04 Sep 2009, 08:52 PM
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
0
Jay
Top achievements
Rank 1
answered on 08 Sep 2009, 01:09 PM
That's perfect.  Thanks Robert!
0
Jay
Top achievements
Rank 1
answered on 30 Jan 2010, 08:13 PM
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
0
Jay
Top achievements
Rank 1
answered on 30 Jan 2010, 08:23 PM
Whoops I jumped the gun.  The error is somewhere else.
0
Markus
Top achievements
Rank 2
answered on 11 Dec 2013, 01:11 PM
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;
       }
Tags
LINQ (LINQ specific questions)
Asked by
Jay
Top achievements
Rank 1
Answers by
Zoran
Telerik team
Jay
Top achievements
Rank 1
Robert
Top achievements
Rank 1
Markus
Top achievements
Rank 2
Share this question
or