How to get random list of rows using subsonic 3

Question

hello I am using Active record pattern of SubSonic 3 version. My requirement is to get 3 random rows from the Table. After some googling I found out that I can use NewID function in SQL but I dont know to get the Randow rows using sub sonic Thanks

Answer 1

There's always a "backdoor" with subsonic. It's called InlineQuery (SubSonic 2.2) or CodingHorror (SubSonic 3): http://subsonicproject.com/docs/CodingHorror

Your SQL Query will probably look like this:

SELECT top 3
newid() as sortorder, id
FROM some_table
ORDER by sortorder

So I would suggest something like this

List<int> result = new CodingHorror(@"
       SELECT TOP 3
       id, newid() as sortorder
       FROM some_table
       ORDER by sortorder
    ).ExecuteTypedList<int>();

If it didn't changed from subsonic 2.2 to 3 the ExcecuteTypedList() method returns the first element from the query when used with a valuetype as the generic type parameter. In this case: id.

This might work, too:

List<Product> result = new CodingHorror(@"
       SELECT TOP 3
       *, newid() as sortorder
       FROM products
       ORDER by sortorder
    ).ExecuteTypedList<Product>();