none
Is there a way to select random rows? RRS feed

  • Question

  • At this point I don't really care if it's L2E or ObjectQuery. I'm just shooting for this TSQL...

    SELECT TOP 10 * FROM myTable ORDER BY NewID()

    Wednesday, April 7, 2010 1:02 PM

All replies

  • Yes...But the SQL (behind the scenes) is not as pretty.  This gets the job done assuming your model is called MyEntities and you are going after a company collection

    using (MyEntities ctx = new MyEntities())
    {
        IQueryable<Company> result = ctx.Companies.OrderBy(c => Guid.NewGuid()).Take(10);
        foreach (Company company in result)
        {
           Console.WriteLine(company.CompanyName1);
        }
        //This is the t-sql
        Console.WriteLine(((System.Data.Objects.ObjectQuery)result).ToTraceString());
    }
    Thursday, April 8, 2010 3:18 AM
  • I get the following error when trying that.

     

    "LINQ to Entities does not recognize the method 'System.Guid NewGuid()' method, and this method cannot be translated into a store expression."

    Thursday, April 8, 2010 12:11 PM
  • Copy and paste your exact LINQ query here...
    Saturday, April 10, 2010 3:18 AM
  • Hi Seth,

    How is the problem now?   As paully21 said, we need you detailed LINQ query for further investigation. 

    Have a nice day, all!


    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, April 13, 2010 9:00 AM
    Moderator
  • Sometimes our table contains large number of records where we need to retrieve some of them (randomly). The TABLESAMPLE clause in SQL Server allows to extract a sampling of rows from a table in the FROM clause. It limits the number of rows returned from a table in the FORM clause to a sample number or PERCENT of rows. The TABLESAMPLE clause takes a parameter that can be a percent or a number representing how many rows to retrieve. The retrieved result of rows are random and they are not in any order. Each time you get a different result set when you run query.

     

    Hope this did answer the questions.


    Cheers, Eliza
    Tuesday, June 15, 2010 7:35 AM