0

What is the best way to retrieve a "X" number of random records using Entity Framework (EF5 if it's relevant). The value of "X" will be set based on where this will be used.

Is there a method for doing this built into EF or is best to pull down a result set and then use a C# random number function to pull the records. Or is there a method that I'm not thinking of?

On the off chance that it's relevant I have a table that stores images that I use for different usages (there is a FK to an image type table). The images that I use in my carousel on the homepage is what I'm wanting to add some variety to...consequently how "random" it is doesn't matter to me much. I'm just trying to get away from the same six or so pictures always being displayed. (Also, I'm not really interested in debating/discussing storing images in a table vs local storage.)

The solution needs to be one using EF via a LINQ statement. If this isn't directly possibly I may end up doing something SIMILAR to what @cmd has recommended in the comments. This would most likely be a matter of retrieving a record count...testing the PK to make sure the resulting object wasn't null and building a LIST of the X number of object's PKs to pass to front end. The carousel lazy loads the images so I don't actually need the image when I'm building the list that will be used by the carousel.

Jared
  • 5,840
  • 5
  • 49
  • 83
  • How random should it be? Any of [these](http://stackoverflow.com/search?q=select+random+rows+sql) good? – Oded Apr 12 '13 at 21:06
  • I would get a count of the table; generate a random number from 1 to the count, then do the select with rownum=generated_number (if on oracle) – cmd Apr 12 '13 at 21:08
  • @Oded EF doesn't use SQL directly and most of those appear to be related to SQL not entity framework. – Jared Apr 12 '13 at 21:13
  • @cmd I'm in the process of building this table and will most likely be using a GUID for the PK. Also, on any DB that I've use...to my knowledge while a sequential PK is generally something that matches the record count you aren't guaranteed this. This might be an issue if the random number you received was 1 yet the row with the PK of 1 was deleted. – Jared Apr 12 '13 at 21:16
  • @Oded It doesn't need to be very random at all. Just trying to keep the homepage image selection from getting stagnant. – Jared Apr 12 '13 at 21:22
  • possible duplicate of [Linq to Entities, random order](http://stackoverflow.com/questions/654906/linq-to-entities-random-order) – Gert Arnold Apr 13 '13 at 18:51
  • @Jared I was not suggesting using the pk as the random index. Some databases like oracle have a rownum that is a number assigned to the result of the query in the database. – cmd Apr 15 '13 at 15:29
  • http://docs.oracle.com/cd/B14117_01/server.101/b10759/pseudocolumns008.htm – cmd Apr 15 '13 at 15:38

1 Answers1

0

Can you just add an ORDER BY RAND() clause to your query?

See this related question: MySQL: Alternatives to ORDER BY RAND()

Community
  • 1
  • 1
Brandon
  • 38,310
  • 8
  • 82
  • 87
  • Entity Framework 99% (in my usages at least) doesn't use SQL **AT ALL** you work with the objects directly and the framework determines the sql to run. – Jared Apr 12 '13 at 21:11