Randomize entity framework query results

Question

Good afternoon,

I have a listview filled using linqdatasource + entity framework iqueryable query.

The query uses a take (top on t-sql) like this:

context.Categories().OrderBy(c=>c.Name).Take(20);

So it brings me the 20 records I want ordered by name.

Now I want to show those 20 records on a random order. Whats the best approach acomplish this?

score 3 · Answer 1 · edited May 23 '17 at 12:26

3

I believe the answer in this post is what you need:

Linq to Entities, random order

EDIT:

Get your top 20 records first. Then with the top 20 items you've already fetched, randomize them all in C#, not involving the database at all:

var yourRecords = context.Categories().OrderBy(c=>c.Name).Take(20); // I believe .Take() triggers the actual database call
yourRecords = yourRecords.OrderBy(a => Guid.NewGuid()); // then randomize the items now that they are in C# memory

edited May 23 '17 at 12:26

Community

1
1

answered Jul 12 '11 at 04:13

ElonU Webdev

2,451
14
15

Hi, thanks for your answer but is not what I need, because it would retrieve me 20 random records from whole data base table and I want to get 20 top records from data base and then show that 20 records on random order – Juan Jimenez Jul 12 '11 at 14:45
that's a good and logical answer, it just leaves my linqdatasource out of the scenario. Do you think it can be done on DB?, like if you make this on TSQL select * from (select top 20 * from Categories order by Name) as Subquery order by NewGuid – Juan Jimenez Jul 12 '11 at 22:45
Yep, that's definitely possible, too. ORDER BY NEWID() is the T-SQL for it: http://www.codekeep.net/snippets/1687fcea-5f5b-4c6d-b9d3-bd049667d2e1.aspx I assume you could wrap your original query, and then order by newid() in your outer query. – ElonU Webdev Jul 13 '11 at 03:25
Hi (sorry for the delay), I've solved it (linq to entities) using an extension method, please see my answer and thank you – Juan Jimenez Sep 12 '11 at 03:32

Juan Jimenez · Accepted Answer · 2011-09-14T19:02:00.030

2

this turned out to be very simple using extension methods, ordering by name first, then calling Take (top on T-sql) and randomizing later

        context.Categories().OrderByName().Take(20).OrderByRandom();


        public static IQueryable<Category> OrderByName(this IQueryable<Category> query)
        {
                return from c in query
                        orderby c.Name
                        select c;
        }



        public static IQueryable<T> OrderByRandom<T>(this IQueryable<T> query)
        {
                return (from q in query
                        orderby Guid.NewGuid()
                        select q);
        }

edited Sep 14 '11 at 19:02

answered Sep 12 '11 at 03:31

Juan Jimenez

5,812
6
28
37

1

this may work using pure lambda expression without extension methods but I haven't tested it – Juan Jimenez Sep 12 '11 at 03:32
1

Cool, glad you got it working. Are the try/catches needed? If all they do is throw the exception, isn't that the default behavior without them? – ElonU Webdev Sep 14 '11 at 18:08

Randomize entity framework query results

2 Answers2