Returning Random Products Using Linq To SQL
Today at work, I was given a requirement to randomize our featured products on the home page. Basically every time you reload the page, you get different set of products. The maximum number of random products you could display on home page must not be more than 20. I could have done the implementation in C# by bringing more products than I need and randomly picking up 20 from the list. Once I implemented the solution, my results were truly not randomized. I ended up with same records quite often because the randomizer function did not have enough products to randomize and plus I was bringing more data than I needed. Meaning to randomly pick 20 products, I had to bring 100 products and apply random function on there. After having done that I realized that if I were to do the randomizing on SQL server I truly would get a random behavior. SQL server has a function called NewID that always give you a random generated number. So if I can order my collection by the random generated Id, my top 20 collection of products would always give me random 20 products from products table. Here is the view and a function that returns a random Id.
In the code above, I have a random function which gets the random Id from the view called RandomView. I could have called NEWID from inside the function but SQL server was not allowing me to call the NEWID function from my function so I ended up creating a view and selecting from the view to get the randomId. Now that I have my function created, I can drag the function in the Linq to SQL designer and Order all my products based on this function and take the first 20 products to get my 20 random products that I have to display on the home page. Below is the Linq query that gives 20 random products from the database.
Notice in the above code, I am ordering my products by the randomId and than than taking the first 20 products. The Linq to SQL provider converts that into top 20 products ordering the products by randomId generated by the my function.