Retrieve Random Record?

[Jay]

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

[Zoran]

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.

[Jay]

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?

[Robert]

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

[Jay]

That's perfect.  Thanks Robert!

[Jay]

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

[Jay]

Whoops I jumped the gun.  The error is somewhere else.

[Markus]

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;

       }