How to order by query by NEWID() - SQLServer

[Murilo]

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

SELECT TOP 1 * 
FROM MY_TABLE
ORDER BY NEWID()

The result I want to achieve is to return a random row from the table named "MY_TABLE"

[Viktor Zhivkov]

Hello Murilo,

You can use the following code snippet to get random row from a table:

01.int allCount = dataContext.Cars.Count();
02.Random rnd;
03.unchecked
04.{
05.    rnd = new Random((int)DateTime.Now.Ticks);
06.}
07.int randomSkip = rnd.Next(0, allCount-1);
08.
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

[Murilo]

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 :)