Return Random Rows using an IQueryable

Question

I have an ASP.NET page that binds a listview to an IQueryable:

<asp:ListView ID="productList" runat="server" 
    DataKeyNames="ProductID" GroupItemCount="6"
    ItemType="products.Models.Product" SelectMethod="GetProducts">
    <EmptyDataTemplate>
        <div>
            Sorry, no products available
        </div>
    </EmptyDataTemplate>
    <GroupTemplate>
        <ul id="da-thumbs" class="da-thumbs">
        <div id="itemPlaceholderContainer" runat="server" class="text-center">
            <div id="itemPlaceholder" runat="server"></div>
        </div>
        </ul>
    </GroupTemplate>
    <ItemTemplate>
        <li> 
            <img src='/products/Catalog/Images/Thumbs/<%#:Item.ImagePath%>' class="img-responsive"/>
            <div>
                <span class="hidden-xs">Short Description</span>
                <span class="spacer visible-xs"></span>
                <a href="<%#: GetRouteUrl("ProductByNameRoute", new {productName = Item.ProductName}) %>" class="btn btn-success btn-block align-mid">View Details</a>     
                <a href="<%#:Item.ShortURL %>" class="btn btn-success btn-block align-mid"><%#:Item.DownloadText%></a>
            </div>
        </li>
    </ItemTemplate>
    <LayoutTemplate>
        <div class="row">
            <asp:PlaceHolder ID="groupPlaceholderContainer" runat="server">
                <div id="groupPlaceholder" runat="server"></div>
            </asp:PlaceHolder>
        </div>                 
    </LayoutTemplate>
</asp:ListView>
<asp:DataPager ID="it" runat="server" PagedControlID="productList" PageSize="6" class="btn-group pager-buttons pagination pagination-large">
    <Fields>
        <asp:NextPreviousPagerField ShowLastPageButton="False" ShowNextPageButton="False" ButtonType="Button" ButtonCssClass="btn" RenderNonBreakingSpacesBetweenControls="false" />
        <asp:NumericPagerField ButtonType="Button" RenderNonBreakingSpacesBetweenControls="false" NumericButtonCssClass="btn" CurrentPageLabelCssClass="btn disabled" NextPreviousButtonCssClass="btn" />
        <asp:NextPreviousPagerField ShowFirstPageButton="False" ShowPreviousPageButton="False" ButtonType="Button" ButtonCssClass="btn" RenderNonBreakingSpacesBetweenControls="false" />
    </Fields>
</asp:DataPager>

GetProducts() is defined as follows:

public IQueryable<Product> GetProducts(
                        [QueryString("id")] int? categoryId,
                        [RouteData] string categoryName)
{
    var _db = new products.Models.ProductContext();
    IQueryable<Product> query = _db.Products;

    if (categoryId.HasValue && categoryId > 0)
    {
        query = query.Where(p => p.CategoryID == categoryId);
    }

    if (!String.IsNullOrEmpty(categoryName))
    {
        query = query.Where(p =>
                            String.Compare(p.Category.CategoryName,
                            categoryName) == 0);
    }

    var random = new Random();
    query = query.OrderBy(product => random.Next()).Where (p => p.Active == 1);
    return query;
}

The issue is Random() does not work here. When I run my app, I get an error

LINQ to Entities does not recognize the method 'Int32 Next()' method, and this method cannot be translated into a store expression.

Probably one solution is to change it to IEnumerable but then I need IQueryable for paging.

Update: After using Taher's solution, this is the internal query I get:

