How to order by query by NEWID() - SQLServer

3 posts, 0 answers
  1. Murilo
    Murilo avatar
    17 posts
    Member since:
    Jul 2012

    Posted 08 Feb 2013 Link to this post

    I want to use the following query with the Telerik ORM, is it possible?

    SELECT TOP 1 * 

    The result I want to achieve is to return a random row from the table named "MY_TABLE"
  2. Viktor Zhivkov
    Viktor Zhivkov avatar
    290 posts

    Posted 13 Feb 2013 Link to this post

    Hello Murilo,

    You can use the following code snippet to get random row from a table: allCount = dataContext.Cars.Count();
    02.Random rnd;
    05.    rnd = new Random((int)DateTime.Now.Ticks);
    06.} randomSkip = rnd.Next(0, allCount-1);
    09.var query = dataContext.Cars.OrderBy(c=>c.CarID).Skip(randomSkip).Take(1);
    10.var sql = query.ToString();
    11.var result = query.ToList();

    First I am getting the total number of rows in the table.
    Then I create a new random number generator and use it to skip to an arbitrary row.
    This option is totally independent from the backend that you use. 

    If you want something closer to your original SQL query and you target only MS SQL as backend, you can use the following snippet:
    1.var query1 = (from c in dataContext.Cars
    2.          orderby "NEWID()".SQL<Guid>()
    3.          select c).Take(1);
    4.var sql1 = query1.ToString();
    5.var result1 = query1.ToList();

    Here the magic is done by the SQL<T>() extension method that allows you to plug some SQL statements in your queries.

    If you need any further assistance, please let me know.

    All the best,
    Viktor Zhivkov
    the Telerik team
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
  3. DevCraft banner
  4. Murilo
    Murilo avatar
    17 posts
    Member since:
    Jul 2012

    Posted 13 Feb 2013 Link to this post

    Thanks for your response Viktor, I tried both codes and both worked as I expected.

    I ended up using your first solution as we may need to use this in future projects aiming Oracle instead of SQL Server.

    Thanks :)
Back to Top