Select N Random Records from SQLite C#

Question

I want to select 20 Random records from a SQLite table of 100 records.

Here is my code and I'm getting the following error. $exception {"Order By does not support: x => NewGuid()"} System.NotSupportedException

int tempRun = 10;
        var dbPath = Path.Combine("OPS.db");
        using (var db = new SQLite.SQLiteConnection(dbPath))
        {
            var rec = db.Table<CoversData>().Where(p => p.homeR >= tempRun).Take(15);

            var randomrec = db.Table<CoversData>().OrderBy(x => Guid.NewGuid()).Take(15);
            // error {"Order By does not support: x => NewGuid()"}  System.NotSupportedException

            foreach (CoversData cd in rec)
            {
                ResultsListBox.Items.Add(cd.Id.ToString() + "  " + cd.GameDate + "  " + cd.HometeamName + "  " + cd.homeR.ToString());
            }
        }

The answer should be using the Linq format not SQL Select statement.

db is an SQLiteConnection - how would you order an instance of a connection? — PaulF, Nov 28 '18 at 16:13
Did you mean _"var randomrec = db.Table().OrderBy(x => Guid.NewGuid()).Take(15);"_ — PaulF, Nov 28 '18 at 16:20
Possible duplicate of [SQLite - ORDER BY RAND()](https://stackoverflow.com/questions/1253561/sqlite-order-by-rand) — Ňɏssa Pøngjǣrdenlarp, Nov 28 '18 at 16:52
I think the exception maybe due to ordering by NewGuid not being supported at the table query level - creating a new Guid instance is done at the .Net application level, it is not a SQLite function. You could try reading all of the table data & then ordering the results at the application level. Something like _"var randomrec = db.Table().ToList().OrderBy(x => Guid.NewGuid()).Take(15);"_ or _"var randomrec = db.Table().Where(x => true).OrderBy(x => Guid.NewGuid()).Take(15);"_ — PaulF, Nov 28 '18 at 17:35
Thanks PaulF. The first solution example was what I was looking for. It works. — Trey Balut, Nov 28 '18 at 17:37
I have posted my comment as an answer - if you accept it then it will help other users with a similar problem identify an answered question. — PaulF, Nov 28 '18 at 17:45

PaulF · Accepted Answer · 2018-11-28T17:49:18.477

The Table<T>() method of the SQLiteConnection class returns a TableQuery<T>; - see http://www.rbdocumentation.com/html/8594fc64-ce81-faa6-1472-25dcdb59a1ce.htm.

The NewGuid not supported exception is due to the OrderBy being treated as an SQLite query command - creating a new Guid instance is done at the .Net application level, it is not a SQLite function.

As you only have 100 records in your table, you could try reading all of the table data & then ordering the results at the application level. Something like :

 var randomrec = db.Table<CoversData>().ToList().OrderBy(x => Guid.NewGuid()).Take(15);

or

 var randomrec = db.Table<CoversData>().Where(x => true).OrderBy(x => Guid.NewGuid()).Take(15);

Select N Random Records from SQLite C#

1 Answers1