{SELECT 
    [Project1].[ProductID] AS [ProductID], 
    [Project1].[ProductName] AS [ProductName], 
    [Project1].[Description] AS [Description], 
    [Project1].[ImagePath] AS [ImagePath], 
    [Project1].[DownloadText] AS [DownloadText], 
    [Project1].[DownloadURL] AS [DownloadURL], 
    [Project1].[ShortURL] AS [ShortURL], 
    [Project1].[UnitPrice] AS [UnitPrice], 
    [Project1].[CategoryID] AS [CategoryID], 
    [Project1].[Active] AS [Active], 
    [Project1].[ShortDescription] AS [ShortDescription], 
    [Project1].[Priority] AS [Priority]
    FROM ( SELECT 
        RAND() AS [C1], 
        [Extent1].[ProductID] AS [ProductID], 
        [Extent1].[ProductName] AS [ProductName], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[ImagePath] AS [ImagePath], 
        [Extent1].[DownloadText] AS [DownloadText], 
        [Extent1].[DownloadURL] AS [DownloadURL], 
        [Extent1].[ShortURL] AS [ShortURL], 
        [Extent1].[UnitPrice] AS [UnitPrice], 
        [Extent1].[CategoryID] AS [CategoryID], 
        [Extent1].[Active] AS [Active], 
        [Extent1].[ShortDescription] AS [ShortDescription], 
        [Extent1].[Priority] AS [Priority]
        FROM [dbo].[Products] AS [Extent1]
        WHERE 1 = [Extent1].[Active]
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC}

Update: Here's the modified code using Taher's and JCL's suggestions:

if (!String.IsNullOrEmpty(categoryName))
{
    Session["rand"] = null;
    query = query.Where(p =>
                        String.Compare(p.Category.CategoryName,
                        categoryName) == 0);
}

var seed = 0;

if (Session["rand"] == null)
{
    seed = (int)DateTime.Now.Ticks % 9395713;//a random number
    Session["rand"] = seed;
}

var readSeed = (int)Session["rand"];
query = query
   .OrderBy(product => SqlFunctions.Rand(product.ProductID * readSeed % 577317));
return query;

Answer 1

There is a class of supported functions for EF called SqlFunctions. You can use the Rand function of this class:

 query = query.OrderBy(product => SqlFunctions.Rand()).Where (p => p.Active == 1);

don't forget to add

 using System.Data.Entity.Core.Objects; 

the problem with this approach is from this source:

Repetitive invocations of RAND() in a single query will produce the same value.

I don't think there is any work around for it. The solutions is to use `Newid() in raw SQL commands which I think is not useful for you, Or loading the query data and then shuffle the rows in memory.

I tried this work around and it seems to work:

var seed = (int)DateTime.Now.Ticks % 9395713;//a random number
query = query
   .OrderBy(product =>SqlFunctions.Rand(product.ProductId * seed % 577317))
   .Where (p => p.Active == 1);

this trick will force the sql server to generate a new random value for each row, as the ProductId changes on each row this changes the Rand seed which forces the sql server to generate new random value.

You can explore different values for seed variable to obtain better results.

EDIT
In order to have a seed per category, you can create a cache of seeds in Application or simply creating an static dictionary of (category, seed) pairs, something like this:

in controller if you use MVC or the page class if you use ASP.NET:

static Dictionary<string, int> categorySeeds = new Dictionary<string, int>();

in the action or the method:

int seed = 0;
if(categorySeeds.ContainsKey(categoryName))
{
   seed = categorySeeds[categoryName];
}
else
{
   seed = (int)DateTime.Now.Ticks % 9395713;//a random number
   categorySeeds.Add(categoryName, seed);
}
//rest of the code

Answer 2

I found this master piece in very similar questions How to efficiently retrieve a list of random elements from an IQueryable

users.OrderBy(_ => Guid.NewGuid()).Take(3)

This retrieves the first 3 elements from the users table, while sorting them by a value which is different each time.

Compared to AD.Net's answer.. well you'd require a list of userids generated randomly.. it doesn't suggest a way to do that

Answer 3

Honestly, the best possible way for this would be using a stored procedure in SQL Server to which you pass the random seed as a parameter (and change it on your code whenever you want to shuffle again).

Otherwise, unless you are passing the same seed to your random, paging will just not work (and it seems to be a requirement), since every time the query gets refreshed you'll get a different ordering.

If you can't have all your data in memory (and shuffle on in-memory objects, not on EF), the best solution would be doing it in the SQL Server directly and just have EF read from the result of that stored procedure.

Or, if modifying your entities is reasonable, then have an integer field on your entity and fill this entity with random values (should be fast if done directly via SQL, since EF as far as I know doesn't do batch updates) every time you want to shuffle, and order by that (this could be tricky in a multi-user environment, but can be done, and you shouldn't necessarily care if several different users get the same sorting, should you?).

These would be the only ways to have paging work in a truly pseudo-random/shuffled order on the server.

Again, if the table is not big (and will not be) and you can just download the whole table (without paging) and page to an in-memory collection, then that'd be easy.

Answer 4

Chage your code to this

 var random = new Random();
 var randomNumber=random.Next();
 query = query.OrderBy(product => randomNumber).Where (p => p.Active == 1);