for unit testing i'd love to load a random result from a table... any suggestions without pulling the entire collection over?

If your database supports paging, then you could do it with dynamic query, otherwise, I think you will have to write and call a custom stored procedure, or at least load a collection of primary keys, pick one via random indexer, and LoadByPrimaryKey:

OrderDetails od = new OrderDetails();

od.Query.es.CountAll = true;
od.Query.es.CountAllAlias = "Count";
od.Query.Load();

int totalRowCount = (int)od.GetColumn("Count");
Random autoRand = new Random();

od = new OrderDetails();

od.Query.OrderBy(od.Query.OrderID, esOrderByDirection.Ascending);
od.Query.OrderBy(od.Query.ProductID, esOrderByDirection.Ascending);
od.Query.es.PageSize = 1;
od.Query.es.PageNumber = autoRand.Next(totalRowCount - 1) + 1;
od.Query.Load();

I'm trying to achieve the same thing using this select statement in MySQL:

SELECT *

FROM tablename

ORDER by RAND()

limit 1

How would i pass that through EntitySpaces?

Thanks

Ryan 

Neat trick, Ryan and Mike.

OrderDetails od = new OrderDetails();

od.Query.es.Top = 1;
od.Query.OrderBy("<RAND()>", esOrderByDirection.Ascending);

od.Query.Load();

LastQuery:

SELECT * FROM `northwind`.`order details` 
  ORDER BY RAND() ASC LIMIT 1

BTW, while syntactically correct for SQL Server as well, that only works for MySQL because, at the server, MySQL is doing the random ORDER BY before it does the LIMIT. SQL Server just gets the first row, and then randomly orders it... Not too useful.

SQL Server LastQuery:

SELECT  TOP 1 * FROM [Northwind].[dbo].[Order Details] 
  ORDER BY RAND() ASC

In SQL Server this command makes the trick.

SELECT TOP 1 * FROM [Northwind].[dbo].[Order Details] ORDER BY newid()

Cool, paschoal1. This does work for SQL Server, where RAND does not.

OrderDetails od = new OrderDetails();

od.Query.es.Top = 1;
od.Query.OrderBy("<NEWID()>", esOrderByDirection.Ascending);

od.Query.Load();

Es Team:

Why not create esOrderByDirection.Randomily to do this?

Of course, it is necessary to investigate other databases to see if it is possible.

Thanks.

I find another way to do this in sql server that involves three queries an uses the function Round. It works.

Maybe it is faster then the other way.

DECLARE @mx int, @mn int
SELECT @mx = MAX(idAssessor) FROM assessor
SELECT @mn = MIN(idAssessor) FROM assessor
SELECT * FROM assessor WHERE idAssessor = ROUND(@mn + (RAND() * (@mx-@mn)),0)

This code isn't working for me.  I think it's because the Order By clause gets surrounded with hard brackets like this...

... Order By [<NEWID()>] Asc

I just double-checked this using ES2008 against SQL Server Northwind. It does not put square brackets around raw SQL, and works as expected.

OrderDetails od = new OrderDetails();

od.Query.es.Top = 1;
od.Query.OrderBy("<NEWID()>", esOrderByDirection.Ascending);

od.Query.Load();

LastQuery:

SELECT  TOP 1 * 
FROM [Order Details] 
ORDER BY NEWID() ASC

Perhaps, if you posted the code for your query, we might spot something. When you post your reply, the last icon on the right will let you paste in source code, and format it for the selected language. Although, after you click "OK", you will probably have to hand-edit "&lt;" and replace it with "<".

Thank you for double checking.  We have not yet upgraded to ES2008 (the wheels of state government often turn slow).  I'll assume that's the issue for now and will find a workaround.

Thanks again!