Simpledata random ordering

Question

A SimpleData issue

I have two tables: Company and products. When I query and get products of all companies meeting a certain criteria, I want to have the results randomly ordered each time they are returned. How do I do that?

Also,

I would love, (If you can help with this) to have a max number of products per company. So

Database.Default.Products.All().Where(Database.Default.Products.Companies.Status=3)

Will return all the products, but i only want a max of 3 or each.

Even in raw SQL, getting a *random* sample of rows is a pain, at least to do efficiently. This is a niche scenario; I would personally be quite surprised if "get n random rows" was built-in for most tools. Does SimplData allow you to execute raw SQL? You *might* have to drop to that... — Marc Gravell, Jul 10 '12 at 12:03

score 3 · Answer 1 · answered Jul 10 '12 at 16:45

There is no built-in support for doing this, because the ways it would be implemented in the different SQL and NoSQL databases that Simple.Data supports are too varied.

Your best bet is to implement the search as a stored procedure, which you can call from Simple.Data as if it were a method on the database object.

Assuming you're using SQL Server, that would be

CREATE PROCEDURE RandomProductSearch(@Status int, @Take int) 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@Take) Products.*
    FROM Products
    JOIN Companies ON Products.CompanyId = Companies.CompanyId
    WHERE Companies.Status = @Status
    ORDER BY NEWID()
END

Which you can then call from Simple.Data like this:

var products = Database.Default.RandomProductSearch(3,10);

Hope that helps.

Simpledata random ordering

1 Answers1