Random row from Linq to Sql

by

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

More Related Contents:

  1. What is the syntax for an inner join in LINQ to SQL?
  2. LINQ – Left Join, Group By, and Count
  3. Making Entity Class Closed for Changes
  4. To return IQueryable or not return IQueryable [closed]
  5. How to compare only Date without Time in DateTime types in Linq to SQL with Entity Framework?
  6. Get SQL query from LINQ to SQL?
  7. Updating an ObservableCollection in a separate thread
  8. Metadata were not loaded using MetadataType
  9. Am I misunderstanding LINQ to SQL .AsEnumerable()?
  10. Entity Framework/Linq to SQL: Skip & Take
  11. LINQ Where Ignore Accentuation and Case
  12. Operation could destabilize the runtime?
  13. How to select only the records with the highest date in LINQ
  14. DISTINCT() and ORDERBY issue
  15. Polymorphism: Is ORM entity a Domain Entity or Data Entity?
  16. Max or Default?
  17. How do I do a left outer join with Dynamic Linq?
  18. Best way to update LINQ to SQL classes after database schema change
  19. Get sum of two columns in one LINQ query
  20. Last and LastOrDefault not supported
  21. IDENTITY INSERT and LINQ to SQL
  22. Linq where column == (null reference) not the same as column == null
  23. Extracting mantissa and exponent from double in c#
  24. Why we need Thread.MemoryBarrier()?
  25. convert double to int
  26. .NET ObservableDictionary
  27. WCF Service Client: The content type text/html; charset=utf-8 of the response message does not match the content type of the binding
  28. A reproducible example of volatile usage
  29. System.Threading.Timer in C# it seems to be not working. It runs very fast every 3 second
  30. Reading XML with an “&” into C# XMLDocument Object

Leave a Comment