Random Row from Linq to Sql

Random row from Linq to Sql

You can do this at the database, by using a fake UDF; in a partial class, add a method to the data context:

partial class MyDataContext {
     [Function(Name="NEWID", IsComposable=true)] 
     public Guid Random() 
     { // to prove not used by our C# code... 
         throw new NotImplementedException(); 
     }
}

Then just order by ctx.Random(); this will do a random ordering at the SQL-Server courtesy of NEWID(). i.e.

var cust = (from row in ctx.Customers
           where row.IsActive // your filter
           orderby ctx.Random()
           select row).FirstOrDefault();

Note that this is only suitable for small-to-mid-size tables; for huge tables, it will have a performance impact at the server, and it will be more efficient to find the number of rows (Count), then pick one at random (Skip/First).

---

for count approach:

var qry = from row in ctx.Customers
          where row.IsActive
          select row;

int count = qry.Count(); // 1st round-trip
int index = new Random().Next(count);

Customer cust = qry.Skip(index).FirstOrDefault(); // 2nd round-trip

Random selection of query result Linq

Sort items by random value and select first:

var q = (from c in session.DB.Question 
         where c.Level =='1' && c.Group =='1' 
         select c)
        .OrderBy(o => SqlFunctions.Rand())
        .First();

linq select a random row

Random rand = new Random();
int toSkip = rand.Next(0, context.Quotes.Count);

context.Quotes.Skip(toSkip).Take(1).First();

Select N Random Records with Linq

Maybe something like this would work:

int randomSkip;
int randomTake;

randomSkip = GenerateSomeAppropriateRandomNumber();
randomTake = GenerateSomeAppropriateRandomNumber();

resultSet = iEnumerable.Skip(randomSkip).Take(randomTake);

Return Random Rows using an IQueryable

There is a class of supported functions for EF called SqlFunctions. You can use the Rand function of this class:

 query = query.OrderBy(product => SqlFunctions.Rand()).Where (p => p.Active == 1);

don't forget to add

 using System.Data.Entity.Core.Objects; 

the problem with this approach is from this source:

Repetitive invocations of RAND() in a single query will produce the same value.

I don't think there is any work around for it. The solutions is to use `Newid() in raw SQL commands which I think is not useful for you, Or loading the query data and then shuffle the rows in memory.

I tried this work around and it seems to work:

var seed = (int)DateTime.Now.Ticks % 9395713;//a random number
query = query
   .OrderBy(product =>SqlFunctions.Rand(product.ProductId * seed % 577317))
   .Where (p => p.Active == 1);

this trick will force the sql server to generate a new random value for each row, as the ProductId changes on each row this changes the Rand seed which forces the sql server to generate new random value.

You can explore different values for seed variable to obtain better results.

EDIT

In order to have a seed per category, you can create a cache of seeds in Application or simply creating an static dictionary of (category, seed) pairs, something like this:

in controller if you use MVC or the page class if you use ASP.NET:

static Dictionary<string, int> categorySeeds = new Dictionary<string, int>();

in the action or the method:

int seed = 0;
if(categorySeeds.ContainsKey(categoryName))
{
   seed = categorySeeds[categoryName];
}
else
{
   seed = (int)DateTime.Now.Ticks % 9395713;//a random number
   categorySeeds.Add(categoryName, seed);
}
//rest of the code

Randomly Select Records Using Linq to SQL

It would probably be better to do this with a for loop to get the item then using LINQ

var result = new List<subject>();
for(i = 0; i < 10; i++) 
{
   randomnumber = GenerateNewRandomNumber();
   result.Add(sc.Subjects.Where(r => r.SubName == sub && r.Level == lev).Skip(randomnumber).take(1).First;

}

---

Related Topics