Studying an Idea for extract random rows with LINQ

Question

This is actually my strategy to extract (for example) 5 random products from a list of products:

// extracts all products with Name Example
IList<Product> products = (from Product p in new Products()
                           where p.Name = "Example"
                           select p).ToList();

// I randomly order first 5 products
int upper = 1;
if (products.Count > 1)
{
    Random r = new Random();
    upper = Math.Min(5, products.Count);
    for (int i = 0; i < upper; i++)
    {
        int randInd = r.Next(i, products.Count);
        var temp = products[i];
        products[i] = products[randInd];
        products[randInd] = temp;
    }
}

// I get the first 5
products = products.Take(upper);

I have to say : I'm annoyed about extract every time all records I need with LINQ, order them and get only few.

I think there is a waste of resources of this process, such as take ALL elements with LINQ if I need only 5.

Is there a method to extract with LINQ only records at some position in the table? I mean, if I have a table of 1000 rows, get only 5 rows randomly.

This will be the same, and the use of resources will improve.

A number of approaches are shown here: http://stackoverflow.com/questions/648196/random-row-from-linq-to-sql — Marc Gravell, Dec 07 '11 at 14:39

score 1 · Answer 1 · answered Dec 09 '11 at 23:40

1

Is there a method to extract with LINQ only records at some position in the table?

The System.Linq.Queryable class provides the ElementAt(int) extension method.

answered Dec 09 '11 at 23:40

CedX

3,854
2
36
45

Looks interessant! But it cycle the whole table if there is a where? – markzzz Dec 10 '11 at 12:37
No, it basically does a Skip(x).Take(1), where x is your random number. But you must ensure to NOT call .ToList() as this statement loads the whole table into memory. – CedX Dec 10 '11 at 14:42
Uhm, that's nice! But what's about the element at Y position is not on where clause? It take the next with where clauses=true? I think this could be the faster strategy to get N random values. Just call that ElementAt N time with my values, right? – markzzz Dec 10 '11 at 15:36
Yes: it searches only the elements covered by the query (including the where clause). So, in your case, you should call 5 times the ElementAt method (more queries against the database but much better memory usage: in 99.99%, the performance will be better too). – CedX Dec 13 '11 at 15:09
"more queries against the database" ? LINQ works on my `Products`, which are already "downloaded" in memory... – markzzz Dec 15 '11 at 09:26
I thought you used LINQ to SQL. Remember, if my answer satisfies you, mark it as accepted ;o) – CedX Dec 15 '11 at 15:23

score 0 · Answer 2 · answered Dec 19 '11 at 10:08

I will give you a great concept to extract random 5 records from MSSQL Server by using Linq. First create a Scalar valued function in your database like this

CREATE FUNCTION GetNewId ( ) 
RETURNS uniqueidentifier 
AS 
BEGIN 
    RETURN (SELECT ID FROM RandomView) 
END

Please import this function in your LINQ dbml file.

Now when you are fetching records from database by using linq query at that time you will use

(from p in new ProductsDataContext()
orderby new ProductsDataContext().GetNewId()
select p).Take(5).ToList();

by using this query you get 5 random records on every call...

I think it will help you lots...

Studying an Idea for extract random rows with LINQ

2 Answers2