2

I need to get out of how I find a random thing in the database as shown to the audience and at the same time it must be able to show one of time.

Normally I have done like this

cmd1.CommandText = @"SELECT TOP 1 opgaver.id, opgaver.rigtigsvar, opgaver.overskift, opgaver.svar1, 
    opgaver.svar2, opgaver.svar3, opgaveLydefiler.mp3 FROM opgaver INNER JOIN opgaveLydefiler ON opgaver.overskift = opgaveLydefiler.navn ORDER BY newid()";

Tasks and task sounds files are put together such that they, like partnerships / has an inner join together.

I've tried to do like this, but I can not right for it to display only one and the same time make a random of it as I have in the database.

Opgaver opgaver = db.opgavers.FirstOrDefault().Take(1);

EIDT - I have chosen to do like this,

var random = new Random();
    var antalopgaver = db.opgavers.Count();
    var number = random.Next(antalopgaver);

    var randomlySelectedItem = db.opgavers.Skip(number).Take(1).FirstOrDefault();
Jesper Petersen
  • 89
  • 10

3 Answers3

3

Here you go:

var item = db.opgavers.OrderBy(q=>Guid.NewGuid()).FirstOrDefault();
Robert McKee
  • 21,305
  • 1
  • 43
  • 57
  • Cool! Would be helpful if you gave a brief explanation of what `.OrderBy(q => Guid.NewGuid())` does. – Rufus L Apr 17 '15 at 21:59
  • It is the exact equivalent of `ORDER BY newid()` in SQL. It creates a new GUID for each row (which is fairly random), and then sorts by it. It's only good for small to medium sized tables. You can also use a fake UDF, as well as found here: http://stackoverflow.com/questions/648196/random-row-from-linq-to-sql but it will generate the exact same code to run (both on the server). – Robert McKee Apr 17 '15 at 22:15
  • So does `Guid.NewGuid()` generate SQL for a unique ID? – Asad Saeeduddin Apr 17 '15 at 22:22
  • 1
    @Asad Yes, it generates the equivalent of what he had originally; it'll use SQL Server's NewID() function. It does it in a more complicated way, but it executes the exact same way... It'll turn `SELECT * FROM table` into `SELECT TOP(1) * FROM (SELECT newid as c1,* FROM table) ORDER BY c1` basically. It won't use `*`, but you get the idea. – Robert McKee Apr 17 '15 at 22:25
  • Sorry, that should have been `SELECT TOP(1) * FROM (SELECT newid() as c1,* FROM table) ORDER BY c1` – Robert McKee Apr 17 '15 at 22:32
  • That is really good. I wonder if it wouldn't be more performant to select the `FirstOrDefault` item based on the criteria `q => q.Id >= rng*maxId`, where `rng` is a number between 0 and 1, and `maxId` is the maximum ID in the table. The query that's being generated now still involves a full table scan. – Asad Saeeduddin Apr 17 '15 at 22:34
  • Depends. That type of query will return all rows to the client, then do processing there. The query I gave will do a full table scan (maybe, I'd be surprised if SQL Server didn't identify this type of query and optimize it) on the server, and only return 1 row. It's definitely the fastest method if you only have a small number of rows and your SQL server isn't running on the same machine. All other approaches I've found either require pulling all the rows to the client, or involve multiple roundtrips to the SQL Server (Count/random). – Robert McKee Apr 17 '15 at 22:39
  • @RobertMcKee No, that kind of query would just find a single row (it still has the `TOP 1` in the query). – Asad Saeeduddin Apr 17 '15 at 22:40
  • @Asad Ah, I see what you did there. Perhaps. I did a quick check my original query runs in .016 seconds (including instantiating the object, etc) on a 16,000 record table. It's not SLOW even for medium sized tables. Downside to rnd*max is that is only random if all id's are present, otherwise id's that follow a gap will be weighted more heavily. And you should use (rnd*(max+1)) in either case, to weight the final answer the same. – Robert McKee Apr 17 '15 at 22:43
  • @RobertMcKee Yeah, it's a great solution, I definitely don't think it is bad. It just bothers me a little that the time cost grows with table size, even though it should fundamentally be a constant time task. If you get a million or billion items, you start getting into seconds. – Asad Saeeduddin Apr 17 '15 at 22:48
  • @Asad Oh yes. That's why I said it was only really appropriate for small to medium sized tables. If it's a larger table, then the other approaches are much better (rnd/count), or if the SQL Server is located on the same machine. It depends on which is more important to you... You always need to balance between transfer time, round trip latency, and query cost. – Robert McKee Apr 17 '15 at 22:54
  • @RobertMcKee Is it safe in general? I mean for example I use DevExpress XPO's Linq implementation a lot, and I'm not sure if they could handle the Guid expression. – Zoltán Tamási Apr 18 '15 at 09:01
  • @ZoltánTamási I can't comment on the quality of DevExpress's LINQ implementation, as I've never used it. You should be able to implement the fake UDF I posted above if it does not -- but again, since I haven't used it, they may not even support that. – Robert McKee Apr 19 '15 at 21:37
