3

I want to query a random row from a table per Entity Framework.

The best solution I can think of in the moment is getting the total count from the table, then making per C# a function to get a random number in the range of the count and then query this random row number.

My problem is that I can't figure out how to directly query a certain row number per Entity Framework. I want to prevent that I need to query the whole table and then select the row number from there.

Or do I have here a general misunderstanding and there is a much simpler way?

Sven Bieder
  • 5,515
  • 2
  • 21
  • 27
  • 1
    Maybe this link will help you I read this a week ago: [http://stackoverflow.com/questions/11494792/getting-3-random-records-from-a-table][1] [1]: http://stackoverflow.com/questions/11494792/getting-3-random-records-from-a-table – CMinor Sep 27 '13 at 12:54
  • @CMinor, that is very inefficient method, instead running Count results in smaller overhead. – Akash Kava Sep 27 '13 at 12:58

2 Answers2

6
// DO NOT USE THIS FOR MORE THEN 100 ROWS
var randomRecord = foos.OrderBy( x=> SqlFunctions.Rand() ).FirstOrDefault();

But this method is less efficient then,

// USE THIS FOR MORE THEN 100 ROWS
var random = Math.Random(foos.Count());

var randomRecord = foos.OrderBy( x=> x.id ).Skip( random ).FirstOrDefault();

For database, querying count is much less overhead then actually performing SORT over RANDOM for thousands of records. As RANDOM is certainly not indexed, so it will take very long to sort. So avoid using first method, use 2nd method that is the best.

Akash Kava
  • 39,066
  • 20
  • 121
  • 167
  • Curious as to how this works, MSDN doesn't give much information on it. Does SqlFunctions.Rand determine the number of rows in a table and return a random number based on this? Or does it work some other way? – BenM Sep 27 '13 at 13:19
  • @BenM, it is just Math.Random inside SQL's own version of Random, you can look up for T-SQL equivalent of RAND, which is less efficient then previous answer. – Akash Kava Sep 27 '13 at 13:21
  • @BenM Every row will get a random float between 0 and 1. So if you order by that you will have a randomized order. – Giannis Paraskevopoulos Sep 27 '13 at 13:22
  • @AkashKava What i am curious about is that i created a test project and put the following `foreach (var t in ve.Table_1.OrderBy(x=>System.Data.Objects.SqlClient.SqlFunctions.Rand())) Console.WriteLine(v.HTMLText);`. Each time i ran it it gave me the same order. Though your code seems it should work. – Giannis Paraskevopoulos Sep 27 '13 at 13:24
  • @AkashKava I'm not the greatest when it comes to SQL. Is that still timely effective when I have a table with a couple 100k rows? – Sven Bieder Sep 27 '13 at 13:24
  • @Akash I've looked that up but I don't understand how it works. It says it returns a random floating point number between 0 and 1. How is this number linked to the number of rows in a particular database so that it can recall a row? – BenM Sep 27 '13 at 13:24
  • @SvenBieder, for more than 100K, certainly using OrderBy with Random is BAD BAD idea, SQL server don't need to do anything for COUNT, because for query less COUNT, it fetches count from Index statistics. – Akash Kava Sep 27 '13 at 13:26
  • @jyparask, theoretically it should work, looks like you need to SEED it with some number, don't know if SQL is doing right. – Akash Kava Sep 27 '13 at 13:27
  • I have tested the second (and advised) solution. It works out just fine. Thank you. – Sven Bieder Sep 27 '13 at 13:44
3

This will get you the (x+1)th row. If x is 0 based, then if x==0 then it will get the 1st row.

Take(n) will dictate the query to take n rows. Skip(n) will dictate the query to skip the first n rows.

Thanks to AkashKava it is clear that in EF you have to use OrderBy before applying Skip.

Since we don't care of a particular order then we can sorted like the following.

Table.OrderBy(x=>x.id).Skip(1).Take(1)

or as also pointed out by AkashKava you could use FirstOrDeafault()

Table.OrderBy(x=>x.id).Skip(1).FirstOrDeafault()
Giannis Paraskevopoulos
  • 18,261
  • 1
  • 49
  • 69
  • I don't think this is what @Sven needs. He wants to be able to grab a random row from the DB without the need to query it for the number of rows first. – BenM Sep 27 '13 at 12:56
  • You have to use OrderBy before using Skip and Take. Also you don't need, Take, you can simply do Skip(x).FirstOrDefault() – Akash Kava Sep 27 '13 at 12:56
  • @AkashKava You don't have to OrderBy() first as he needs a random number no reason to have a static resource. You are right, i could use the FirstOrDefault(). – Giannis Paraskevopoulos Sep 27 '13 at 12:58
  • @jyparask, Read the documentation on MSDN, Skip will not work without OrderBy, don't spread your half knowledge. – Akash Kava Sep 27 '13 at 12:59
  • @BenM As i understand this `My problem is that I can't figure out how to directly query a certain row number per Entity Framework. I want to prevent that I need to query the whole table and then select the row number from there.` Means that he doesn't want to fetch all data in order to select a single row. He has stated that he already has a function to produce a random number. – Giannis Paraskevopoulos Sep 27 '13 at 13:00
  • @AkashKava To be honest i haven't tested it in EF. I will and let you know. – Giannis Paraskevopoulos Sep 27 '13 at 13:02
  • @jyparask that is correct. Up to the point of having the random number I have everything. I just can't figure out how to query a direct row number with Entity Framework. – Sven Bieder Sep 27 '13 at 13:03
  • @AkashKava There you go. I have tested it in EF and it was as you mentioned. I edited my answer and thanks for pointing me to the right direction. – Giannis Paraskevopoulos Sep 27 '13 at 13:08
  • @jyparask, you will have to use primary key for order by, x=>1 will not result in proper randomizing, if you order by primary key and keep n for some random, that will help you get better results. – Akash Kava Sep 27 '13 at 13:10
  • @AkashKava You are a tough one, aren't you...:) – Giannis Paraskevopoulos Sep 27 '13 at 13:12
  • Here is one more :) , http://stackoverflow.com/questions/11494792/getting-3-random-records-from-a-table/19051721#19051721 – Akash Kava Sep 27 '13 at 13:15