2

Here is an idea in short.

If let's say you are interested in the top 100 records, for example ordered by the date of addition. Then try to generate a random number between 0 and 99 like this:

var random = new Random();
var number = random.Next(100);

Then use this number as offset for your query:

var item = db.opgavers.OrderByDescending(e => e.DateAdded).Skip(number).Take(1).FirstOrDefault();

I advice using FirstOrDefault against First because that way you can handle for example the empty database case, which is sometimes a valid state.

I used Take(1) because I think it is the safest way to ensure that the query will contain the LIMIT clause. Otherwise some LINQ providers might do it else way.

If you can't do such an ordering what I supposed, then as others have pointed out, you could get the number of rows before the query, and use it instead of 100. But that's another query to the database which is sometimes OK, sometimes not so much.

Zoltán Tamási
  • 12,249
  • 8
  • 65
  • 93
  • @ZoltánTamási Okay, but what about when I get over the 99 ?, so am I suppose in and set it to the figures that I have? or how? – Jesper Petersen Apr 17 '15 at 22:15
  • @JesperPetersen Could you clarify that question? – Asad Saeeduddin Apr 17 '15 at 22:41
  • @ZoltánTamási If I use the first (); I get errors do that I can run two tasks through without problems but then after a fault. – Jesper Petersen Apr 17 '15 at 22:47
  • I mean, let also say that I have 500 tasks but I only want one of them being shown by time but also let say about one week that I just 350 tasks more so I would prefer to be deleted having to enter the code and write spoken. @ZoltánTamási – Jesper Petersen Apr 17 '15 at 22:49
  • @ZoltánTamási Great answer, but you can probably remove the `Take` without missing anything. Also, you can precompute the number of items in the table to skip more intelligently. – Asad Saeeduddin Apr 17 '15 at 22:58
  • 1
    @JesperPetersen I ment you could order the items by for example the date of addition, and then do the 100 limit, that's why I wrote that I supposed that top 100 records are in interest. I will clarify it. – Zoltán Tamási Apr 18 '15 at 09:00
0

You can order your records by a random number, and then take the first :

var random = new Random();
var randomlySelectedItem = 
  db.opgavers.Select(o => new { op = o, sort = random.Next(0,10000) })
             .OrderBy(obj => obj.sort)
             .FirstOrDefault();

Replace the hard coded 10,000 by a number that is basically corresponding to the count of your items (does not need to be the exact count)

kall2sollies
  • 1,429
  • 2
  • 17
  • 33
  • What do I do if I get over the 10000?, It will not make it easier for database work that just had to find out how many there are in the database? – Jesper Petersen Apr 17 '15 at 22:30
  • You could count them first... Or you could just do an `random.Next(0,1)` since it returns a double. You don't necessarily have to provide a random range exactly the same count as items in your database. If the range is narrower, it will still work, if bigger, the better. Finally, you could mix solutions together and replace `random.Next(0,10000)` by `Guid.NewGuid()`. – kall2sollies Apr 20 '15 at 07:36